前言

眾所周知，MySQL的存儲(chǔ)引擎有MyISAM和InnoDB，鎖粒度分別是表鎖和行鎖。

后者的出現(xiàn)從某種程度上是彌補(bǔ)前者的不足，比如：MyISAM不支持事務(wù)，InnoDB支持事務(wù)。表鎖雖然開(kāi)銷(xiāo)小，鎖表快，但高并發(fā)下性能低。行鎖雖然開(kāi)銷(xiāo)大，鎖表慢，但高并發(fā)下相比之下性能更高。事務(wù)和行鎖都是在確保數(shù)據(jù)準(zhǔn)確的基礎(chǔ)上提高并發(fā)的處理能力。下面分別進(jìn)行介紹：

行鎖

行鎖的劣勢(shì)：

• 開(kāi)銷(xiāo)大；
• 加鎖慢；
• 會(huì)出現(xiàn)死鎖

行鎖的優(yōu)勢(shì)：

• 鎖的粒度小，發(fā)生鎖沖突的概率低；
• 處理并發(fā)的能力強(qiáng)

加鎖的方式：

• 自動(dòng)加鎖：對(duì)于UPDATE、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖；
• 無(wú)鎖：對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖；當(dāng)然我們也可以顯示的加鎖：
• 共享鎖：select * from tableName where ... + lock in share more
• 排他鎖：select * from tableName where ... + for update

InnoDB和MyISAM的最大不同點(diǎn)有兩個(gè)：

• InnoDB支持事務(wù)(transaction)；
• 默認(rèn)采用行級(jí)鎖。

加鎖可以保證事務(wù)的一致性，下面我們來(lái)學(xué)習(xí)一下MySQL的事務(wù)知識(shí).

MySQL 事務(wù)屬性

事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元，事務(wù)具有ACID屬性。 原子性（Atomicity）：事務(wù)是一個(gè)原子操作單元。在當(dāng)時(shí)原子是不可分割的最小元素，其對(duì)數(shù)據(jù)的修改，要么全部成功，要么全部都不成功。 一致性（Consistent）：事務(wù)開(kāi)始到結(jié)束的時(shí)間段內(nèi)，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。 隔離性（Isolation）：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的"獨(dú)立"環(huán)境執(zhí)行。 持久性（Durable）：事務(wù)完成后，它對(duì)于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

事務(wù)常見(jiàn)問(wèn)題

更新丟失（Lost Update） 原因：當(dāng)多個(gè)事務(wù)選擇同一行操作，并且都是基于最初選定的值，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生更新覆蓋的問(wèn)題。類(lèi)比github提交沖突。

臟讀（Dirty Reads） 原因：事務(wù)A讀取了事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù)。若事務(wù)B回滾數(shù)據(jù)，事務(wù)A的數(shù)據(jù)存在不一致性的問(wèn)題。

不可重復(fù)讀（Non-Repeatable Reads） 原因：事務(wù)A第一次讀取最初數(shù)據(jù)，第二次讀取事務(wù)B已經(jīng)提交的修改或刪除數(shù)據(jù)。導(dǎo)致兩次讀取數(shù)據(jù)不一致。不符合事務(wù)的隔離性。

幻讀（Phantom Reads） 原因：事務(wù)A根據(jù)相同條件第二次查詢(xún)到事務(wù)B提交的新增數(shù)據(jù)，兩次數(shù)據(jù)結(jié)果集不一致。不符合事務(wù)的隔離性。

幻讀和臟讀有點(diǎn)類(lèi)似 臟讀是事務(wù)B里面修改了數(shù)據(jù)， 幻讀是事務(wù)B里面新增了數(shù)據(jù)。

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

數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大。這是因?yàn)槭聞?wù)隔離實(shí)質(zhì)上是將事務(wù)在一定程度上"串行"進(jìn)行，這顯然與"并發(fā)"是矛盾的。根據(jù)自己的業(yè)務(wù)邏輯，權(quán)衡能接受的最大副作用。從而平衡了"隔離" 和 "并發(fā)"的問(wèn)題。MySQL默認(rèn)隔離級(jí)別是可重復(fù)讀。 臟讀，不可重復(fù)讀，幻讀，其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題，必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決。

+------------------------------+---------------------+--------------+--------------+--------------+
| 隔離級(jí)別                      | 讀數(shù)據(jù)一致性         | 臟讀         | 不可重復(fù) 讀   | 幻讀         |
+------------------------------+---------------------+--------------+--------------+--------------+
| 未提交讀(Read uncommitted)    | 最低級(jí)別            | 是            | 是           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+
| 已提交讀(Read committed)      | 語(yǔ)句級(jí)              | 否           | 是           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可重復(fù)讀(Repeatable read)     | 事務(wù)級(jí)              | 否           | 否           | 是           |
+------------------------------+---------------------+--------------+--------------+--------------+
| 可序列化(Serializable)        | 最高級(jí)別，事務(wù)級(jí)     | 否           | 否           | 否           |
+------------------------------+---------------------+--------------+--------------+--------------+

查看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別：show variables like 'tx_isolation';

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

間隙鎖

當(dāng)我們用范圍條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做"間隙(GAP)"。InnoDB也會(huì)對(duì)這個(gè)"間隙"加鎖，這種鎖機(jī)制就是所謂的間隙鎖(Next-Key鎖)。

Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;
?
Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)

危害(坑)：若執(zhí)行的條件是范圍過(guò)大，則InnoDB會(huì)將整個(gè)范圍內(nèi)所有的索引鍵值全部鎖定，很容易對(duì)性能造成影響。

排他鎖

排他鎖，也稱(chēng)寫(xiě)鎖，獨(dú)占鎖，當(dāng)前寫(xiě)操作沒(méi)有完成前，它會(huì)阻斷其他寫(xiě)鎖和讀鎖。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4000 |
+----+------+------+
1 row in set (0.00 sec)
?
mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
?
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
# Transaction_B
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (9.53 sec)

共享鎖

共享鎖，也稱(chēng)讀鎖，多用于判斷數(shù)據(jù)是否存在，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響。當(dāng)如果事務(wù)對(duì)讀鎖進(jìn)行修改操作，很可能會(huì)造成死鎖。如下圖所示。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)
?
mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# Transaction_B
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)
?
mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

分析行鎖定

通過(guò)檢查InnoDB_row_lock 狀態(tài)變量分析系統(tǒng)上的行鎖的爭(zhēng)奪情況 show status like 'innodb_row_lock%'

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í)間長(zhǎng)度；非常重要的參數(shù)， innodb_row_lock_time_avg: 每次等待所花平均時(shí)間；非常重要的參數(shù)， innodb_row_lock_time_max: 從系統(tǒng)啟動(dòng)到現(xiàn)在等待最常的一次所花的時(shí)間； innodb_row_lock_waits: 系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)；非常重要的參數(shù)。直接決定優(yōu)化的方向和策略。

行鎖優(yōu)化

1 盡可能讓所有數(shù)據(jù)檢索都通過(guò)索引來(lái)完成，避免無(wú)索引行或索引失效導(dǎo)致行鎖升級(jí)為表鎖。 2 盡可能避免間隙鎖帶來(lái)的性能下降，減少或使用合理的檢索范圍。 3 盡可能減少事務(wù)的粒度，比如控制事務(wù)大小，而從減少鎖定資源量和時(shí)間長(zhǎng)度，從而減少鎖的競(jìng)爭(zhēng)等，提供性能。 4 盡可能低級(jí)別事務(wù)隔離，隔離級(jí)別越高，并發(fā)的處理能力越低。

表鎖

表鎖的優(yōu)勢(shì)：開(kāi)銷(xiāo)??；加鎖快；無(wú)死鎖 表鎖的劣勢(shì)：鎖粒度大，發(fā)生鎖沖突的概率高，并發(fā)處理能力低 加鎖的方式：自動(dòng)加鎖。查詢(xún)操作（SELECT），會(huì)自動(dòng)給涉及的所有表加讀鎖，更新操作（UPDATE、DELETE、INSERT），會(huì)自動(dòng)給涉及的表加寫(xiě)鎖。也可以顯示加鎖： 共享讀鎖：lock table tableName read; 獨(dú)占寫(xiě)鎖：lock table tableName write; 批量解鎖：unlock tables;

共享讀鎖

對(duì)MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀操作，但會(huì)阻塞對(duì)同一表的寫(xiě)操作。只有當(dāng)讀鎖釋放后，才能執(zhí)行其他進(jìn)程的寫(xiě)操作。在鎖釋放前不能取其他表。

Transaction-A
mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)
?
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
?
mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES
?
mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated
?
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
?
mysql> select * from innodb_lock;
8 rows in set (0.01 sec)
?
mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)

獨(dú)占寫(xiě)鎖

對(duì)MyISAM表的寫(xiě)操作（加寫(xiě)鎖），會(huì)阻塞其他進(jìn)程對(duì)同一表的讀和寫(xiě)操作，只有當(dāng)寫(xiě)鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的讀寫(xiě)操作。在鎖釋放前不能寫(xiě)其他表。

Transaction-A
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)
?
mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)
?
mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)
?
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)
?
mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES
?
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (42.83 sec)

總結(jié)：表鎖，讀鎖會(huì)阻塞寫(xiě)，不會(huì)阻塞讀。而寫(xiě)鎖則會(huì)把讀寫(xiě)都阻塞。

查看加鎖情況

show open tables; 1表示加鎖，0表示未加鎖。

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

分析表鎖定

可以通過(guò)檢查table_locks_waited 和 table_locks_immediate 狀態(tài)變量分析系統(tǒng)上的表鎖定：show status like 'table_locks%'

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+

table_locks_immediate: 表示立即釋放表鎖數(shù)。 table_locks_waited: 表示需要等待的表鎖數(shù)。此值越高則說(shuō)明存在著越嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。

此外，MyISAM的讀寫(xiě)鎖調(diào)度是寫(xiě)優(yōu)先，這也是MyISAM不適合做寫(xiě)為主表的存儲(chǔ)引擎。因?yàn)閷?xiě)鎖后，其他線程不能做任何操作，大量的更新會(huì)使查詢(xún)很難得到鎖，從而造成永久阻塞。

什么場(chǎng)景下用表鎖

InnoDB默認(rèn)采用行鎖，在未使用索引字段查詢(xún)時(shí)升級(jí)為表鎖。MySQL這樣設(shè)計(jì)并不是給你挖坑。它有自己的設(shè)計(jì)目的。 即便你在條件中使用了索引字段，MySQL會(huì)根據(jù)自身的執(zhí)行計(jì)劃，考慮是否使用索引(所以explain命令中會(huì)有possible_key 和 key)。如果MySQL認(rèn)為全表掃描效率更高，它就不會(huì)使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時(shí)，別忘了檢查SQL的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引。

第一種情況：全表更新。事務(wù)需要更新大部分或全部數(shù)據(jù)，且表又比較大。若使用行鎖，會(huì)導(dǎo)致事務(wù)執(zhí)行效率低，從而可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和更多的鎖沖突。

第二種情況：多表級(jí)聯(lián)。事務(wù)涉及多個(gè)表，比較復(fù)雜的關(guān)聯(lián)查詢(xún)，很可能引起死鎖，造成大量事務(wù)回滾。這種情況若能一次性鎖定事務(wù)涉及的表，從而可以避免死鎖、減少數(shù)據(jù)庫(kù)因事務(wù)回滾帶來(lái)的開(kāi)銷(xiāo)。

頁(yè)鎖

開(kāi)銷(xiāo)和加鎖時(shí)間介于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)處理能力一般。只需了解一下。

補(bǔ)充：行級(jí)鎖與死鎖

　　MyISAM中是不會(huì)產(chǎn)生死鎖的，因?yàn)镸yISAM總是一次性獲得所需的全部鎖，要么全部滿足，要么全部等待。而在InnoDB中，鎖是逐步獲得的，就造成了死鎖的可能。

　　在MySQL中，行級(jí)鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語(yǔ)句操作了主鍵索引，MySQL就會(huì)鎖定這條主鍵索引；如果一條語(yǔ)句操作了非主鍵索引，MySQL會(huì)先鎖定該非主鍵索引，再鎖定相關(guān)的主鍵索引。 在UPDATE、DELETE操作時(shí)，MySQL不僅鎖定WHERE條件掃描過(guò)的所有索引記錄，而且會(huì)鎖定相鄰的鍵值，即所謂的next-key locking。

　　當(dāng)兩個(gè)事務(wù)同時(shí)執(zhí)行，一個(gè)鎖住了主鍵索引，在等待其他相關(guān)索引。另一個(gè)鎖定了非主鍵索引，在等待主鍵索引。這樣就會(huì)發(fā)生死鎖。

　　發(fā)生死鎖后，InnoDB一般都可以檢測(cè)到，并使一個(gè)事務(wù)釋放鎖回退，另一個(gè)獲取鎖完成事務(wù)。

有多種方法可以避免死鎖，這里只介紹常見(jiàn)的三種

　　1、如果不同程序會(huì)并發(fā)存取多個(gè)表，盡量約定以相同的順序訪問(wèn)表，可以大大降低死鎖機(jī)會(huì)。

　　2、在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；

　　3、對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖定顆粒度，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率；

總結(jié)

1 InnoDB 支持表鎖和行鎖，使用索引作為檢索條件修改數(shù)據(jù)時(shí)采用行鎖，否則采用表鎖。 2 InnoDB 自動(dòng)給修改操作加鎖，給查詢(xún)操作不自動(dòng)加鎖 3 行鎖可能因?yàn)槲词褂盟饕?jí)為表鎖，所以除了檢查索引是否創(chuàng)建的同時(shí)，也需要通過(guò)explain執(zhí)行計(jì)劃查詢(xún)索引是否被實(shí)際使用。 4 行鎖相對(duì)于表鎖來(lái)說(shuō)，優(yōu)勢(shì)在于高并發(fā)場(chǎng)景下表現(xiàn)更突出，畢竟鎖的粒度小。 5 當(dāng)表的大部分?jǐn)?shù)據(jù)需要被修改，或者是多表復(fù)雜關(guān)聯(lián)查詢(xún)時(shí)，建議使用表鎖優(yōu)于行鎖。 6 為了保證數(shù)據(jù)的一致完整性，任何一個(gè)數(shù)據(jù)庫(kù)都存在鎖定機(jī)制。鎖定機(jī)制的優(yōu)劣直接影響到一個(gè)數(shù)據(jù)庫(kù)的并發(fā)處理能力和性能。

到此這篇關(guān)于MySQL中表鎖和行鎖機(jī)制淺析的文章就介紹到這了,更多相關(guān)MySQL表鎖和行鎖機(jī)制內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！