2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

引言

數(shù)據(jù)庫鎖定機(jī)制簡(jiǎn)單來說就是數(shù)據(jù)庫為了保證數(shù)據(jù)的一致性而使各種共享資源在被并發(fā)訪問變得有序所設(shè)計(jì)的一種規(guī)則；對(duì)于任何一種數(shù)據(jù)庫來說都需要有相應(yīng)的鎖定機(jī)制，Mysql也不例外。

mysql 事務(wù)

一般來說，事務(wù)是必須滿足4個(gè)條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。

原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。

一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。

隔離性：數(shù)據(jù)庫允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。

持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。

MYSQL 事務(wù)處理主要有兩種方法：

用 BEGIN, ROLLBACK, COMMIT來實(shí)現(xiàn)
1)、BEGIN 開始一個(gè)事務(wù)
2)、ROLLBACK 事務(wù)回滾
3)、COMMIT 事務(wù)確認(rèn)

直接用 SET 來改變 MySQL 的自動(dòng)提交模式:
1)、SET AUTOCOMMIT=0 禁止自動(dòng)提交
2)、SET AUTOCOMMIT=1 開啟自動(dòng)提交

事務(wù)隔離級(jí)別

mysql事務(wù)隔離級(jí)別分為如下4種：

(1)讀不提交(Read Uncommited，RU)

這種隔離級(jí)別下，事務(wù)間完全不隔離，會(huì)產(chǎn)生臟讀，可以讀取未提交的記錄，實(shí)際情況下不會(huì)使用。

(2)讀提交(Read commited，RC)

僅能讀取到已提交的記錄，這種隔離級(jí)別下，會(huì)存在幻讀現(xiàn)象，所謂幻讀是指在同一個(gè)事務(wù)中，多次執(zhí)行同一個(gè)查詢，返回的記錄不完全相同的現(xiàn)象。幻讀產(chǎn)生的根本原因是，在RC隔離級(jí)別下，每條語句都會(huì)讀取已提交事務(wù)的更新，若兩次查詢之間有其他事務(wù)提交，則會(huì)導(dǎo)致兩次查詢結(jié)果不一致。雖然如此，讀提交隔離級(jí)別在生產(chǎn)環(huán)境中使用很廣泛。

(3)可重復(fù)讀(Repeatable Read, RR)

可重復(fù)讀隔離級(jí)別解決了不可重復(fù)讀的問題，但依然沒有解決幻讀的問題。那么不可重復(fù)讀與幻讀有什么區(qū)別呢？不可重復(fù)讀重點(diǎn)在修改，即讀取過的數(shù)據(jù)，兩次讀的值不一樣；而幻讀則側(cè)重于記錄數(shù)目變化【插入和刪除】。一般教科書上告訴我們只有到串行化隔離級(jí)別才解決幻讀問題，但mysql的innodb比較特殊，RR即解決了幻讀問題，主要通過GAP鎖實(shí)現(xiàn)。另外，不是所有的數(shù)據(jù)庫都實(shí)現(xiàn)了該隔離級(jí)別，后面會(huì)簡(jiǎn)單介紹下mysql是如何實(shí)現(xiàn)可重復(fù)讀隔離級(jí)別的。

(4)串行化(Serializable)

在串行化隔離模式下，消除了臟讀，幻象，但事務(wù)并發(fā)度急劇下降，事務(wù)的隔離級(jí)別與事務(wù)的并發(fā)度成反比，隔離級(jí)別越高，事務(wù)的并發(fā)度越低。實(shí)際生產(chǎn)環(huán)境下，dba會(huì)在并發(fā)和滿足業(yè)務(wù)需求之間作權(quán)衡，選擇合適的隔離級(jí)別。

mysql 鎖類型

MySQL 各存儲(chǔ)引擎使用了三種類型（級(jí)別）的鎖定機(jī)制：行級(jí)鎖定，頁級(jí)鎖定和表級(jí)鎖定。

1、行級(jí)鎖定

鎖定對(duì)象的顆粒度很小，只對(duì)當(dāng)前行進(jìn)行鎖定，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力；弊端就是獲取鎖釋放鎖更加頻繁，系統(tǒng)消耗更大，同時(shí)行級(jí)鎖定也最容易發(fā)生死鎖；
行級(jí)鎖定的主要是Innodb存儲(chǔ)引擎和NDB Cluster存儲(chǔ)引擎；

2、表級(jí)鎖定

一次會(huì)將整張表鎖定，該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來的系統(tǒng)負(fù)面影響最小，而且可以避免死鎖問題；弊端就是鎖定資源爭(zhēng)用的概率最高，并發(fā)處理能力最低；
使用表級(jí)鎖定的主要是MyISAM，Memory，CSV等一些非事務(wù)性存儲(chǔ)引擎。

3、頁級(jí)鎖定

鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，每頁有多行數(shù)據(jù)，并發(fā)處理能力以及獲取鎖定所需要的資源開銷在兩者之間；
頁級(jí)鎖定主要是BerkeleyDB 存儲(chǔ)引擎；

兩段鎖協(xié)議(2PL)

兩段鎖協(xié)議規(guī)定所有的事務(wù)應(yīng)遵守的規(guī)則：
1.在對(duì)任何數(shù)據(jù)進(jìn)行讀、寫操作之前，首先要申請(qǐng)并獲得對(duì)該數(shù)據(jù)的封鎖；
2.在釋放一個(gè)封鎖之后，事務(wù)不再申請(qǐng)和獲得其它任何封鎖；

即事務(wù)的執(zhí)行分為兩個(gè)階段：
第一階段是獲得封鎖的階段，稱為擴(kuò)展階段；第二階段是釋放封鎖的階段，稱為收縮階段；

1begin;insert?...???加鎖1?update?...???加鎖2?commit;??????事務(wù)提交時(shí)，釋放鎖1，鎖2

如果在加鎖2的時(shí)候，加鎖不成功，則進(jìn)入等待狀態(tài)，直到加鎖成功才繼續(xù)執(zhí)行；
如果有另外一個(gè)事務(wù)獲取鎖的時(shí)候順序剛好相反，是有可能導(dǎo)致死鎖的；為此有了一次性封鎖法，要求事務(wù)必須一次性將所有要使用的數(shù)據(jù)全部加鎖，否則就不能繼續(xù)執(zhí)行；

鎖模式

Innodb 實(shí)現(xiàn)了兩種類型的行鎖：

共享鎖（S）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。

意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，innodb 就將請(qǐng)求的鎖授予該事務(wù)；反之，如果兩者不兼容，該事務(wù)就要等待鎖釋放。?
意向鎖是 innodb 自動(dòng)加的，不需要用戶干預(yù)。對(duì)于 update、delete 和 insert 語句，innodb 會(huì)自動(dòng)給涉及數(shù)據(jù)集加排它鎖（X）；對(duì)于普通 select 語句，innodb 不會(huì)加任何鎖。

事務(wù)可以通過以下語句顯式給記錄集加共享鎖或排它鎖。

共享鎖（S）：select * from table_name where … lock in share mode.
排它鎖（X）: select * from table_name where … for update.
用 select… in share mode 獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時(shí)來確認(rèn)某行記錄是否存在，并確保沒有人對(duì)這個(gè)記錄進(jìn)行 update 或者 delete 操作。但是如果當(dāng)前事務(wù)也需要對(duì)該記錄進(jìn)行更新操作,則有可能造成死鎖，對(duì)于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用，應(yīng)該使用 select… for update 方式獲得排他鎖。

事務(wù)隔離級(jí)別和鎖的關(guān)系

數(shù)據(jù)庫隔離級(jí)別：未提交讀(Read uncommitted)，已提交讀(Read committed)，可重復(fù)讀(Repeatable read)和可串行化(Serializable)；

未提交讀(Read uncommitted)：可能讀取到其他會(huì)話中未提交事務(wù)修改的數(shù)據(jù)，會(huì)出現(xiàn)臟讀(Dirty Read)；

已提交讀(Read committed)：只能讀取到已經(jīng)提交的數(shù)據(jù)，會(huì)出現(xiàn)不可重復(fù)讀(NonRepeatable Read)；

可重復(fù)讀(Repeatable read)：InnoDB默認(rèn)級(jí)別，不會(huì)出現(xiàn)不可重復(fù)讀(NonRepeatable Read)，但是會(huì)出現(xiàn)幻讀(Phantom Read);

可串行化(Serializable)：強(qiáng)制事務(wù)排序，使之不可能相互沖突，從而解決幻讀問題，使用表級(jí)共享鎖，讀寫相互都會(huì)阻塞；

常用的2種隔離級(jí)別是：已提交讀(Read committed)和可重復(fù)讀(Repeatable read)；mysql的默認(rèn)隔離級(jí)別是可重復(fù)讀。

行鎖的實(shí)現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這一點(diǎn)MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB將使用表鎖！在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。

由于MySQL的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會(huì)出現(xiàn)鎖沖突的

當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖。如果不同的索引碰巧都落到了同一個(gè)行上，那么同樣會(huì)阻塞。

即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的，如果MySQL認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表，它就不會(huì)使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時(shí)，別忘了檢查SQL的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引。

間隙鎖

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一個(gè)范圍條件的檢索，InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖，也會(huì)對(duì)empid大于101（這些記錄并不存在）的“間隙”加鎖。
InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關(guān)隔離級(jí)別的要求，對(duì)于上面的例子，要是不使用間隙鎖，如果其他事務(wù)插入了empid大于100的任何記錄，那么本事務(wù)如果再次執(zhí)行上述語句，就會(huì)發(fā)生幻讀；另外一方面，是為了滿足其恢復(fù)和復(fù)制的需要

還要特別說明的是，InnoDB除了通過范圍條件加鎖時(shí)使用間隙鎖外，如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖，InnoDB也會(huì)使用間隙鎖！

MySQL的恢復(fù)機(jī)制是通過BINLOG記錄來執(zhí)行IUD操作來同步Slave的，這就要求：在一個(gè)事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀，這已經(jīng)超過了ISO/ANSI SQL92“可重復(fù)讀”隔離級(jí)別的要求，實(shí)際上是要求事務(wù)要串行化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用范圍條件更新記錄時(shí)，無論在Read Commited或是Repeatable Read隔離級(jí)別下，InnoDB都要使用間隙鎖，但這并不是隔離級(jí)別要求的。

INSERT…SELECT…和 CREATE TABLE…SELECT…語句，可能會(huì)阻止對(duì)源表的并發(fā)更新，造成對(duì)源表鎖的等待。如果查詢比較復(fù)雜的話，會(huì)造成嚴(yán)重的性能問題，我們?cè)趹?yīng)用中應(yīng)盡量避免使用。實(shí)際上，MySQL將這種SQL叫作不確定（non-deterministic）的SQL，不推薦使用。

測(cè)試mysql隔離級(jí)別

已提交讀

接下來我們測(cè)試下mysql的已提交讀，我們先看下mysql的隔離級(jí)別：

1mysql>?SELECT?@@tx_isolation; 2+----------------+ 3|?@@tx_isolation?| 4+----------------+ 5|?REPEATABLE-READ?| 6+----------------+

設(shè)置隔離級(jí)別為已提交讀

1set?session?transaction?isolation?level?read?committed;2Query?OK,?0?rows?affected?(0.02?sec)34mysql>?SELECT?@@tx_isolation;5+----------------+6|?@@tx_isolation?|7+----------------+8|?READ-COMMITTED?|9+----------------+ 101?row?in?set?(0.01?sec)

準(zhǔn)備測(cè)試數(shù)據(jù):

1CREATE?TABLE?`test_lock`?( 2??`id`?int(11)?NOT?NULL?AUTO_INCREMENT, 3??`name`?varchar(255)?NOT?NULL, 4??`type`?int(11)?DEFAULT?NULL, 5??PRIMARY?KEY?(`id`) 6)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8 7 8mysql>?insert?into?test_lock?values(null,'zhaohui',1); 9mysql>?insert?into?test_lock?values(null,'zhaohui2',2);

模擬多個(gè)事務(wù)交叉執(zhí)行:
Session1執(zhí)行查詢

1mysql>?begin2????->?;3Query?OK,?0?rows?affected?(0.02?sec)45mysql>?select?*?from?test_lock?where?id=1;6+----+---------+------+7|?id?|?name????|?type?|8+----+---------+------+9|??1?|?zhaohui?|????1?| 10+----+---------+------+ 111?row?in?set?(0.05?sec)

Session2更新數(shù)據(jù)

1?begin;2Query?OK,?0?rows?affected?(0.08?sec)34mysql>?update?test_lock?set?name='zhaohui_new'?where?id=1;5Query?OK,?1?row?affected?(0.02?sec)6Rows?matched:?1??Changed:?1??Warnings:?078mysql>?commit;9Query?OK,?0?rows?affected?(0.03?sec) 10

Session1執(zhí)行查詢

1select?*?from?test_lock?where?id=1; 2+----+-------------+------+ 3|?id?|?name????????|?type?| 4+----+-------------+------+ 5|??1?|?zhaohui_new?|????1?| 6+----+-------------+------+ 71?row?in?set?(0.04?sec)

Session1中出現(xiàn)了不可重復(fù)讀(NonRepeatable Read)，也就是在查詢的時(shí)候沒有鎖住相關(guān)的數(shù)據(jù)，導(dǎo)致出現(xiàn)了不可重復(fù)讀，但是寫入、修改和刪除數(shù)據(jù)還是加鎖了，如下所示：
Session1更新數(shù)據(jù)

1mysql>?begin; 2Query?OK,?0?rows?affected?(0.01?sec) 3 4mysql>?update?test_lock?set?name='zhaohui_new2'?where?id=1; 5Query?OK,?1?row?affected?(0.01?sec) 6Rows?matched:?1??Changed:?1??Warnings:?0

Session2更新數(shù)據(jù)

1mysql>?begin 2????->?; 3Query?OK,?0?rows?affected?(0.05?sec) 4 5mysql>?update?test_lock?set?name='zhaohui_new3'?where?id=1; 6ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction

Session2更新在更新同一條數(shù)據(jù)的時(shí)候超時(shí)了，在更新數(shù)據(jù)的時(shí)候添加了排他鎖；

可重復(fù)讀

查看和設(shè)置隔離級(jí)別

1mysql>?set?session?transaction?isolation?level?repeatable?read;2Query?OK,?0?rows?affected?(0.07?sec)34mysql>?SELECT?@@tx_isolation;5+-----------------+6|?@@tx_isolation??|7+-----------------+8|?REPEATABLE-READ?|9+-----------------+ 101?row?in?set?(0.05?sec)

模擬多個(gè)事務(wù)交叉執(zhí)行:
Session1執(zhí)行查詢

1mysql>?begin;2Query?OK,?0?rows?affected?(0.03?sec)34mysql>?select?*?from?test_lock?where?type=2;5+----+----------+------+6|?id?|?name?????|?type?|7+----+----------+------+8|??2?|?zhaohui2?|????2?|9+----+----------+------+ 101?row?in?set?(0.02?sec)

Session2更新數(shù)據(jù)

1mysql>?begin; 2Query?OK,?0?rows?affected?(0.05?sec) 3 4mysql>?update?test_lock?set?name='zhaohui2_new'?where?type=2; 5Query?OK,?1?row?affected?(0.03?sec) 6Rows?matched:?1??Changed:?1??Warnings:?0 7 8mysql>?commit; 9Query?OK,?0?rows?affected?(0.06?sec)

Session1執(zhí)行查詢

1mysql>?select?*?from?test_lock?where?type=2; 2+----+----------+------+ 3|?id?|?name?????|?type?| 4+----+----------+------+ 5|??2?|?zhaohui2?|????2?| 6+----+----------+------+ 71?row?in?set?(0.03?sec)

可以發(fā)現(xiàn)2次查詢的數(shù)據(jù)結(jié)果是一樣的，實(shí)現(xiàn)了可重復(fù)讀(Repeatable read)，再來看一下是否有幻讀的問題；

Session3插入數(shù)據(jù)

1mysql>?begin; 2Query?OK,?0?rows?affected?(0.01?sec) 3 4mysql>?insert?into?test_lock(name,type)?values('zhaohui3',2); 5Query?OK,?1?row?affected?(0.02?sec) 6 7mysql>?commit; 8Query?OK,?0?rows?affected?(0.02?sec)

Session1執(zhí)行查詢

1mysql>?select?*?from?test_lock?where?type=2; 2+----+----------+------+ 3|?id?|?name?????|?type?| 4+----+----------+------+ 5|??2?|?zhaohui2?|????2?| 6+----+----------+------+ 71?row?in?set?(0.01?sec)

這里發(fā)現(xiàn)一個(gè)奇怪的現(xiàn)象,按理說應(yīng)該會(huì)出現(xiàn)兩條記錄，但是mysql在重復(fù)讀的隔離級(jí)別下沒有出現(xiàn)幻讀的情況；
? ?我們知道innodb默認(rèn)是行鎖級(jí)別，如何通過悲觀鎖的方式去實(shí)現(xiàn)可重復(fù)讀和不出現(xiàn)幻讀的現(xiàn)象，對(duì)讀取的數(shù)據(jù)加共享鎖，對(duì)同樣的數(shù)據(jù)執(zhí)行更新操作就只能等待，這樣就可以保證可重復(fù)讀，但是對(duì)于不出現(xiàn)幻讀的現(xiàn)象無法通過鎖定行數(shù)據(jù)來解決；
? ?最終看到的現(xiàn)象是沒有幻讀的問題，同時(shí)如果對(duì)讀取的數(shù)據(jù)加共享鎖，更新相同數(shù)據(jù)應(yīng)該會(huì)等待，上面的實(shí)例中并沒有出現(xiàn)等待，所以mysql內(nèi)部應(yīng)該還有其他鎖機(jī)制--MVCC機(jī)制；

共享鎖使用

Session1查詢數(shù)據(jù)

1mysql>?begin;2Query?OK,?0?rows?affected?(0.03?sec)34mysql>?select?*?from?test_lock?where?type=2?lock?in?share?mode;5+----+--------------+------+6|?id?|?name?????????|?type?|7+----+--------------+------+8|??2?|?zhaohui2_new?|????2?|9|??3?|?zhaohui3?????|????2?| 10+----+--------------+------+ 112?rows?in?set?(0.15?sec)

Session2查詢數(shù)據(jù)

1mysql>?begin;2Query?OK,?0?rows?affected?(0.01?sec)34mysql>?select?*?from?test_lock?where?type=2?lock?in?share?mode;5+----+--------------+------+6|?id?|?name?????????|?type?|7+----+--------------+------+8|??2?|?zhaohui2_new?|????2?|9|??3?|?zhaohui3?????|????2?| 10+----+--------------+------+ 112?rows?in?set?(0.05?sec)

Session3 更新數(shù)據(jù)

1mysql>?begin; 2Query?OK,?0?rows?affected?(0.02?sec) 3 4mysql>?update?test_lock?set?name='zhaohui3_new'?where?id=3; 5ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction

Session1和Session2使用了共享鎖，所以可以存在多個(gè)，并不沖突，但是Session3更新操作需要加上排他鎖，和共享鎖不能同時(shí)存在；

排他鎖使用

Session1查詢數(shù)據(jù)

1mysql>?begin;2Query?OK,?0?rows?affected?(0.07?sec)34mysql>?select?*?from?test_lock?where?type=2?for?update;5+----+--------------+------+6|?id?|?name?????????|?type?|7+----+--------------+------+8|??2?|?zhaohui2_new?|????2?|9|??3?|?zhaohui3?????|????2?| 10+----+--------------+------+ 112?rows?in?set?(15.02?sec)

Session2查詢數(shù)據(jù)

1mysql>begin; 2Query?OK,?0?rows?affected?(0.07?sec) 3 4mysql>?select?*?from?test_lock?where?type=2?for?update; 5ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction

排他鎖只能有一個(gè)同時(shí)存在，如果session1獲取了鎖，那么session2將會(huì)等待超時(shí)。

mysql ?MVCC

MVCC 簡(jiǎn)介

MVCC (Multiversion Concurrency Control)，即多版本并發(fā)控制技術(shù),它使得大部分支持行鎖的事務(wù)引擎，不再單純的使用行鎖來進(jìn)行數(shù)據(jù)庫的并發(fā)控制，取而代之的是把數(shù)據(jù)庫的行鎖與行的多個(gè)版本結(jié)合起來，只需要很小的開銷,就可以實(shí)現(xiàn)非鎖定讀，從而大大提高數(shù)據(jù)庫系統(tǒng)的并發(fā)性能

MVCC 實(shí)現(xiàn)

MVCC是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的. 不同存儲(chǔ)引擎的MVCC. 不同存儲(chǔ)引擎的MVCC實(shí)現(xiàn)是不同的,典型的有樂觀并發(fā)控制和悲觀并發(fā)控制.

InnoDB的MVCC,是通過在每行記錄后面保存兩個(gè)隱藏的列來實(shí)現(xiàn)的,這兩個(gè)列，分別保存了這個(gè)行的創(chuàng)建時(shí)間，一個(gè)保存的是行的刪除時(shí)間。這里存儲(chǔ)的并不是實(shí)際的時(shí)間值,而是系統(tǒng)版本號(hào)(可以理解為事務(wù)的ID)，沒開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)就會(huì)自動(dòng)遞增，事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的ID.

innodb存儲(chǔ)的最基本row中包含一些額外的存儲(chǔ)信息 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

6字節(jié)的DATA_TRX_ID 標(biāo)記了最新更新這條行記錄的transaction id，每處理一個(gè)事務(wù)，其值自動(dòng)+1

7字節(jié)的DATA_ROLL_PTR 指向當(dāng)前記錄項(xiàng)的rollback segment的undo log記錄，找之前版本的數(shù)據(jù)就是通過這個(gè)指針

6字節(jié)的DB_ROW_ID，當(dāng)由innodb自動(dòng)產(chǎn)生聚集索引時(shí)，聚集索引包括這個(gè)DB_ROW_ID的值，否則聚集索引中不包括這個(gè)值.，這個(gè)用于索引當(dāng)中

DELETE BIT位用于標(biāo)識(shí)該記錄是否被刪除，這里的不是真正的刪除數(shù)據(jù)，而是標(biāo)志出來的刪除。真正意義的刪除是在commit的時(shí)候

?

?

具體的執(zhí)行過程

begin->用排他鎖鎖定該行->記錄redo log->記錄undo log->修改當(dāng)前行的值，寫事務(wù)編號(hào)，回滾指針指向undo log中的修改前的行

上述過程確切地說是描述了UPDATE的事務(wù)過程，其實(shí)undo log分insert和update undo log，因?yàn)閕nsert時(shí)，原始的數(shù)據(jù)并不存在，所以回滾時(shí)把insert undo log丟棄即可，而update undo log則必須遵守上述過程

下面分別以select、delete、 insert、 update語句來說明：

SELECT

Innodb檢查每行數(shù)據(jù)，確保他們符合兩個(gè)標(biāo)準(zhǔn)：

1、InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行(也就是數(shù)據(jù)行的版本必須小于等于事務(wù)的版本)，這確保當(dāng)前事務(wù)讀取的行都是事務(wù)之前已經(jīng)存在的，或者是由當(dāng)前事務(wù)創(chuàng)建或修改的行

2、行的刪除操作的版本一定是未定義的或者大于當(dāng)前事務(wù)的版本號(hào)，確定了當(dāng)前事務(wù)開始之前，行沒有被刪除

符合了以上兩點(diǎn)則返回查詢結(jié)果。

INSERT

InnoDB為每個(gè)新增行記錄當(dāng)前系統(tǒng)版本號(hào)作為創(chuàng)建ID。

DELETE

InnoDB為每個(gè)刪除行的記錄當(dāng)前系統(tǒng)版本號(hào)作為行的刪除ID。

UPDATE

InnoDB復(fù)制了一行。這個(gè)新行的版本號(hào)使用了系統(tǒng)版本號(hào)。它也把系統(tǒng)版本號(hào)作為了刪除行的版本。

這里簡(jiǎn)單做下總結(jié)：

insert操作時(shí) “創(chuàng)建時(shí)間”=DB_ROW_ID，這時(shí)，“刪除時(shí)間 ”是未定義的；

update時(shí)，復(fù)制新增行的“創(chuàng)建時(shí)間”=DB_ROW_ID，刪除時(shí)間未定義，舊數(shù)據(jù)行“創(chuàng)建時(shí)間”不變，刪除時(shí)間=該事務(wù)的DB_ROW_ID；

delete操作，相應(yīng)數(shù)據(jù)行的“創(chuàng)建時(shí)間”不變，刪除時(shí)間=該事務(wù)的DB_ROW_ID；

select操作對(duì)兩者都不修改，只讀相應(yīng)的數(shù)據(jù)

?

本公眾號(hào)團(tuán)隊(duì)成員由餓了么、阿里、螞蟻金服等同事組成，關(guān)注架構(gòu)師之巔，可以了解最前沿的技術(shù)。

轉(zhuǎn)載于:https://my.oschina.net/u/3207581/blog/1923384

總結(jié)

以上是生活随笔為你收集整理的Mysql 锁的机制的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。