深入理解 MySQL ——鎖、事務(wù)與并發(fā)控制

本文首發(fā)于vivo互聯(lián)網(wǎng)技術(shù)微信公眾號(hào)
作者：張碩
本文對(duì) MySQL 數(shù)據(jù)庫中有關(guān)鎖、事務(wù)及并發(fā)控制的知識(shí)及其原理做了系統(tǒng)化的介紹和總結(jié)，希望幫助讀者能更加深刻地理解 MySQL 中的鎖和事務(wù)，從而在業(yè)務(wù)系統(tǒng)開發(fā)過程中可以更好地優(yōu)化與數(shù)據(jù)庫的交互。

目錄:
1.MySQL 服務(wù)器邏輯架構(gòu)
2.MySQL 鎖
3.事務(wù)
4.隔離級(jí)別
5.并發(fā)控制 與 MVCC
6.MySQL 死鎖問題

1、MySQL 服務(wù)器邏輯架構(gòu)

（圖片來源MySQL官網(wǎng)）
每個(gè)連接都會(huì)在 MySQL 服務(wù)端產(chǎn)生一個(gè)線程（內(nèi)部通過線程池管理線程），比如一個(gè) select 語句進(jìn)入，MySQL 首先會(huì)在查詢緩存中查找是否緩存了這個(gè) select 的結(jié)果集，如果沒有則繼續(xù)執(zhí)行解析、優(yōu)化、執(zhí)行的過程；否則會(huì)之間從緩存中獲取結(jié)果集。

2、MySQL 鎖

2.1、Shared and Exclusive Locks （共享鎖與排他鎖）

它們都是標(biāo)準(zhǔn)的行級(jí)鎖。
共享鎖（S）共享鎖也稱為讀鎖，讀鎖允許多個(gè)連接可以同一時(shí)刻并發(fā)的讀取同一資源,互不干擾；
排他鎖（X）排他鎖也稱為寫鎖，一個(gè)寫鎖會(huì)阻塞其他的寫鎖或讀鎖，保證同一時(shí)刻只有一個(gè)連接可以寫入數(shù)據(jù)，同時(shí)防止其他用戶對(duì)這個(gè)數(shù)據(jù)的讀寫。
注意：所謂共享鎖、排他鎖其實(shí)均是鎖機(jī)制本身的策略，通過這兩種策略對(duì)鎖做了區(qū)分。

2.2、Intention Locks（意向鎖）

InnoDB 支持多粒度鎖(鎖粒度可分為行鎖和表鎖)，允許行鎖和表鎖共存。例如，一個(gè)語句，例如 LOCK TABLES…WRITE 接受指定表上的獨(dú)占鎖。為了實(shí)現(xiàn)多粒度級(jí)別的鎖定，InnoDB 使用了意圖鎖。

意向鎖：表級(jí)別的鎖。先提前聲明一個(gè)意向，并獲取表級(jí)別的意向鎖（共享意向鎖 IS 或排他意向鎖 IX），如果獲取成功，則稍后將要或正在(才被允許)，對(duì)該表的某些行加鎖(S或X)了。（除了 LOCK TABLES ... WRITE,會(huì)鎖住表中所有行，其他場景意向鎖實(shí)際不鎖住任何行)

舉例來說：

SELECT ... LOCK IN SHARE MODE，要獲取IS鎖；An intention shared lock (IS)

SELECT ... FOR UPDATE ，要獲取IX鎖；An intention exclusive lock (IX) i

意向鎖協(xié)議在事務(wù)能夠獲取表中的行上的共享鎖之前，它必須首先獲取表上的IS鎖或更強(qiáng)的鎖。 在事務(wù)能夠獲取表中的行上的獨(dú)占鎖之前，它必須首先獲取表上的IX鎖。

前文說了，意向鎖實(shí)現(xiàn)的背景是多粒度鎖的并存場景。如下兼容性的匯總：

意向鎖僅表意向，是一種較弱的鎖，意向鎖之間兼容并行(IS、IX 之間關(guān)系兼容并行)。 X與IS\IX互斥；S與IX互斥?？梢泽w會(huì)到，意向鎖是比X\S更弱的鎖，存在一種預(yù)判的意義！先獲取更弱的IX\IS鎖，如果獲取失敗就不必要再花費(fèi)跟大開銷獲取更強(qiáng)的X\S鎖 ... ...

2.3、Record Locks (索引行鎖)

record lock 是一個(gè)在索引行記錄的鎖。比如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果c1 上的索引被使用到。防止任何其他事務(wù)變動(dòng) c1 = 10 的行。

record lock 總是會(huì)在索引行上加鎖。即使一個(gè)表并沒有設(shè)置任何索引，這種時(shí)候 innoDB 會(huì)創(chuàng)建一個(gè)隱式的聚集索引（primary Key）,然后在這個(gè)聚集索引上加鎖。

當(dāng)查詢字段沒有索引時(shí)，比如 update table set columnA="A" where columnB=“B".如果 columnB 字段不存在索引（或者不是組合索引前綴），這條語句會(huì)鎖住所有記錄也就是鎖表。如果語句的執(zhí)行能夠執(zhí)行一個(gè) columnB 字段的索引，那么僅會(huì)鎖住滿足 where 的行(RecordLock)。

鎖出現(xiàn)查看示例：

(使用 show engine innodb status 命令查看)：

```范圍查詢

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t

trx id 10078 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000000274f; asc 'O;;

2: len 7; hex b60000019d0110; asc ;;

2.4、Gap locks（間隙鎖）

Gap Locks:鎖定索引記錄之間的間隙([2])，或者鎖定一個(gè)索引記錄之前的間隙([1])，或者鎖定一個(gè)索引記錄之后的間隙([3])。

示例：如圖[1]、[2]、[3]部分。一般作用于我們的范圍篩選查詢> 、< 、between......

例如， SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事務(wù)將值3插入到列 userId 中。因?yàn)樵摲秶鷥?nèi)所有現(xiàn)有值之間的間隙都是鎖定的。

對(duì)于使用唯一索引來搜索唯一行的語句 select a from ，不產(chǎn)生間隙鎖定。(不包含組合唯一索引，也就是說 gapLock 不作用于單列唯一索引）

例如，如果id列有唯一的索引，下面的語句只對(duì)id值為100的行使用索引記錄鎖，其他會(huì)話是否在前一個(gè)間隙中插入行并不重要:

``` SELECT * FROM t1 WHERE id = 100;

```如果id沒有索引或具有非惟一索引，則語句將鎖定前面的間隙。

間隙可以跨越單個(gè)索引值、多個(gè)索引值(如上圖2,3)，甚至是空的。

間隙鎖是性能和并發(fā)性之間權(quán)衡的一種折衷，用于某些特定的事務(wù)隔離級(jí)別，如RC級(jí)別（RC級(jí)別：REPEATABLE READ，我司為了減少死鎖，關(guān)閉了gap鎖，使用RR級(jí)別）。

在重疊的間隙中（或者說重疊的行記錄）中允許gap共存比如同一個(gè) gap 中，允許一個(gè)事務(wù)持有 gap X-Lock（gap 寫鎖\排他鎖)，同時(shí)另一個(gè)事務(wù)在這個(gè) gap 中持有(gap 寫鎖\排他鎖)

CREATE TABLE new_table (

id int(11) NOT NULL AUTO_INCREMENT,

a int(11) DEFAULT NULL,

b varchar(45) DEFAULT NULL,

PRIMARY KEY (id),

KEY idx_new_table_a (a),

KEY idx_new_table_b (b)

) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

INSERT INTO new_table VALUES (1,1,'1'),(2,3,'2'),(3,5,'3'),(4,8,'4'),(5,11,'5'),(6,2,'6'),(7,2,'7'),(8,2,'8'),(9,4,'9'),(10,4,'10');

######## 事務(wù)一 ########

START TRANSACTION;

SELECT * FROM new_table WHERE a between 5 and 8 FOR UPDATE;

##暫不commit

######## 事務(wù)二 ########

SELECT * FROM new_table WHERE a = 4 FOR UPDATE;

##順利執(zhí)行！ 因?yàn)間ap鎖可以共存；

######## 事務(wù)三 ########

SELECT * FROM new_table WHERE b = 3 FOR UPDATE;

##獲取鎖超時(shí)，失敗。因?yàn)槭聞?wù)一的gap鎖定了 b=3的數(shù)據(jù)。

2.5、next-key lock

next-key lock 是 record lock 與 gap lock 的組合。

比如 存在一個(gè)查詢匹配 b=3 的行(b上有個(gè)非唯一索引)，那么所謂 NextLock 就是：在b=3 的行加了 RecordLock 并且使用 GapLock 鎖定了 b=3 之前（“之前”：索引排序）的所有行記錄。

MySQL 查詢時(shí)執(zhí)行 行級(jí)鎖策略，會(huì)對(duì)掃描過程中匹配的行進(jìn)行加鎖（X 或 S），也就是加Record Lock，同時(shí)會(huì)對(duì)這個(gè)記錄之前的所有行加 GapLock 鎖。 假設(shè)一個(gè)索引包含值10、11、13和20。該索引可能的NexKey Lock鎖定以下區(qū)間：

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

另外，值得一提的是 ： innodb 中默認(rèn)隔離級(jí)別(RR)下，next key Lock 自動(dòng)開啟。（很好理解，因?yàn)?gap 作用于RR，如果是 RC，gapLock 不會(huì)生效，那么 next key lock 自然也不會(huì)）

鎖出現(xiàn)查看示例：(使用 show engine innodb status 命令查看)：

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t

trx id 10080 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000000274f; asc 'O;;

2: len 7; hex b60000019d0110; asc ;;

2.6、Insert Intention Locks（插入意向鎖）

一個(gè) insert intention lock 是一種發(fā)生在 insert 插入語句時(shí)的 gap lock 間隙鎖，鎖定插入行之前的所有行。

這個(gè)鎖以這樣一種方式表明插入的意圖，如果插入到同一索引間隙中的多個(gè)事務(wù)沒有插入到該間隙中的相同位置，則它們不需要等待對(duì)方。

假設(shè)存在值為4和7的索引記錄。嘗試分別插入值為5和6的獨(dú)立事務(wù)，在獲得所插入行上的獨(dú)占鎖之前，每個(gè)事務(wù)使用 insert intention lock 鎖定4和7之間的間隙，但不會(huì)阻塞彼此，因?yàn)檫@些行不沖突。

示例：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO child (id) values (90),(102);

##事務(wù)一

mysql> START TRANSACTION;

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

+-----+

| id |

+-----+

| 102 |

+-----+

##事務(wù)二

mysql> START TRANSACTION;

mysql> INSERT INTO child (id) VALUES (101);

##失敗，已被鎖定

mysql> SHOW ENGINE INNODB STATUS

RECORD LOCKS space id 31 page no 3 n bits 72 index PRIMARY of table test.child

trx id 8731 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000066; asc f;;

1: len 6; hex 000000002215; asc " ;;

2: len 7; hex 9000000172011c; asc r ;;...

2.7、 AUTO-INC Locks

AUTO-INC 鎖是一種特殊的表級(jí)鎖，產(chǎn)生于這樣的場景：事務(wù)插入(inserting into )到具有 AUTO_INCREMENT 列的表中。

在最簡單的情況下，如果一個(gè)事務(wù)正在向表中插入值，那么其他任何事務(wù)必須等待向該表中插入它們自己的值，以便由第一個(gè)事務(wù)插入的行接收連續(xù)的主鍵值。

2.8 Predicate Locks for Spatial Indexes 空間索引的謂詞鎖

略

3、事務(wù)

事務(wù)就是一組原子性的 sql，或者說一個(gè)獨(dú)立的工作單元。 事務(wù)就是說，要么 MySQL 引擎會(huì)全部執(zhí)行這一組sql語句，要么全部都不執(zhí)行（比如其中一條語句失敗的話）。

• 自動(dòng)提交（AutoCommit，MySQL 默認(rèn)）

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit關(guān)閉

set autocommit=1; //1表示AutoCommit開啟

MySQL 默認(rèn)采用 AutoCommit 模式，也就是每個(gè) sql 都是一個(gè)事務(wù)，并不需要顯示的執(zhí)行事務(wù)。如果 autoCommit 關(guān)閉，那么每個(gè) sql 都默認(rèn)開啟一個(gè)事務(wù)，只有顯式的執(zhí)行“commit”后這個(gè)事務(wù)才會(huì)被提交。

• 顯示事務(wù) (START TRANSACTION...COMMIT)

比如，tim 要給 bill 轉(zhuǎn)賬100塊錢：

1.檢查 tim 的賬戶余額是否大于100塊；

2.tim 的賬戶減少100塊；

3.bill 的賬戶增加100塊；

這三個(gè)操作就是一個(gè)事務(wù)，必須打包執(zhí)行，要么全部成功， 要么全部不執(zhí)行，其中任何一個(gè)操作的失敗都會(huì)導(dǎo)致所有三個(gè)操作“不執(zhí)行”——回滾。

CREATE DATABASE IF NOT EXISTS employees;

USE employees;

CREATE TABLE employees.account (

id BIGINT (11) NOT NULL AUTO_INCREMENT,

p_name VARCHAR (4),

p_money DECIMAL (10, 2) NOT NULL DEFAULT 0,

PRIMARY KEY (id)

) ;

INSERT INTO employees.account (id, p_name, p_money) VALUES ('1', 'tim', '200');

INSERT INTO employees.account (id, p_name, p_money) VALUES ('2', 'bill', '200');

START TRANSACTION;

SELECT p_money FROM account WHERE p_name="tim";-- step1

UPDATE account SET p_money=p_money-100 WHERE p_name="tim";-- step2

UPDATE account SET p_money=p_money+100 WHERE p_name="bill";-- step3

COMMIT;

一個(gè)良好的事務(wù)系統(tǒng)，必須滿足ACID特點(diǎn)：

3.1、事務(wù)的ACID：

• A:atomiciy 原子性：一個(gè)事務(wù)必須保證其中的操作要么全部執(zhí)行，要么全部回滾，不可能存在只執(zhí)行了一部分這種情況出現(xiàn)。

• C:consistency 一致性：數(shù)據(jù)必須保證從一種一致性的狀態(tài)轉(zhuǎn)換為另一種一致性狀態(tài)。比如上一個(gè)事務(wù)中執(zhí)行了第二步時(shí)系統(tǒng)崩潰了，數(shù)據(jù)也不會(huì)出現(xiàn) bill 的賬戶少了100塊，但是 tim 的賬戶沒變的情況。要么維持原裝（全部回滾），要么 bill 少了100塊同時(shí) tim 多了100塊，只有這兩種一致性狀態(tài)的。

• I：isolation 隔離性：在一個(gè)事務(wù)未執(zhí)行完畢時(shí)，通常會(huì)保證其他 Session 無法看到這個(gè)事務(wù)的執(zhí)行結(jié)果。
  **
• D:durability 持久性：事務(wù)一旦 commit，則數(shù)據(jù)就會(huì)保存下來，即使提交完之后系統(tǒng)崩潰，數(shù)據(jù)也不會(huì)丟失**。

4、隔離級(jí)別

查看系統(tǒng)隔離級(jí)別：

select @@global.tx_isolation;

查看當(dāng)前會(huì)話隔離級(jí)別

select @@tx_isolation;

設(shè)置當(dāng)前會(huì)話隔離級(jí)別

SET session TRANSACTION ISOLATION LEVEL serializable;

設(shè)置全局系統(tǒng)隔離級(jí)別

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4.1、 READ UNCOMMITTED (未提交讀,可臟讀)

事務(wù)中的修改，即使沒有提交，對(duì)其他會(huì)話也是可見的?？梢宰x取未提交的數(shù)據(jù)——臟讀。臟讀會(huì)導(dǎo)致很多問題，一般不適用這個(gè)隔離級(jí)別。 實(shí)例：

-- ------------------------- read-uncommitted實(shí)例 ------------------------------

-- 設(shè)置全局系統(tǒng)隔離級(jí)別

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Session A

START TRANSACTION;

SELECT * FROM USER;

UPDATE USER SET NAME="READ UNCOMMITTED";

-- commit;

-- Session B

SELECT * FROM USER;

//SessionB Console 可以看到Session A未提交的事物處理，在另一個(gè)Session 中也看到了，這就是所謂的臟讀

id name

2 READ UNCOMMITTED

34 READ UNCOMMITTED

4.2、READ COMMITTED (提交讀或不可重復(fù)讀，幻讀)

一般數(shù)據(jù)庫都默認(rèn)使用這個(gè)隔離級(jí)別（MySQL 不是）， 這個(gè)隔離級(jí)別保證了一個(gè)事務(wù)如果沒有完全成功（commit 執(zhí)行完），事務(wù)中的操作對(duì)其他會(huì)話是不可見的。

-- ------------------------- read-cmmitted實(shí)例 ------------------------------

-- 設(shè)置全局系統(tǒng)隔離級(jí)別

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Session A

START TRANSACTION;

SELECT * FROM USER;

UPDATE USER SET NAME="READ COMMITTED";

-- COMMIT;

-- Session B

SELECT * FROM USER;

//Console OUTPUT:

id name

2 READ UNCOMMITTED

34 READ UNCOMMITTED

---

-- 當(dāng) Session A執(zhí)行了commit，Session B得到如下結(jié)果：

id name

2 READ COMMITTED

34 READ COMMITTED

也就驗(yàn)證了read committed 級(jí)別在事物未完成 commit 操作之前修改的數(shù)據(jù)對(duì)其他 Session 不可見，執(zhí)行了 commit 之后才會(huì)對(duì)其他 Session 可見。 我們可以看到 Session B 兩次查詢得到了不同的數(shù)據(jù)。

read committed 隔離級(jí)別解決了臟讀的問題，但是會(huì)對(duì)其他 Session 產(chǎn)生兩次不一致的讀取結(jié)果（因?yàn)榱硪粋€(gè) Session 執(zhí)行了事務(wù)，一致性變化）。

4.3、 REPEATABLE READ (可重復(fù)讀)

一個(gè)事務(wù)中多次執(zhí)行統(tǒng)一讀 SQL,返回結(jié)果一樣。 這個(gè)隔離級(jí)別解決了臟讀的問題，幻讀問題。這里指的是 innodb 的 rr 級(jí)別，innodb 中使用 next-key 鎖對(duì)"當(dāng)前讀"進(jìn)行加鎖，鎖住行以及可能產(chǎn)生幻讀的插入位置，阻止新的數(shù)據(jù)插入產(chǎn)生幻行。 下文中詳細(xì)分析。具體請(qǐng)參考 MySQL 手冊(cè)：

https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

4.4、 SERIALIZABLE (可串行化)

最強(qiáng)的隔離級(jí)別，通過給事務(wù)中每次讀取的行加鎖，寫加寫鎖，保證不產(chǎn)生幻讀問題，但是會(huì)導(dǎo)致大量超時(shí)以及鎖爭用問題。

5、并發(fā)控制 與 MVCC

MVCC (multiple-version-concurrency-control）它是個(gè)行級(jí)鎖的變種， 在普通讀情況下避免了加鎖操作，因此開銷更低。雖然實(shí)現(xiàn)不同，但通常都是實(shí)現(xiàn)非阻塞讀，對(duì)于寫操作只鎖定必要的行。

• 一致性讀 （就是讀取快照）select * from table ....

當(dāng)前讀(就是讀取實(shí)際的持久化的數(shù)據(jù))特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，處理的都是當(dāng)前的數(shù)據(jù)，需要加鎖。 select from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete;

注意：select ...... from where...... （沒有額外加鎖后綴）使用MVCC，保證了讀快照(MySQL 稱為 consistent read)，所謂一致性讀或者讀快照就是讀取當(dāng)前事務(wù)開始之前的數(shù)據(jù)快照，在這個(gè)事務(wù)開始之后的更新不會(huì)被讀到。詳細(xì)情況下文 select 的詳述。

對(duì)于加鎖讀 SELECT with FOR UPDATE (排他鎖) or LOCK IN SHARE MODE (共享鎖)、 update、delete語句，要考慮是否是唯一索引的等值查詢。

INNODB 的 MVCC 通常是通過在每行數(shù)據(jù)后邊保存兩個(gè)隱藏的列來實(shí)現(xiàn)(其實(shí)是三列，第三列是用于事務(wù)回滾，此處略去)，一個(gè)保存了行的創(chuàng)建版本號(hào)，另一個(gè)保存了行的更新版本號(hào)（上一次被更新數(shù)據(jù)的版本號(hào)） 這個(gè)版本號(hào)是每個(gè)事務(wù)的版本號(hào)，遞增的。這樣保證了 innodb 對(duì)讀操作不需要加鎖也能保證正確讀取數(shù)據(jù)。

5.1、MVCC select無鎖操作 與 維護(hù)版本號(hào)

下邊在 MySQL 默認(rèn)的 Repeatable Read 隔離級(jí)別下，具體看看 MVCC 操作：

* Select（快照讀，所謂讀快照就是讀取當(dāng)前事務(wù)之前的數(shù)據(jù)。）：

a.InnoDB 只 select 查找版本號(hào)早于當(dāng)前版本號(hào)的數(shù)據(jù)行，這樣保證了讀取的數(shù)據(jù)要么是在這個(gè)事務(wù)開始之前就已經(jīng) commit 了的（早于當(dāng)前版本號(hào)），要么是在這個(gè)事務(wù)自身中執(zhí)行創(chuàng)建操作的數(shù)據(jù)（等于當(dāng)前版本號(hào)）。

b.查找行的更新版本號(hào)要么未定義，要么大于當(dāng)前的版本號(hào)(為了保證事務(wù)可以讀到老數(shù)據(jù))，這樣保證了事務(wù)讀取到在當(dāng)前事務(wù)開始之后未被更新的數(shù)據(jù)。

注意： 這里的 select 不能有 for update、lock in share 語句。 總之要只返回滿足以下條件的行數(shù)據(jù)，達(dá)到了快照讀的效果：

(行創(chuàng)建版本號(hào)< =當(dāng)前版本號(hào) && (行更新版本號(hào)==null or 行更新版本號(hào)>當(dāng)前版本號(hào) ) )

* Insert InnoDB為這個(gè)事務(wù)中新插入的行，保存當(dāng)前事務(wù)版本號(hào)的行作為行的行創(chuàng)建版本號(hào)。

• Delete InnoDB 為每一個(gè)刪除的行保存當(dāng)前事務(wù)版本號(hào)，作為行的刪除標(biāo)記。

* Update 將存在兩條數(shù)據(jù)，保持當(dāng)前版本號(hào)作為更新后的數(shù)據(jù)的新增版本號(hào)，同時(shí)保存當(dāng)前版本號(hào)作為老數(shù)據(jù)行的更新版本號(hào)。

當(dāng)前版本號(hào)—寫—>新數(shù)據(jù)行創(chuàng)建版本號(hào) && 當(dāng)前版本號(hào)—寫—>老數(shù)據(jù)更新版本號(hào)();

5.2、臟讀 vs 幻讀 vs 不可重復(fù)讀

臟讀：一事務(wù)未提交的中間狀態(tài)的更新數(shù)據(jù) 被其他會(huì)話讀取到。

當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改， 而這種修改還沒有 提交到數(shù)據(jù)庫中(commit 未執(zhí)行)， 這時(shí)，另外會(huì)話也訪問這個(gè)數(shù)據(jù)，因?yàn)檫@個(gè)數(shù)據(jù)是還沒有提交， 那么另外一個(gè)會(huì)話讀到的這個(gè)數(shù)據(jù)是臟數(shù)據(jù)，依據(jù)臟數(shù)據(jù)所做的操作也可能是不正確的。

不可重復(fù)讀：簡單來說就是在一個(gè)事務(wù)中讀取的數(shù)據(jù)可能產(chǎn)生變化，ReadCommitted 也稱為不可重復(fù)讀。

在同一事務(wù)中，多次讀取同一數(shù)據(jù)返回的結(jié)果有所不同。 換句話說就是，后續(xù)讀取可以讀到另一會(huì)話事務(wù)已提交的更新數(shù)據(jù)。 相反，“可重復(fù)讀”在同一事務(wù)中多次讀取數(shù)據(jù)時(shí)，能夠保證所讀數(shù)據(jù)一樣， 也就是，后續(xù)讀取不能讀到另一會(huì)話事務(wù)已提交的更新數(shù)據(jù)。

幻讀：會(huì)話T1事務(wù)中執(zhí)行一次查詢，然后會(huì)話T2新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢的條件。然后T1又使用相同 的查詢?cè)俅螌?duì)表進(jìn)行檢索，但是此時(shí)卻看到了事務(wù)T2剛才插入的新行。這個(gè)新行就稱為“幻像”，因?yàn)閷?duì)T1來說這一行就像突然 出現(xiàn)的一樣。innoDB 的 RR 級(jí)別無法做到完全避免幻讀，下文詳細(xì)分析。

5.3、 如何保證 rr 級(jí)別絕對(duì)不產(chǎn)生幻讀？

在使用的 select ...where 語句中加入 for update (排他鎖) 或者 lock in share mode (共享鎖)語句來實(shí)現(xiàn)。其實(shí)就是鎖住了可能造成幻讀的數(shù)據(jù)，阻止數(shù)據(jù)的寫入操作。

其實(shí)是因?yàn)閿?shù)據(jù)的寫入操作(insert 、update)需要先獲取寫鎖，由于可能產(chǎn)生幻讀的部分，已經(jīng)獲取到了某種鎖，所以要在另外一個(gè)會(huì)話中獲取寫鎖的前提是當(dāng)前會(huì)話中釋放所有因加鎖語句產(chǎn)生的鎖。

5.4、 從另一個(gè)角度看鎖：顯式鎖、隱式鎖

隱式鎖：我們上文說的鎖都屬于不需要額外語句加鎖的隱式鎖。

顯示鎖：

SELECT ... LOCK IN SHARE MODE(加共享鎖);

SELECT ... FOR UPDATE(加排他鎖);

詳情上文已經(jīng)說過。

5.5、查看鎖情況

通過如下 sql 可以查看等待鎖的情況

select * from information_schema.innodb_trx where trx_state="lock wait";

或

show engine innodb status;

6、MySQL 死鎖問題

死鎖，就是產(chǎn)生了循環(huán)等待鏈條，我等待你的資源，你卻等待我的資源，我們都相互等待，誰也不釋放自己占有的資源，導(dǎo)致無線等待下去。 比如：

//Session A

START TRANSACTION;

UPDATE account SET p_money=p_money-100 WHERE p_name="tim";

UPDATE account SET p_money=p_money+100 WHERE p_name="bill";

COMMIT;

//Thread B

START TRANSACTION;

UPDATE account SET p_money=p_money+100 WHERE p_name="bill";

UPDATE account SET p_money=p_money-100 WHERE p_name="tim";

COMMIT;

當(dāng)線程A執(zhí)行到第一條語句UPDATE account SET p_money=p_money-100 WHERE p_name="tim";鎖定了p_name="tim" 的行數(shù)據(jù)；并且試圖獲取p_name="bill" 的數(shù)據(jù)；

此時(shí)，恰好，線程B也執(zhí)行到第一條語句：UPDATE account SET p_money=p_money+100 WHERE p_name="bill";鎖定了 p_name="bill" 的數(shù)據(jù)，同時(shí)試圖獲取p_name="tim" 的數(shù)據(jù)；

此時(shí)，兩個(gè)線程就進(jìn)入了死鎖，誰也無法獲取自己想要獲取的資源，進(jìn)入無線等待中，直到超時(shí)！

innodb_lock_wait_timeout 等待鎖超時(shí)回滾事務(wù)：

直觀方法是在兩個(gè)事務(wù)相互等待時(shí)，當(dāng)一個(gè)等待時(shí)間超過設(shè)置的某一閥值時(shí)，對(duì)其中一個(gè)事務(wù)進(jìn)行回滾，另一個(gè)事務(wù)就能繼續(xù)執(zhí)行。

這種方法簡單有效，在i nnodb 中，參數(shù)innodb_lock_wait_timeout 用來設(shè)置超時(shí)時(shí)間。

wait-for graph 算法來主動(dòng)進(jìn)行死鎖檢測：innodb 還提供了 wait-for graph算法來主動(dòng)進(jìn)行死鎖檢測，每當(dāng)加鎖請(qǐng)求無法立即滿足需要并進(jìn)入等待時(shí)，wait-for graph 算法都會(huì)被觸發(fā)。

6.1、如何盡可能避免死鎖

以固定的順序訪問表和行。比如兩個(gè)更新數(shù)據(jù)的事務(wù)，事務(wù)A 更新數(shù)據(jù)的順序 為1，2；事務(wù)B更新數(shù)據(jù)的順序?yàn)?，1。這樣更可能會(huì)造成死鎖；

大事務(wù)拆小。大事務(wù)更傾向于死鎖，如果業(yè)務(wù)允許，將大事務(wù)拆?。?/p>

在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率；

降低隔離級(jí)別。如果業(yè)務(wù)允許，將隔離級(jí)別調(diào)低也是較好的選擇，比如將隔離級(jí)別從RR調(diào)整為RC，可以避免掉很多因?yàn)間ap鎖造成的死鎖。（我司 MySQL 規(guī)范做法）；

為表添加合理的索引。可以看到如果不走索引將會(huì)為表的每一行記錄添加上鎖，死鎖的概率大大增大。

延伸閱讀：

• MySQL官網(wǎng)參考文檔：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html