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

MySQL?InnoDB鎖類型及鎖原理實例解析

 更新時間：2022年11月27日 14:27:10   作者：丨Jack_Chen丨  
這篇文章主要為大家介紹了MySQL?InnoDB鎖類型及鎖原理實例解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

鎖

鎖是用來解決事務(wù)對數(shù)據(jù)的并發(fā)訪問的問題的。MyISAM支持表鎖，InnoDB同時支持表鎖和行鎖。

表加鎖語法：

lock tables xxx read;
lock tables xxx write;
unlock tables;

鎖分類

兩個行級別的鎖：

共享鎖Shared Locks、排他鎖Exclusive Locks

兩個表級別的鎖：

意向共享鎖、意向排他鎖

鎖的算法:

三個Record Locks、Gap Locks、Next-Key Locks，把它們叫做鎖的算法，也就是分別在什么情況下鎖定什么范圍。

插入意向鎖：

是一個特殊的間隙鎖。間隙鎖不允許插入數(shù)據(jù)，但是插入意向鎖允許多個事務(wù)同時插入數(shù)據(jù)到同一個范圍。比如（4,7),一個事務(wù)插入5,一個事務(wù)插入6，不會發(fā)生鎖等待。

自增鎖：

是一種特殊的表鎖，用來防止自增字段重復，數(shù)據(jù)插入以后就會釋放，不需要等到事務(wù)提交才釋放。如果需要選擇更快的自增值生成速度或者更加連續(xù)的自增值，就要通過修改自增鎖的模式改變。

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.01 sec)

0:traditonal：每次都會產(chǎn)生表鎖

1:consecutive：會產(chǎn)生一個輕量鎖，simple insert會獲得批量的鎖，保證連插入，默認值

2:interleaved：不會鎖表，來一個處理一個，并發(fā)最高

共享鎖

共享鎖是一個行級別的鎖，它叫Shared Locks

獲取一行數(shù)據(jù)的讀鎖以后，可以用來讀取數(shù)據(jù)，所以它也叫做讀鎖

注意不要在加上讀鎖以后去寫數(shù)據(jù)，不然可能會出現(xiàn)死鎖的情況

多個事務(wù)可以共享一把讀鎖。

作用：

因為共享鎖會阻塞其他事務(wù)的修改，所以可以用在不允許其他事務(wù)修改數(shù)據(jù)的情況

給一行數(shù)據(jù)手動加上一把讀鎖：

SELECT SQL LOCK IN SHARE MODE;

釋放鎖：

只要事務(wù)結(jié)束，鎖就會自動釋放鎖

驗證

驗證共享鎖是否可以重復獲取

事務(wù)1：開啟事務(wù)，執(zhí)行查詢，不提交事務(wù)

BEGIN;
SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;

事務(wù)2：開啟事務(wù)，執(zhí)行查詢，正常查詢

BEGIN;
SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;
COMMIT;

事務(wù)3：開啟事務(wù)，執(zhí)行修改，遇到阻塞

BEGIN;
UPDATE tableName set column1='test' WHERE id=1;

排他鎖

排他鎖是一個行級別的鎖，叫做Exclusive Locks，它是用來操作數(shù)據(jù)的，所以又叫做寫鎖。

只要一個事務(wù)獲取了一行數(shù)據(jù)的排它鎖，其他的事務(wù)就不能再獲取這一行數(shù)據(jù)的共享鎖和排它鎖。

加鎖方式

1.自動加排他鎖：在操作數(shù)據(jù)的時候，包括增刪改，都會默認加上一個排它鎖。

2.手動加鎖：用一個FOR UPDATE給一行數(shù)據(jù)加上一個排它鎖，這個無論是在代碼里還是操作數(shù)據(jù)的工具里，都比較常用。

驗證

驗證排它鎖的特性

事務(wù)1：開啟事務(wù)，執(zhí)行查詢，不提交事務(wù)

BEGIN;
UPDATE tableName set column1='test' WHERE id=1;

事務(wù)2：開啟事務(wù)，執(zhí)行查詢，出現(xiàn)阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;

事務(wù)3：開啟事務(wù)，執(zhí)行查詢，出現(xiàn)阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務(wù)4：開啟事務(wù)，執(zhí)行查詢，出現(xiàn)阻塞

BEGIN;
UPDATE tableName set column1='test' WHERE id=1;

意向鎖

意向鎖是表級別的鎖，是由數(shù)據(jù)庫自己維護的，分為：意向共享鎖、意向排他鎖

當給一行數(shù)據(jù)加上共享鎖之前，數(shù)據(jù)庫會自動在這張表上面加一個意向共享鎖

當給一行數(shù)據(jù)加上排他鎖之前，數(shù)據(jù)庫會自動在這張表上面加一個意向排他鎖

反過來：

如果一張表上面至少有一個意向共享鎖，說明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了共享鎖

如果一張表上面至少有一個意向排他鎖，說明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了排他鎖

驗證

事務(wù)1：開啟事務(wù)，執(zhí)行查詢，并手動加上排他鎖，事務(wù)不提交

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務(wù)2：開啟事務(wù)，給表加上鎖，出現(xiàn)阻塞

BEGIN;
LOCK TABLES tableName WRITE;

釋放表鎖

unlock tables;

記錄鎖

當對唯一索引和主鍵索引使用等值查詢，精準匹配一條記錄的時候，使用的就是記錄鎖。

間隙鎖

當查詢記錄不存在，沒有命中任何一行數(shù)據(jù)，無論是用等值查詢還是范圍查詢，它使用的都是間隙鎖。

間隙鎖主要是阻塞插入insert。相同的間隙鎖之間不沖突。

臨鍵鎖

當使用范圍查詢，不僅僅命中Record記錄，還包含間隙，在這種情況下使用的是臨鍵鎖，它是MySQL里面默認的行鎖算法，相當于記錄鎖加上間隙鎖。

唯一性索引，等值查詢匹配到一條記錄的時候，退化成記錄鎖。沒有匹配到任何記錄的時候，退化成間隙鎖。

死鎖

死鎖的發(fā)生需要滿足一定的條件，在發(fā)生死鎖時，InnoDB一般都能通過算法（wait-for graph)自動檢測到。

死鎖產(chǎn)生條件

同一時刻只能有一個事務(wù)持有這把鎖

其他事務(wù)需要在這個事務(wù)釋放鎖之后才能獲取鎖，而不可以強行剝奪

當多個事務(wù)形成等待環(huán)路的時候，即發(fā)生死鎖

說到底就是因為鎖本身是互斥的

行鎖發(fā)生死鎖

模擬1

事務(wù)1

BEGIN;
1.SELECT * FROM tableName WHERE id=1 FOR UPDATE;
3.UPDATE tableName SET column1='test'  WHERE id=2;

事務(wù)2

BEGIN;
2.DELETE FROM tableName WHERE id=2;
4.DELETE FROM tableName WHERE id=1;

在第一個事務(wù)中，檢測到死鎖，馬上退出釋放鎖，第二個事務(wù)獲得鎖，不需要等待50秒

1213-Deadlock found when trying to get lock; try restarting transaction

模擬2

事務(wù)1

BEGIN;
1.SELECT * FROM tableName WHERE id=1 LOCK in SHARE MODE;
3.UPDATE tableName SET column1='aa'  WHERE id=1;

事務(wù)2

BEGIN;
2.SELECT * FROM tableName WHERE id=1 LOCK in SHARE MODE;
4.UPDATE tableName SET column1='bb'  WHERE id=1;

在第二個事務(wù)中，檢測到死鎖，馬上退出釋放鎖，第一個事務(wù)獲得鎖，不需要等待50秒

1213-Deadlock found when trying to get lock; try restarting transaction

表鎖發(fā)生死鎖

BEGIN;
1.LOCK TABLES tableName1 WRITE;
3.LOCK TABLES tableName2 WRITE; 阻塞，直到4執(zhí)行，釋放tableName1鎖，獲取tableName2的鎖

BEGIN;
2.LOCK TABLES tableName2 WRITE;
4.LOCK TABLES tableName1 WRITE; 鎖機制檢測到死鎖，自動釋放鎖，獲取tableName1

鎖的釋放

死鎖在事務(wù)結(jié)束（commit、rollback）、或客戶端斷開連接時釋放鎖。

事務(wù)阻塞

如果一個事務(wù)一直未釋放鎖，其他事務(wù)會被阻塞50秒，通過參數(shù)控制獲取鎖的等待時間，默認是50秒。

mysql> show VARIABLES like 'innodb_lock_wait_timeout' ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

死鎖的避免

在程序中，操作多張表時，盡量以相同的順序來訪問（避免形成等待環(huán)路）

批量操作單張表數(shù)據(jù)的時候，先對數(shù)據(jù)進行排序（避免形成等待環(huán)路）

申請足夠級別的鎖，如果要操作數(shù)據(jù)，就申請排它鎖

盡量使用索引訪問數(shù)據(jù)，避免沒有where條件的操作，避免鎖表

如果可以，大事務(wù)化成小事務(wù)

使用等值查詢而不是范圍查詢查詢數(shù)據(jù)，命中記錄，避免間隙鎖對并發(fā)的影響

鎖的日志

查看行鎖信息

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 436657 |
| Innodb_row_lock_time_avg      | 15057  |
| Innodb_row_lock_time_max      | 51578  |
| Innodb_row_lock_waits         | 29     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits:當前正在等待鎖定的數(shù)量
Innodb_row_lock_time:從系統(tǒng)啟動到現(xiàn)在鎖定的總時間長度，單位ms
Innodb_row_lock_time_avg:每次等待所花平均時間
Innodb_row_lock_time_max:從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時間
Innodb_row_lock_waits:從系統(tǒng)啟動到現(xiàn)在總共等待的次數(shù)

查看當前運行的所有事務(wù)，還有具體的語句

select * from information_schema.INNODB_TRX;

當前出現(xiàn)的鎖

select * from information_schema.INNODB_LOCKS;

鎖等待的對應(yīng)關(guān)系

select * from information_schema.INNODB_LOCK_WAITS;

mysql> select * from information_schema.INNODB_TRX;
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                     | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 517172          | LOCK WAIT | 2022-10-09 22:28:59 | 517172:809:3:6        | 2022-10-09 22:33:20 |          4 |                 185 | SELECT * FROM tableName WHERE id=1 FOR UPDATE | starting index read |                 1 |                 2 |                4 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 329261206788832 | RUNNING   | 2022-10-09 22:28:12 | NULL                  | NULL                |          2 |                 203 | NULL                                          | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> select * from information_schema.INNODB_LOCKS;
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id                 | lock_trx_id     | lock_mode | lock_type | lock_table         | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 517172:809:3:6          | 517172          | X         | RECORD    | `mydb`.`tableName` | PRIMARY    |        809 |         3 |        6 | 1         |
| 329261206788832:809:3:6 | 329261206788832 | S         | RECORD    | `mydb`.`tableName` | PRIMARY    |        809 |         3 |        6 | 1         |
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+-------------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id        |
+-------------------+-------------------+-----------------+-------------------------+
| 517172            | 517172:809:3:6    | 329261206788832 | 329261206788832:809:3:6 |
+-------------------+-------------------+-----------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

開啟標準監(jiān)控和鎖監(jiān)控，得到更加詳細的鎖信息

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

行鎖的原理

在InnoDB中，行鎖是通過鎖住索引來實現(xiàn)的。因此，當一個事務(wù)鎖住一行數(shù)據(jù)的時候，其他的事務(wù)不能操作這一行數(shù)據(jù)，是因為它鎖住了這行數(shù)據(jù)對應(yīng)的索引。

不帶任何索引的表

不帶任何索引的表中，在沒有索引或者沒有用到索引的情況下，會鎖住整張表

事務(wù)1：開啟事務(wù)，執(zhí)行查詢，不提交事務(wù)

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務(wù)2：開啟事務(wù)，執(zhí)行修改，進入阻塞

BEGIN;
UPDATE tableName SET column1='test'  WHERE id=3;

事務(wù)3：開啟事務(wù)，執(zhí)行查詢，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE id=2 FOR UPDATE;

帶主鍵索引的表

在帶主鍵索引的表中，使用相同id加鎖會沖突，使用不同id加鎖，可以成功

事務(wù)1：開啟事務(wù)，執(zhí)行查詢，不提交事務(wù)

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務(wù)2：開啟事務(wù)，執(zhí)行查詢，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務(wù)3：開啟事務(wù)，執(zhí)行修改、查詢，正常執(zhí)行

BEGIN;
UPDATE tableName SET column1='test'  WHERE id=3;
SELECT * FROM tableName WHERE id=3 FOR UPDATE;

帶唯一索引的表

在帶唯一索引的表中，使用相同唯一值會加鎖會沖突，使用不同唯一值加鎖，可以成功。

事務(wù)1：開啟事務(wù)，執(zhí)行查詢，不提交事務(wù)

BEGIN;
SELECT * FROM tableName WHERE column1='column1' FOR UPDATE;

事務(wù)2：開啟事務(wù)，執(zhí)行查詢，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE column1='column1' FOR UPDATE;

事務(wù)3：開啟事務(wù)，執(zhí)行查詢，查詢的是上述事務(wù)操作的加鎖的那條數(shù)據(jù)，進入阻塞

BEGIN;
SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事務(wù)4：開啟事務(wù)，執(zhí)行查詢，正常執(zhí)行

BEGIN;
SELECT * FROM tableName WHERE column1='column2' FOR UPDATE;
SELECT * FROM tableName WHERE id=2 FOR UPDATE;

結(jié)論

1.表必定有索引

鎖是基于索引進行鎖數(shù)據(jù)的，因此，一張表必定有索引

如果定義主鍵（PRIMARYKEY)，那么InnoDB會選擇主鍵作為聚集索引

如果沒有顯式定義主鍵，則 InnoDB 會選擇第一個不包含有NULL值的唯一索引作為主鍵索引

如果也沒有這樣的唯一索引，則 InnoDB 會選擇內(nèi)置6字節(jié)長的ROWID作為隱藏的聚集索引，它會隨著行記錄的寫入而主鍵遞增

一張不帶任何索引的表，造成鎖表，是因為查詢沒有使用索引，會進行全表掃描，然后把每一個隱藏的聚集索引都鎖住。

2.唯一索引數(shù)據(jù)行加鎖，主鍵索引同樣被鎖

聚集索引就是按照每張表的主鍵構(gòu)造一棵B+樹，同時葉子節(jié)點中存放的即為整張表的行記錄數(shù)據(jù)。

輔助索引，也叫非聚集索引，和聚集索引相比，葉子節(jié)點中并不包含行記錄的全部數(shù)據(jù)，而是包含二級索引和主鍵的值。例如column1的索引和主鍵id值1

主鍵索引里面除了索引之外，還存儲了完整的數(shù)據(jù)。所以通過輔助索引鎖定一行數(shù)據(jù)的時候，它跟檢索數(shù)據(jù)的步驟是一樣的，會通過主鍵值找到主鍵索引，因此會鎖定。

本質(zhì)上是因為鎖定的是同一行數(shù)據(jù)，所以會相互沖突。

以上就是MySQL InnoDB鎖類型及鎖原理實例解析的詳細內(nèi)容，更多關(guān)于MySQL InnoDB鎖類型鎖原理的資料請關(guān)注腳本之家其它相關(guān)文章！

相關(guān)文章

  • MySQL流程控制函數(shù)匯總分析講解

    MySQL流程控制函數(shù)匯總分析講解

    MySQL流程控制函數(shù)包括if、case、while、repeat、loop、leave、iterate等，可以在SQL語句中實現(xiàn)條件判斷、循環(huán)、跳出等功能，提高了SQL語句的靈活性和功能性
    2023-04-04
  • mysql5.6.zip格式壓縮版安裝圖文教程

    mysql5.6.zip格式壓縮版安裝圖文教程

    這篇文章主要為大家詳細介紹了mysql5.6.zip格式壓縮版安裝圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2018-12-12
  • 快速解決mysql57服務(wù)突然不見了的問題

    快速解決mysql57服務(wù)突然不見了的問題

    下面小編就為大家?guī)硪黄焖俳鉀Qmysql57服務(wù)突然不見了的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
    2017-05-05
  • MYSQL事務(wù)的隔離級別與MVCC

    MYSQL事務(wù)的隔離級別與MVCC

    這篇文章主要介紹了MYSQL事務(wù)的隔離級別與MVCC，文章首先通過事務(wù)的相關(guān)內(nèi)容展開主題主要介紹，具有一定的參考價值，需要的小伙伴可以參一下
    2022-05-05
  • MAC下MySQL忘記初始密碼怎么辦

    MAC下MySQL忘記初始密碼怎么辦

    MySQL初始密碼忘記怎么辦，這篇文章主要介紹了MAC下MySQL忘記初始密碼的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2017-02-02
  • 關(guān)于MySQL數(shù)據(jù)庫死鎖的案例和解決方案

    關(guān)于MySQL數(shù)據(jù)庫死鎖的案例和解決方案

    MySQL Update語句防止死鎖是指在修改MySQL數(shù)據(jù)庫的數(shù)據(jù)時,為避免多個進程同時修改同一數(shù)據(jù)行而造成死鎖的情況,引入了一些機制來防止死鎖的產(chǎn)生,本文介紹了一個 MySQL 數(shù)據(jù)庫死鎖的案例和解決方案,需要的朋友可以參考下
    2023-09-09
  • MySQL創(chuàng)建和刪除數(shù)據(jù)庫的命令及相關(guān)PHP腳本的操作方法

    MySQL創(chuàng)建和刪除數(shù)據(jù)庫的命令及相關(guān)PHP腳本的操作方法

    這篇文章主要介紹了MySQL創(chuàng)建和刪除數(shù)據(jù)庫的命令及相關(guān)PHP腳本的操作方法,這里主要講述Linux中在mysqladmin下的命令操作,需要的朋友可以參考下
    2015-11-11
  • 關(guān)于MySQL死鎖問題的深入分析

    關(guān)于MySQL死鎖問題的深入分析

    這篇文章主要給大家介紹了關(guān)于MySQL死鎖問題的深入分析，文中通過示例代碼介紹的非常詳細，對大家的學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
    2019-11-11
  • 淺談MySQL user權(quán)限表

    淺談MySQL user權(quán)限表

    MySQL 在安裝時會自動創(chuàng)建一個名為 mysql 的數(shù)據(jù)庫，mysql 數(shù)據(jù)庫中存儲的都是用戶權(quán)限表。本文就詳細的介紹一下MySQL user權(quán)限表 ，感興趣的可以了解一下
    2021-06-06
  • mysql主鍵,外鍵,非空,唯一,默認約束及創(chuàng)建表的方法

    mysql主鍵,外鍵,非空,唯一,默認約束及創(chuàng)建表的方法

    這篇文章主要介紹了mysql主鍵,外鍵,非空,唯一,默認約束及創(chuàng)建表的方法,在數(shù)據(jù)庫中，數(shù)據(jù)表是數(shù)據(jù)庫中最重要、最基本的操作對象，是數(shù)據(jù)存儲的基本單位
    2022-07-07

最新評論