innodb事務(wù)

一. 鎖分類(lèi)

lock 用來(lái)鎖定數(shù)據(jù)庫(kù)中的對(duì)象: 如表, 頁(yè), 行; 一般 lock 只在事務(wù)提交或回滾后釋放. 鎖分為共享鎖(S)和排他鎖(X). innoDB支持多粒度鎖同時(shí)存在

1. Locking Read 示例

  • 場(chǎng)景一 (for share):

    • 假設(shè)想要在一個(gè)名叫 child 的表中插入新 row, 前提要確保在名叫 parent 的表中包含一個(gè) parent row. 想要確保引用的完整性, 可以按照如下步驟組織代碼:
    • 首先, 不能使用 noblocking read 讀取 parent row 的方式, 檢查 parent row 是否存在. 因?yàn)槠渌氖聞?wù)可能在你 select 和 insert 操作之間, 把 parent row 刪除了. 而你這個(gè)事務(wù)卻意識(shí)不到; 為了避免這個(gè)問(wèn)題, 要使用 select ... for share 鎖定讀讀取 parent 表中 name = 'jones' 的行
    SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

    在 for share 查詢返回 'jones' 后, 就可以安全的在 child 表中 insert 一個(gè) child 行, 最后提交事務(wù). 任何想要在 parent 表的 'jones' 行上獲取 X 鎖的事務(wù), 都要等待你這個(gè)事務(wù)完成. 換句話說(shuō): 其他事務(wù)要等到所有表到達(dá)一致性后側(cè)能繼續(xù)進(jìn)行.

  • 場(chǎng)景二 (for update):

    • 假設(shè)有一個(gè)計(jì)數(shù)表 CHILD_CODES, 每次從這個(gè)表獲取 id 的最新值作為主鍵插入到 child 表中, 然后讓 CHILD_CODES 表的計(jì)數(shù)值 +1;
    • 這種情況, select ... for share 就不適用了. 因?yàn)閮蓚€(gè)事務(wù)可能在同一時(shí)間讀到相同的值, 從而導(dǎo)致 child 表的主鍵重復(fù). 所以應(yīng)該使用 for update 形式的鎖定讀, 最后在事務(wù)中 update 即可
    SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

2. 讓 Locking Read 并發(fā) - NOWAIT 和 SKIP LOCKED

因?yàn)槭褂?SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 鎖定讀時(shí), 當(dāng)被讀取的 row 被其它事務(wù)加鎖時(shí), 就必須等待獲得所的事務(wù)獲取鎖才能返回, 這種等待有時(shí)并不必要. 我們想讓鎖定讀立刻返回, 或者僅僅是想看看查詢的結(jié)果是否可用. 為了避免等待鎖釋放, 可以在鎖定讀上加上 NOWAIT 和 SKIP LOCKED 選項(xiàng).

  • NOWAIT
    使用 NOWAIT 的鎖定讀不會(huì)等待獲取鎖, 會(huì)立刻返回. 如果被讀的行被鎖定, 則返回異常
  • SKIP LOCKED
    使用 NOWAIT 的鎖定讀不會(huì)等待獲取鎖, 會(huì)立刻返回. 返回的結(jié)果集中, 去除被鎖定的行

注意: 這兩個(gè)選項(xiàng)對(duì)于應(yīng)用來(lái)說(shuō)并不安全. 一個(gè)會(huì)返回異常, 另一個(gè)返回的結(jié)果集和數(shù)據(jù)庫(kù)的狀態(tài)并不一致
使用示例:

# 事務(wù) 1:
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# 事務(wù) 2:

mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# 事務(wù) 3:
mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

15.7.3 不同 sql 加什么鎖

1. 什么操作會(huì)導(dǎo)致加鎖

locking read, UPDATE, 或者 DELETE 操作, 一般會(huì)設(shè)置'記錄鎖' (record locks) 在 sql 語(yǔ)句 scan 到的每個(gè)索引記錄上, 無(wú)論 scan 到的索引記錄是否滿足 where 條件. 因?yàn)?innodb 并不記得 where 條件是什么, 只是知道掃描到的索引范圍. 所謂加鎖, 一般是加 next-key lock, 這個(gè)鎖會(huì)組織 insert 數(shù)據(jù)到記錄之前. 但是
* 可以手動(dòng)關(guān)閉 gapping lock, 從而讓 next-key lock 也失效
* 其次, 事務(wù)隔離級(jí)別也會(huì)影響 sql 語(yǔ)句加什么鎖

2. 索引對(duì)加鎖的影響

如果查詢動(dòng)作中用到了二級(jí)索引, 且在二級(jí)索引上加了 x 鎖, 則還會(huì)對(duì)其對(duì)應(yīng)的聚簇索引加鎖
如果查詢動(dòng)作沒(méi)有找到合適的索引, 則會(huì)導(dǎo)致全表掃描, 且表中的每一行都會(huì)被鎖定. 反過(guò)來(lái)也會(huì)組織所有其他用戶插入到這張表. 因此, 建立合適的索引來(lái)減少掃描的行數(shù)是很重要的

3. 不同 sql 加的不同鎖

  1. SELECT ... FROM

    • 該語(yǔ)句屬于非阻塞的 consistent read. 讀的是數(shù)據(jù)庫(kù)的快照, 且除非隔離級(jí)別為 SERIALIZABLE 否則不加鎖.
    • 對(duì)于 隔離級(jí)別, 對(duì)遇到的索引記錄設(shè)置共享版 next-key lock (s版鎖). 但是如果讀取動(dòng)作使用了唯一索引讀取唯一行, 則只在結(jié)果索引上加記錄鎖

  2. SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE

    • 該語(yǔ)句屬于 locking read, 會(huì)釋放掃描中遇到的不符合條件的行鎖
    • SELECT ... FOR UPDATE 會(huì)阻止其他事務(wù)的 SELECT ... FOR SHARE 鎖定讀取. 非阻塞的 Consistent reads 會(huì)忽略記錄上的鎖

  3. locking read, update ... where 和 delete ... where 操作, 根據(jù)查詢條件是使用唯一索引還是執(zhí)行范圍查找來(lái)決定加不同結(jié)構(gòu)的鎖

    • 查詢條件只有唯一索引: 只加 index lock 鎖, 不在唯一結(jié)果前加 gap 鎖
    • 對(duì)于其他查詢條件: innodb 鎖 scan 到的索引范圍. 對(duì)范圍內(nèi)的每個(gè)索引使用 gap locks 或 next-key locks 來(lái)組織其他事務(wù)插入到索引區(qū)間內(nèi)

  4. INSERT

    • 該操作會(huì)在被查入行上設(shè)置 x 鎖(排它鎖). 該排它鎖是一個(gè) index-record lock 而不是通常的 next-key lock, 就是說(shuō) insert 操作不會(huì)阻止其他事務(wù)在被查入行之前插入數(shù)據(jù)
    • 在加上述 index-record lock 之前, 先要加一個(gè)名叫 insert intention gap lock 的意向插入間隙鎖. 這個(gè)鎖表示, 即使不同事務(wù)要在同一個(gè)間隙內(nèi)插入,只要插入位置不同, 2個(gè)事務(wù)就不用互相等待. 比如: 有2個(gè)索引記錄值4和7, 2個(gè)事務(wù)想分別插入值5和6, 雖然他們的意向插入間隙鎖都鎖的(4,7)區(qū)間, 但因?yàn)楂@取的事不同的 x 鎖 (上述index-record lock), 所以不會(huì)互相阻塞
    • insert 操作會(huì)出現(xiàn)重復(fù)鍵錯(cuò)誤. 當(dāng)兩個(gè)不同的事務(wù)插入同一個(gè)索引值時(shí), 即使事務(wù)不提交, 后續(xù)等待插入的事務(wù)也會(huì)由于插入了相同的值而報(bào)重復(fù)鍵錯(cuò)誤, 然后所有報(bào)錯(cuò)的事務(wù)都會(huì)進(jìn)入隊(duì)列等待獲取一個(gè)該索引的 s 鎖 (共享鎖); 一旦原先的事務(wù)因刪除該索引值或回滾而釋放鎖, 就會(huì)讓隊(duì)列中等待獲取 s 鎖的所有事務(wù)都獲取 s 鎖, 接下來(lái)這些事務(wù)嘗試獲取索引值上的 x 鎖但都無(wú)法獲取成功, 因?yàn)槊總€(gè)事務(wù)都享有索引上的 s 鎖而無(wú)法再獲取 x 鎖, 從而導(dǎo)致死鎖.
    • 示例:
    CREATE TABLE t1（i INT，PRIMARY KEY（i））ENGINE = InnoDB;
# 事務(wù)1: 
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

# 事務(wù)2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

# 事務(wù)3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);

# 事務(wù)1: 
COMMIT;

    第一步: 事務(wù)1 獲取了行上的 x 鎖
    第二步: 事務(wù)2 和 事務(wù)3 都因?yàn)橹貜?fù)鍵錯(cuò)誤進(jìn)入等待隊(duì)列, 等待 VALUES(1) 這行的 s 鎖.
    第三步: 事務(wù)1 提交, 釋放了 VALUES(1) 的 x 鎖. 此時(shí) 事務(wù)2 和 事務(wù)3 獲取了 VALUES(1) 的 s 鎖, 接下來(lái)嘗試獲取 VALUES(1) 的 x 鎖但都無(wú)法成功而進(jìn)入思索狀態(tài), 因?yàn)閷?duì)方都持有 VALUES(1) 的 s 鎖

  5. INSERT ... ON DUPLICATE KEY
    不同于上面的 INSERT 操作, 該操作在發(fā)生重復(fù)鍵錯(cuò)誤時(shí), 會(huì)在 row 上加 x 鎖, 而不是 s 鎖;

    • 如果是主鍵(pk)重復(fù), 則加的是 x 版 index record lock (排它版索引記錄鎖)
    • 如果是唯一鍵(unique key)重復(fù), 則加的是 x 版 next-key lock

  6. REPLACE
    因?yàn)?REPLACE 是根據(jù)唯一鍵來(lái)查找替換, 所以其執(zhí)行類(lèi)似于普通的 INSERT 操作 (先后加上insert intention gap 鎖和index record 鎖); 區(qū)別是如果發(fā)生重復(fù)鍵, 在 REPLACE 操作中表示找到要替換的行, 則在被替換的行上加排他的 next-key 鎖 (x鎖)

  7. INSERT INTO T SELECT ... FROM S WHERE ...

    1. 該語(yǔ)句在要插入表 T 的每一行上設(shè)置 排它版 index record 鎖
    2. 對(duì)于 select 語(yǔ)句部分, 不同隔離級(jí)別會(huì)加不同的鎖
      • 如果是 READ COMMITTED 隔離級(jí)別, 對(duì)表 s 的查找當(dāng)做非阻塞的 consistent read, 所以不加鎖
      • 如果是其他隔離級(jí)別, 則在表 S 的查詢行上加共享版的 next-key 鎖

  8. CREATE TABLE ... SELECT ...語(yǔ)句
    加鎖同 INSERT INTO T SELECT ... FROM S WHERE ...
    `

  9. REPLACE INTO t SELECT ... FROM s WHERE ... 或 UPDATE t ... WHERE col IN (SELECT ... FROM s ...)
    該語(yǔ)句在表 S 上加共享的 next-key 鎖

  10. 自增列
    innodb 會(huì)在自增列的末尾加一個(gè)排它鎖, 當(dāng)該條sql語(yǔ)句執(zhí)行完畢就會(huì)釋放排它鎖, 不用等待事務(wù)完畢再釋放

15.7.4 幽靈行 (Phantom Rows)

1. 什么是幽靈行

2. innoDB 使用 next-key locking 算法解決幻讀

3. 為什么 next-key lock 可以做唯一性檢查

# 分別測(cè)試, a 是索引, a不是索引, a是主鍵的情況, 體會(huì) next-key lock , 全表掃描 lock, 和唯一鍵 lock index record
create table t(a int,key idx_a(a));

root@localhost : test 10:56:13>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@localhost : test 10:56:15>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
+------+
5 rows in set (0.00 sec)

section A:

root@localhost : test 10:56:27>start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 10:56:29>select * from t where a = 8 for update;
+------+
| a    |
+------+
|    8 |
+------+
1 row in set (0.00 sec)


section B:
root@localhost : test 10:54:50>begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 10:56:51>select * from t;
+------+
| a    |
+------+
|    1 |
|    3 |
|    5 |
|    8 |
|   11 |
+------+
5 rows in set (0.00 sec)

root@localhost : test 10:56:54>insert into t values(2);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:57:01>insert into t values(4);
Query OK, 1 row affected (0.00 sec)

++++++++++
root@localhost : test 10:57:04>insert into t values(6);

root@localhost : test 10:57:11>insert into t values(7);

root@localhost : test 10:57:15>insert into t values(9);

root@localhost : test 10:57:33>insert into t values(10);
++++++++++
上面全被鎖住，阻塞住了

root@localhost : test 10:57:33>insert into t values(8); 是否會(huì)阻塞?? (唯一性檢查)

READ COMMITED: 一個(gè)事務(wù)內(nèi)讀到了其他事物提交的結(jié)果, 導(dǎo)致2次讀結(jié)果不同, 為不可重復(fù)讀

-- 普通字段版, 放棄對(duì)不符合where條件的行的鎖
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

-- Session A
START TRANSACTION;
-- UPDATE t SET b = 5 WHERE b = 3;
SELECT * FROM t

-- Session B
START TRANSACTION;
UPDATE t SET b = 4 WHERE b = 2;

-- Session A
SELECT * FROM t -- 與上次結(jié)果不一致, 不可重復(fù)讀

-- where條件包含索引字段, 則只有索引字段作為是否放棄 lock 的依據(jù)
CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

-- Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

-- Session B 
START TRANSACTION;
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;  -- 阻塞

https://www.cnblogs.com/rjzheng/p/9950951.html 什么隔離級(jí)別和什么查詢條件配合 , 決定加什么鎖

15.7.5 innoDB 處理死鎖

死鎖就是事務(wù)無(wú)法再記性下去. 因?yàn)槊總€(gè)事務(wù)都持有對(duì)方希望獲得的鎖, 只能互相等待

  1. 死鎖示例
-- 表準(zhǔn)備
CREATE TABLE t (i INT)
INSERT INTO t (i) VALUES(1);

-- 事務(wù)1 
START TRANSACTION;
SELECT * FROM t WHERE i = 1 FOR SHARE;  -- s鎖 i = 1

-- 事務(wù)2
START TRANSACTION;
DELETE FROM t WHERE i = 1;    -- 獲取 x 鎖, 但阻塞執(zhí)行, 因?yàn)槭聞?wù)1 的 s鎖不兼容

-- 事務(wù)1 
DELETE FROM t WHERE i = 1;   -- 此時(shí)發(fā)生死鎖, 事務(wù)1無(wú)法獲取x鎖. x鎖在事務(wù)1上
  1. 如何解決innoDB死鎖?
    • (1) 使用 SHOW ENGINE INNODB STATUS 命令查看給出的死鎖原因
    • (2) 如果 innoDB 頻繁發(fā)生死鎖, 可以打開(kāi) innodb_print_all_deadlocks 設(shè)置打印所有發(fā)生的死鎖日志. 調(diào)試完畢再把該選項(xiàng)關(guān)閉
    • (3) 如果死鎖導(dǎo)致事務(wù)失敗, 重啟事務(wù)就好
    • (4) 確保事務(wù)足夠小, 足夠短, 從而減少死鎖發(fā)生的可能性. 盡早提交事務(wù)
    • (5) 給表增加合適的索引, 讓 query 掃描更少的索引記錄從而加更少的鎖 (比如RR隔離級(jí)別下的 next-key lock)
    • (6) 如果允許 select 的結(jié)果集是舊版的快照數(shù)據(jù), 就不要加 FOR UPDATE 或 FOR SHARE 進(jìn)行鎖定讀. 這種情形下使用 READ COMMITTED 隔離級(jí)別也是可以的, 讓 select 每次拉取新快照
    • (7) 如果上述方法都沒(méi)用, 直接加表級(jí)鎖, 防止多個(gè)事務(wù)同時(shí)更新表 
      SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
    • (8) 最后一個(gè)辦法是增加一個(gè)只有一行的 semaphore 表, 讓所有事務(wù)序列化的進(jìn)行. 每次事務(wù)訪問(wèn)其他表前先更新這個(gè)表
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 事務(wù) InnoDB事務(wù)模型目標(biāo)：將 多版本數(shù)據(jù)庫(kù) 的特性與傳統(tǒng)的 兩階段鎖定 相結(jié)合 事務(wù)隔離級(jí)別（Transac...
    段一萌閱讀 242評(píng)論 0贊 0
  • 事務(wù)隔離級(jí)別 事務(wù)隔離是數(shù)據(jù)庫(kù)處理的基礎(chǔ)之一。隔離是I的首字母縮寫(xiě)ACID;隔離級(jí)別是在多個(gè)事務(wù)進(jìn)行更改并同時(shí)執(zhí)行...
    wwq2020閱讀 686評(píng)論 0贊 0
  • 1、事務(wù)四大特性：ACID Atomicity 原子性：事務(wù)的操作要么一起成功，要么一起失?。?Consisten...
    smartmhs閱讀 257評(píng)論 0贊 0
  • 今天感恩節(jié)哎，感謝一直在我身邊的親朋好友。感恩相遇！感恩不離不棄。 中午開(kāi)了第一次的黨會(huì)，身份的轉(zhuǎn)變要...
    余生動(dòng)聽(tīng)閱讀 10,798評(píng)論 0贊 11
  • 彩排完，天已黑
    劉凱書(shū)法閱讀 4,452評(píng)論 1贊 3

友情鏈接更多精彩內(nèi)容