最近碰到很多鎖問題，所以解決了后，仔細再去閱讀了關于鎖的書籍，整理如下：
1，鎖的種類
Innodb存儲引擎實現了如下2種標準的行級鎖：
?? 共享鎖(S lock)，允許事務讀取一行數據。
?? 排它鎖(X lock)，允許事務刪除或者更新一行數據。
?
當一個事務獲取了行r的共享鎖，那么另外一個事務也可以立即獲取行r的共享鎖，因為讀取并未改變行r的數據，這種情況就是鎖兼容。但是如果有事務想獲得行r的排它鎖，則它必須等待事務釋放行r上的共享鎖—這種情況就是鎖不兼容，二者兼容性如下表格所示：
|
排它鎖和共享鎖的兼容性 |
||
|
? |
X?排它鎖 |
S?共享鎖 |
|
X?排它鎖 |
沖突 |
沖突 |
|
S?共享鎖 |
沖突 |
兼容 |
?
2，鎖的擴展
Innodb存儲引擎支持多粒度鎖定，這種鎖定允許在行級別上的鎖和表級別上的鎖同時存在。為了支持在不同粒度上進行加鎖操作，InnoDB存儲引擎支持一種額外的鎖方式，就是意向鎖。意向鎖是表級別的鎖，其設計目的主要是為了在一個事務中揭示下一行將被請求的鎖的類型。它也分為兩種：
?? 意向共享鎖（IS Lock），事務想要獲得一個表中某幾行的共享鎖。
?? 意向排它鎖（IX Lock），事務想要獲得一個表中某幾行的排它鎖。
?
由于InnoDB支持的是行級別鎖，所以意向鎖其實不大會阻塞除了全表scan以下的任何請求。共享鎖、排它鎖、意向共享鎖、意向排它鎖相互之前都是有兼容/互斥關系的，可以用一個兼容性矩陣表示(y表示兼容，n表示不兼容)，如下所示:
|
? |
X?排它鎖 |
S?共享鎖 |
IX?意向排它鎖 |
IS?意向共享鎖 |
|
X?排它鎖 |
沖突 |
沖突 |
沖突 |
沖突 |
|
S?共享鎖 |
沖突 |
兼容 |
沖突 |
兼容 |
|
IX?意向排它鎖 |
沖突 |
沖突 |
兼容 |
兼容 |
|
IS?意向共享鎖 |
沖突 |
兼容 |
兼容 |
兼容 |
?????????解析：X和S的相互兼容關系step1描述過了，IX和IS的相互關系全部是兼容，這也很好理解，因為它們都只是“有意”，還處于YY階段，沒有真干，所以是可以兼容的；
剩下的就是X和IX，X和IS, S和IX，?S和IS的關系了，我們可以由X和S的關系推導出這四組關系。
簡單的說：X和IX的=X和X的關系。為什么呢？因為事務在獲取IX鎖后，接下來就有權利獲取X鎖。如果X和IX兼容的話，就會出現兩個事務都獲取了X鎖的情況，這與我們已知的X與X互斥是矛盾的，所以X與IX只能是互斥關系。其余的三組關系同理，可用同樣的方式推導出來。
?
?
3，模擬鎖場景
在InnoDB Plugin之前，我們只能通過SHOW FULL PROCESSLIS和SHOW ENGINE INNODB STATUS來查看當前的數據庫請求，然后再判斷事務中鎖的情況。新版本的InnoDB Plugin中，在information_schema庫中添加了3張表，INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS。通過這3個表，可以更簡單的監控當前的事務并且分析可能存在的鎖問題。如果數據庫正常運行，這3個表都是空的，沒有任何記錄。
3.1，開啟事務t1、t2，模擬鎖
開啟2個session窗口，并且開啟2個事務t1和t2。
在第一個窗口開啟事務t1執行一個鎖定操作，如下t1事務窗口界面：
mysql>?set?autocommit?=0; Query?OK,?0?rows?affected?(0.00?sec) mysql>?begin; Query?OK,?0?rows?affected?(0.00?sec) ??開始執行鎖定操作 mysql>?select?*?from?test.t1?where?a<p style="word-wrap: break-word; margin-top: 5px; margin-bottom: 5px; padding-top: 0px; padding-bottom: 0px; font-family: 'sans serif', tahoma, verdana, helvetica; line-height: 18px;"><span style="word-wrap: break-word; font-size: 14px;"></span><br></p><p style="word-wrap: break-word; margin-top: 5px; margin-bottom: 5px; padding-top: 0px; padding-bottom: 0px; font-family: 'sans serif', tahoma, verdana, helvetica; line-height: 18px;"><span style="word-wrap: break-word; font-size: 14px;">?</span></p><p style="word-wrap: break-word; margin-top: 5px; margin-bottom: 5px; padding-top: 0px; padding-bottom: 0px; font-family: 'sans serif', tahoma, verdana, helvetica; line-height: 18px;"><span style="word-wrap: break-word; font-size: 14px;">mysql></span></p><p style="word-wrap: break-word; margin-top: 5px; margin-bottom: 5px; padding-top: 0px; padding-bottom: 0px; font-family: 'sans serif', tahoma, verdana, helvetica; line-height: 18px;"><span style="word-wrap: break-word; font-size: 14px;">這個時候，事務</span><span style="word-wrap: break-word; font-size: 14px;">t1</span><span style="word-wrap: break-word; font-size: 14px;">已經鎖定了表</span><span style="word-wrap: break-word; font-size: 14px;">t1</span><span style="word-wrap: break-word; font-size: 14px;">的所有</span><span style="word-wrap: break-word; font-size: 14px;">a<span style="word-wrap: break-word; font-size: 14px;">的數據行，然后去第二個窗口開啟第二個事務</span><span style="word-wrap: break-word; font-size: 14px;">t2</span><span style="word-wrap: break-word; font-size: 14px;">，如下，會看到</span><span style="word-wrap: break-word; font-size: 14px;">update</span><span style="word-wrap: break-word; font-size: 14px;">語句一直在等待事務</span><span style="word-wrap: break-word; font-size: 14px;">t1</span><span style="word-wrap: break-word; font-size: 14px;">釋放鎖資源，過了幾秒后，會有報錯信息，如下</span><span style="word-wrap: break-word;"><span style="word-wrap: break-word; font-size: 14px;">t2</span><span style="word-wrap: break-word; font-size: 14px;">事務窗口界面：</span><span style="word-wrap: break-word;"></span></span></span></p><pre class="brush:php;toolbar:false">mysql>?begin; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?update?test.t1?set?b='t2'?where?a=1; ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction mysql>
?
3.2，通過3個系統表來查看鎖信息
l??1，INNODB_TRX表
先看下表的重要字段以及記錄的信息
a)?????????trx_id：innodb存儲引擎內部事務唯一的事務id。
b)?????????trx_state：當前事務的狀態。
c)?????????trx_started：事務開始的時間。
d)?????????trx_requested_lock_id：等待事務的鎖id，如trx_state的狀態為LOCK WAIT，那么該值代表當前事務之前占用鎖資源的id，如果trx_state不是LOCK WAIT的話，這個值為null。
e)?????????trx_wait_started：事務等待開始的時間。
f)??????????trx_weight：事務的權重，反映了一個事務修改和鎖住的行數。在innodb的存儲引擎中，當發生死鎖需要回滾時，innodb存儲引擎會選擇該值最小的事務進行回滾。
g)?????????trx_mysql_thread_id：正在運行的mysql中的線程id，show full processlist顯示的記錄中的thread_id。
h)?????????trx_query：事務運行的sql語句，在實際中發現，有時會顯示為null值，當為null的時候，就是t2事務中等待鎖超時直接報錯（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就顯示為null值
……
因為前面模擬了事務鎖場景，開啟了t1和t2事務，現在去查看這個表信息，會有2條記錄如下：
mysql>?select?*?from?INNODB_TRXG ***************************?1.?row?*************************** ????????????????????trx_id:?3015646 ?????????????????trx_state:?LOCK?WAIT ???????????????trx_started:?2014-10-07?18:29:39 ?????trx_requested_lock_id:?3015646:797:3:2 ??????????trx_wait_started:?2014-10-07?18:29:39 ????????????????trx_weight:?2 ???????trx_mysql_thread_id:?18 ?????????????????trx_query:?update?test.t1?set?b='t2'?where?a=1 ???????trx_operation_state:?starting?index?read ?????????trx_tables_in_use:?1 ?????????trx_tables_locked:?1 ??????????trx_lock_structs:?2 ?????trx_lock_memory_bytes:?376 ???????????trx_rows_locked:?1 ?????????trx_rows_modified:?0 ???trx_concurrency_tickets:?0 ???????trx_isolation_level:?READ?COMMITTED ?????????trx_unique_checks:?1 ????trx_foreign_key_checks:?1 trx_last_foreign_key_error:?NULL ?trx_adaptive_hash_latched:?0 ?trx_adaptive_hash_timeout:?10000 ??????????trx_is_read_only:?0 trx_autocommit_non_locking:?0 ***************************?2.?row?*************************** ????????????????????trx_id:?3015645 ?????????????????trx_state:?RUNNING ???????????????trx_started:?2014-10-07?18:29:15 ?????trx_requested_lock_id:?NULL ??????????trx_wait_started:?NULL ????????????????trx_weight:?2 ???????trx_mysql_thread_id:?17 ?????????????????trx_query:?NULL ???????trx_operation_state:?NULL ?????????trx_tables_in_use:?0 ?????????trx_tables_locked:?0 ??????????trx_lock_structs:?2 ?????trx_lock_memory_bytes:?376 ???????????trx_rows_locked:?4 ?????????trx_rows_modified:?0 ???trx_concurrency_tickets:?0 ???????trx_isolation_level:?READ?COMMITTED ?????????trx_unique_checks:?1 ????trx_foreign_key_checks:?1 trx_last_foreign_key_error:?NULL ?trx_adaptive_hash_latched:?0 ?trx_adaptive_hash_timeout:?10000 ??????????trx_is_read_only:?0 trx_autocommit_non_locking:?0 2?rows?in?set?(0.00?sec) ? mysql>
這里只是記錄了一些當前正在運行的事務，比如事務t2正在運行trx_query: update test.t1 set b=’t2′ where a=1的sql語句，t1先執行，所以是trx_state: RUNNING先申請的資源一直在運行，而t2后run的所以是trx_state: LOCK WAIT一直在等待t1執行完后釋放資源。 但是并不能仔細判斷鎖的一些詳細情況，我們需要再去看INNODB_LOCKS表數據。
?
l??2，INNODB_LOCKS表
a)?????????lock_id：鎖的id以及被鎖住的空間id編號、頁數量、行數量
b)?????????lock_trx_id：鎖的事務id。
c)?????????lock_mode：鎖的模式。
d)?????????lock_type：鎖的類型，表鎖還是行鎖
e)?????????lock_table：要加鎖的表。
f)??????????lock_index：鎖的索引。
g)?????????lock_space：innodb存儲引擎表空間的id號碼
h)?????????lock_page：被鎖住的頁的數量，如果是表鎖，則為null值。
i)???????????lock_rec：被鎖住的行的數量，如果表鎖，則為null值。
j)???????????lock_data：被鎖住的行的主鍵值，如果表鎖，則為null值。
mysql>?select?*?from?INNODB_LOCKSG ***************************?1.?row?*************************** ????lock_id:?3015646:797:3:2 lock_trx_id:?3015646 ??lock_mode:?X ??lock_type:?RECORD ?lock_table:?`test`.`t1` ?lock_index:?PRIMARY ?lock_space:?797 ??lock_page:?3 ???lock_rec:?2 ??lock_data:?1 ***************************?2.?row?*************************** ????lock_id:?3015645:797:3:2 lock_trx_id:?3015645 ??lock_mode:?X ??lock_type:?RECORD ?lock_table:?`test`.`t1` ?lock_index:?PRIMARY ?lock_space:?797 ??lock_page:?3 ???lock_rec:?2 ??lock_data:?1 2?rows?in?set?(0.00?sec) ? mysql>
這里我們可以看到當前的鎖信息了，2個事務都鎖定了，看相同的數據lock_space: 797、lock_page: 3、lock_rec: 2可以得出事務t1和事務t2訪問了相同的innodb數據塊，再通過lock_data字段信息lock_data: 1，看到鎖定的數據行都是主鍵為1的數據記錄，可見2個事務t1和t2都申請了相同的資源，因此會被鎖住，事務在等待。
通過lock_mode: X值也可以看出事務t1和t2申請的都是排它鎖。
?
???????? PS：當執行范圍查詢更新的時候，這個lock_data的值并非是完全準確。當我們運行一個范圍更新時，lock_data只返回最先找到的第一行的主鍵值id；另外如果當前資源被鎖住了，與此同時由于鎖住的頁因為InnoDB存儲引擎緩沖池的容量，而導致替換緩沖池頁面，再去查看INNODB_LOCKS表時，這個lock_data會顯示未NULL值，意味著InnoDB存儲引擎不會從磁盤進行再一次查找。
?
?
l??3，INNODB_LOCK_WAITS表
當事務量比較少，我們可以直觀的查看，當事務量非常大，鎖等待也時常發生的情況下，這個時候可以通過INNODB_LOCK_WAITS表來更加直觀的反映出當前的鎖等待情況：
INNODB_LOCK_WAITS表主要字段如下：
1)?????????requesting_trx_id：申請鎖資源的事務id。
2)?????????requested_lock_id：申請的鎖的id。
3)?????????blocking_trx_id：阻塞的事務id。
4)?????????blocking_lock_id：阻塞的鎖的id。
去看下當前鎖等待信息，如下所示：
mysql>?select?*?from?INNODB_LOCK_WAITSG ***************************?1.?row?*************************** requesting_trx_id:?3015646 requested_lock_id:?3015646:797:3:2 ??blocking_trx_id:?3015645 ?blocking_lock_id:?3015645:797:3:2 1?row?in?set?(0.00?sec) mysql>
這里我們可以看到事務t1(3015646)申請了鎖資源，而事務t2(3015645)則阻塞了事務t1的申請。我們管理其他表，得到更直觀的詳細信息，如下所示：
mysql>?SELECT?it2.`trx_id`?AS?waiting_trx_id,?it2.`trx_mysql_thread_id`?AS?waiting_thread,it2.`trx_query`?AS?waiting_query,??it1.`trx_id`?AS?blocking_trx_id,?it1.`trx_mysql_thread_id`?blocking_thread,?it1.`trx_query`?blocking_query?FROM?`information_schema`.`INNODB_LOCK_WAITS`?ilw,?`information_schema`.`INNODB_TRX`?it1,`information_schema`.`INNODB_TRX`?it2?WHERE?it1.`trx_id`=ilw.`blocking_trx_id`?AND?it2.`trx_id`=ilw.`requesting_trx_id`; +----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+ |?waiting_trx_id?|?waiting_thread?|?waiting_query???????????????????????|?blocking_trx_id?|?blocking_thread?|?blocking_query?| +----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+ |?3015647????????|?????????????18?|?update?test.t1?set?b='t2'?where?a>2?|?3015645?????????|??????????????17?|?NULL???????????| +----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+ 1?row?in?set?(0.00?sec) ? mysql>
?
4，一致性的非鎖定讀操作
4.1，CNR原理解析
一致性的非鎖定行讀（consistent nonlocking read，簡稱CNR）是指InnoDB存儲引擎通過行多版本控制（multi versioning）的方式來讀取當前執行時間數據庫中運行的數據。如果讀取的行正在執行delete、update操作，這時讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB存儲引擎會去讀取行的一個快照數據，如下圖所示：
非鎖定讀，是因為不需要等待訪問的行上X鎖的釋放，快照數據是指該行之前版本的數據，該實現是通過Undo段來實現，而Undo用來在事務中回滾數據，因此快照本身是沒有額外的開銷，此外讀取快照是不需要上鎖的，因為沒有必要對歷史的數據進行修改。
?
非鎖定讀大大提高了數據讀取的并發性，在InnoDB存儲引擎默認設置下，這是默認的讀取方式，既讀取不會占用和等待表上的鎖。但是不同事務隔離級別下，讀取的方式不同，不是每一個事務隔離級別下的都是一致性讀。同樣，即使都是使用一致性讀，但是對于快照數據的定義也不相同。
?
快照數據其實就是當前數據之前的歷史版本，可能有多個版本。如上圖所示，一個行可能不止有一個快照數據。我們稱這種技術為行多版本技術。由此帶來的并發控制，稱之為多版本并發控制（Multi Version Concurrency Control，MVCC）。
?
在Read Committed和Repeatable Read模式下，innodb存儲引擎使用默認的非鎖定一致讀。在Read Committed隔離級別下，對于快照數據，非一致性讀總是讀取被鎖定行的最新一份快照數據；而在Repeatable Read隔離級別下，對于快照數據，非一致性讀總是讀取事務開始時的行數據版本。
?
4.2，CNR實例
開啟2個Session A和B。
Session??A： mysql>?begin; Query?OK,?0?rows?affected?(0.01?sec) ? mysql>?select?*?from?t1?where?a=1; +---+----+----+ |?a?|?b??|?c??| +---+----+----+ |?1?|?c2?|?c2?| +---+----+----+ 1?row?in?set?(0.00?sec) ? mysql>
Session A中事務已經開始，讀取了a=1的數據，但是還沒有結束事務，這時我們再開啟一個Session B，以此模擬并發的情況，然后對Session B做如下操作：
mysql>?begin; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?update?t1?set?a=111?where?a=1; Query?OK,?1?row?affected?(0.00?sec) Rows?matched:?1??Changed:?1??Warnings:?0 ? mysql>
Session?中將a=1的行修改為a=111，但是事務同樣沒有提交，這樣a=1的行其實加了一個X鎖。這時如果再在Session A中讀取a=1的數據，根據innodb存儲引擎的特性，在Read Committed和Repeatable Read事務隔離級別下，會使用非鎖定的一致性讀。回到Session A，節著上次未提交的事務，執行select * from t1 where a=1;的操作，顯示的數據應該都是原來的數據：
mysql>?select?*?from?t1?where?a=1; +---+----+----+ |?a?|?b??|?c??| +---+----+----+ |?1?|?c2?|?c2?| +---+----+----+ 1?row?in?set?(0.00?sec) ? mysql>
因為當前a=1的數據被修改了1次，所以只有一個版本的數據，接著我們在Session B中commit上次的事務。如：
mysql>?commit; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>
?
Session B提交事務后，這時再在Session A中運行select * from t1 where a=1;的sql語句，在READ-COMMITTED和REPEATABLE-READ事務隔離級別下，得到的結果就會不一樣，對于READ-COMMITTED模事務隔離級別，它總是讀取行的最新版本，如果行被鎖定了，則讀取該行的最新一個快照(fresh snapshot)。因此在這個例子中，因為Session B已經commit了事務，所以在READ-COMMITTED事務隔離級別下會得到如下結果，查詢a=1就是為null記錄，因為a=1的已經被commit成了a=111，但是如果查詢a=111的記錄則會被查到，如下所示：
mysql>?show?variables?like?'tx_isolation'; +---------------+----------------+ |?Variable_name?|?Value??????????| +---------------+----------------+ |?tx_isolation??|?READ-COMMITTED?| +---------------+----------------+ 1?row?in?set?(0.00?sec) ? mysql>?select?*?from?t1?where?a=1; Empty?set?(0.00?sec) ? mysql>?select?*?from?t1?where?a=111; +-----+----+----+ |?a???|?b??|?c??| +-----+----+----+ |?111?|?c2?|?c2?| +-----+----+----+ 1?row?in?set?(0.01?sec) ? mysql>
?
但是如果在REPEATABLE-READ事務隔離級別下，總是讀取事務開始時的數據，所以得到的結果截然不同，如下所示：
mysql>?show?variables?like?'tx_isolation'; +---------------+-----------------+ |?Variable_name?|?Value???????????| +---------------+-----------------+ |?tx_isolation??|?REPEATABLE-READ?| +---------------+-----------------+ 1?row?in?set?(0.00?sec) ? mysql>?select?*?from?t1?where?a=1; +---+----+----+ |?a?|?b??|?c??| +---+----+----+ |?1?|?c0?|?c2?| +---+----+----+ 1?row?in?set?(0.00?sec) ? mysql>?select?*?from?t1?where?a=111; Empty?set?(0.00?sec) ? mysql>
?
對于READ-COMMITTED的事務隔離級別而言，從數據庫理論的角度來看，其實違反了事務ACID的I的特性，既是隔離性，整理成時序表，如下圖所示。
|
Time |
Session? A |
Session? B |
|
|?time 1 |
Begin; Select * from t1 where a=1;有記錄 |
? |
|
|?time 2 |
? |
Begin; Update t1 set a=111 where a=1; |
|
|?time 3 |
Select * from t1 where a=1;有記錄 |
? |
|
|?time 4 |
? |
Commit; |
|
|?time 5 |
Select * from t1 where a=1;?無記錄 |
? |
|
V?time 6 |
Commit; |
? |
如果按照ACID原理中的I原理隔離性，在整個會話中Session A中，Select * from t1 where a=1;應該查詢出來的數據保持一直，但是在time 5那一刻?Session A未結束的時候，查詢出來的結果已經變化了和time 1、time 3已經不一致了，不滿足ACID的隔離性。
?
5，SELECT … FOR UPDATE && SELECT … LOCK IN SHARE MODE
默認情況下，innodb存儲引擎的select操作使用一致性非鎖定讀，但是在某些情況下，需要對讀操作進行加鎖。Innodb存儲引擎對select語句支持2種添加鎖操作;
???SELECT … FOR UPDATE?對于讀取的行記錄加一個X排它鎖，其他事務如果要對這些行進行dml或者select操作都會被阻塞。
???SELECT … LOCK IN SHARE MODE?對于讀取的行記錄添加一個S共享鎖。其它事務可以向被鎖定的行加S鎖，但是不允許添加X鎖，否則會被阻塞。
對于一致性 非鎖定讀，即使讀取的行數已經被SELECT … FOR UPDATE了，但是也是可以進行讀取的。
???????? PS：… FOR UPDATE以及LOCK IN SHARE MODE必須在一個事務中，如果事務commit了，鎖也就釋放了，所以在使用的時候務必加上begin;start transaction或者set autocommit=0;
?
例子如下：
會話A：開啟事務，執行LOCK IN SHARE MODE;鎖定
mysql>?begin; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?SELECT?*?FROM?t1?WHERE?a=1?LOCK?IN?SHARE?MODE; +---+----+----+ |?a?|?b??|?c??| +---+----+----+ |?1?|?c0?|?c2?| +---+----+----+ 1?row?in?set?(0.00?sec) ? mysql>
?
同時在另外一個窗口開啟會話B，執行dml操作
mysql>?begin; Query?OK,?0?rows?affected?(0.00?sec) ? mysql>?update?t1?set?a=111?where?a=1;
…這里會卡住，沒有信息。
?
再開啟一個會話C，查詢INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表，就會看到鎖的詳細信息：
mysql>?select?*?from?INNODB_LOCKS; +-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ |?lock_id?????????|?lock_trx_id?|?lock_mode?|?lock_type?|?lock_table??|?lock_index?|?lock_space?|?lock_page?|?lock_rec?|?lock_data?| +-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ |?3015708:797:3:2?|?3015708?????|?X?????????|?RECORD????|?`test`.`t1`?|?PRIMARY????|????????797?|?????????3?|????????2?|?1?????????| |?3015706:797:3:2?|?3015706?????|?S?????????|?RECORD????|?`test`.`t1`?|?PRIMARY????|????????797?|?????????3?|????????2?|?1?????????| +-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ 2?rows?in?set?(0.00?sec) ? mysql> mysql>?select?*?from?INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ |?requesting_trx_id?|?requested_lock_id?|?blocking_trx_id?|?blocking_lock_id?| +-------------------+-------------------+-----------------+------------------+ |?3015708???????????|?3015708:797:3:2???|?3015706?????????|?3015706:797:3:2??| +-------------------+-------------------+-----------------+------------------+ 1?row?in?set?(0.00?sec) ? mysql> mysql>?SELECT?it2.`trx_id`?AS?waiting_trx_id,?it2.`trx_state`?AS?waiting_trx_status,it2.`trx_mysql_thread_id`?AS?waiting_thread,it2.`trx_query`?AS?waiting_query, ????->?it1.`trx_id`?AS?blocking_trx_id,?it1.`trx_mysql_thread_id`?blocking_thread,?it1.`trx_query`?blocking_query ????->?FROM?`information_schema`.`INNODB_LOCK_WAITS`?ilw,?`information_schema`.`INNODB_TRX`?it1,`information_schema`.`INNODB_TRX`?it2 ????->?WHERE?it1.`trx_id`=ilw.`blocking_trx_id`?AND?it2.`trx_id`=ilw.`requesting_trx_id`; +----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+ |?waiting_trx_id?|?waiting_trx_status?|?waiting_thread?|?waiting_query?????????????????|?blocking_trx_id?|?blocking_thread?|?blocking_query?| +----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+ |?3015708????????|?LOCK?WAIT??????????|?????????????18?|?update?t1?set?a=111?where?a=1?|?3015706?????????|??????????????21?|?NULL???????????| +----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+ 1?row?in?set?(0.00?sec) ? mysql>
會話A開啟的事務1（事務id：3015706）執行了SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
語句已經在a=1的行上加了S鎖，所以會話B開啟的事務2（事務id：23015708）執行的update t1 set a=111 where a=1;sql語句往a=1的行上加X鎖，就被會話A的事務1阻塞了，所以事務2的狀態值就是LOCK WAIT，一直在等待。直到事務等待超時，報錯如下：
mysql> update t1 set a=111 where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
此時，會話B中的事務2就終止了update t1 set a=111 where a=1;的dml請求操作。
?
6，自增長和鎖
自增長在數據庫中是非常常見的一種屬性，在Innodb的存儲引擎的內存結構中，對每個含有自增長值的表都有一個自增長計數器（auto-increment counter）。當對有自增長字段的表進行insert時候，這個計數器會被初始化，執行如下的sql語句來得到計數器的值。
SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;
插入操作會依據這個自增長的計數器值+1賦予自增長列，這個實現方式稱為AUTO-INC Locking，這種鎖其實是一種特殊的表鎖機制，為了提高插入的性能，鎖不是在一個事務完成后才釋放，而是在完成對自增長值插入的sql語句后立即釋放。
?
從mysql 5.1.22版本開始，提供了一些輕量級互斥的自增長實現機制，這種機制大大提高自增長值插入的性能。還提供了一個參數innodb_autoinc_lock_mode，默認值為1.
?
自增長的分類：
在mysql的innodb表中，自增長列必須是索引，而且必須為索引的第一列，如果是第二個列會報錯如下所示：
mysql>?CREATE?TABLE?t(a?INT?AUTO_INCREMENT?,b?VARCHAR(10),KEY?(b,a)); ERROR?1075?(42000):?Incorrect?table?definition;?there?can?be?only?one?auto?column?and?it?must?be?defined?as?a?key mysql> mysql>?CREATE?TABLE?t(a?INT?AUTO_INCREMENT?,b?VARCHAR(10),PRIMARY?KEY?(a),KEY?(b,a)); Query?OK,?0?rows?affected?(0.01?sec) ? mysql>
而在myisam表中，則沒有這樣的限制，如下所示：
mysql>??CREATE?TABLE?t_myisam(a?INT?AUTO_INCREMENT?,b?VARCHAR(10),KEY?(b,a))engine=myisam; Query?OK,?0?rows?affected?(0.01?sec)
?
mysql>
?
7，MySQL外鍵和鎖
在innodb存儲引擎中，對于一個外鍵列，如果沒有顯式的針對這個列添加索引Innodb存儲引擎會自動的對其添加一個索引，這樣可以避免表鎖，這點比oracle做的較好一些，oracle需要自己手動添加外鍵鎖。
?以上就是MySQL 溫故而知新–Innodb存儲引擎中的鎖的內容，更多相關內容請關注PHP中文網（www.php.cn）！
