測試MySQL鎖的問題

秋風五丈原發表於2021-06-24
MySql

測試MySQL鎖的問題

目錄

InnoDB支援三種行鎖：

  • Record Lock：單個行記錄上面的鎖

  • Gap Lock：間隙鎖，鎖定一個範圍，但不會包含記錄本身

  • Next-Key Lock：Gap Lock + Record Lock，鎖定一個範圍，並且鎖定記錄本身。

    比如一個索引有10，11，13，20，那麼該索引可能被Next-Key Locking鎖住的區間為：

    (-∞,10], (10,11], (11, 13], (13, 20], (20, +∞]

1 Record Lock

InnoDB對於primary key（如果是多個列，且查詢了所有的列，即點對點查詢），會把間隙鎖降級為Record Lock，比如下面的例子：

表的schema為：

create table t(
    a int PRIMARY KEY
);

insert into t select 1;
insert into t select 2;
insert into t select 5;

session A和session B的操作順序如下：


/*session a*/
begin;
select * from t where a = 5 for update;

/*session b*/
begin;
/*不會阻塞，因為對於唯一索引，innodb採用的是record lock，即鎖住單行*/
insert into t select 4;
commit;


/*session a*/
commit;

2 Next-Key Lock

如果是二級索引，因為不具備唯一性，為了防止幻讀的產生，InnoDB會鎖住相關的範圍，比如下面的例子：

表的schema為：

/*測試Next-Key Lock的問題*/
create table z(
    a INT,  
    b INT,
    PRIMARY KEY(a), 
    KEY(b)
);

insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

session A和session B的操作順序如下：


/*session a*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
/*按照書裡面講的，這個語句會鎖住範圍(1,3)(3,6)，所以我們嘗試下*/
select * from z where b = 3 for update;

/*session b*/
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
/*阻塞，因為session a中鎖定了範圍，所以這裡也會被鎖住*/
select * from z where a = 5 lock in share mode;
/*下面的兩個語句也會被鎖住*/
insert into z select 4,2;
insert into z select 6,5;

/*但是下面的語句就不會被鎖住，因為不在鎖定的範圍裡面*/
insert into z select 8,6;
insert into z select 2,0;
insert into z select 6,7;
commit;


/*session a*/
commit;

2 死鎖測試

表的schema如下：

create table t(
    a int PRIMARY KEY
);

insert into t values(1),(2),(4),(5);

測試MySQL版本：8.0.20

  • 先來看最常見的AB-BA鎖問題：

    時間線 session A session B
    1 begin;
    select * from t where a = 2 for update;```
    2 begin;
    select * from t where a = 4 for update;
    3 /*session a：嘗試更新4*/
    update t set a = a + 100 where a = 4;
    4 /*session b：嘗試更新2，直接發生死鎖*/
    update t set a = a + 100 where a= 2;

在session B的時間點4的時候，mysql直接報錯，並對sessionB進行回滾操作：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  • 接下來是另外一種較為隱蔽的死鎖；

    先看第一種情況

    時間線 session A session B
    1 begin;
    select * from t where a = 4 for update;
    2 /*因為sessionA中鎖定了a=4這一行，所以這裡會一直阻塞住，但是，不同的是，它成功的獲取到了1,2的行鎖，正在等待4的鎖*/
    begin;
    select * from t where a <= 4 lock in share mode;
    3 /*發生死鎖，雖然session b在等待，但是session b已經獲取到了部分的gap lock，即[1,3)，所以這裡直接報死鎖錯誤*/
    insert into t values(3);

    這裡的情況稍微有些不同，sessionB雖然被阻塞了，但是它成功的獲取到了a=1，a=22的行鎖（Record Lock），此時正在等待a=4的行鎖，如果在session A的時間點3的時候，我們允許插入成功，那麼sessionA提交後，sessionB lock in share mode成功後，是不是應該在回過頭來獲取3的Record Lock？這樣子是不合理的，所以Mysql這裡就認定為死鎖，直接回退了undo比較小的事務，那麼問題來了，Mysql是怎麼做到的呢？個人猜測可能是通過gap lock來實現的，只是在performance_schema.data_locks表中查不到而已，進一步的情況需要檢視原始碼。

    再來看第二種情況：

    時間線 session A session B
    1 begin;
    select * from t where a = 4 for update;
    select * from t where a = 2 for update;
    2 /*因為sessionA中鎖定了a=2和a=4這兩行，所以這裡會一直阻塞住，但是，不同的是，它成功的獲取到了1的行鎖，正在等待2的鎖*/
    begin;
    select * from t where a <= 4 lock in share mode;
    3 /*和第一種情況不同的是，這裡直接成功了，並沒有認定為死鎖*/
    insert into t values(3);

    這裡之所以沒有問題，是因為session B在時間點2的時候，已經獲取到了a=1的Record Lock，此時正在等待a=2的行鎖，因為3>2，所以允許session A對a=3加上X鎖，假設session A在時間點3後commit了，這個時候session B在時間點2的語句也成功了，檢視performance_schema.data_locks可以看到，session B獲取到了a=1, a=2, a=3, a=4的行鎖。

    這是Mysql實現鎖的一個細節之處，需要好好理解下。

相關文章