MySQL Lock机制

<p>INDEX:</p><p>MySQL事务隔离级别</p><p>MVCC</p><p>MySQL Lock类型</p><p>MySQL MDL</p><p>CONTENT:</p><p>1. MySQL事务隔离级别</p><p>Read Uncommit</p><p>RU:</p><p>允许脏读, 也就是说本事务中可能读到其他事务中未提交的修改数据.</p><p>Read Commit</p><p>RC</p><p>只能读到已经提交的数据. Oracle等多数数据库默认都是该级别(不可重复读).</p><p>Repeatable Read</p><p>RR</p><p>可重复读, 在同一个事务内的查询都是事务开始时刻的一致性数据, InnoDB默认的事务隔离级别. 该隔离级别消除了不可重复读, 但是还存在幻想读.</p><p>Serializable</p><p>S</p><p>完全串行化的读, 每次读都需要获取表级别的共享锁, 读写相互组赛.</p><pre class="brush:bash;toolbar:false">transaction-isolation={READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE} 2.MVCC MVCC:Multiversionconcurrencycontrol</pre><p>基于锁的并发控制机制, 悲观机制;</p><p>基于版本的并发控制机制, 乐观机制;</p><p>读不阻塞写, 写也不阻塞读, 等到提交的时候才检验是否有冲突. 由于没有锁, 所以读写不会相互阻塞, 从而大大提升了并发性能.</p><p>3. MySQL Lock类型</p><p>根据锁的类型分:</p><p>共享锁</p><p>排他锁</p><p>意向共享锁</p><p>意向排他锁</p><p>根据锁的粒度分:</p><p>表锁</p><p>行锁</p><p>而在InnoDB存储引擎层实现了行锁(Record Lock), gap lock, next-key lock.</p><p>查看lock的信息</p><pre class="brush:bash;toolbar:false">showvariableslike&#39;tx_isolation&#39;; showengineinnodbstatus\G SELECT r.trx_idwaiting_trx_id,r.trx_querywaiting_query,b.trx_idblocking_trx_id,b.trx_queryblocking_query,b.trx_mysql_thread_idblocking_thread,b.trx_started,b.trx_wait_started FROM information_schema.innodb_lock_waitsw INNERJOINinformation_schema.innodb_trxb ONb.trx_id=w.blocking_trx_id INNERJOINinformation_schema.innodb_trxr ONr.trx_id=w.requesting_trx_id; MDL</pre><p>先熟悉一下MDL_key的state name:</p><p>global read lock</p><p>commit lock</p><p>schema metadata lock</p><p>table metadata lock</p><p>stored function metadata lock</p><p>stored procedure metadata lock</p><p>trigger metadata lock</p><p>event metadata lock</p><p>从MySQL对锁持有的时间分:</p><p>Statement</p><p>语句执行期间获取的lock, 语句执行结束时自动释放的.</p><p>Transaction</p><p>在一个事务中, 此事务所设计到的所有表均要获取MDL, 一直到事务commit/rollback释放.</p><p>两阶段加锁协议:</p><p>事务执行分为两个阶段,</p><p>第一个阶段获得封锁;</p><p>第二个阶段释放封锁;</p><p>Explicit</p><p>需要MDL_context::release_lock(), 譬如: lock table; flush tables with read lock;</p><p>从MySQL的锁粒度分:</p><p>scope锁(MDL_scoped_lock(const MDL_key *key_arg))</p><p>名称<span ></span>意义</p><p>IS<span ></span>意向共享锁</p><p>IX<span ></span>意向排他锁</p><p>S<span ></span>共享锁</p><p>X<span ></span>排他锁</p><table><thead><tr class="firstRow"><th>名称</th><th>意义</th></tr></thead><tbody><tr><td>IS</td><td>意向共享锁</td></tr><tr><td>IX</td><td>意向排他锁</td></tr><tr><td>S</td><td>共享锁</td></tr><tr><td>X</td><td>排他锁</td></tr></tbody></table><p>优先级: X -&gt;S -&gt;IX (IS和其他类型的锁都是兼容的)</p><p>兼容性</p><p> individual locks转换为scoped lock:</p><pre class="brush:bash;toolbar:false">----------------+-------------+ Typeofrequest|Correspond.| forindiv.lock|scopedlock| ----------------+-------------+ S,SH,SR,SW|IS| SNW,SNRW,X|IX| SNW,SNRW-&gt;X|IX(*)| request与granted之间的兼容矩阵: |Typeofactive| Request|scopedlock| type|IS(**)IXSX| ---------+------------------+ IS|++++| IX|++--| S|+-+-| X|+---|</pre><p> request与waiting兼容矩阵:</p><pre class="brush:bash;toolbar:false">|Pending| Request|scopedlock| type|IS(**)IXSX| ---------+-----------------+ IS|++++| IX|++--| S|+++-| X|++++| Here:&quot;+&quot;--meansthatrequestcanbesatisfied &quot;-&quot;--meansthatrequestcan&#39;tbesatisfiedandshouldwait (*)Sinceforupgradablelockswealwaystakeintentionexclusivescoped lockatthesametimewhenobtainingthesharedlock,thereisno needtoobtainsuchlockduringtheupgradeitself. (**)Sinceintentionsharedscopedlocksarecompatiblewithallother typeoflockswedon&#39;tevenhaveanyaccountingforthem. object锁(MDL_object_lock(constMDL_key*key_arg))</pre><table><thead><tr class="firstRow"><th>名称</th><th>意义</th></tr></thead><tbody><tr><td>MDL_INTENTION_EXCLUSIVE</td><td>仅适用scoped locks, 可升级IX-&gt;X, 和其他IX兼容; 但是和scoped S and X不兼容.</td></tr><tr><td>MDL_SHARED</td><td>共享锁. 访问字典对象(table metadata), 不能访问数据</td></tr><tr><td>MDL_SHARED_HIGH_PRIO</td><td>高优先级shared mdl, 不像shared lock那样, 在申请时会忽略X lock的等待; 用于访问metadata(no date), 填充INDORMATION_SCHEMA表. 兼容SNRW</td></tr><tr><td>MDL_SHARED_READ</td><td>共享读锁. 能读table metadata, 也可读表数据(如select), 譬如: SELECTs, subqueries, and LOCK TABLE ... READ</td></tr><tr><td>MDL_SHARED_WRITE</td><td>共享写锁. 读取metadata, modify/read table data, (INSERT, UPDATE, DELETE) statements,SELECT ... FOR UPDATE. but not LOCK TABLE ... WRITE or DDL)</td></tr><tr><td>MDL_SHARED_UPGRADABLE</td><td>alter table第一阶段获取. 读取metadata, modify/read table data(升级到SNW, 获取row_level lock), 可升级SHU-&gt;SNW/X.</td></tr><tr><td>MDL_SHARED_NO_WRITE</td><td>alter table第一阶段获取; 可以并发read table, 但不可以update; 可升级SNW-&gt;X.</td></tr><tr><td>MDL_SHARED_NO_READ_WRITE</td><td>读取表metadata, modify/read table data. 但是阻止对表的读写操作. 用于LOCK TABLES WRITE statement. 可升级SNRW-&gt;X, 除S/SH外, 不兼容任何mdl.</td></tr><tr><td>MDL_EXCLUSIVE</td><td>最高级别MDL锁, 通常用于Drop/Create/Rename等操作. 也用于其它对象创建或者删除时, 譬如: create trigger等</td></tr></tbody></table><p>名称<span ></span>意义</p><p>MDL_INTENTION_EXCLUSIVE<span ></span>仅适用scoped locks, 可升级IX-&gt;X, 和其他IX兼容; 但是和scoped S and X不兼容.</p><p>MDL_SHARED<span ></span>共享锁. 访问字典对象(table metadata), 不能访问数据</p><p>MDL_SHARED_HIGH_PRIO<span ></span>高优先级shared mdl, 不像shared lock那样, 在申请时会忽略X lock的等待; 用于访问metadata(no date), 填充INDORMATION_SCHEMA表. 兼容SNRW</p><p>MDL_SHARED_READ<span ></span>共享读锁. 能读table metadata, 也可读表数据(如select), 譬如: SELECTs, subqueries, and LOCK TABLE ... READ</p><p>MDL_SHARED_WRITE<span ></span>共享写锁. 读取metadata, modify/read table data, (INSERT, UPDATE, DELETE) statements,SELECT ... FOR UPDATE. but not LOCK TABLE ... WRITE or DDL)</p><p>MDL_SHARED_UPGRADABLE<span ></span>alter table第一阶段获取. 读取metadata, modify/read table data(升级到SNW, 获取row_level lock), 可升级SHU-&gt;SNW/X.</p><p>MDL_SHARED_NO_WRITE<span ></span>alter table第一阶段获取; 可以并发read table, 但不可以update; 可升级SNW-&gt;X.</p><p>MDL_SHARED_NO_READ_WRITE<span ></span>读取表metadata, modify/read table data. 但是阻止对表的读写操作. 用于LOCK TABLES WRITE statement. 可升级SNRW-&gt;X, 除S/SH外, 不兼容任何mdl.</p><p>MDL_EXCLUSIVE<span ></span>最高级别MDL锁, 通常用于Drop/Create/Rename等操作. 也用于其它对象创建或者删除时, 譬如: create trigger等</p><p>兼容性</p><p> request与granted兼容矩阵:(0为不可能情况)</p><pre class="brush:bash;toolbar:false">Request|Grantedrequestsforlock| type|SSHSRSWSNWSNRWX| ----------+------------------------------+ S|++++++-| SH|++++++-| SR|+++++--| SW|++++---| SNW|+++----| SNRW|++-----| X|-------| SNW-&gt;X|---0000| SNRW-&gt;X|--00000|</pre><p> request与waiting兼容矩阵:</p><pre class="brush:bash;toolbar:false">Request|Pendingrequestsforlock| type|SSHSRSWSNWSNRWX| ----------+-----------------------------+ S|++++++-| SH|+++++++| SR|+++++--| SW|++++---| SNW|++++++-| SNRW|++++++-| X|+++++++| SNW-&gt;X|+++++++| SNRW-&gt;X|+++++++| Here:&quot;+&quot;--meansthatrequestcanbesatisfied &quot;-&quot;--meansthatrequestcan&#39;tbesatisfiedandshouldwait &quot;0&quot;--meansimpossiblesituationwhichwilltriggerassert @noteIncasesthencurrentcontextalreadyhas&quot;stronger&quot;type oflockontheobjectitwillbeautomaticallygranted thankstousageoftheMDL_context::find_ticket()method.</pre><p>数据结构</p><p>和锁相关的数据结构:</p><p>MDL_context: 字典锁上下文. 包含一个事物所有的字典锁请求.</p><p>MDL_request: 字典锁请求. 包含对某个对象的某种锁的请求.</p><p>MDL_ticket: 字典锁排队. MDL_request就是为了获取一个ticket.</p><p>MDL_lock: 锁资源. 一个对象全局唯一, 可以允许多个可以并发的事物同时获得.</p><p>源码文件主要是sql/mdl.cc</p><p>加锁/解锁分析</p><p>SELECT加锁调用:</p><pre class="brush:bash;toolbar:false">mysql_execute_command--&gt;execute_sqlcom_select--&gt;open_normal_and_derived_tables--&gt; open_tables--&gt;open_and_process_table--&gt;open_table--&gt;MDL_context::acquire_lock</pre><p>看看acquire lock的流程:</p><p>检查session是否持有该object的share lock? 如果是, 则grant.</p><pre class="brush:bash;toolbar:false">if((ticket=find_ticket(mdl_request,&amp;found_duration))) { mdl_request-&gt;ticket=ticket; }</pre><p>如果找不到, 在MDL_map中找到MDL_lock, grant lock.</p><pre class="brush:bash;toolbar:false">if(!(lock=mdl_locks.find_or_insert(key))) { } ticket-&gt;m_lock=lock; if(lock-&gt;can_grant_lock(mdl_request-&gt;type,this)) { lock-&gt;m_granted.add_ticket(ticket); mysql_prlock_unlock(&amp;lock-&gt;m_rwlock); m_tickets[mdl_request-&gt;duration].push_front(ticket); mdl_request-&gt;ticket=ticket; }</pre><p>如果立即加锁失败, 则将ticket加入到lock的waiting队列中</p><pre class="brush:bash;toolbar:false">lock=ticket-&gt;m_lock; lock-&gt;m_waiting.add_ticket(ticket);</pre><p>死锁检测</p><pre class="brush:bash;toolbar:false">find_deadlock(); voidMDL_context::find_deadlock() { while(1) { if(!visit_subgraph(&amp;dvisitor)) { breadk; } victim=dvisitor.get_victim(); (void)victim-&gt;m_wait.set_status(MDL_wait::VICTIM); victim-&gt;unlock_deadlock_victim(); if(victim==this) break; //发现deadlock,函数退出 } }</pre><p>SELECT解锁调用:</p><pre class="brush:bash;toolbar:false">mysql_execute_command--&gt;MDL_context::release_transactional_locks--&gt; MDL_context::release_locks_stored_before--&gt;MDL_context::release_lock</pre><p>看看release lock的流程:</p><p>通过ticket信息找到MDL_lock</p><pre class="brush:bash;toolbar:false">DBUG_ASSERT(this==ticket-&gt;get_ctx()); mysql_mutex_assert_not_owner(&amp;LOCK_open); 将ticket从MDL_lock的grantedlist中remove lock-&gt;remove_ticket(&amp;MDL_lock::m_granted,ticket); voidMDL_lock::Ticket_list::remove_ticket(MDL_ticket*ticket) { m_list.remove(ticket); }</pre><p>检查MDL_lock的waiting list和granted list是否为空? 如果是, 则将MDL_lock从MDL_map中remove</p><pre class="brush:bash;toolbar:false">voidMDL_map::remove(MDL_lock*lock){ mysql_mutex_lock(&amp;m_mutex); my_hash_delete(&amp;m_locks,(uchar*)lock); if(ref_usage==ref_release) { MDL_lock::destroy(lock); } } voidMDL_context::destroy() { DBUG_ASSERT(m_tickets[MDL_STATEMENT].is_empty()&amp;&amp; m_tickets[MDL_TRANSACTION].is_empty()&amp;&amp; m_tickets[MDL_EXPLICIT].is_empty()); mysql_prlock_destroy(&amp;m_LOCK_waiting_for); }</pre><p>如果非空, 遍历waiting list, 尝试加锁, 并从waiting list中remove, 加入到granted list中, 唤醒对应session</p><p>SELECT与DDL(alter table)问题</p><p>经常会看到大SQL查询堵住alter table, alter table又会堵住后面的一堆SQL ......</p><p>看到一堆wait for table metadata lock, 醉了T.T</p><p>下来分析根本原因:</p><p>先看看alter的MDL流程:</p><p>opening tables阶段</p><p>MDL_INTENTION_EXCLUSIVE</p><p>MDL_SHARED_UPGRADABLE, 可升级到MDL_SHARED_NO_WRITE</p><p>copy data阶段</p><p>copy data to tmp table...</p><p>del原表, 将tmp表rename回去</p><p>MDL_SHARED_NO_WRITE升级到MDL_EXCLUSIVE</p><p>事务commit, release MDL</p><pre class="brush:bash;toolbar:false">releaseMDL_INTENTION_EXCLUSIVE releaseMDL_EXCLUSIVE</pre><p>select是需要获取表的MDL_SHARED_READ</p><p>在alter table中的rename之前表被持有MDL_SHARED_NO_WRITE, 此时申请MDL_SHARED_READ是可以成功的, 兼容.</p><p>这里关键的步骤是rename环节, SNW-&gt;X; 如果此时表在被select(即被持有MDL_SHARED_READ); 从兼容矩阵可以看出SNW升级到X是不能与SR兼容的, 所以这个alter table的rename环节就必须等待select结束!</p><p>几种典型的加锁/释放锁的流程</p><pre class="brush:bash;toolbar:false">selectstatement</pre><p>opening tables阶段, 加共享读锁</p><p>加MDL_INTENTION_EXCLUSIVE锁</p><p>加MDL_SHARED_READ锁</p><p>commit阶段, 释放MDL锁</p><p>释放MDL_INTENTION_EXCLUSIVE锁</p><p>释放MDL_SHARED_READ锁</p><p>DML statement</p><p>opening tables阶段, 加共享写锁</p><p>加MDL_INTENTION_EXCLUSIVE锁</p><p>加MDL_SHARED_WRITE锁</p><p>commit阶段, 释放MDL锁</p><p>释放MDL_INTENTION_EXCLUSIVE锁</p><p>释放MDL_SHARED_WRITE锁</p><p>DDL(alter table) statement</p><p>opening tables阶段, 加共享写锁</p><p>加MDL_INTENTION_EXCLUSIVE锁</p><p>加MDL_SHARED_UPGRADABLE锁, 升级到MDL_SHARED_NO_WRITE</p><p>读取数据, copy data, 流程:</p><p>create {tmp} table, 定义tmp表结构</p><pre class="brush:bash;toolbar:false">copydatafromoldtabletotmp(new)table</pre><p>tmp表替换老表(rename)</p><p>将MDL_SHARED_NO_WRITE升级到MDL_EXCLUSIVE</p><p>commit阶段, 释放MDL锁</p><p>释放MDL_INTENTION_EXCLUSIVE锁</p><p>释放MDL_EXCLUSIVE锁</p><pre class="brush:bash;toolbar:false">Lovetruthbutpardonerrors</pre>
RangeTime:0.005691s
RangeMem:219.58 KB
返回顶部 留言