1、背景
在項(xiàng)目中，我們經(jīng)常使用到update語(yǔ)句，那么update語(yǔ)句會(huì)鎖定表中的那些記錄呢？此處我們通過(guò)一些簡(jiǎn)單的案例來(lái)模擬下。此處是我自己的一個(gè)理解，如果那個(gè)地方理解錯(cuò)了，歡迎指出
2、前置知識(shí)
2.1 數(shù)據(jù)庫(kù)的隔離級(jí)別
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
2.2 數(shù)據(jù)庫(kù)版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
2.3 數(shù)據(jù)庫(kù)的存儲(chǔ)引擎
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)
2.4 鎖是加在記錄上還是索引上
鎖是加在索引上，那如果表中沒有建立索引，是否就是加在表上的呢？其實(shí)不是，也是加在索引的，會(huì)存在一個(gè)默認(rèn)的。
Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking
參考鏈接： https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
2.5 update...where加鎖的基本單位是
UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters
此處可以理解加鎖的單位是: next-key鎖
2.6 行級(jí)鎖
2.6.1 Record Locks
記錄鎖，即只會(huì)鎖定一條記錄。其實(shí)是鎖定這條記錄的索引。
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.
2.6.2 Gap Locks
間隙鎖，間隙鎖是在索引記錄之間的間隙上的鎖，即鎖定一個(gè)區(qū)間。前開后開區(qū)間，不包括記錄本身。
間隙鎖如果是使用單列唯一索引值進(jìn)行更新的話，是會(huì)退化成Record Lock。
間隙鎖的目的：
- 防止新的數(shù)據(jù)插入到間隙中
- 防止已經(jīng)存在的數(shù)據(jù)被更新到間隙中。
Gap locking is not needed for statements that lock rows using a unique index to search > for a unique row. (This
does not includethe case that the search condition includes only > some columns of amultiple-column unique index; in that case, gap locking does occur.)
2.6.3 Next-Key Locks
Next-Key Lock 是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。也是鎖定一個(gè)區(qū)間，前開后閉區(qū)間。包括記錄本身。
如果索引值包括 1,5,10,30，那么next key 鎖可能涵蓋如下區(qū)間
(negative infinity, 1]
(1, 115
(5, 10]
(10, 30]
(30, positive infinity)
negative infinity指的是負(fù)無(wú)窮。positive infinity指的是正無(wú)窮。
2.6.4 測(cè)試鎖表的表結(jié)構(gòu)
create table test_record_lock
(
id int not null comment '主鍵',
age int null comment '年齡，普通索引',
name varchar(10) null comment '姓名，無(wú)索引',
constraint test_record_lock_pk
primary key (id)
)
comment '測(cè)試記錄鎖';
create index test_record_lock_age_index
on test_record_lock (age);
2.6.5 表中的測(cè)試數(shù)據(jù)
mysql> select * from test_record_lock;
+----+------+--------+
| id | age | name |
+----+------+--------+
| 1 | 10 | 張三 |
| 5 | 20 | 李四 |
| 8 | 25 | 王五 |
+----+------+--------+
3 rows in set (0.00 sec)
2.7 查看數(shù)據(jù)庫(kù)中當(dāng)前的鎖
select * from performance_schema.data_locks;
字段解釋：
| 字段 | 值 | 解釋 |
|---|---|---|
lock_type |
TABLE | 鎖是加在表上 |
| RECORD | 鎖加在記錄上 | |
lock_mode |
IX | 意向排他鎖 |
| X或者S | next-key lock 鎖定記錄本身和記錄之前的間隙 |
|
| X,REC_NOT_GAP | Record Lock 只鎖記錄自身 | |
| S,REC_NOT_GAP | Record Lock 只鎖記錄自身 | |
| X,GAP | gap lock | |
| X,INSERT_INTENTION | 插入意向鎖 | |
lock_data |
具體的某個(gè)數(shù)字 | 表示主鍵的值 |
| 值,值 | 第一個(gè)值：普通索引的值 第二個(gè)值：主鍵值 |
疑問：X,GAP是否可以理解成X鎖退化成了GAP鎖。
3、測(cè)試數(shù)據(jù)加鎖
3.1 唯一索引測(cè)試
此處適用單個(gè)字段的唯一索引，不適合多個(gè)字段的唯一索引
3.1.1 等值更新-記錄存在
解釋：
- 加next-key lock，那么鎖定的記錄范圍為 (1,5]。
- 因?yàn)槭俏ㄒ凰饕也樵兊闹荡嬖?，next-key lock退化成record lock，即最終只鎖定了id=5的這一行數(shù)據(jù)。其余的數(shù)據(jù)不影響。
3.1.2 等值查詢-記錄不存在-01
解釋：
- 加next-key lock，那么鎖定的記錄范圍為 (5,8]。
- 因?yàn)槭俏ㄒ凰饕?，且查詢的值不存在，next-key lock退化成gap，即最終鎖定的數(shù)據(jù)范圍為(5,8)。其余的數(shù)據(jù)不影響。
3.1.3 等值更新-記錄不存在-02
3.1.4 范圍更新
1、小于或等于最大臨界值
此時(shí)可以發(fā)現(xiàn)表中掃描到的記錄都加上了next key lock(鎖加在索引上)
2、大于或等于最小臨界值
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update test_record_lock set name = 'aaa' where id >= 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select LOCK_TYPE,INDEX_NAME,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
+-----------+------------+---------------+------------------------+
| LOCK_TYPE | INDEX_NAME | LOCK_MODE | LOCK_DATA |
+-----------+------------+---------------+------------------------+
| TABLE | NULL | IX | NULL |
| RECORD | PRIMARY | X,REC_NOT_GAP | 1 |
| RECORD | PRIMARY | X | supremum pseudo-record |
| RECORD | PRIMARY | X | 8 |
| RECORD | PRIMARY | X | 5 |
+-----------+------------+---------------+------------------------+
5 rows in set (0.01 sec)
此時(shí)只可向表中插入比最小臨界值小的記錄。
3、正常范圍
3.2 普通索引測(cè)試
3.2.1 等值更新-記錄存在
解釋：
- 先對(duì)普通索引
age加上next-key lock，鎖定的范圍是(10,20] - next-key lock還會(huì)鎖住本記錄，因此在id索引的值等于5上加了Record Lock
- 因?yàn)槭瞧胀ㄋ饕⑶抑颠€存在，因此還會(huì)對(duì)本記錄的下一個(gè)區(qū)間增加間隙鎖 Gap Lock，鎖定的范圍為 (20,25)
3.2.2 等值更新-記錄不存在
解釋：
- 獲取next-key lock 鎖定的范圍為 (10,20]
- 因?yàn)樾枰碌挠涗洸淮嬖?，next-key lock退化成 gap lock，所以鎖定的范圍為(10,20)
- 因?yàn)槭瞧胀ㄋ饕矣涗洸淮嬖?，所以不需要再次查找下一個(gè)區(qū)間。
3.2.3 范圍更新
解釋：
- 普通索引的范圍更新，next-key-lock不回退化成 gap lock。
3.3 無(wú)索引更新
從上圖中可知，無(wú)索引更新數(shù)據(jù)表危險(xiǎn)，需要謹(jǐn)慎處理。無(wú)索引更新，會(huì)導(dǎo)致全表掃描，導(dǎo)致將掃描到的所有記錄都加上next-key lock。
3、參考鏈接
1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
2、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
