InnoDB鎖機制

??InnoDB存儲引擎?持?級鎖，其?類可以細分為共享鎖和排它鎖兩類

1. 共享鎖(S)：允許擁有共享鎖的事務(wù)讀取該?數(shù)據(jù)播演。當(dāng)?個事務(wù)擁有??的共享鎖時，另外的事務(wù)可以在同??數(shù)據(jù)也獲得共享鎖伴奥，但另外的事務(wù)?法獲得同??數(shù)據(jù)上的排他鎖
2. 排它鎖(X)：允許擁有排它鎖的事務(wù)修改或刪除該?數(shù)據(jù)写烤。當(dāng)?個事務(wù)擁有??的排他鎖時，另外的事務(wù)在此?數(shù)據(jù)上?法獲得共享鎖和排它鎖拾徙，只能等待第?個事務(wù)的鎖釋放

??除了共享鎖和排他鎖之外洲炊，InnoDB也?持意圖鎖。該鎖類型是屬于表級鎖尼啡，表明事務(wù)在后期會對該表的?施加共享鎖或者排它鎖选浑。所以對意圖鎖也有兩種類型：

1. 共享意圖鎖(IS)：事務(wù)將會對表的?施加共享鎖
2. 排他意圖鎖(IX)：事務(wù)將會對表的?施加排它鎖

??舉例來說select … for share mode語句就是施加了共享意圖鎖，?select … for update語句就是施加了排他意圖鎖
這四種鎖之間的相互共存和排斥關(guān)系如下：

InnoDB鎖相關(guān)系統(tǒng)表

Information_schema.innodb_trx記錄了InnoDB中每?個正在執(zhí)?的事務(wù)读恃，包括該事務(wù)獲得的鎖信息，事務(wù)開始時間代态，事務(wù)是否在等待鎖等信息

??performance_schema.data_locks記錄了InnoDB中事務(wù)的每個鎖信息寺惫，以及當(dāng)前事務(wù)的鎖正在阻?其他事務(wù)獲得鎖

InnoDB鎖機制

?級鎖

???級鎖是施加在索引?數(shù)據(jù)上的鎖，?如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE語句是在t.c1=10的索引?上增加鎖蹦疑，來阻?其他事務(wù)對對應(yīng)索引?的insert/update/delete操作西雀。當(dāng)?個InnoDB表沒有任何索引時，則?級鎖會施加在隱含創(chuàng)建的聚簇索引上歉摧，所以說當(dāng)?條sql沒有?任何索引時艇肴，那么將會在每?條聚集索引后?加X鎖，這個類似于表鎖叁温，但原理上和表鎖應(yīng)該是完全不同的

 mysql> create table temp(id int,name varchar(10));
 Query OK, 0 rows affected (0.01 sec)

 mysql> insert into temp values(1,'a'),(2,'b'),(3,'c');

 mysql> alter table temp add primary key(id); ##增加索引之后
 Query OK, 0 rows affected (0.01 sec)

間隔鎖

??當(dāng)我們?范圍條件?不是相等條件檢索數(shù)據(jù)再悼，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖膝但；對于鍵值在條件范圍內(nèi)但并不存在的記錄冲九，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖
??間隔鎖是施加在索引記錄之間的間隔上的鎖跟束，鎖定?個范圍的記錄莺奸、但不包括記錄本身,?如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE語句丑孩，盡管有可能對c1字段來說當(dāng)前表?沒有=15的值，但還是會阻?=15的數(shù)據(jù)的插?操作灭贷，是因為間隔鎖已經(jīng)把索引查詢范圍內(nèi)的間隔數(shù)據(jù)也都鎖住了
??間隔鎖的使?只在部分事務(wù)隔離級別才是?效的
??間隔鎖只會阻?其他事務(wù)的插?操作

• gap lock的前置條件：
  1 事務(wù)隔離級別為REPEATABLE-READ温学，且sql?的索引為?唯? 索引（?論是等值檢索還是范圍檢索）
  2 事務(wù)隔離級別為REPEATABLE-READ，且sql是?個范圍的當(dāng)前 讀操作氧腰，這時即使是唯?索引也會加gap lock

innodb_locks_unsafe_for_binlog(強制不使?間隔鎖)參數(shù)在8.0中已經(jīng)取消

 mysql> CREATE TABLE `temp` ( 
  `id` int(11) NOT NULL
  ,`name` varchar(10) DEFAULT NULL
  ,PRIMARY KEY (`id`)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1
 mysql> insert into temp values(1,'a'),(2,'b'),(3,'c');

 mysql> update temp set name='abc' where id>4;
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 0 Changed: 0 Warnings: 0

鏈接2：

 mysql> insert into temp values(4,‘a(chǎn)bc’); ##等待

Next-key鎖

??在默認情況下，mysql的事務(wù)隔離級別是可重復(fù)讀古拴，默認采?next-key locks箩帚。所謂Next-Key Locks，就是記錄鎖和間隔鎖的結(jié)合黄痪， 即除了鎖住記錄本身紧帕，還要再鎖住索引之間的間隙。

插?意圖鎖

??插?意圖鎖是在插?數(shù)據(jù)時?先獲得的?種間隔鎖桅打，對這種間隔鎖是嗜，只要不同的事務(wù)插?的數(shù)據(jù)位置是不?樣的，即使都是同?個間隔挺尾，也不會產(chǎn)?互斥關(guān)系鹅搪，?如有?個索引有4和7兩個值，如果兩個事務(wù)分別插?5和6兩個值時遭铺，雖然兩個事務(wù) 都會在索引4和7之間施加間隔鎖丽柿，但由于后續(xù)插?的數(shù)值不?樣，所以兩者不會互斥魂挂。
???如下例中事務(wù)A對索引>100的值施加了排他間隔鎖甫题，?事務(wù)B在插?數(shù)據(jù)之前就試圖先施加插?意圖鎖?必須等待
事務(wù)A：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql>  INSERT INTO child (id) values (90),(102);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事務(wù)B：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO child (id) VALUES (101);#插入被阻止，直到事務(wù)A commit
Query OK, 1 row affected (4.94 sec)

可以通過show engine innodb status命令查看插?意向鎖被阻?

?增鎖

???增鎖是針對事務(wù)插?表中?增列時施加的?種特殊的表級鎖涂召，即當(dāng)?個事務(wù)在插??增數(shù)據(jù)時坠非，另?個事務(wù)必須等待前?個事務(wù)完成插?，以便獲得順序的?增值
??參數(shù)innodb_autoinc_lock_mode可以控制?增鎖的使??法

InnoDB鎖相關(guān)系統(tǒng)變量

查看當(dāng)前系統(tǒng)隔離級別

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.03 sec)

查看是否開啟?動提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>  show variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

查看innodb事務(wù)等待事務(wù)的超時時間（秒）

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

鏈接1：

 Mysql> set autocommit=0;
 mysql> update temp set name='abc' where id>4;

鏈接2：

 Mysql> set autocommit=0;
 mysql> insert into temp values(4,'abc');
 …… ## 等待50秒后超時果正，事務(wù)回滾
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB事務(wù)隔離級別

InnoDB存儲引擎提供了四種事務(wù)隔離級別炎码，分別是：

• READ UNCOMMITTED：讀取未提交內(nèi)容
• READ COMMITTED：讀取提交內(nèi)容
• REPEATABLE READ：可重復(fù)讀，默認值舱卡。
• SERIALIZABLE：串?化
  ??可以通過 --transaction-isolation 參數(shù)設(shè)置實例級別的事務(wù)隔離級別辅肾，也可以通過set [session/global] transaction isolation level語句修改當(dāng)前數(shù)據(jù)庫鏈接或者是后續(xù)創(chuàng)建的所有數(shù)據(jù)庫鏈接的事務(wù)隔離級別，每個事務(wù)隔離級別所對應(yīng)的鎖的使??法都有所不同轮锥。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

1. REPEATABLE READ：
   ??可重復(fù)讀矫钓，默認值。表明對同?個事務(wù)來說第?次讀數(shù)據(jù)時會創(chuàng)建快照，在事務(wù)結(jié)束前的其他讀操作(不加鎖)會獲得和第?次讀相同的結(jié)果新娜。當(dāng)讀操作是加鎖的讀語句(select … forupdate或者lock in share mode),或者update和delete語句時赵辕，加鎖的?式依賴于語句是否使?唯?索引訪問唯?值或者范圍值
   ??當(dāng)訪問的是唯?索引的唯?值時，則InnoDB會在索引?施加?鎖
   ??當(dāng)訪問唯?索引的范圍值時，則會在掃描的索引?上增加間隔鎖或者next-key鎖以防?其他鏈接對此范圍的插?
2. READ COMMITTED：
   ??讀取提交內(nèi)容。意味著每次讀都會有??最新的快照栅迄。對于加鎖讀語句(select … for update和lock in share mode)，或者update蚕键，delete語句會在對應(yīng)的?索引上增加鎖，但不像可重復(fù)讀?樣會增加間隔鎖衰粹，因此其他的事務(wù)執(zhí)?插?操作時如果是插??索引?上的數(shù)值锣光，則不影響插?。
   ??由于該隔離級別是禁?間隔鎖的铝耻，所以會導(dǎo)致幻讀的情況

幻讀:事務(wù)A 按照一定條件進行數(shù)據(jù)讀取誊爹， 期間事務(wù)B 插入了相同搜索條件的新數(shù)據(jù)，事務(wù)A再次按照原先條件進行讀取時瓢捉，發(fā)現(xiàn)了事務(wù)B 新插入的數(shù)據(jù) 稱為幻讀
不可重復(fù)讀:如果事務(wù)A 按一定條件搜索频丘， 期間事務(wù)B 刪除了符合條件的某一條數(shù)據(jù)，導(dǎo)致事務(wù)A 再次讀取時數(shù)據(jù)少了一條泡态。這種情況歸為 不可重復(fù)讀

??如果是使?此隔離級別搂漠，就必須使??級別的?進制?志
??此隔離級別還有另外的特點：
??對于update和delete語句只會在約束條件對應(yīng)的?上增加鎖
??對update語句來說，如果對應(yīng)的?上已經(jīng)有鎖某弦，則InnoDB會執(zhí)?半?致讀的操作状答，來確定update語句對應(yīng)的?在上次commit之后的數(shù)據(jù)是否在鎖的范圍，如果不是刀崖，則不影響update操作，如果是拍摇，則需要等待對應(yīng)的鎖解開

?如如下情況：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

COMMIT; 

表中并沒有任何索引亮钦，所以會使?隱藏創(chuàng)建的聚簇索引來施加?級鎖,當(dāng)?shù)?個鏈接執(zhí)?修改:

SET autocommit = 0;
UPDATE t SET b = 5 WHERE b = 3; 

之后第?個鏈接執(zhí)?修改：

SET autocommit = 0;
UPDATE t SET b = 4 WHERE b = 2;

對可重復(fù)讀隔離級別來說，第?個事務(wù)的修改會在每?記錄上都增加排他鎖充活，并且直到事務(wù)結(jié)束后鎖才會釋放

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

?第?個事務(wù)會?直等待前?事務(wù)的鎖被釋放后才能執(zhí)?

x-lock(1,2); block and wait for first UPDATE to commit or roll back

對讀取提交內(nèi)容事務(wù)隔離級別來說蜂莉，第?個修改操作會在所有?上都加排他鎖，即首先第一條語句同樣會獲取所有行的x-lock混卵，然后它會去檢查where條件映穗，如果不匹配會立即釋放掉這條記錄的x-lock

x-lock(1,2); unlock(1,2) releases those for rows that it does not modify
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

?第?個事務(wù)通過半?致讀的?式判斷每?的最后commit的數(shù)據(jù)是否在修改的范圍?，會在未加鎖的?上加上排他鎖(即 第二條查詢語句會先讀取每條記錄的一個最新的快照幕随，然后去檢查where條件是否匹配蚁滋。)

3. READ UNCOMMITTED：
   讀取未提交內(nèi)容，所讀到的數(shù)據(jù)可能是臟數(shù)據(jù)
4. SERIALIZABLE：
   串?化，此隔離級別更接近于可重復(fù)讀這個級別辕录，只是當(dāng)autocommit功能被禁?后睦霎，InnoDB引擎會將每個select語句隱含的轉(zhuǎn)化為select … lock in share mode
   
   
   

Autocommit/commit/rollback

??當(dāng)設(shè)置autocommit屬性開啟時，每個SQL語句都會隱含成為獨?的事務(wù)走诞。
??默認情況下autocommit屬性是開啟的副女，也就意味著當(dāng)每個SQL語句最后執(zhí)?結(jié)果不返回錯誤時都會執(zhí)?commit語句
??當(dāng)返回失敗時會執(zhí)?rollback語句。?當(dāng)autocommit屬性開啟時蚣旱，可以通過執(zhí)?start transaction或者begin語句來顯示的開啟?個事務(wù)碑幅，?事務(wù)?可以包含多個SQL語句，最終事務(wù)的結(jié)束是由commit或者rollback來終結(jié)
???當(dāng)在數(shù)據(jù)庫鏈接?執(zhí)?set autocommit=0代表當(dāng)前數(shù)據(jù)庫鏈接禁??動提交塞绿，事務(wù)的終結(jié)由commit或者rollback決定沟涨，同時也意味著下?個事務(wù)的開始
??如果?個事務(wù)在autocommit=0的情況下數(shù)據(jù)庫鏈接退出?沒有執(zhí)?commit語句，則這個事務(wù)會回滾
???些特定的語句會隱含的終結(jié)事務(wù)位隶，就好?是執(zhí)?了commit語句
??commit語句代表將此事務(wù)的數(shù)據(jù)修改永久化拷窜，并對其他事務(wù)可?，?rollback則代表將此事務(wù)的數(shù)據(jù)修改回滾涧黄。
??commit和rollback都會把當(dāng)前事務(wù)執(zhí)?所施加的鎖釋放
??當(dāng)使?多語句事務(wù)時篮昧，如果全局的autocommit屬性是開啟的，則開始此事務(wù)的?式可以使set autocommit=0將當(dāng) 前鏈接的屬性關(guān)閉笋妥，最后執(zhí)?commit和rollback懊昨；或者是顯示的使?start transaction語句開啟事務(wù)

 mysql> -- Do a transaction with autocommit turned on.
 mysql> START TRANSACTION;
 mysql> INSERT INTO customer VALUES (10, 'Heikki');
 Query OK, 1 row affected (0.00 sec)

 mysql> COMMIT;
 Query OK, 0 rows affected (0.00 sec)

 mysql> -- Do another transaction with autocommit turned off.

 mysql> SET autocommit=0;
 mysql> INSERT INTO customer VALUES (15, 'John');
 Query OK, 1 row affected (0.00 sec)

 mysql> INSERT INTO customer VALUES (20, 'Paul');
 Query OK, 1 row affected (0.00 sec)

 mysql> DELETE FROM customer WHERE b = 'Heikki';
 Query OK, 1 row affected (0.00 sec)

 mysql> -- Now we undo those last 2 inserts and the delete.

 mysql> ROLLBACK;
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT * FROM customer;
 +------+--------+
 | a | b |
 +------+--------+
 | 10 | Heikki |
 +------+--------+

?致讀

??在默認的隔離級別下?致讀是指InnoDB在多版本控制中在事務(wù)的?次讀時產(chǎn)??個鏡像，在?次讀時間點之前其他事務(wù)提交的修改可以讀取到春宣，??次讀時間點之后其他事務(wù)提交的修改或者是未提交的修 改都讀取不到
??唯?例外的情況是在?次讀時間點之前的本事務(wù)未提交的修改數(shù)據(jù)可以讀取到
??在讀取提交數(shù)據(jù)隔離級別下酵颁，?致讀的每個讀取操作都會有??的鏡像
???致讀操作不會施加任何的鎖，所以就不會阻?其他事務(wù)的修改動作

在下?的例?中月帝，鏈接A對鏈接B所做的修改躏惋，只有在它的事務(wù)和鏈接B的事務(wù)都提交的情況下才能看到

?致讀在某些DDL語句下不?效：

1. 碰到drop table語句時，由于InnoDB不能使?被drop的表嚷辅，所以?法實現(xiàn)?致讀
2. 碰到alter table語句時簿姨，也?法實現(xiàn)?致讀
3. 當(dāng)碰到insert into… select,update … select和create table … select語句時，在默認的事務(wù)隔離級別下簸搞，語句的執(zhí)?更類似于在讀取提交數(shù)據(jù)的隔離級別下

加鎖讀操作

??當(dāng)在?個事務(wù)中在讀操作結(jié)束后會執(zhí)?insert和update操作時扁位，普通的讀操作?法阻?其他事務(wù)對相同數(shù)據(jù)執(zhí)?修改操作，所以InnoDB提供了兩種在讀操作時就增加鎖的?式
??select … lock in share mode：在讀取的?數(shù)據(jù)上施加共享鎖趁俊，其他的事務(wù)可以讀相同的數(shù)據(jù)但?法修改域仇；如果在執(zhí)?此語句時有其他事務(wù)對相同的數(shù)據(jù)已經(jīng)施加了鎖，則需要等待事務(wù)完結(jié)釋放鎖
??select … for update：和update操作?樣寺擂，在涉及的?上施加排他鎖暇务，并阻?任何其他事務(wù)對涉及?上的修改操作泼掠、以及加鎖讀操作，但不會阻?對涉及?上的?般讀（不加鎖）操作
???如在?表中插???數(shù)據(jù)般卑，要確保對應(yīng)的列在?表中有值武鲁，通過?般的讀操作先查?表有值然后再插?的?法是不保險的，因為在讀操作和插?操作之間就有可能其他事務(wù)會將?表的數(shù)據(jù)修改掉蝠检。那保險的做法是在查詢?表是?加鎖讀的?式沐鼠，?如：SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
??再?如當(dāng)表中有?個?數(shù)計數(shù)字段時，使??致讀和lock in sharemode都有可能導(dǎo)致重復(fù)錯誤數(shù)據(jù)出現(xiàn)叹谁，因為有可能兩個事務(wù)會讀到相同的值饲梭，在這種情況下就要使?select … for update語句保證?個事務(wù)在讀時，另?個事務(wù)必須等待

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

具體例子：
商品goods表中有一個字段status焰檩，status為1代表商品未被下單憔涉，status為2代表商品已經(jīng)被下單，那么我們對某個商品下單時必須確保該商品status為1析苫。假設(shè)商品的id為1兜叨。如果不采用鎖，那么操作方法如下：

//1.查詢出商品信息
select status from t_goods where id=1;
//2.根據(jù)商品信息生成訂單
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status為2
update t_goods set status=2;

在上面的場景中衩侥，商品信息從查詢出來到修改国旷，中間有一個處理訂單的過程，使用select … for update的原理就是茫死，當(dāng)我們在查詢出goods信息后就把當(dāng)前的數(shù)據(jù)鎖定跪但，直到我們修改完畢后再解鎖。那么在這個過程中峦萎，因為goods被鎖定了屡久，就不會出現(xiàn)有第三者來對其進行修改了。要使用悲觀鎖爱榔，我們必須關(guān)閉mysql數(shù)據(jù)庫的自動提交屬性被环。

set autocommit=0;　　
//設(shè)置完autocommit后，我們就可以執(zhí)行我們的正常業(yè)務(wù)了详幽。具體如下：
//0.開始事務(wù)
begin;/begin work;/start transaction; (三者選一就可以)
//1.查詢出商品信息
select status from t_goods where id=1 for update;
//2.根據(jù)商品信息生成訂單
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status為2
update t_goods set status=2;
//4.提交事務(wù)
commit;/commit work;

SQL語句對應(yīng)的鎖

??加鎖讀蛤售，修改和刪除SQL語句都會在索引掃描過的每??增加鎖，也就是說不光是在where條件限制的索引?上增加鎖妒潭，也會對掃描到的間隔增加間隔鎖
??如果SQL語句是使??級索引查找數(shù)據(jù)?且施加的是排他鎖，則InnoDB也會在對應(yīng)的聚簇索引?上施加鎖
??如果SQL語句沒有任何索引可以使?揣钦，則MySQL需要掃描全表數(shù)據(jù)雳灾，?每?數(shù)據(jù)都會被施加鎖，所以?個良好的習(xí)慣是為InnoDB添加合適的索引
??針對不同的語句冯凹，InnoDB會施加不同的鎖：
??Select…from語句屬于?致性讀谎亩，在默認情況下不施加任何的鎖炒嘲，除?在可串?化隔離級別下，會施加共享next-key鎖在掃描的索引?上匈庭，當(dāng)碰到使?唯?索引查找唯?值時只在唯?值上施加鎖
??Select…lock in share mode語句會在索引掃描?上施加共享next-key鎖夫凸，除?是當(dāng)碰到使?唯?索引查找唯?值時只在唯?值上施加鎖
??Select…for update語句會對掃描索引的?上施加排他next-key鎖，除?是當(dāng)碰到使?唯?索引查找唯?值時只在唯?值上施加鎖
??Update語句會對掃描索引的?上施加排他next-key鎖阱持，除?是當(dāng)碰到使?唯?索引查找唯?值時只在唯?值上施加鎖夭拌。
??Delete語句會對掃描索引的?上施加排他next-key鎖，除?是當(dāng)碰到使?唯?索引查找唯?值時只在唯?值上施加鎖
??Insert語句會對索引掃描的?上施加鎖衷咽，但不是next-key鎖鸽扁，所以不會阻?其他事務(wù)對該?值前的間隔上插?數(shù)據(jù)
??Insert into T select…from S語句會對插?到T表的?施加排他鎖（?間隔鎖），?在默認隔離級別下會對訪問的S表上的?施加共享next-key鎖
??當(dāng)表上有外鍵約束時镶骗，對任何的insert,update和delete操作都會在需要檢查外鍵約束的?上施加共享?鎖
??Lock table語句是施加表級鎖

幻讀

??幻讀問題發(fā)?在同?個事務(wù)中當(dāng)相同的讀操作在前后兩次讀數(shù)據(jù)時返回不同的結(jié)果集桶现。
???如在表的ID字段上有?個索引，當(dāng)希望對ID>100的數(shù)據(jù)進?后續(xù)修改時鼎姊，我們會使?如下的語句： SELECT * FROM child WHERE id > 100 FOR UPDATE骡和，?如果表??前只有90和102兩個值時，如果沒有間隔鎖鎖住90到102之間的間隔相寇，則其他的事務(wù)會插??如101這個值慰于，這樣的話在第?次讀數(shù)據(jù)時就會返回三?記錄?導(dǎo)致幻讀
??為了阻?幻讀情況的發(fā)?，InnoDB使?了?種?法next-key鎖將索引?鎖和間隔鎖合并在?起裆赵。InnoDb會在索引掃描的?上施加?級共享鎖或者排他鎖东囚，?next-key鎖也會在每個索引?之前的間隔上施加鎖，會導(dǎo)致其他的session不能在每個索引之前的間隔內(nèi)插?新的索引值
??間隔鎖會施加在索引讀碰到的?數(shù)據(jù)上战授，所以對上例來說為了阻?插?任何>100的值页藻，也會將最后掃描的索引值102之前的間隔鎖住

 mysql> show status like '%innodb_row_lock%';
 +-------------------------------+-------+
 | Variable_name                 | Value |
 +-------------------------------+-------+
 | Innodb_row_lock_current_waits | 0     |
 | Innodb_row_lock_time          | 0     |
 | Innodb_row_lock_time_avg      | 0     |
 | Innodb_row_lock_time_max      | 0     |
 | Innodb_row_lock_waits         | 0     |
 +-------------------------------+-------+

Innodb_row_lock_current_waits：當(dāng)前等待鎖的數(shù)量
Innodb_row_lock_time：系統(tǒng)啟動到現(xiàn)在、鎖定的總時間?度
Innodb_row_lock_time_avg：每次平均鎖定的時間
Innodb_row_lock_time_max：最??次鎖定時間
Innodb_row_lock_waits：系統(tǒng)啟動到現(xiàn)在植兰、總共鎖定次數(shù)

InnoDB死鎖

??死鎖的情況發(fā)?在不同的的事務(wù)相互之間擁有對?需要的鎖份帐，?導(dǎo)致相互?直?限等待
??死鎖可能發(fā)?在不同的事務(wù)都會對多個相同的表和相同的?上施加鎖，但事務(wù)對表的操作順序不相同
??為了減少死鎖的發(fā)?楣导，要避免使?lock table語句废境，要盡量讓修改數(shù)據(jù)的范圍盡可能的?和快速；當(dāng)不同的事務(wù)要修改多個表或者?量數(shù)據(jù)時筒繁，盡可能的保證修改的順序在事務(wù)之間要?致
??默認情況下InnoDB下的死鎖?動偵測功能是開啟的噩凹，當(dāng)InnoDB發(fā)現(xiàn)死鎖時，會將其中的?個事務(wù)作為犧牲品回滾毡咏。

可以通過設(shè)置innodb_deadlock_detect參數(shù)可以打開或關(guān)閉死鎖檢測:
innodb_deadlock_detect = on 打開死鎖檢測驮宴，數(shù)據(jù)庫發(fā)生死鎖時自動回滾（默認選項）
innodb_deadlock_detect = off 關(guān)閉死鎖檢測，發(fā)生死鎖的時候呕缭，用鎖超時來處理堵泽，通過設(shè)置鎖超時參innodb_lock_wait_timeout可以在超時發(fā)生時回滾被阻塞的事務(wù)

??通過innodb_deadlock_detect參數(shù)配置?動偵測功能是否開啟修己，如果關(guān)閉的話，InnoDB就使?innodb_lock_wait_timeout參數(shù)來?動回滾等待?夠時間的事務(wù)
??可以通過show engine innodb status語句查看最后?次發(fā)?死鎖的情況

?如以下例?產(chǎn)?的死鎖：
鏈接1：

 mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
 Query OK, 0 rows affected (1.07 sec)

 mysql> INSERT INTO t (i) VALUES(1);
 Query OK, 1 row affected (0.09 sec)

 mysql> START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; ##在 i=1記錄上加共享鎖
 +------+
 | i    |
 +------+
 | 1    | 
 +------+

鏈接2：

mysql> START TRANSACTION;
 Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1; ##請求在i=1的記錄上增加排他鎖迎罗，但被鏈接1的事務(wù)阻?

鏈接1：

mysql> DELETE FROM t WHERE i = 1;
 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

??這個死鎖發(fā)?是因為鏈接1試圖施加排他鎖睬愤，但因為鏈接2上的事務(wù)已經(jīng)在請求排他鎖，?這個鎖的釋放必須要等待鏈接1上的事務(wù)釋放共享鎖纹安，?鏈接1上原本的共享鎖由于順序的原因也?法升級為排它鎖尤辱，所以就導(dǎo)致了死鎖的發(fā)?。

InnoDB死鎖檢測和回滾

??默認情況下死鎖檢測功能是開啟的钻蔑，當(dāng)死鎖發(fā)?時InnoDB會?動檢測到并犧牲(回滾)其中的?個或者?個事務(wù)啥刻，以便讓其他的事務(wù)繼續(xù)執(zhí)?下去。
??InnoDB選擇犧牲的事務(wù)往往是代價?較?的事務(wù)咪笑，其代價計算是根據(jù)事務(wù)insert,update, delete的數(shù)據(jù)?規(guī)模決定
??如果事務(wù)中的某個語句因為錯誤?回滾可帽，則這個語句上的鎖可能還會保留，是因為InnoDB僅會存儲?鎖信息窗怒，?不會存儲?鎖是由事務(wù)中的哪個語句產(chǎn)?的
??如果在?個事務(wù)中映跟，select語句調(diào)?了函數(shù)，?函數(shù)中的某個語句執(zhí)?失敗扬虚，則那個語句會回滾努隙，如果在整個事務(wù)結(jié)束時執(zhí)?rollback，則整個事務(wù)回滾
??可以通過innodb_deadlock_detect 參數(shù)關(guān)閉死鎖檢測功能辜昵，?僅僅?innodb_lock_wait_timeout的功能來釋放鎖等待

減少死鎖發(fā)?的?法

??在事務(wù)性數(shù)據(jù)庫中荸镊，死鎖是個經(jīng)典的問題，但只要發(fā)?的頻率不?則死鎖問題不需要太過擔(dān)?
查看死鎖的?法有兩種：

通過show engine innodb status命令可以查看最后?個死鎖的情況
通過innodb_print_all_deadlocks參數(shù)配置可以將所有死鎖的信息都打印到MySQL的錯誤?志中

減少死鎖發(fā)?的?法：

• 盡可能的保持事務(wù)?型化堪置，減少事務(wù)執(zhí)?的時間可以減少發(fā)?影響的概率
• 及時執(zhí)?commit或者rollback躬存，來盡快的釋放鎖
• 可以選?較低的隔離級別，?如如果要使?select... for update和select...lock in share mode語句時可以使?讀取提交數(shù)據(jù)隔離級別
• 當(dāng)要訪問多個表數(shù)據(jù)或者要訪問相同表的不同?集合時舀锨，盡可能的保證每次訪問的順序是相同的岭洲。?如可以將多個語句封裝在存儲過程中，通過調(diào)?同?個存儲過程的?法可以減少死鎖的發(fā)?
• 增加合適的索引以便語句執(zhí)?所掃描的數(shù)據(jù)范圍?夠?
• 盡可能的少使?鎖坎匿，?如如果可以承擔(dān)幻讀的情況盾剩，則直接使?select語句，?不要使?select...for update語句
• 如果沒有其他更好的選擇替蔬，則可以通過施加表級鎖將事務(wù)執(zhí)?串?化告私，最?限度的限制死鎖發(fā)?

 SET autocommit=0;
 LOCK TABLES t1 WRITE, t2 READ, ...;
 ... do something with tables t1 and t2 here ...
 COMMIT;
 UNLOCK TABLES;