InnoDB鎖機(jī)制

??InnoDB存儲(chǔ)引擎?持?級(jí)鎖，其?類可以細(xì)分為共享鎖和排它鎖兩類

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

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

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

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

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

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

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

InnoDB鎖機(jī)制

?級(jí)鎖

???級(jí)鎖是施加在索引?數(shù)據(jù)上的鎖，?如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE語(yǔ)句是在t.c1=10的索引?上增加鎖，來(lái)阻?其他事務(wù)對(duì)對(duì)應(yīng)索引?的insert/update/delete操作。當(dāng)?個(gè)InnoDB表沒(méi)有任何索引時(shí)，則?級(jí)鎖會(huì)施加在隱含創(chuàng)建的聚簇索引上，所以說(shuō)當(dāng)?條sql沒(méi)有?任何索引時(shí)，那么將會(huì)在每?條聚集索引后?加X(jué)鎖，這個(gè)類似于表鎖，但原理上和表鎖應(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ù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖
??間隔鎖是施加在索引記錄之間的間隔上的鎖，鎖定?個(gè)范圍的記錄、但不包括記錄本身,?如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE語(yǔ)句，盡管有可能對(duì)c1字段來(lái)說(shuō)當(dāng)前表?沒(méi)有=15的值，但還是會(huì)阻?=15的數(shù)據(jù)的插?操作，是因?yàn)殚g隔鎖已經(jīng)把索引查詢范圍內(nèi)的間隔數(shù)據(jù)也都鎖住了
??間隔鎖的使?只在部分事務(wù)隔離級(jí)別才是?效的
??間隔鎖只會(huì)阻?其他事務(wù)的插?操作

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

innodb_locks_unsafe_for_binlog(強(qiáng)制不使?間隔鎖)參數(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鎖

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

插?意圖鎖

??插?意圖鎖是在插?數(shù)據(jù)時(shí)?先獲得的?種間隔鎖，對(duì)這種間隔鎖，只要不同的事務(wù)插?的數(shù)據(jù)位置是不?樣的，即使都是同?個(gè)間隔，也不會(huì)產(chǎn)?互斥關(guān)系，?如有?個(gè)索引有4和7兩個(gè)值，如果兩個(gè)事務(wù)分別插?5和6兩個(gè)值時(shí)，雖然兩個(gè)事務(wù) 都會(huì)在索引4和7之間施加間隔鎖，但由于后續(xù)插?的數(shù)值不?樣，所以兩者不會(huì)互斥。
???如下例中事務(wù)A對(duì)索引>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)

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

?增鎖

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

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

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

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

查看是否開(kāi)啟?動(dòng)提交

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ù)的超時(shí)時(shí)間（秒）

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秒后超時(shí)，事務(wù)回滾
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB事務(wù)隔離級(jí)別

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

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

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

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

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

??如果是使?此隔離級(jí)別，就必須使??級(jí)別的?進(jìn)制?志
??此隔離級(jí)別還有另外的特點(diǎn)：
??對(duì)于update和delete語(yǔ)句只會(huì)在約束條件對(duì)應(yīng)的?上增加鎖
??對(duì)update語(yǔ)句來(lái)說(shuō)，如果對(duì)應(yīng)的?上已經(jīng)有鎖，則InnoDB會(huì)執(zhí)?半?致讀的操作，來(lái)確定update語(yǔ)句對(duì)應(yīng)的?在上次commit之后的數(shù)據(jù)是否在鎖的范圍，如果不是，則不影響update操作，如果是，則需要等待對(duì)應(yīng)的鎖解開(kāi)

?如如下情況：

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; 

表中并沒(méi)有任何索引，所以會(huì)使?隱藏創(chuàng)建的聚簇索引來(lái)施加?級(jí)鎖,當(dāng)?shù)?個(gè)鏈接執(zhí)?修改:

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

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

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

對(duì)可重復(fù)讀隔離級(jí)別來(lái)說(shuō)，第?個(gè)事務(wù)的修改會(huì)在每?記錄上都增加排他鎖，并且直到事務(wù)結(jié)束后鎖才會(huì)釋放

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

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

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

對(duì)讀取提交內(nèi)容事務(wù)隔離級(jí)別來(lái)說(shuō)，第?個(gè)修改操作會(huì)在所有?上都加排他鎖，即首先第一條語(yǔ)句同樣會(huì)獲取所有行的x-lock，然后它會(huì)去檢查where條件，如果不匹配會(huì)立即釋放掉這條記錄的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)

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

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

Autocommit/commit/rollback

??當(dāng)設(shè)置autocommit屬性開(kāi)啟時(shí)，每個(gè)SQL語(yǔ)句都會(huì)隱含成為獨(dú)?的事務(wù)。
??默認(rèn)情況下autocommit屬性是開(kāi)啟的，也就意味著當(dāng)每個(gè)SQL語(yǔ)句最后執(zhí)?結(jié)果不返回錯(cuò)誤時(shí)都會(huì)執(zhí)?commit語(yǔ)句
??當(dāng)返回失敗時(shí)會(huì)執(zhí)?rollback語(yǔ)句。?當(dāng)autocommit屬性開(kāi)啟時(shí)，可以通過(guò)執(zhí)?start transaction或者begin語(yǔ)句來(lái)顯示的開(kāi)啟?個(gè)事務(wù)，?事務(wù)?可以包含多個(gè)SQL語(yǔ)句，最終事務(wù)的結(jié)束是由commit或者rollback來(lái)終結(jié)
???當(dāng)在數(shù)據(jù)庫(kù)鏈接?執(zhí)?set autocommit=0代表當(dāng)前數(shù)據(jù)庫(kù)鏈接禁??動(dòng)提交，事務(wù)的終結(jié)由commit或者rollback決定，同時(shí)也意味著下?個(gè)事務(wù)的開(kāi)始
??如果?個(gè)事務(wù)在autocommit=0的情況下數(shù)據(jù)庫(kù)鏈接退出?沒(méi)有執(zhí)?commit語(yǔ)句，則這個(gè)事務(wù)會(huì)回滾
???些特定的語(yǔ)句會(huì)隱含的終結(jié)事務(wù)，就好?是執(zhí)?了commit語(yǔ)句
??commit語(yǔ)句代表將此事務(wù)的數(shù)據(jù)修改永久化，并對(duì)其他事務(wù)可?，?rollback則代表將此事務(wù)的數(shù)據(jù)修改回滾。
??commit和rollback都會(huì)把當(dāng)前事務(wù)執(zhí)?所施加的鎖釋放
??當(dāng)使?多語(yǔ)句事務(wù)時(shí)，如果全局的autocommit屬性是開(kāi)啟的，則開(kāi)始此事務(wù)的?式可以使set autocommit=0將當(dāng) 前鏈接的屬性關(guān)閉，最后執(zhí)?commit和rollback；或者是顯示的使?start transaction語(yǔ)句開(kāi)啟事務(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 |
 +------+--------+

?致讀

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

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

?致讀在某些DDL語(yǔ)句下不?效：

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

加鎖讀操作

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

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

具體例子：
商品goods表中有一個(gè)字段status，status為1代表商品未被下單，status為2代表商品已經(jīng)被下單，那么我們對(duì)某個(gè)商品下單時(shí)必須確保該商品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;

在上面的場(chǎng)景中，商品信息從查詢出來(lái)到修改，中間有一個(gè)處理訂單的過(guò)程，使用select … for update的原理就是，當(dāng)我們?cè)诓樵兂鰃oods信息后就把當(dāng)前的數(shù)據(jù)鎖定，直到我們修改完畢后再解鎖。那么在這個(gè)過(guò)程中，因?yàn)間oods被鎖定了，就不會(huì)出現(xiàn)有第三者來(lái)對(duì)其進(jìn)行修改了。要使用悲觀鎖，我們必須關(guān)閉mysql數(shù)據(jù)庫(kù)的自動(dòng)提交屬性。

set autocommit=0;　　
//設(shè)置完autocommit后，我們就可以執(zhí)行我們的正常業(yè)務(wù)了。具體如下：
//0.開(kāi)始事務(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ǔ)句對(duì)應(yīng)的鎖

??加鎖讀，修改和刪除SQL語(yǔ)句都會(huì)在索引掃描過(guò)的每??增加鎖，也就是說(shuō)不光是在where條件限制的索引?上增加鎖，也會(huì)對(duì)掃描到的間隔增加間隔鎖
??如果SQL語(yǔ)句是使??級(jí)索引查找數(shù)據(jù)?且施加的是排他鎖，則InnoDB也會(huì)在對(duì)應(yīng)的聚簇索引?上施加鎖
??如果SQL語(yǔ)句沒(méi)有任何索引可以使?，則MySQL需要掃描全表數(shù)據(jù)，?每?數(shù)據(jù)都會(huì)被施加鎖，所以?個(gè)良好的習(xí)慣是為InnoDB添加合適的索引
??針對(duì)不同的語(yǔ)句，InnoDB會(huì)施加不同的鎖：
??Select…from語(yǔ)句屬于?致性讀，在默認(rèn)情況下不施加任何的鎖，除?在可串?化隔離級(jí)別下，會(huì)施加共享next-key鎖在掃描的索引?上，當(dāng)碰到使?唯?索引查找唯?值時(shí)只在唯?值上施加鎖
??Select…lock in share mode語(yǔ)句會(huì)在索引掃描?上施加共享next-key鎖，除?是當(dāng)碰到使?唯?索引查找唯?值時(shí)只在唯?值上施加鎖
??Select…for update語(yǔ)句會(huì)對(duì)掃描索引的?上施加排他next-key鎖，除?是當(dāng)碰到使?唯?索引查找唯?值時(shí)只在唯?值上施加鎖
??Update語(yǔ)句會(huì)對(duì)掃描索引的?上施加排他next-key鎖，除?是當(dāng)碰到使?唯?索引查找唯?值時(shí)只在唯?值上施加鎖。
??Delete語(yǔ)句會(huì)對(duì)掃描索引的?上施加排他next-key鎖，除?是當(dāng)碰到使?唯?索引查找唯?值時(shí)只在唯?值上施加鎖
??Insert語(yǔ)句會(huì)對(duì)索引掃描的?上施加鎖，但不是next-key鎖，所以不會(huì)阻?其他事務(wù)對(duì)該?值前的間隔上插?數(shù)據(jù)
??Insert into T select…from S語(yǔ)句會(huì)對(duì)插?到T表的?施加排他鎖（?間隔鎖），?在默認(rèn)隔離級(jí)別下會(huì)對(duì)訪問(wèn)的S表上的?施加共享next-key鎖
??當(dāng)表上有外鍵約束時(shí)，對(duì)任何的insert,update和delete操作都會(huì)在需要檢查外鍵約束的?上施加共享?鎖
??Lock table語(yǔ)句是施加表級(jí)鎖

幻讀

??幻讀問(wèn)題發(fā)?在同?個(gè)事務(wù)中當(dāng)相同的讀操作在前后兩次讀數(shù)據(jù)時(shí)返回不同的結(jié)果集。
???如在表的ID字段上有?個(gè)索引，當(dāng)希望對(duì)ID>100的數(shù)據(jù)進(jìn)?后續(xù)修改時(shí)，我們會(huì)使?如下的語(yǔ)句： SELECT * FROM child WHERE id > 100 FOR UPDATE，?如果表??前只有90和102兩個(gè)值時(shí)，如果沒(méi)有間隔鎖鎖住90到102之間的間隔，則其他的事務(wù)會(huì)插??如101這個(gè)值，這樣的話在第?次讀數(shù)據(jù)時(shí)就會(huì)返回三?記錄?導(dǎo)致幻讀
??為了阻?幻讀情況的發(fā)?，InnoDB使?了?種?法next-key鎖將索引?鎖和間隔鎖合并在?起。InnoDb會(huì)在索引掃描的?上施加?級(jí)共享鎖或者排他鎖，?next-key鎖也會(huì)在每個(gè)索引?之前的間隔上施加鎖，會(huì)導(dǎo)致其他的session不能在每個(gè)索引之前的間隔內(nèi)插?新的索引值
??間隔鎖會(huì)施加在索引讀碰到的?數(shù)據(jù)上，所以對(duì)上例來(lái)說(shuō)為了阻?插?任何>100的值，也會(huì)將最后掃描的索引值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)啟動(dòng)到現(xiàn)在、鎖定的總時(shí)間?度
Innodb_row_lock_time_avg：每次平均鎖定的時(shí)間
Innodb_row_lock_time_max：最??次鎖定時(shí)間
Innodb_row_lock_waits：系統(tǒng)啟動(dòng)到現(xiàn)在、總共鎖定次數(shù)

InnoDB死鎖

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

可以通過(guò)設(shè)置innodb_deadlock_detect參數(shù)可以打開(kāi)或關(guān)閉死鎖檢測(cè):
innodb_deadlock_detect = on 打開(kāi)死鎖檢測(cè)，數(shù)據(jù)庫(kù)發(fā)生死鎖時(shí)自動(dòng)回滾（默認(rèn)選項(xiàng)）
innodb_deadlock_detect = off 關(guān)閉死鎖檢測(cè)，發(fā)生死鎖的時(shí)候，用鎖超時(shí)來(lái)處理，通過(guò)設(shè)置鎖超時(shí)參innodb_lock_wait_timeout可以在超時(shí)發(fā)生時(shí)回滾被阻塞的事務(wù)

??通過(guò)innodb_deadlock_detect參數(shù)配置?動(dòng)偵測(cè)功能是否開(kāi)啟，如果關(guān)閉的話，InnoDB就使?innodb_lock_wait_timeout參數(shù)來(lái)?動(dòng)回滾等待?夠時(shí)間的事務(wù)
??可以通過(guò)show engine innodb status語(yǔ)句查看最后?次發(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; ##請(qǐng)求在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

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

InnoDB死鎖檢測(cè)和回滾

??默認(rèn)情況下死鎖檢測(cè)功能是開(kāi)啟的，當(dāng)死鎖發(fā)?時(shí)InnoDB會(huì)?動(dòng)檢測(cè)到并犧牲(回滾)其中的?個(gè)或者?個(gè)事務(wù)，以便讓其他的事務(wù)繼續(xù)執(zhí)?下去。
??InnoDB選擇犧牲的事務(wù)往往是代價(jià)?較?的事務(wù)，其代價(jià)計(jì)算是根據(jù)事務(wù)insert,update, delete的數(shù)據(jù)?規(guī)模決定
??如果事務(wù)中的某個(gè)語(yǔ)句因?yàn)殄e(cuò)誤?回滾，則這個(gè)語(yǔ)句上的鎖可能還會(huì)保留，是因?yàn)镮nnoDB僅會(huì)存儲(chǔ)?鎖信息，?不會(huì)存儲(chǔ)?鎖是由事務(wù)中的哪個(gè)語(yǔ)句產(chǎn)?的
??如果在?個(gè)事務(wù)中，select語(yǔ)句調(diào)?了函數(shù)，?函數(shù)中的某個(gè)語(yǔ)句執(zhí)?失敗，則那個(gè)語(yǔ)句會(huì)回滾，如果在整個(gè)事務(wù)結(jié)束時(shí)執(zhí)?rollback，則整個(gè)事務(wù)回滾
??可以通過(guò)innodb_deadlock_detect 參數(shù)關(guān)閉死鎖檢測(cè)功能，?僅僅?innodb_lock_wait_timeout的功能來(lái)釋放鎖等待

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

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

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

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

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

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