Mysql鎖

mysql 鎖機制

標簽（空格分隔）： mysql

參考文檔

https://www.2cto.com/database/201508/429967.html
http://www.cnblogs.com/aipiaoborensheng/p/5767459.html

概念

共享鎖（S）：允許一個事務去讀一行纺裁，阻止其他事務獲得相同的數(shù)據(jù)集的排他鎖玫镐。
排他鎖（X）：允許獲得排他鎖的事務更新數(shù)據(jù)，但是組織其他事務獲得相同數(shù)據(jù)集的共享鎖和排他鎖筒溃。
對于insert置侍、update映之、delete，InnoDB會自動給涉及的數(shù)據(jù)加排他鎖（X）蜡坊；對于一般的Select語句杠输，InnoDB不會加任何鎖，事務可以通過以下語句給顯示加共享鎖或排他鎖秕衙。

共享鎖

select * from table_name where .....lock in share mode

Note left of 事務1: select * from table_1 where id=1 lock in share mode;
事務1-->事務2: 
Note right of 事務2: select * from table_1 where id=1 lock in share mode;
事務2-->事務1: 
Note left of 事務1: update table_1 set age=10 where id=1;
Note left of 事務1: 事務1更新時發(fā)現(xiàn)此行鎖被其他事務享用蠢甲，等待
事務1-->事務2: 
Note right of 事務2: update table_1 set age=12 where id=1;
Note right of 事務2: 事務2更新時發(fā)現(xiàn)此行鎖被其他事務享用，也等待据忘，導致死鎖

排他鎖

select * from table_name where .....for update

Note left of 事務1: select * from table_1 where id=1 for update;
事務1-->事務2: 
Note right of 事務2: select * from table_1 where id=1 for update;
Note right of 事務2: 等待...
事務2-->事務1: 
Note left of 事務1: update table_1 set age=10 where id=1;
Note left of 事務1: 更新完后釋放鎖
事務1-->事務2: 
Note right of 事務2: 獲得鎖后鹦牛，得到其他事務提交的記錄

行鎖的三種形式

Record lock:鎖定一條記錄。
Gap lock
Next-key lock

innoDB鎖問題

事務（Transaction）及其ACID屬性

原子性（Actomicity）：事務是一個原子操作單元勇吊，其對數(shù)據(jù)的修改曼追，要么全都執(zhí)行，要么全都不執(zhí)行萧福。

一致性（Consistent）：在事務開始和完成時拉鹃，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應用于事務的修改鲫忍，以操持完整性膏燕；事務結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）也都必須是正確的悟民。

隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制坝辫，保證事務在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行。這意味著事務處理過程中的中間狀態(tài)對外部是不可見的射亏，反之亦然近忙。

持久性（Durable）：事務完成之后，它對于數(shù)據(jù)的修改是永久性的智润，即使出現(xiàn)系統(tǒng)故障也能夠保持及舍。

并發(fā)事務帶來的問題

更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時窟绷，由于每個事務都不知道其他事務的存在锯玛，就會發(fā)生丟失更新問題——最后的更新覆蓋了其他事務所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本攘残。每個編輯人員獨立地更改其副本拙友，然后保存更改后的副本，這樣就覆蓋了原始文檔歼郭。最后保存其更改保存其更改副本的編輯人員覆蓋另一個編輯人員所做的修改遗契。如果在一個編輯人員完成并提交事務之前，另一個編輯人員不能訪問同一文件病曾，則可避免此問題

臟讀（Dirty Reads）：一個事務正在對一條記錄做修改牍蜂，在這個事務并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)知态；這時捷兰，另一個事務也來讀取同一條記錄，如果不加控制负敏，第二個事務讀取了這些“臟”的數(shù)據(jù)贡茅，并據(jù)此做進一步的處理，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系其做。這種現(xiàn)象被形象地叫做“臟讀”顶考。

不可重復讀（Non-Repeatable Reads）：一個事務在讀取某些數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了妖泄！這種現(xiàn)象叫做“不可重復讀”驹沿。

幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務插入了滿足其查詢條件的新數(shù)據(jù)蹈胡，這種現(xiàn)象就稱為“幻讀”渊季。

事務隔離級別

| 隔離級別 | 臟讀| 不可重復讀 | 幻讀 |
| -| - |
| 未提交讀（Read uncommitted） | √ | √ | √ |
| 已提交度（Read committed） | x | √ | √ |
| 可重復讀（Repeatable read） |x | x | √ |
| 可序列化（Serializable） | x | x | x |

mysql行鎖的特性

innodb 的行鎖是在有索引的情況下,沒有索引的表是鎖定全表的.
實例：
id是主鍵
| id| name|
| -| - |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
事務1update第一條id=1的數(shù)據(jù)，事務不提交罚渐；事務2接著update第二條id=2的數(shù)據(jù)的時候等待却汉，原因是id沒有加上索引，導致事務1鎖的是表鎖而不是行鎖荷并。
如果是使用相同的索引鍵合砂，會出現(xiàn)鎖沖突。
示例：tab_with_index表中id字段有索引源织，name字段沒有索引翩伪。
事務1：

select * from tab_with_index where id = 1 and name = '1' for update;

事務2：

select * from tab_with_index where id = 1 and name = '4' for update;

雖然事務2訪問的是和事務1不同的記錄，但是因為使用了相同的索引谈息，所以需要等待鎖缘屹。

當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行侠仇，另外囊颅，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖踢代。
示例：表tab_with_index的id字段有主鍵索引，name字段有普通索引嗅骄。
事務1：

select * from tab_with_index where id = 1 for update;

事務2：

select * from tab_with_index where name = '2' for update;

事務2使用name的索引訪問記錄胳挎，因為記錄沒有被索引，所以也可以獲得鎖溺森。

間隙鎖（Next-Key鎖）

當我們用范圍條件而不是相等條件檢索數(shù)據(jù)慕爬，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖屏积；對于鍵值在條件范圍內(nèi)但并不存在的記錄医窿，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖炊林，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）姥卢。
示例：

Select * from  emp where empid > 100 for update;

是一個范圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖渣聚，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖独榴。
InnoDB 使用間隙鎖的目的，一方面是為了防止幻讀奕枝，以滿足相關(guān)隔離級別的要求棺榔，對于上面的例子，要是不使用間隙鎖隘道，如果其他事務插入了empid大于100的任何記錄症歇，那么本事務如果再次執(zhí)行上述語句，就會發(fā)生幻讀.
還要特別說明的是谭梗，InnoDB除了通過范圍條件加鎖時使用間隙鎖外忘晤，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖默辨！

一致性非鎖定讀

一致性非鎖定讀是指InnoDB存儲引擎通過多版本并發(fā)控制技術(shù)來讀取當前數(shù)據(jù)庫的數(shù)據(jù)德频。如果當前讀取的行正在執(zhí)行delete或者update操作，這時讀取操作不會等行鎖的釋放缩幸，而是去讀取行的快照數(shù)據(jù)壹置。

非鎖定一致性讀.png

快照數(shù)據(jù)是指改行之前版本的數(shù)據(jù)，該實現(xiàn)是通過undo段來實現(xiàn)的表谊，而undo段用來在事務中保存回滾數(shù)據(jù)钞护，因此使用快照沒有增加額外的開銷。
這是InnoDB存儲引擎的默認讀取方式爆办。

注意

不同的事務隔離級別下讀取的方式不同难咕，并不是每個事務隔離級別下都是采用非鎖定的一致性讀。四種隔離級別中，READ COMMITTED和REPEATABLE READ這兩種隔離級別使用非鎖定的一致性讀余佃。
不同的事務即使都使用非鎖定的一致性讀暮刃，但是對于快照數(shù)據(jù)的定義也各不相同。READ COMMITTED級別下非鎖定讀總是讀取鎖定行的最新一份快照數(shù)據(jù)爆土；而REPEATABLE READ級別下非鎖定讀總是讀取事務開始時的數(shù)據(jù)版本椭懊。

例子

事務A	事務B
select * from table where id='1';
.	update table set id =3 where id=1;
select * from table where id='1';
.	commit;
select * from table where id='1';

上述例子中事務B update以后事務A第一次select的時候RC級別和RR級別獲取的結(jié)果都是id=1的那一條數(shù)據(jù)；第二次select的時候步势，由于事務B已經(jīng)提交氧猬，RC級別select的結(jié)果就是id=3，而RR級別讀取的是事務開始時的數(shù)據(jù)坏瘩，id=1盅抚。

一致性鎖定讀

默認配置下事務的隔離級別為REPEATABLE READ，select操作為非一致性鎖定讀倔矾，但某些情況下需要對數(shù)據(jù)庫讀取操作進行加鎖保證數(shù)據(jù)的一致性妄均。select 有兩種一致的鎖定讀：

select ... for update
select ... lock in share mode

自增長與鎖

InnoDB存儲引擎內(nèi)部對每個含有自增長列的表有一個自增長計數(shù)器，當進行insert操作時破讨，首先獲取計數(shù)器的最大值丛晦，加1后進行insert操作。這個操作會加一個特殊的表鎖提陶，AUTO-INC LOCK烫沙。這個鎖并不是在事務提交后才釋放，而是在insert語句執(zhí)行完后釋放隙笆。

缺點

雖然是insert后就釋放鎖锌蓄，不是事務提交后才釋放，但是必須等前一個insert的完成才能進行下一次insert撑柔，性能較差瘸爽。

改進

TODO...

外鍵與鎖

在對外鍵值進行update和insert操作時首先需要查詢父表的記錄，即select父表铅忿，這個select操作不是使用一致性非鎖定讀剪决，因為會發(fā)生數(shù)據(jù)不一致的問題，為此需要使用一致性鎖定讀檀训，這時使用的select ... lock in share mode方式柑潦。當父表對應記錄加X鎖后，子表的操作將會阻塞峻凫。

例子