MySQL Lock机制

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