LBCC (Lock Base Concurrency Control) 基於鎖的併發控制
※英文文件、中文文件
※行鎖鎖的是索引，沒索引會變成表鎖，有 PK 會自動有主鍵索引
※自動提交
SET AUTOCOMMIT=0; # 關閉自動提交
show variables like 'autocommit';
※關閉不可寫的指令
SET SQL_SAFE_UPDATES=0; # 這樣才可以新增、修改、刪除
show variables like 'sql_safe_updates';
※開啟事務
START TRANSACTION 或 BEGIN
※回滾和提交
ROLBACK、COMMIT
※隔離等級
# READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE
mysql 預設是 REPEATABLE-READ
set session transaction_isolation='REPEATABLE-READ'; # 不寫 session 也行，預設就是 session
set global transaction_isolation='REPEATABLE-READ';
SELECT @@global.transaction_isolation;
SELECT @@session.transaction_isolation;
●兩個 session 隔離等級不同，如 A 是 READ-UNCOMMITTED，B 是 READ-COMMITTED，以自己設定的為準，A 讀 B 的會有髒資料；相反則不會
紅框為同一個 session，如果改了活頁籤其中一個，同一個 session 都會改到；綠框為不同 session，mysql workbench 要在圖的下方再按一次就會出現
※準備測試資料
CREATE TABLE `bruce_tbl` (
`id` int DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
KEY `id_index` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO bruce_test.bruce_tbl (`id`,`name`) VALUES (1,'abc');
INSERT INTO bruce_test.bruce_tbl (`id`,`name`) VALUES (5,'bbb');
INSERT INTO bruce_test.bruce_tbl (`id`,`name`) VALUES (9,'ccc');
INSERT INTO bruce_test.bruce_tbl (`id`,`name`) VALUES (11,'ddd');
以下的測試是用 8.0.23 版
以下的測試都是用鎖的，如 selec * from table 是沒有鎖的，不管怎樣都可以執行的，不在這裡說明的範圍
●以下的測試第一次都要先 SET AUTOCOMMIT=0; 而且還要執行 start transaction 或 begin 再執行以下要測試的語句，然後都測試完後，再 rollback 或 commit
※exclusive locks 排他鎖、shared locks 共享鎖
排他鎖：在 select 最後加 for update、增刪改
共享鎖：在 select 最後加 for share 或 lock in share mode，但後者還可以有更多選項，如會阻塞就直接返回
排他鎖只能一個，一個 sql 取得了排他鎖，其他的鎖都會阻塞，要查的出資料才有鎖
共享鎖可以多個，但不能有排他鎖，要查的出資料才有鎖
測試排他鎖：
A session：
SELECT * FROM bruce_test.bruce_tbl where id = 1 for update; # 執行到這裡先停，然後執行 B session
B session：
SELECT * FROM bruce_test.bruce_tbl where id = 1 for update; # 會阻塞
SELECT * FROM bruce_test.bruce_tbl where id = 5 for update; # 不會阻塞
# 資料庫的 id 有四筆，1 5 9 11，如果不是這四個值也不會阻塞
測試共享鎖：
A session：
SELECT * FROM bruce_test.bruce_tbl where id = 1 for share;
B session：
SELECT * FROM bruce_test.bruce_tbl where id = 1 for share; # 不會阻塞
SELECT * FROM bruce_test.bruce_tbl where id = 1 for update; # 會阻塞
※Intentioin locks 意向鎖
分成意向共享鎖和意向排他鎖，都是表鎖，無法人工增加，是 mysql 自動幫我們加上的
事務要取得排他鎖前要先取得意向鎖排他鎖；事務要取得排他鎖前要先取得意向共享鎖
意向鎖目的是為了加快效能，如某一行被 for update，就表示有意向排他鎖和排他鎖，另一個 session 想鎖這張表 (lock tables talble_name read) 會發現有意向排他鎖，就知道有其中一行被鎖了，這樣就不用一行一行去找哪一行被鎖了
這兩個鎖都可以多個而且互相都是相容的
以下是官網截出來的表，IX 表示意向排他鎖；IS 表示意向共享鎖，指的都是表鎖，共享和排他的例子鎖的是行，mysql 是可以同時有表鎖和行鎖的
首先看直的，排他鎖只能有一個，所以全部都是 confilict
再來是共享鎖，可以多個共享鎖和意向共享鎖，其他有排他的都不行
意向共享除了排他外都是可以的
意向排他只能和意向排他和意向共享，其他都不行，連共享鎖也不行
※record locks 記錄鎖
測試方法同測試排他鎖
※gap locks 間隙鎖
隔離等級為 RR 才有用，範圍條件裡沒有命中記錄時為間隙鎖，不能新增記錄，但可以用排他鎖查詢，因為都是沒有記錄的範圍
● A 是 RR，B 是 RC，A 的範圍沒有記錄，會有間隙鎖，這時 B 想新增範圍裡的資料會等待
● 資料庫的 id 是 1 5 9 11
測試一 A session：
SELECT * FROM bruce_test.bruce_tbl where id > 5 and id < 9 for update; # 這個範圍裡資料庫沒有資料才能叫間隙鎖
測試一 B session：
SELECT * FROM bruce_test.bruce_tbl where id = 8 for update;
# 主鍵索引：都沒鎖
# 唯一索引：5不會鎖，鎖的是 9
# 普通索引：5不會鎖，鎖的是 9
insert into bruce_test.bruce_tbl (id, name) values (5, 'xxx');
# 主鍵索引：鎖的是 6~8，也就是 where 的範圍
# 唯一索引：鎖的是 6~9
# 普通索引：鎖的是 5~8
測試二 A session：
SELECT * FROM bruce_test.bruce_tbl where id > 15 for update;
測試二 B session：
insert into bruce_test.bruce_tbl (id, name) values (11, 'xxx');
# 主鍵索引：鎖 12(含)之後
# 唯一索引：鎖 12(含)之後
# 普通索引：鎖 11(含)之後
※next-key locks 臨鍵鎖
為記錄鎖 + 間隙鎖，隔離等級為 RR 才有用
● 假設資料庫的 id(index) 是 1 5 9 11，這時 id > 5 and id < 10，除了不能新增外也不可用排他鎖
A session：
SELECT * FROM bruce_test.bruce_tbl where id > 5 and id < 10 for update;
B session：
SELECT * FROM bruce_test.bruce_tbl where id = 11 for update;
# 主鍵、唯一、普通索引都是鎖 9 和 11，不鎖 5
insert into bruce_test.bruce_tbl (id, name) values (8, 'xxx');
# 主鍵索引：鎖 6~10
# 唯一索引：鎖 6~11
# 普通索引：鎖 5~10
測試二 A session：
SELECT * FROM bruce_test.bruce_tbl where id > 5 for update;
測試二 B session：
SELECT * FROM bruce_test.bruce_tbl where id = 9 for update;
# 主鍵、唯一、普通索引都是鎖 9 和 11，不鎖 5
insert into bruce_test.bruce_tbl (id, name) values (9, 'xxx');
# 主鍵索引：鎖 6(含) 以上
# 唯一索引：鎖 6(含) 以上
# 普通索引：鎖 5(含) 以上
MVCC (Multi-Version Concurrency Control) 多版本併發控制
snapshot read 快照讀：像照相機一樣照起來，以後資料都從這邊拿
current read 當前讀：直接去資料庫拿，所以拿的都是最新的資料
在兩個讀之間有增刪改其中之一，會觸發當前讀
目的是想解決幻讀，但必竟隔離等級是 RR，不是 SERIALIZABLE，所以只解決了一部分，在第一次讀的時候用的是快照讀，之後其他 session 新增了記錄，此時在查詢一次並不會產生新的記錄，會有問題的如下：
測試：
A session：# 執行第一行後時先停一下，然後執行 B session，然後再繼續
SELECT * FROM bruce_test.bruce_tbl where id = 1; # 假設查出來的 name 是 aaa
update bruce_test.bruce_tbl set name = CONCAT(name, '123') where id = 1; # 這行產生了讀已提交的資料
SELECT * FROM bruce_test.bruce_tbl where id = 1; # sesion 還沒結束，name 應該要是 aaa123，但確變成了 abc123，因為觸發了當前讀
B session：
update bruce_test.bruce_tbl set name = 'abc' where id = 1; # 直接提交
沒有留言:
張貼留言