歡迎光臨
每天分享高質量文章

深入理解 MySQL—鎖、事務與併發控制

本文轉載自“vivo 互聯網技術”,已獲授權。

本文對 MySQL 資料庫中有關鎖、事務及併發控制的知識及其原理做了系統化的介紹和總結,希望幫助讀者能更加深刻地理解 MySQL 中的鎖和事務,從而在業務系統開發過程中可以更好地優化與資料庫的交互。

1. MySQL 服務器邏輯架構


(圖片來源MySQL官網)

每個連接都會在 MySQL 服務端產生一個執行緒(內部通過執行緒池管理執行緒),比如一個 select 陳述句進入,MySQL 首先會在查詢快取中查找是否快取了這個 select 的結果集,如果沒有則繼續執行解析、優化、執行的過程;否則會之間從快取中獲取結果集。

2. MySQL鎖


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

它們都是標準的行級鎖

  • 共享鎖(S) 共享鎖也稱為讀鎖,讀鎖允許多個連接可以同一時刻併發的讀取同一資源,互不干擾;

  • 排他鎖(X) 排他鎖也稱為寫鎖,一個寫鎖會阻塞其他的寫鎖或讀鎖,保證同一時刻只有一個連接可以寫入資料,同時防止其他用戶對這個資料的讀寫。

註意:所謂共享鎖、排他鎖其實均是鎖機制本身的策略,通過這兩種策略對鎖做了區分。

2.2、Intention Locks(意向鎖)

InnoDB 支持多粒度鎖(鎖粒度可分為行鎖和表鎖),允許行鎖和表鎖共存。例如,一個陳述句,例如 LOCK TABLES…WRITE 接受指定表上的獨占鎖。為了實現多粒度級別的鎖定,InnoDB 使用了意圖鎖。

意向鎖:表級別的鎖。先提前宣告一個意向,並獲取表級別的意向鎖(共享意向鎖 IS 或排他意向鎖 IX),如果獲取成功,則稍後將要或正在(才被允許),對該表的某些行加鎖(S或X)了。(除了 LOCK TABLES … WRITE,會鎖住表中所有行,其他場景意向鎖實際不鎖住任何行)

舉例來說:

SELECT … LOCK IN SHARE MODE,要獲取IS鎖;An intention shared lock (IS)

SELECT … FOR UPDATE ,要獲取IX鎖;An intention exclusive lock (IX) i

意向鎖協議 在事務能夠獲取表中的行上的共享鎖之前,它必須首先獲取表上的IS鎖或更強的鎖。在事務能夠獲取表中的行上的獨占鎖之前,它必須首先獲取表上的IX鎖。

前文說了,意向鎖實現的背景是多粒度鎖的並存場景。如下兼容性的彙總:

意向鎖僅表意向,是一種較弱的鎖,意向鎖之間兼容並行(IS、IX 之間關係兼容並行)。X與IS\IX互斥;S與IX互斥。可以體會到,意向鎖是比X\S更弱的鎖,存在一種預判的意義!先獲取更弱的IX\IS鎖,如果獲取失敗就不必要再花費跟大開銷獲取更強的X\S鎖 … …

2.3、Record Locks (索引行鎖)

record lock 是一個在索引行記錄的鎖。

比如,SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE,如果c1 上的索引被使用到。防止任何其他事務變動 c1 = 10 的行。

record lock 總是會在索引行上加鎖。即使一個表並沒有設置任何索引,這種時候 innoDB 會創建一個隱式的聚集索引(primary Key),然後在這個聚集索引上加鎖。

當查詢欄位沒有索引時,比如 update table set columnA=”A” where columnB=“B”.如果 columnB 欄位不存在索引(或者不是組合索引前綴),這條陳述句會鎖住所有記錄也就是鎖表。如果陳述句的執行能夠執行一個 columnB 欄位的索引,那麼僅會鎖住滿足 where 的行(RecordLock)。

鎖出現查看示例:

(使用 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
 0len 4hex 8000000a; asc     ;;
 1len 6hex 00000000274f; asc     'O;;
 2len 7hex b60000019d0110; asc        ;;

2.4、Gap locks(間隙鎖)

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

示例:如圖[1]、[2]、[3]部分。一般作用於我們的範圍篩選查詢> 、< 、between……

例如, SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事務將值3插入到列 userId 中。因為該範圍內所有現有值之間的間隙都是鎖定的。

  • 對於使用唯一索引來搜索唯一行的陳述句 select a from ,不產生間隙鎖定(不包含組合唯一索引,也就是說 gapLock 不作用於單列唯一索引)

例如,如果id列有唯一的索引,下麵的陳述句只對id值為100的行使用索引記錄鎖,其他會話是否在前一個間隙中插入行並不重要:

“`
SELECT * FROM t1 WHERE id = 100;

“`如果id**沒有索引或具有非惟一索引,則陳述句將鎖定前面的間隙**。

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

  • 間隙鎖是性能和併發性之間權衡的一種折衷,用於某些特定的事務隔離級別,如RC級別(RC級別:REPEATABLE READ,我司為了減少死鎖,關閉了gap鎖,使用RR級別)。

  • 在重疊的間隙中(或者說重疊的行記錄)中允許gap共存

    比如同一個 gap 中,允許一個事務持有 gap X-Lock(gap 寫鎖\排他鎖),同時另一個事務在這個 gap 中持有(gap 寫鎖\排他鎖)

CREATE TABLE `new_table` (
  `id` int(11NOT NULL AUTO_INCREMENT,
  `a` int(11DEFAULT NULL,
  `b` varchar(45DEFAULT 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');



######## 事務一 ########
START TRANSACTION;
SELECT * FROM new_table WHERE a between 5 and 8 FOR UPDATE;
##暫不commit

######## 事務二 ########

SELECT * FROM new_table WHERE a = 4 FOR UPDATE;

##順利執行!因為gap鎖可以共存;


######## 事務三 ########

 SELECT * FROM new_table WHERE b = 3 FOR UPDATE;

##獲取鎖超時,失敗。因為事務一的gap鎖定了 b=3的資料。

2.5、next-key lock

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

比如 存在一個查詢匹配 b=3 的行(b上有個非唯一索引),那麼所謂 NextLock 就是:在b=3 的行加了 RecordLock 並且使用 GapLock 鎖定了 b=3 之前(“之前”:索引排序)的所有行記錄。

MySQL 查詢時執行 行級鎖策略,會對掃描過程中匹配的行進行加鎖(X 或 S),也就是加Record Lock,同時會對這個記錄之前的所有行加 GapLock 鎖。假設一個索引包含值10、11、13和20。該索引可能的NexKey Lock鎖定以下區間:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

另外,值得一提的是 :innodb 中預設隔離級別(RR)下,next key Lock 自動開啟。 (很好理解,因為 gap 作用於RR,如果是 RC,gapLock 不會生效,那麼 next key lock 自然也不會)

鎖出現查看示例: (使用 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
 0len 8hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0len 4hex 8000000a; asc     ;;
 1len 6hex 00000000274f; asc     'O;;
 2len 7hex b60000019d0110; asc        ;;

2.6、Insert Intention Locks(插入意向鎖)

一個 insert intention lock 是一種發生在 insert 插入陳述句時的 gap lock 間隙鎖,鎖定插入行之前的所有行。

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

假設存在值為4和7的索引記錄。嘗試分別插入值為5和6的獨立事務,在獲得所插入行上的獨占鎖之前,每個事務使用 insert intention lock 鎖定4和7之間的間隙,但不會阻塞彼此,因為這些行不衝突。

示例:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

##事務一
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
id  |
+-----+
102 |
+-----+

##事務二

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 4hex 80000066; asc    f;;
 1: len 6hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

2.7、 AUTO-INC Locks

AUTO-INC 鎖是一種特殊的表級鎖,產生於這樣的場景:事務插入(inserting into )到具有 AUTO_INCREMENT 列的表中。

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

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

3. 事務


事務就是一組原子性的 sql,或者說一個獨立的工作單元。事務就是說,要麼 MySQL 引擎會全部執行這一組sql陳述句,要麼全部都不執行(比如其中一條陳述句失敗的話)。

  • 自動提交(AutoCommit,MySQL 預設)

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit關閉
set autocommit=1; //1表示AutoCommit開啟

MySQL 預設採用 AutoCommit 樣式,也就是每個 sql 都是一個事務,並不需要顯示的執行事務。如果 autoCommit 關閉,那麼每個 sql 都預設開啟一個事務,只有顯式的執行“commit”後這個事務才會被提交。

  • 示事務 (START TRANSACTION…COMMIT)

比如,tim 要給 bill 轉賬100塊錢:

1.檢查 tim 的賬戶餘額是否大於100塊;

2.tim 的賬戶減少100塊;

3.bill 的賬戶增加100塊;

這三個操作就是一個事務,必須打包執行,要麼全部成功, 要麼全部不執行,其中任何一個操作的失敗都會導致所有三個操作“不執行”——回滾。

CREATE DATABASE IF NOT EXISTS employees;
USE employees;

CREATE TABLE `employees`.`account` (
  `id` BIGINT (11NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (102NOT 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;

一個良好的事務系統,必須滿足ACID特點:

3.1、事務的ACID:

  • A:atomiciy 原子性:一個事務必須保證其中的操作要麼全部執行,要麼全部回滾,不可能存在只執行了一部分這種情況出現。

  • C:consistency 一致性:資料必須保證從一種一致性的狀態轉換為另一種一致性狀態。 比如上一個事務中執行了第二步時系統崩潰了,資料也不會出現 bill 的賬戶少了100塊,但是 tim 的賬戶沒變的情況。要麼維持原裝(全部回滾),要麼 bill 少了100塊同時 tim 多了100塊,只有這兩種一致性狀態的。

  • I:isolation 隔離性:在一個事務未執行完畢時,通常會保證其他 Session 無法看到這個事務的執行結果。

  • D:durability 持久性:事務一旦 commit,則資料就會儲存下來,即使提交完之後系統崩潰,資料也不會丟失。

4. 隔離級別


查看系統隔離級別:
select @@global.tx_isolation;
查看當前會話隔離級別
select @@tx_isolation;
設置當前會話隔離級別
SET session TRANSACTION ISOLATION LEVEL serializable;
設置全域性系統隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

事務中的修改,即使沒有提交,對其他會話也是可見的。可以讀取未提交的資料——臟讀臟讀會導致很多問題,一般不適用這個隔離級別。實體:

-- ------------------------- read-uncommitted實體 ------------------------------
-- 設置全域性系統隔離級別
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未提交的事物處理,在另一個Session 中也看到了,這就是所謂的臟讀
id    name
2    READ UNCOMMITTED
34    READ UNCOMMITTED

4.2、READ COMMITTED (提交讀或不可重覆讀,幻讀)

一般資料庫都預設使用這個隔離級別(MySQL 不是), 這個隔離級別保證了一個事務如果沒有完全成功(commit 執行完),事務中的操作對其他會話是不可見的。

-- ------------------------- read-cmmitted實體 ------------------------------
-- 設置全域性系統隔離級別
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


---------------------------------------------------
-- 當 Session A執行了commit,Session B得到如下結果:
id    name
2    READ COMMITTED
34    READ COMMITTED

也就驗證了 read committed 級別在事物未完成 commit 操作之前修改的資料對其他 Session 不可見,執行了 commit 之後才會對其他 Session 可見。我們可以看到 Session B 兩次查詢得到了不同的資料

read committed 隔離級別解決了臟讀的問題,但是會對其他 Session 產生兩次不一致的讀取結果(因為另一個 Session 執行了事務,一致性變化)。

4.3、 REPEATABLE READ (可重覆讀)

一個事務中多次執行統一讀 SQL,傳回結果一樣。這個隔離級別解決了臟讀的問題,幻讀問題。這裡指的是 innodb 的 rr 級別,innodb 中使用 next-key 鎖對”當前讀”進行加鎖,鎖住行以及可能產生幻讀的插入位置,阻止新的資料插入產生幻行。下文中詳細分析。具體請參考 MySQL 手冊:

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

4.4、 SERIALIZABLE (可串行化)

最強的隔離級別,通過給事務中每次讀取的行加鎖,寫加寫鎖,保證不產生幻讀問題,但是會導致大量超時以及鎖爭用問題。

5. 併發控制 與 MVCC


 

MVCC (multiple-version-concurrency-control)

它是個行級鎖的變種, 在普通讀情況下避免了加鎖操作,因此開銷更低。雖然實現不同,但通常都是實現非阻塞讀對於寫操作只鎖定必要的行。

  • 一致性讀 (就是讀取快照)select * from table ….

  • 當前讀(就是讀取實際的持久化的資料)特殊的讀操作,插入/更新/刪除操作,屬於當前讀,處理的都是當前的資料,需要加鎖。select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete;

註意:select …… from where…… (沒有額外加鎖後綴)使用MVCC,保證了讀快照(MySQL 稱為 consistent read),所謂一致性讀或者讀快照就是讀取當前事務開始之前的資料快照,在這個事務開始之後的更新不會被讀到。詳細情況下文 select 的詳述。

對於加鎖讀 SELECT with FOR UPDATE (排他鎖) or LOCK IN SHARE MODE (享鎖)、 update、delete陳述句,要考慮是否是唯一索引的等值查詢。

INNODB 的 MVCC 通常是通過在每行資料後邊儲存兩個隱藏的列來實現(其實是三列,第三列是用於事務回滾,此處略去),一個儲存了行的創建版本號,另一個儲存了行的更新版本號(上一次被更新資料的版本號) 這個版本號是每個事務的版本號,遞增的。這樣保證了 innodb 對讀操作不需要加鎖也能保證正確讀取資料。

5.1、MVCC select無鎖操作 與 維護版本號

下邊在 MySQL 預設的 Repeatable Read 隔離級別下,具體看看 MVCC 操作:

  • Select(快照讀,所謂讀快照就是讀取當前事務之前的資料。):

    a.InnoDB 只 select 查找版本號早於當前版本號的資料行這樣保證了讀取的資料要麼是在這個事務開始之前就已經 commit 了的(早於當前版本號),要麼是在這個事務自身中執行創建操作的資料(等於當前版本號)。

    b.查找行的更新版本號要麼未定義,要麼大於當前的版本號(為了保證事務可以讀到老資料),這樣保證了事務讀取到在當前事務開始之後未被更新的資料。

    註意:這裡的 select 不能有 for update、lock in share 陳述句。總之要只傳回滿足以下條件的行資料,達到了快照讀的效果:

(行創建版本號當前版本號 && (行更新版本號==null or 行更新版本號>當前版本號 ) )

  • Insert

    InnoDB為這個事務中新插入的行,儲存當前事務版本號的行作為行的行創建版本號。

  • Delete InnoDB 為每一個刪除的行儲存當前事務版本號,作為行的刪除標記。

  • Update

    將存在兩條資料,保持當前版本號作為更新後的資料的新增版本號,同時儲存當前版本號作為老資料行的更新版本號。

當前版本號—寫—>新資料行創建版本號 && 當前版本號—寫—>老資料更新版本號();

5.2、臟讀 vs 幻讀 vs 不可重覆讀

臟讀:一事務未提交的中間狀態的更新資料 被其他會話讀取到。

當一個事務正在訪問資料,並且對資料進行了修改, 而這種修改還沒有 提交到資料庫中(commit 未執行), 這時,另外會話也訪問這個資料,因為這個資料是還沒有提交, 那麼另外一個會話讀到的這個資料是臟資料,依據臟資料所做的操作也可能是不正確的。

不可重覆讀:簡單來說就是在一個事務中讀取的資料可能產生變化,ReadCommitted 也稱為不可重覆讀。

在同一事務中,多次讀取同一資料傳回的結果有所不同。換句話說就是,後續讀取可以讀到另一會話事務已提交的更新資料。相反,“可重覆讀”在同一事務中多次讀取資料時,能夠保證所讀資料一樣, 也就是,後續讀取不能讀到另一會話事務已提交的更新資料。

幻讀:會話T1事務中執行一次查詢,然後會話T2新插入一行記錄,這行記錄恰好可以滿足T1所使用的查詢的條件。然後T1又使用相同 的查詢再次對錶進行檢索,但是此時卻看到了事務T2剛纔插入的新行。這個新行就稱為“幻像”,因為對T1來說這一行就像突然 出現的一樣。innoDB 的 RR 級別無法做到完全避免幻讀,下文詳細分析。

5.3、 如何保證 rr 級別絕對不產生幻讀?

在使用的 select …where 陳述句中加入 for update (排他鎖) 或者 lock in share mode (共享鎖)陳述句來實現。其實就是鎖住了可能造成幻讀的資料,阻止資料的寫入操作。

其實是因為資料的寫入操作(insert 、update)需要先獲取寫鎖,由於可能產生幻讀的部分,已經獲取到了某種鎖,所以要在另外一個會話中獲取寫鎖的前提是當前會話中釋放所有因加鎖陳述句產生的鎖。

5.4、 從另一個角度看鎖:顯式鎖、隱式鎖

隱式鎖我們上文說的鎖都屬於不需要額外陳述句加鎖的隱式鎖。

顯示鎖:

SELECT ... LOCK IN SHARE MODE(加共享鎖);
SELECT ... FOR UPDATE(加排他鎖);

詳情上文已經說過。

5.5、查看鎖情況

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

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

show engine innodb status;

6、MySQL 死鎖問題

死鎖,就是產生了迴圈等待鏈條,我等待你的資源,你卻等待我的資源,我們都相互等待,誰也不釋放自己占有的資源,導致無線等待下去。比如:

//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;

當執行緒A執行到第一條陳述句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;鎖定了p_name=”tim” 的行資料;並且試圖獲取 p_name=”bill” 的資料;

此時,恰好,執行緒B也執行到第一條陳述句:UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;鎖定了 p_name=”bill” 的資料,同時試圖獲取 p_name=”tim” 的資料;

此時,兩個執行緒就進入了死鎖,誰也無法獲取自己想要獲取的資源,進入無線等待中,直到超時!

innodb_lock_wait_timeout  等待鎖超時回滾事務:

直觀方法是在兩個事務相互等待時,當一個等待時間超過設置的某一閥值時,對其中一個事務進行回滾,另一個事務就能繼續執行。

這種方法簡單有效,在i nnodb 中,引數 innodb_lock_wait_timeout 用來設置超時時間。

wait-for graph 演算法來主動進行死鎖檢測:innodb 還提供了 wait-for graph 演算法來主動進行死鎖檢測,每當加鎖請求無法立即滿足需要併進入等待時,wait-for graph 演算法都會被觸發。

6.1、如何盡可能避免死鎖

  • 以固定的順序訪問表和行。比如兩個更新資料的事務,事務A 更新資料的順序 為1,2;事務B更新資料的順序為2,1。這樣更可能會造成死鎖;

  • 大事務拆小。大事務更傾向於死鎖,如果業務允許,將大事務拆小;

  • 在同一個事務中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率;

  • 降低隔離級別。如果業務允許,將隔離級別調低也是較好的選擇,比如將隔離級別從RR調整為RC,可以避免掉很多因為gap鎖造成的死鎖。(我司 MySQL 規範做法);

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

    已同步到看一看
    赞(0)

    分享創造快樂