淺談MySql?update會(huì)鎖定哪些范圍的數(shù)據(jù)
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 鎖是加在記錄上還是索引上
鎖是加在索引上
,那如果表中沒(méi)有建立索引,是否就是加在表上的呢?其實(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
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ū)間。前開(kāi)后開(kāi)區(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 include the case that the search condition includes only > some columns of a multiple-column unique index; in that case, gap locking does occur.)
2.6.3 Next-Key Locks
Next-Key Lock 是索引記錄上
的記錄鎖
和索引記錄之前
的間隙上的間隙鎖
的組合。也是鎖定一個(gè)區(qū)間,前開(kāi)后閉區(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è)值:主鍵值 |
疑問(wèn):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)樾枰碌挠涗洸淮嬖冢琻ext-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
。
4、參考鏈接
1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
2、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
到此這篇關(guān)于淺談MySql update會(huì)鎖定哪些范圍的數(shù)據(jù)的文章就介紹到這了,更多相關(guān)MySql update鎖定范圍內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Ubuntu Server下MySql數(shù)據(jù)庫(kù)備份腳本代碼
為了mysql數(shù)據(jù)庫(kù)的安全,我們需要定時(shí)備份mysql數(shù)據(jù)庫(kù),這里提供下腳本代碼,需要的朋友可以參考下2013-06-06MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法(最新推薦)
在MySQL中,如果我們想要在一個(gè)條件函數(shù)如CASE內(nèi)部使用聚合函數(shù)如MAX獲取某個(gè)字段的最大值,我們通常需要在外部查詢或子查詢中執(zhí)行這個(gè)聚合操作,并將結(jié)果作為參數(shù)傳遞給條件函數(shù),下面通過(guò)實(shí)例代碼講解MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法,感興趣的朋友一起看看吧2024-05-05MySQL性能優(yōu)化的最佳20+條經(jīng)驗(yàn)
這篇文章主要為大家詳細(xì)介紹了MySQL性能優(yōu)化的最佳20+條經(jīng)驗(yàn),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2016-09-09MySQL創(chuàng)建定時(shí)任務(wù)實(shí)例(每天凌晨1點(diǎn)、每小時(shí)、每分鐘、某一時(shí)間點(diǎn))
在mysql中有時(shí)候要定時(shí)更新或者刪除一部分?jǐn)?shù)據(jù)需要用到mysql的定時(shí)任務(wù),下面這篇文章主要給大家介紹了關(guān)于MySQL創(chuàng)建定時(shí)任務(wù)的相關(guān)資料,包括每天凌晨1點(diǎn)、每小時(shí)、每分鐘、某一時(shí)間點(diǎn)等,需要的朋友可以參考下2023-03-03

mysql 5.6 從陌生到熟練之_數(shù)據(jù)庫(kù)備份恢復(fù)的實(shí)現(xiàn)方法

MySQL?優(yōu)化?index?merge引起的死鎖分析