" />

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

淺談MySql?update會鎖定哪些范圍的數(shù)據(jù)

 更新時間:2022年06月24日 08:36:45   作者:huan1993的技術(shù)分享  
本文主要介紹了記錄一下MySql?update會鎖定哪些范圍的數(shù)據(jù),文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1、背景

在項目中,我們經(jīng)常使用到update語句,那么update語句會鎖定表中的那些記錄呢?此處我們通過一些簡單的案例來模擬下。此處是我自己的一個理解,如果那個地方理解錯了,歡迎指出

2、前置知識

2.1 數(shù)據(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ù)庫版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

2.3 數(shù)據(jù)庫的存儲引擎

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 鎖是加在記錄上還是索引上

鎖是加在索引上,那如果表中沒有建立索引,是否就是加在表上的呢?其實不是,也是加在索引的,會存在一個默認(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 行級鎖

2.6.1 Record Locks

記錄鎖,即只會鎖定一條記錄。其實是鎖定這條記錄的索引。

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

間隙鎖,間隙鎖是在索引記錄之間的間隙上的鎖,即鎖定一個區(qū)間。前開后開區(qū)間,不包括記錄本身。

間隙鎖如果是使用單列唯一索引值進行更新的話,是會退化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 是索引記錄上記錄鎖索引記錄之前間隙上的間隙鎖的組合。也是鎖定一個區(qū)間,前開后閉區(qū)間。包括記錄本身。

如果索引值包括 1,5,10,30,那么next key 鎖可能涵蓋如下區(qū)間

(negative infinity, 1]
(1, 115
(5, 10]
(10, 30]
(30, positive infinity)

negative infinity指的是負(fù)無窮。positive infinity指的是正無窮。

2.6.4 測試鎖表的表結(jié)構(gòu)

create table test_record_lock
(
    id   int         not null comment '主鍵',
    age  int         null comment '年齡,普通索引',
    name varchar(10) null comment '姓名,無索引',
    constraint test_record_lock_pk
        primary key (id)
)
    comment '測試記錄鎖';

create index test_record_lock_age_index
    on test_record_lock (age);

2.6.5 表中的測試數(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ù)庫中當(dāng)前的鎖

select * from performance_schema.data_locks;

字段解釋:

字段解釋
lock_typeTABLE鎖是加在表上
 RECORD鎖加在記錄上
lock_modeIX意向排他鎖
 X或者Snext-key lock 
鎖定記錄本身和記錄之前的間隙
 X,REC_NOT_GAPRecord Lock 只鎖記錄自身
 S,REC_NOT_GAPRecord Lock 只鎖記錄自身
 X,GAPgap lock
 X,INSERT_INTENTION插入意向鎖
lock_data具體的某個數(shù)字表示主鍵的值
 值,值第一個值:普通索引的值
第二個值:主鍵值

疑問:X,GAP是否可以理解成X鎖退化成了GAP鎖。

3、測試數(shù)據(jù)加鎖

3.1 唯一索引測試

此處適用單個字段的唯一索引,不適合多個字段的唯一索引

3.1.1 等值更新-記錄存在

解釋:

加next-key lock,那么鎖定的記錄范圍為 (1,5]。

因為是唯一索引,且查詢的值存在,next-key lock退化成record lock,即最終只鎖定了id=5的這一行數(shù)據(jù)。其余的數(shù)據(jù)不影響。

3.1.2 等值查詢-記錄不存在-01

解釋:

  • 加next-key lock,那么鎖定的記錄范圍為 (5,8]。
  • 因為是唯一索引,且查詢的值不存在,next-key lock退化成gap,即最終鎖定的數(shù)據(jù)范圍為(5,8)。其余的數(shù)據(jù)不影響。

3.1.3 等值更新-記錄不存在-02

3.1.4 范圍更新

1、小于或等于最大臨界值

此時可以發(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)

此時只可向表中插入比最小臨界值小的記錄。

3、正常范圍

3.2 普通索引測試

3.2.1 等值更新-記錄存在

解釋:

  • 先對普通索引age加上next-key lock,鎖定的范圍是(10,20]
  • next-key lock還會鎖住本記錄,因此在id索引的值等于5上加了Record Lock
  • 因為是普通索引并且值還存在,因此還會對本記錄的下一個區(qū)間增加間隙鎖 Gap Lock,鎖定的范圍為 (20,25)

3.2.2 等值更新-記錄不存在

解釋:

  • 獲取next-key lock 鎖定的范圍為 (10,20]
  • 因為需要更新的記錄不存在,next-key lock退化成 gap lock,所以鎖定的范圍為(10,20)
  • 因為是普通索引且記錄不存在,所以不需要再次查找下一個區(qū)間。

3.2.3 范圍更新

解釋:

普通索引的范圍更新,next-key-lock不回退化成 gap lock。

3.3 無索引更新

從上圖中可知,無索引更新數(shù)據(jù)表危險,需要謹(jǐn)慎處理。無索引更新,會導(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會鎖定哪些范圍的數(shù)據(jù)的文章就介紹到這了,更多相關(guān)MySql update鎖定范圍內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • Ubuntu Server下MySql數(shù)據(jù)庫備份腳本代碼

    Ubuntu Server下MySql數(shù)據(jù)庫備份腳本代碼

    為了mysql數(shù)據(jù)庫的安全,我們需要定時備份mysql數(shù)據(jù)庫,這里提供下腳本代碼,需要的朋友可以參考下
    2013-06-06
  • MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法(最新推薦)

    MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法(最新推薦)

    在MySQL中,如果我們想要在一個條件函數(shù)如CASE內(nèi)部使用聚合函數(shù)如MAX獲取某個字段的最大值,我們通常需要在外部查詢或子查詢中執(zhí)行這個聚合操作,并將結(jié)果作為參數(shù)傳遞給條件函數(shù),下面通過實例代碼講解MYSQL滿足條件函數(shù)里放查詢最大函數(shù)的方法,感興趣的朋友一起看看吧
    2024-05-05
  • MySQL分布式恢復(fù)進階

    MySQL分布式恢復(fù)進階

    這篇文章主要介紹了MySQL分布式恢復(fù)進階,文章圍繞主題展開詳細的內(nèi)容介紹,具有一定的參考價值,需要的小伙伴可以參考一下,希望對你的學(xué)習(xí)有所幫助
    2022-07-07
  • 詳解mysql不等于null和等于null的寫法

    詳解mysql不等于null和等于null的寫法

    這篇文章主要介紹了詳解mysql不等于null和等于null的寫法,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-09-09
  • MySQL性能優(yōu)化的最佳20+條經(jīng)驗

    MySQL性能優(yōu)化的最佳20+條經(jīng)驗

    這篇文章主要為大家詳細介紹了MySQL性能優(yōu)化的最佳20+條經(jīng)驗,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2016-09-09
  • MySQL創(chuàng)建定時任務(wù)實例(每天凌晨1點、每小時、每分鐘、某一時間點)

    MySQL創(chuàng)建定時任務(wù)實例(每天凌晨1點、每小時、每分鐘、某一時間點)

    在mysql中有時候要定時更新或者刪除一部分?jǐn)?shù)據(jù)需要用到mysql的定時任務(wù),下面這篇文章主要給大家介紹了關(guān)于MySQL創(chuàng)建定時任務(wù)的相關(guān)資料,包括每天凌晨1點、每小時、每分鐘、某一時間點等,需要的朋友可以參考下
    2023-03-03
  • InnoDB引擎中的事務(wù)詳解

    InnoDB引擎中的事務(wù)詳解

    這篇文章主要介紹了InnoDB引擎中的事務(wù)詳解,事務(wù)Transaction是訪問和更新數(shù)據(jù)庫的程序執(zhí)行單元;事務(wù)中可能包含一個或多個sql語句,這些語句要么都執(zhí)行,要么都不執(zhí)行,需要的朋友可以參考下
    2023-09-09
  • MySQL 四種連接和多表查詢詳解

    MySQL 四種連接和多表查詢詳解

    這篇文章主要介紹了MySQL多表查詢,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2021-07-07
  • mysql 5.6 從陌生到熟練之_數(shù)據(jù)庫備份恢復(fù)的實現(xiàn)方法

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

    下面小編就為大家?guī)硪黄猰ysql 5.6 從陌生到熟練之_數(shù)據(jù)庫備份恢復(fù)的實現(xiàn)方法。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧
    2016-10-10
  • MySQL?優(yōu)化?index?merge引起的死鎖分析

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

    這篇文章主要介紹了MySQL?優(yōu)化?index?merge引起的死鎖分析,MySQL通過優(yōu)化索引合并是遇到的死鎖問題,下面具體分析需要的小伙伴可以參考一下
    2022-04-04

最新評論