1.InnoDB的鎖定機(jī)制

InnoDB存儲(chǔ)引擎支持行級(jí)鎖，支持事務(wù)處理，事務(wù)是有一組SQL語(yǔ)句組成的邏輯處理單元，他的ACID特性如下：

原子性(Atomicity): 事務(wù)具有原子不可分割的特性，要么一起執(zhí)行，要么都不執(zhí)行。 一致性(Consistency): 在事務(wù)開(kāi)始和事務(wù)結(jié)束時(shí)，數(shù)據(jù)都保持一致?tīng)顟B(tài)。 隔離性(Isolation): 在事務(wù)開(kāi)始和結(jié)束過(guò)程中，事務(wù)保持著一定的隔離特性，保證事務(wù)不受外部并發(fā)數(shù)據(jù)操作的影響。 持久性(Durability): 在事務(wù)完成后，數(shù)據(jù)將會(huì)被持久化到數(shù)據(jù)庫(kù)中。

并發(fā)事務(wù)能提高數(shù)據(jù)庫(kù)資源的利用率，提高了數(shù)據(jù)庫(kù)的事務(wù)吞吐量，但并發(fā)事務(wù)也存在一些問(wèn)題，主要包括：

更新丟失(Lost Update): 兩個(gè)事務(wù)更新同一條數(shù)據(jù)，但第二個(gè)事務(wù)中途失敗退出，導(dǎo)致兩個(gè)修改都失效了；因?yàn)榇藭r(shí)數(shù)據(jù)庫(kù)沒(méi)有執(zhí)行任何鎖操作，并發(fā)事務(wù)并沒(méi)有被隔離。(現(xiàn)代數(shù)據(jù)庫(kù)已經(jīng)不存在這種問(wèn)題) 臟讀(Dirty Reads): 一個(gè)事務(wù)讀了某行數(shù)據(jù)，但是另一個(gè)事務(wù)已經(jīng)更新了這行數(shù)據(jù)，這是非常危險(xiǎn)的，很可能導(dǎo)致所有的操作被回滾。 不可重復(fù)讀: 一個(gè)事務(wù)對(duì)一行數(shù)據(jù)重復(fù)讀取兩次(多次)，可是得到了不同的結(jié)果,在兩次讀取過(guò)程中，有可能存在另一個(gè)事務(wù)對(duì)數(shù)據(jù)進(jìn)行了修改。 幻讀:事務(wù)在操作過(guò)程中進(jìn)行兩次查詢(xún)，第二次查詢(xún)結(jié)果包含了第一次沒(méi)有出現(xiàn)的數(shù)據(jù)。出現(xiàn)幻讀的主要原因是兩次查詢(xún)過(guò)程中另一個(gè)事務(wù)插入新的數(shù)據(jù)。

數(shù)據(jù)庫(kù)并發(fā)中的“更新丟失”通常應(yīng)該是完全避免的，但防止更新丟失數(shù)據(jù)，并不能單靠數(shù)據(jù)庫(kù)事務(wù)控制來(lái)解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來(lái)解決，而以上出現(xiàn)的數(shù)據(jù)庫(kù)問(wèn)題都必要由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決。為了避免數(shù)據(jù)庫(kù)事務(wù)并發(fā)帶來(lái)的問(wèn)題，在標(biāo)準(zhǔn)SQL規(guī)范中定義了4個(gè)事務(wù)的隔離級(jí)別，不同的隔離級(jí)別對(duì)事務(wù)處理不一樣。

數(shù)據(jù)庫(kù)隔離級(jí)別的比較

隔離級(jí)別 讀數(shù)據(jù)一致性 臟讀 不可重復(fù)讀 幻讀 未提交讀 (Read uncommitted) 最低級(jí)別，只能保證不讀取物理上損壞的數(shù)據(jù) 是 是 是 已提交讀 (Read committed) 語(yǔ)句級(jí) 否 是 是 可重復(fù)讀 (Repeatable read) 事務(wù)級(jí) 否 否 是 可序列化 (Serializable) 最高級(jí)別，事務(wù)級(jí) 否 否 否

InnoDB存儲(chǔ)引擎實(shí)現(xiàn)了4中行鎖，分別時(shí)共享鎖(S)、排他鎖(X)、意向共享鎖(IS)、意向排他鎖(IX)。

共享鎖：大家都能讀，但是不能改，只有其中一個(gè)獨(dú)占共享鎖時(shí)候才能改； 排它鎖：我要改，你們都不能改，也不能讀(但可以MVCC快照讀) 理解意向鎖

意向鎖不會(huì)和行級(jí)的S和X鎖沖突，只會(huì)和表級(jí)的S和X鎖沖突

意向鎖是為了避免遍歷全部行鎖

考慮這個(gè)例子：

事務(wù)A鎖住了表中的一行，讓這一行只能讀，不能寫(xiě)。

之后，事務(wù)B申請(qǐng)整個(gè)表的寫(xiě)鎖。

如果事務(wù)B申請(qǐng)成功，那么理論上它就能修改表中的任意一行，這與A持有的行鎖是沖突的。

數(shù)據(jù)庫(kù)需要避免這種沖突，就是說(shuō)要讓B的申請(qǐng)被阻塞，直到A釋放了行鎖。

數(shù)據(jù)庫(kù)要怎么判斷這個(gè)沖突呢？

step1：判斷表是否已被其他事務(wù)用表鎖鎖表

step2：判斷表中的每一行是否已被行鎖鎖住。

注意step2，這樣的判斷方法效率實(shí)在不高，因?yàn)樾枰闅v整個(gè)表。

于是就有了意向鎖。

在意向鎖存在的情況下，事務(wù)A必須先申請(qǐng)表的意向共享鎖，成功后再申請(qǐng)一行的行鎖。

在意向鎖存在的情況下，上面的判斷可以改成

step1：不變

step2：發(fā)現(xiàn)表上有意向共享鎖，說(shuō)明表中有些行被共享行鎖鎖住了，因此，事務(wù)B申請(qǐng)表的寫(xiě)鎖會(huì)被阻塞。

1.1通過(guò)索引檢索數(shù)據(jù)，上共享鎖，行鎖(如果不通過(guò)索引，會(huì)使用表鎖)

1.1通過(guò)索引檢索數(shù)據(jù)，上共享鎖，行鎖SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------對(duì)主鍵索引上共享鎖,其他事務(wù)也能獲取到共享鎖mysql> select * from test where id=1 lock in share mode;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 1 |+----+------+-------+-------+1 row in set (0.01 sec)--------------------------------------------------------------------------------事務(wù)B也能繼續(xù)加共享鎖mysql> select * from test where id=1 lock in share mode;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 1 |+----+------+-------+-------+1 row in set (0.01 sec)但無(wú)法更新，因?yàn)槭聞?wù)A也加了共享鎖mysql> update test set level=11 where id=1;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transactionMORE:無(wú)法加排它鎖select *from test where id=1 for update;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction可以更新未加鎖的，比如mysql> update test set level=11 where id=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------事務(wù)A也無(wú)法更新，因?yàn)槭聞?wù)B加了共享鎖mysql> update test set level=11 where id=1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction--------------------------------------------------------------------------------任意一個(gè)釋放共享鎖，則獨(dú)占共享鎖的事務(wù)可以更新mysql> commit;Query OK, 0 rows affected (0.00 sec)--------------------------------------------------------------------------------事務(wù)B釋放鎖，事務(wù)A獨(dú)占，可以更新了mysql> update test set level=11 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

1.2通過(guò)索引檢索數(shù)據(jù)，上排他鎖，行鎖

1.2通過(guò)索引檢索數(shù)據(jù)，上排他鎖，行鎖SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------對(duì)主鍵索引上排他鎖,其他事務(wù)也能獲取到共享鎖mysql> select *from test whereid=1 for update;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 1 |+----+------+-------+-------+1 row in set (0.01 sec)--------------------------------------------------------------------------------事務(wù)B則不能繼續(xù)上排它鎖，會(huì)發(fā)生等待mysql> select *from test where id=1 for update;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transactionMORE:也不能更新，因?yàn)楦乱彩巧吓潘imysql> update test set level=2 where id=1;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction也不能上共享鎖mysql> select * from test where level=1 lock in share mode;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction--------------------------------------------------------------------------------事務(wù)A可以更新mysql> update test set level=11 where id=1;Query OK, 1 row affected (0.08 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------釋放排它鎖mysql> commit;Query OK, 0 rows affected (0.00 sec)--------------------------------------------------------------------------------事務(wù)A釋放鎖，事務(wù)B就可以加排它鎖了mysql> select * from test where id=1 for update;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 1 |+----+------+-------+-------+1 row in set (0.00 sec)

1.3通過(guò)索引更新數(shù)據(jù)，也是上排他鎖，行鎖

對(duì)于 update,insert,delete 語(yǔ)句會(huì)自動(dòng)加排它鎖

1.3通過(guò)索引更新數(shù)據(jù)，也是上排他鎖，行鎖SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------更新id=1的行，就給該行上了排它鎖，其他事務(wù)無(wú)法更新該行mysql> update test set level=11 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------事務(wù)B則不能更新id=1的行，會(huì)發(fā)生等待mysql> update test set level=21 where id=1;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transactionMORE:也不能上排它鎖mysql> select *from test where id=1 for update;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction也不能上共享鎖mysql> select * from test where level=1 lock in share mode;ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction--------------------------------------------------------------------------------釋放排它鎖mysql> commit;Query OK, 0 rows affected (0.00 sec)--------------------------------------------------------------------------------事務(wù)A釋放鎖，事務(wù)B就可以加排它鎖了mysql> select * from test where id=1 for update;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 11|+----+------+-------+-------+1 row in set (0.00 sec)

2.1臟讀

//臟讀//2.1臟讀SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)set session transaction isolationset session transaction isolation level read uncommitted;level read uncommitted; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec) mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------mysql> update test set level=100 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------//臟讀mysql> select *from test where id=1;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 100 |+----+------+-------+-------+1 row in set (0.00 sec)--------------------------------------------------------------------------------rollback;Query OK, 0 rows affected (0.01 sec)mysql> select *from test where id=1;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 1 |+----+------+-------+-------+1 row in set (0.00 sec)

2.2不可重復(fù)讀

2.2不可重復(fù)讀//臟讀SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)set session transaction isolationset session transaction isolation level read uncommitted;level read uncommitted; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec) mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------mysql> update test set level=100 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------mysql> select *from test where id=1;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 100 |+----+------+-------+-------+1 row in set (0.00 sec)--------------------------------------------------------------------------------mysql> update test set level=1000 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------//不可重復(fù)讀//讀三次，第一次是level是1，第二次是100，第三次是1000mysql> select *from test where id=1;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 1000|+----+------+-------+-------+1 row in set (0.00 sec)

2.3幻讀

//2.3幻讀SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)set session transaction isolationset session transaction isolation level read uncommitted;level read uncommitted; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec) mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------mysql> update test set level=100 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0--------------------------------------------------------------------------------mysql> select *from test where id=1;+----+------+-------+-------+| id | name | money | level |+----+------+-------+-------+| 1 | tom | 100 | 100 |+----+------+-------+-------+1 row in set (0.00 sec)--------------------------------------------------------------------------------mysql> insert into test (name, money，level) VALUES (’tim’,250，4);Query OK, 1 row affected (0.01 sec)--------------------------------------------------------------------------------//幻讀//讀兩次，第二次多了tim的數(shù)據(jù)//如果是rr級(jí)別，需要使用當(dāng)前讀select * from test lock in share mode;否則因?yàn)镸VCC的緣故，是讀不到tim的mysql> select * from test;+----+-------+-------+-------+| id | name | money | level |+----+-------+-------+-------+| 1 | tom | 100 | 1 || 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 || 4 | tim | 250 | 4 |+----+-------+-------+-------+4 row in set (0.00 sec)3 間隙鎖(Net-Key鎖)

MVCC使RR級(jí)別下，事務(wù)當(dāng)前讀，來(lái)避免了讀情況下的幻讀問(wèn)題，但如果寫(xiě)更新時(shí)候呢？在范圍更新的同時(shí)，往范圍內(nèi)插入新數(shù)據(jù)，怎么辦？

于是就有了間隙鎖，在更新某個(gè)區(qū)間數(shù)據(jù)時(shí)，將會(huì)鎖定這個(gè)區(qū)間的所有記錄。例如update XXX where id between 1 and 100， 就會(huì)鎖住id從1到100之間的所有的記錄。值得注意的是，在這個(gè)區(qū)間中假設(shè)某條記錄并不存在，該條記錄也會(huì)被鎖住，這時(shí)，如果另一個(gè)事務(wù)往這個(gè)區(qū)間添加數(shù)據(jù)，就必須等待上一個(gè)事務(wù)釋放鎖資源。

使用間隙鎖有兩個(gè)目的，一是防止幻讀；二是滿足其恢復(fù)和賦值的需求。

3.1范圍間隙鎖，顯式左開(kāi)右閉區(qū)間

//間隙鎖(Net-Key鎖) 范圍間隙鎖，左開(kāi)右閉區(qū)間SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------mysql> update test set level=0where money between 0 and 200;Query OK, 2 rows affected (0.02 sec)Rows matched: 2 Changed: 2 Warnings: 0理論上應(yīng)該鎖定[0,300)這個(gè)區(qū)間--------------------------------------------------------------------------------插入money=0等待mysql> insert into test (name, money,level) VALUES (’tim’,0,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=90等待mysql> insert into test (name, money,level) VALUES (’tim’,90,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=100等待mysql> insert into test (name, money,level) VALUES (’tim’,100,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=299等待mysql> insert into test (name, money,level) VALUES (’tim’,299,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=300 okmysql> insert into test (name, money,level) VALUES (’tim’,300,0);Query OK, 1 row affected (0.00 sec)

3.2單個(gè)間隙鎖 隱式區(qū)間

上小節(jié)是指定update某個(gè)區(qū)間，那如果說(shuō)是只update一個(gè)值呢？還會(huì)有間隙鎖么？

//間隙鎖(Net-Key鎖) 單個(gè)間隙鎖，左開(kāi)右閉區(qū)間SessionA SessionBmysql> set autocommit=0; mysql> set autocommit=0;Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> select * from test; mysql> select * from test;--------------------------------------------------------------------------------+----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level |+----+-------+-------+-------+ +----+-------+-------+-------+| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 || 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 || 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |+----+-------+-------+-------+ +----+-------+-------+-------+3 rows in set (0.00 sec) 3 rows in set (0.00 sec)--------------------------------------------------------------------------------mysql> update test set level=0where money = 200;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0理論上應(yīng)該鎖定[0,300)這個(gè)區(qū)間--------------------------------------------------------------------------------插入money=0 okmysql> insert into test (name, money,level) VALUES (’tim’,0,0);Query OK, 1 row affected (0.00 sec)插入money=90 okmysql> insert into test (name, money,level) VALUES (’tim’,90,0);Query OK, 1 row affected (0.00 sec)插入money=100等待mysql> insert into test (name, money,level) VALUES (’tim’,100,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=150等待mysql> insert into test (name, money,level) VALUES (’tim’,150,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=200等待mysql> insert into test (name, money,level) VALUES (’tim’,200,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=240等待mysql> insert into test (name, money,level) VALUES (’tim’,240,0);ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction插入money=300 okmysql> insert into test (name, money,level) VALUES (’tim’,300,0);Query OK, 1 row affected (0.00 sec)

當(dāng)不指定區(qū)間時(shí)，隱式的區(qū)間為索引B+數(shù)前后兩個(gè)節(jié)點(diǎn)的值所確定的區(qū)間，也是左開(kāi)右閉，對(duì)于上述例子，就是[0,300)這個(gè)區(qū)間。

總結(jié)

到此這篇關(guān)于Mysql InnoDB鎖定機(jī)制的文章就介紹到這了,更多相關(guān)Mysql InnoDB鎖定機(jī)制內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！