Mysql的鎖你知道多少個
我們知道，數(shù)據(jù)也是一種供許多用戶共享訪問的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性，是所有數(shù)據(jù)庫必須解決的一個問題，鎖的沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這一角度來說，鎖對于數(shù)據(jù)庫而言就顯得尤為重要。接下來我們了解各種鎖的特征。
1.1 表鎖
表級鎖是mysql鎖中粒度最大的一種鎖，表示當前的操作對整張表加鎖，資源開銷比行鎖少，不會出現(xiàn)死鎖的情況，但是發(fā)生鎖沖突的概率很大。該鎖定機制最大的特點是實現(xiàn)邏輯非常簡單，帶來的系統(tǒng)負面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級鎖一次會將整個表鎖定，所以可以很好地避免困擾我們的死鎖問題。InnoDB支持表級鎖。但是默認的行鎖，而且只有在查詢或者其他SQL語句通過索引才會使用行鎖。
1.2 行鎖
行鎖的是mysql鎖中粒度最小的一種鎖，因為鎖的粒度很小，所以發(fā)生資源爭搶的概率也最小，并發(fā)性能最大，但是也會造成死鎖，每次加鎖和釋放鎖的開銷也會變大。目前主要是Innodb使用行鎖。根據(jù)使用方式也分為共享鎖（S鎖或者讀鎖）和排它鎖（X鎖或者寫鎖）。
1.3共享鎖（讀鎖，S鎖）
共享鎖的具體邏輯如下案例：
若事務A對數(shù)據(jù)對象o加上S鎖，則事務A可以讀數(shù)據(jù)對象o但不能修改，其他事務只能再對數(shù)據(jù)對象o加S鎖，而不能加X鎖，直到事務A釋放數(shù)據(jù)對象o上的S鎖。這保證了其他事務可以讀數(shù)據(jù)對象o，但在事務A釋放數(shù)據(jù)對象o上的S鎖之前不能對數(shù)據(jù)對象o做任何修改。
語法和案例如下：
# 加讀鎖
Lock table tablename read;
#釋放鎖
unlock table;
案例如下 ：
#session1 給表創(chuàng)建讀鎖（創(chuàng)建一個連接就是一個回話session）
mysql> lock table userinfo read;
Query OK, 0 rows affected (0.01 sec)
#可以讀取當前表的數(shù)據(jù)
mysql> select * from userinfo ;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 27 |
| 2 | lisi | 27 |
| 3 | dd | NULL |
| 4 | dd | 2 |
+----+----------+------+
4 rows in set (0.00 sec)
#session1 因為給userinfo表加了讀鎖，所以不能讀取其他表數(shù)據(jù)
mysql> select * from student_score;
ERROR 1100 (HY000): Table 'student_score' was not locked with LOCK TABLES
#session1 因為給表加的讀鎖，所以當前回話不能對表進行其他操作，
mysql> update userinfo set age=age+1;
ERROR 1099 (HY000): Table 'userinfo' was locked with a READ lock and can't be updated
#session2，在session1 沒有釋放鎖之前，session2可以進行讀取userinfo
mysql> select * from userinfo;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 27 |
| 2 | lisi | 27 |
| 3 | dd | NULL |
| 4 | dd | 2 |
+----+----------+------+
4 rows in set (0.00 sec)
#session2，在session1 沒有釋放鎖之前，session2修改或刪除表userinfo的任何數(shù)據(jù)都被阻塞
mysql> update userinfo set age=1 where name='dd';
。。。。一直等待，直到seesion1 釋放鎖
#session1 釋放鎖,session1在執(zhí)行下面釋放鎖語句后，session2才會提交上面修改語句。
mysql> unlock tables;
1.4 排它鎖（寫鎖,X鎖）
共享鎖的具體邏輯如下案例：
若事務A對數(shù)據(jù)對象o加上X鎖，事務A可以讀數(shù)據(jù)對象o也可以修改數(shù)據(jù)對象o，其他事務不能再對數(shù)據(jù)對象o加任何鎖，直到事務A釋放數(shù)據(jù)對象o上的鎖。這保證了其他事務在事務A釋放數(shù)據(jù)對象o上的鎖之前不能再讀取和修改數(shù)據(jù)對象o。
# 加寫鎖給表
Lock table tablename write;
# 加寫鎖給行
select ... for update;
#釋放表鎖
unlock table;
案例如下 ：
# session1 當前會話session1給表加寫鎖。
mysql> lock table userinfo write;
Query OK, 0 rows affected (0.00 sec)
#session1 當前會話可以修改此表數(shù)據(jù)
mysql> update userinfo set age=9;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
#session1 當前會話可以查詢此表
mysql> select * from userinfo;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 9 |
| 2 | lisi | 9 |
| 3 | dd | 9 |
| 4 | dd | 9 |
+----+----------+------+
4 rows in set (0.00 sec)
#session2，在session1 沒有釋放鎖之前，session2對表的任何數(shù)據(jù)任何操作（增刪改查）userinfo的任何數(shù)據(jù)都被阻塞
mysql> update userinfo set age=1 where name='dd';
。。。。一直等待，直到seesion1 釋放鎖
如上是鎖表，接下來我們看看給行加鎖的效果：
#修改提交模式，改為手動提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
#session1 當前會話給id=1的行加寫鎖。
mysql> select * from userinfo where id=1 for update;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 10 |
+----+----------+------+
1 row in set (0.00 sec)
#session2 當前會話2 可以查詢當前表數(shù)據(jù)。
mysql> select * from userinfo;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 11 |
| 2 | lisi | 12 |
| 3 | dd | 12 |
| 4 | dd | 11 |
+----+----------+------+
4 rows in set (0.00 sec)
#session2 當前會話2 可以操作其他行數(shù)據(jù)。
mysql> update userinfo set age=12 where id=2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
#session2 當前會話2修改數(shù)據(jù)涉及到id=1的行，所以被阻塞知道行鎖被釋放
mysql> update userinfo set age=12 ;
。。。。一直被阻塞，直到sesssion1 執(zhí)行commit語句。
#session1 回話1 提交數(shù)據(jù)，也就是釋放鎖
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
1.5意向鎖
意向共享鎖和意向排它鎖總稱為意向鎖。意向鎖的出現(xiàn)是為了支持Innodb支持多粒度鎖。它是表級別的鎖。兩種鎖含義如下：
1.意向共享鎖：事務想要在獲得表中某些記錄的共享鎖（讀鎖），需要在表上先加意向共享鎖。
2.意向互斥鎖：事務想要在獲得表中某些記錄的互斥鎖（寫鎖），需要在表上先加意向互斥鎖。
當我們需要給一個加表鎖的時候，我們需要根據(jù)意向鎖去判斷表中有沒有數(shù)據(jù)行被鎖定，以確定是否能加成功。如果意向鎖是行鎖，那么我們就得遍歷表中所有數(shù)據(jù)行來判斷。如果意向鎖是表鎖，則我們直接判斷一次就知道表中是否有數(shù)據(jù)行被鎖定了。所以說將意向鎖設置成表級別的鎖的性能比行鎖高的多。
所以一項所的作用就是當一個事務在需要獲取資源的鎖定時，如果該資源已經被排他鎖占用，則數(shù)據(jù)庫會自動給該事務申請一個該表的意向鎖。如果自己需要一個共享鎖定，就申請一個意向共享鎖。如果需要的是某行（或者某些行）的排他鎖定，則申請一個意向排他鎖。
業(yè)務執(zhí)行流程如下：
有了意向鎖之后，事務A在申請行鎖（寫鎖）之前，數(shù)據(jù)庫會自動先給事務A申請表的意向排他鎖。當事務B去申請表的寫鎖時就會失敗，因為表上有意向排他鎖之后事務B申請表的寫鎖時會被阻塞。明顯提高性能?？焖倥袛嗪么吮碛斜绘i的數(shù)據(jù)，所以，不能獲取到此表的表級別的鎖。
1.6 樂觀鎖
樂觀鎖不是數(shù)據(jù)庫自帶的，是需要我們自己寫業(yè)務去實現(xiàn)。樂觀鎖是指操作數(shù)據(jù)庫時(更新操作)，想法很樂觀，認為這次的操作不會導致沖突，在操作數(shù)據(jù)時，并不進行任何其他的特殊處理（也就是不加鎖），而在進行更新后，再去判斷是否有沖突了。
操作流程如下：
在表中的數(shù)據(jù)進行更新操作時，先給數(shù)據(jù)表加一個版本(version)字段，每操作一次，將那條記錄的版本號加1。也就是先查詢出那條記錄，獲取出version字段,如果要對那條記錄進行操作(更新),則先判斷此刻version的值是否與剛剛查詢出來時的version的值相等，如果相等，則說明這段期間，沒有其他程序對其進行操作，則可以執(zhí)行更新，將version字段的值加1；如果更新時發(fā)現(xiàn)此刻的version值與剛剛獲取出來的version的值不相等，則說明這段期間已經有其他程序對其進行操作了，則不進行更新操作。這樣做的好處是避免了長事務中的數(shù)據(jù)庫加鎖開銷，大大提升了大并發(fā)量下的系統(tǒng)整體性能表現(xiàn)。
偽業(yè)務代碼如下：
#首先獲取修改之前的數(shù)據(jù)和版本號
SELECT data AS old_data, version AS old_version FROM …;
#進行修改，條件是當前數(shù)據(jù)的版本號和之前的老版本號一致，表示沒有人進行過修改
Int updaterow= UPDATE SET data = new_data, version = new_version WHERE version = old_version
// 表示修改成功，拿到了樂觀鎖
if (updated row > 0) {
// 樂觀鎖獲取成功，操作完成
} else {
// 樂觀鎖獲取失敗，回滾并重試
}
1.7 悲觀鎖
悲觀鎖指的是對數(shù)據(jù)被外界（包括本系統(tǒng)當前的其他事務，以及來自外部系統(tǒng)的事務處理）修改持保守態(tài)度，因此，在整個數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機制（也只有數(shù)據(jù)庫層提供的鎖機制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在本系統(tǒng)中實現(xiàn)了加鎖機制，也無法保證外部系統(tǒng)不會修改數(shù)據(jù)）
案例如下：
#關閉自動提交后
set autocommit=0;
#開始事務
start transaction;
#查詢當前商品信息，然后通過for update鎖定數(shù)據(jù)防止其他事務修改
select status from goods where goodsid=1 for update;
#根據(jù)商品信息生成訂單
insert into orders (id,goodsid) values (null,1);
#修改商品status為2
update t_goods set status=2;
#執(zhí)行完畢,提交事務
commit;
上述案例就實現(xiàn)了悲觀鎖，悲觀鎖就是悲觀主義者，它會認為我們在事務A中操作數(shù)據(jù)1的時候，一定會有事務B來修改數(shù)據(jù)1,所以，在第2步我們將數(shù)據(jù)查詢出來后直接加上排它鎖（X）鎖，防止別的事務來修改事務1，直到我們commit后，才釋放了排它鎖。
需要注意的是，樂觀鎖保證了數(shù)據(jù)處理時的安全性但是更新有可能會失敗，甚至是更新幾次都失敗，這是有風險的。所以如果寫入居多，對吞吐要求不高，可使用悲觀鎖。而且要注意的是悲觀鎖加鎖造成了開銷增加，降低了性能，而且還會出現(xiàn)死鎖。
1.8 間歇鎖
間隙鎖（Gap Lock）是Innodb在提交下為了解決幻讀問題時引入的鎖機制，（下面的所有案例沒有特意強調都使用可重復讀隔離級別）幻讀的問題存在是因為新增或者更新操作，這時如果進行范圍查詢的時候（加鎖查詢），會出現(xiàn)不一致的問題，這時使用不同的行鎖已經沒有辦法滿足要求，需要對一定范圍內的數(shù)據(jù)進行加鎖，間隙鎖就是解決這類問題的。在可重復讀隔離級別下，數(shù)據(jù)庫是通過行鎖和間隙鎖共同組成的（next-key lock），來實現(xiàn)的。加鎖規(guī)則有以下特性：
1.加鎖的基本單位是（next-key lock）,他是前開后閉原則
2.插敘過程中訪問的對象會增加鎖
3.索引上的等值查詢--給唯一索引加鎖的時候，next-key lock升級為行鎖
4.索引上的等值查詢--向右遍歷時最后一個值不滿足查詢需求時，next-key lock 退化為間隙鎖
5.唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
id(主鍵) | name | age |
5 | name1 | 18 |
10 | name2 | 19 |
15 | name3 | 20 |
20 | name4 | 21 |
25 | name5 | 22 |
以上數(shù)據(jù)為了解決幻讀問題，更新的時候不只是對上述的五條數(shù)據(jù)增加行鎖，還對于中間的取值范圍增加了6間隙鎖，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+∞]
如下案例：
#session1 操作會話session1 開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#session1 更新id=11的數(shù)據(jù)，獲取行鎖。
mysql> select * from userinfo where id=11 for update;
Empty set (0.00 sec)
#session2 操作會話session2 開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#session2 在會話session2 新增數(shù)據(jù)主鍵為12
mysql> insert into userinfo values(12,'hh',18);
.... 一直處于阻塞狀態(tài)
#如果等待時間過長，session1沒有釋放鎖，會拋出如下異常。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
如上事務session1和事務session2時，事務session1會對數(shù)據(jù)庫表增加（10，15]這個區(qū)間鎖，這時insert id = 12 的數(shù)據(jù)的時候就會因為區(qū)間鎖（10，15]而被鎖住無法執(zhí)行。
特別注意，間歇鎖，如果操作不當會出現(xiàn)死鎖。如下：
事務A | 事務B |
begin; | |
begin; insert into user value(7,7,7) | |
insert into user value(7,7,7) |
如上，事務A獲取到（5,10]之間的間隙鎖不允許其他的DDL操作，在事務提交，間隙鎖釋放之前，事務B也獲取到了間隙鎖（5,10]，這時兩個事務就處于死鎖狀態(tài)
1.9記錄鎖
記錄鎖，它封鎖索引記錄，作用于唯一索引上。如果執(zhí)行這條語句（select * from userinfo where id=4 for update;）它會在id=4的索引記錄上加鎖，以阻止其他事務插入，更新，刪除id=4的這一行。就是說，當一個會話執(zhí)行這條語句之后，其它會話，執(zhí)行下面這幾種語句，都會處于阻塞狀態(tài)。
select * from userinfo where id=1 for update;
delete from userinfo where id=4;
insert into userinfo values(4,'hh',18);
阻塞時間過長可能出拋出如下錯誤：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
需要注意的是，如果把事務的隔離級別降級為讀提交(Read Committed, RC)，間隙鎖則會自動失效。
1.10臨鍵鎖
臨鍵鎖，作用于非唯一索引上，是記錄鎖與間隙鎖的組合，如下圖表格。
id(主鍵) | name | age |
5 | name1 | 18 |
10 | name2 | 19 |
15 | name3 | 20 |
20 | name4 | 21 |
25 | name5 | 22 |
它的封鎖范圍，既包含索引記錄，又包含索引之前的區(qū)間，即（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+∞]。
如下在事務中執(zhí)行。
Update userinfo SET age=19 where id= 10;
Select * from userinfo where id=10 FOR UPDATE;
這兩個語句都會鎖定（5,10]，(10,15)這兩個區(qū)間。即， InnoDB 會獲取該記錄行的 臨鍵鎖 ，并同時獲取該記錄行下一個區(qū)間的間隙鎖。
1.11 死鎖
死鎖是指兩個或兩個以上事務在執(zhí)行過程中因爭搶鎖資源而造成的互相等待的現(xiàn)象。如下圖7-3所示的操作流程。
上圖所示，這兩種情況即為死鎖產生的常規(guī)情景。事務A等著事務B釋放鎖，事務B等著事務A釋放鎖。就會出現(xiàn)相互等待，一直等待下去。避免這種問題的辦法有兩種，一種是，等待事務超時，主動回滾。第二種是進行死鎖檢查，主動回滾某條事務，讓別的事務能繼續(xù)走下去?？梢允褂孟嚓P指令查詢當前查看正在被鎖的事務。
#查看正在被鎖的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#可以通過如下指令，殺死當前事務進程，釋放鎖。trx_id字段對應上面語句結果對應的第一個字段值
kill trx_id;
作者 | YLY969
來源 | https://www.cnblogs.com/yiming15/p/15001258.html