背景

支援事務、行級鎖。

事務及ACID屬性

原子性
一緻性
隔離性
持久性

并發事務處理帶來的問題

更新丢失
髒讀
不可重複讀
幻讀

事務隔離級别

更新丢失是應用的責任

髒讀、不可重複讀、幻讀是資料庫讀一緻性的問題，必須由資料庫提供一定的事務隔離機制來解決。

讀取資料前，加鎖，阻止其他事務對資料進行修改。
生成一個資料請求時間點的一緻性資料快照，并用這個快照提供一定級别的一緻性讀取。從使用者角度看，好像是資料庫可以提供一個資料的多個版本，是以，這種技術叫做資料多版本并發控制（多版本資料庫）。
4種隔離級别

讀資料一緻性及允許的并發副作用	讀資料一緻性	髒讀	不可重複讀	幻讀
未送出讀	最低級别，隻保證不讀實體上損壞的資料	是	是	是
已送出讀	語句級	否	是	是
可重複讀	事務級	否	否	是
可序列化	最進階别，事務級	否	否	否

擷取InnoDB行鎖争用情況

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_waits和Innodb_row_lock_time_avg的值比較高，可以查詢information_schema相關表來檢視表情況，或者設定InnoDB Monitors來進一步觀察發生鎖沖突的表、資料行等，并分析。

use information_schema
show * from innodb_locks \G;
show * from innodb_locks_waits \G;

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
show engine innodb status \G;

螢幕可以用下列語句來停止

DROP TABLE innodb_monitor;

InnoDB的行鎖模式及加鎖方法

類型

共享鎖（S）

SELECT * FROM table_name WHERE .. LOCK IN SHARE MODE

排它鎖（X）

SELEXT * FROM table_name WHERE .. FOR UPDATE

意向共享鎖（IS）：必須先獲得該表的IX鎖
意向排它鎖（IX）：必須先獲得該表的IS鎖

X	IX	S	IS
X	沖突	沖突	沖突	沖突
IX	沖突	相容	沖突	相容
S	沖突	沖突	相容	相容
IS	沖突	相容	相容	相容

意向鎖是InnoDB自動加的，不需要幹預。對insert、update、delete，InnoDB會自動為相關資料集加排它鎖；對于普通的select，不加鎖；事務可以通過下面語句加鎖。

S : select * from table_name where... lock in share mode
X : select * from table_name where... for update

實作方式

通過給索引上的索引項加鎖實作的，如果沒有索引，InnoDB會通過隐藏的聚簇索引來對記錄進行加鎖。

3種形式

Record lock:對索引項加鎖
Gap lock :對索引項之間的間隙，第一條記錄前的間隙或最後一條記錄後的間隙進行加鎖。
Next-key lock:前兩種的組合。對記錄及其前面的間隙加鎖。

行鎖的特性會出現鎖沖突，影響并發性能

通過InnoDB的行鎖實作特點，如果不通過索引條件檢索資料，那麼InnoDB将對表中所有記錄加鎖，效果和表鎖一樣。

mysql> select * from tab_no_index;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
+------+------+
4 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_no_index where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id=1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

在另一個終端裡就會出現鎖等待

mysql> select * from tab_no_index where id=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

因為InnoDB是針對索引加鎖，索引通路不同行時也可能出現鎖沖突。應用設計的時候要注意這一點。

mysql> select * from table_with_index;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
|    1 | 4    |
+------+------+
5 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_with_index where id=1 and name='1' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

另一個終端裡就會鎖等待，因為用了同一個索引id=1

mysql> select * from table_with_index where id=1 and name='4' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

不同的索引可以鎖定不同的行，但是不能鎖定同一行。
不使用索引的情況會對表加鎖。（P272索引問題，會用trace檔案中的屬性對比選擇，如果全表掃描的代價更小就不用索引）
Next-Key鎖

當我們使用的條件是範圍條件，并請求共享或排它鎖時，InnoDB會給複合條件的已有資料的索引項加鎖；對于鍵值在範圍條件内，但是并不存在的記錄叫做間隙，InnoDB也會給間隙加鎖，這種鎖機制就是Next-Key鎖。

select * from emp where empid > 100 for update;

 InnoDB不僅會給empid值為101的記錄加鎖，也會對大于101但是記錄不存在的“記錄”加鎖。InnoDB加入這種機制是為了防止幻讀（A事務更新了**整個表**，B向表中加入一條，A事務的使用者發現沒整理好，就像幻覺一樣）。
  這裡的幻讀值的是如果不使用間隙鎖，如果其他事務插入了一條大于100的記錄，再次執行上述語句，就會發生幻讀；另一方面是為了滿足其恢複和複制的需要。
在使用範圍條件檢索并鎖定資料時，InnoDB這種機制會阻塞符合條件範圍内鍵值的并發插入，這會造成鎖等待。是以，為了避免間隙加鎖，設計應用時，要注意優化業務邏輯，盡量用相等條件來通路更新資料。
如過給一個不存在的相等條件加鎖時也使用Next-Key鎖。

恢複和複制的需要，對InnoDB鎖機制的影響

Binlog的三種格式對應三種複制方式。

Binlog格式	複制方式
Statement	binlog_format=Statement(Statement_Base Replication) (SBR)	基于SQL語句的複制
Row	binlog_format=Row(Row_Base Replication) (RBR)	基于行的複制
Mixed	binlog_format=Mixed	混合複制

混合的方法是：對于安全的SQL語句采用基于SQL語句的複制方式；否則采用基于行的複制模式。

還有第四種複制方式：使用全局事務ID的複制（GTIDs）：主要是解決主從自動同步一緻問題。

對于基于SQL語句的恢複和複制來說，因為BINLOG是根據事務的送出順序記錄的，是以正确恢複和複制的前提條件是：在一個事務未送出前，其他并行事務不能插入滿足其鎖定條件的任何記錄，即不能産生幻讀。實際就是要求串行化，但是這超過了IOS/ANSI SQL92"可重複讀"隔離級别的要求。這也是為什麼許多情況下采用Next-Key鎖的原因

使用intsert into .. select..和create table .. select .. 語句時，會對select後的源表加鎖。如果查詢比較複雜，冰法的事務無法執行，會引發嚴重的性能能問題。是以要盡量避免使用，MySQL成之為不确定SQL（Unsfe SQL）。如果一定要使用，又不希望對源表的并發的更新産生影響，可以采用3種措施：
将innodb_locks_unsafe_for_binlog的值設定為“on”，強制mysql使用多版本一緻性讀。但是此時産生的binlog是不正确的，是以不推薦使用。
通過select * from source_tab ... Into outfile和load data infile..語句組合實作。
采用基于行的binlog日志格式和基于行資料的複制。

InnoDB在不同隔離級别下的一緻性讀及鎖的差異

對于很多SQL，隔離級别越高，InnoDB給記錄加的鎖就越嚴格（尤其是使用範圍條件的時候），産生鎖沖突的可能性就越高，進而對并發性事務處理性能的影響就越大。

結論是在實際中，要盡量降低隔離級别，以減少鎖争用的幾率。

實際中，通過優化事務的邏輯，大部分應用的隔離級别使用Read Committed就足夠了。對于必須使用高的隔離級别的事務，在程式中執行下列語句動态改變隔離級别的方式滿足需求。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
或者 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

何時使用表鎖

事務需要更新大部分或全部資料，表有比較大
事務涉及多個表，比較複雜，可能發生死鎖

行鎖是我們選擇InnoDB表的原因，是以上面兩種情況不能太多，否則就要選擇MyISAM表了。

在InnoDB下，使用表鎖要注意以下兩點：

表鎖不是InnoDB引擎管理的，而是其由上一層的MySQL Server負責的，僅當autocommit=0、InnoDB_table_locks=1(預設設定)時，InnoDB層才能知道MySQL加的表鎖，MySQL Server也才能感覺到InnoDB加的行鎖，這種情況下，InnoDB才能自動識别涉及表級鎖的死鎖。
在用LOCK TABLE 對表進行加鎖時，要設定autocommit=0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隐式地送出事務；COMMIT或者ROLLBACK并不能釋放用LOCK TABLES加的表鎖，必須用UNLOCK TABLES釋放表鎖。

SET AUTOCOMMIT=0;
LOCK TABLES T1 WRITE, T2 READ, ...;
...
COMMIT;
UNLOCK TABLES;

死鎖

一般情況下，InnoDB會發現死鎖，并使一個事務釋放鎖并回退，另一個事務獲得鎖，繼續完成事務。但是，涉及外部鎖或表級鎖的情況下，InnoDB并不能完全檢測到死鎖，這是需要設定鎖等待逾時參數innodb_lock_wait_timeout來解決。

innodb_lock_wait_timeout并不隻是用來解決死鎖問題，在并發通路比較高的情況下，如果大量事務沒有立即獲得所需的鎖而挂起，會占用大量的計算機資源，造成嚴重的性能問題，甚至拖垮資料庫。通過設定合适的數值來避免這種情況的發生。

通常來說，死鎖都是應用設計的問題，通過調整業務流程、資料庫對象設計、事務大小，以及通路資料庫的SQL語句，絕大部分死鎖都可以避免。

避免死鎖的常用方法

在應用中，如果不同的程式會并發存取多個表，應盡量約定以相同的順序來通路表，這樣可以大大降低發生死鎖的機會。

第一個終端

mysql> set autocommit=0
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select first_name,last_name from actor where actor_id=1 for update;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| PENELOPE   | GUINESS   |
+------------+-----------+
1 row in set (0.00 sec)

mysql> insert into country(country_id,country) values(110,'test');

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> 
mysql> insert into country(country_id,country) values(110,'test');


ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

第二個終端

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into country(country_id,country) values(110,'test');
Query OK, 1 row affected (0.00 sec)

mysql> select first_name,last_name from actor where actor_id=1 for update;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| PENELOPE   | GUINESS   |
+------------+-----------+
1 row in set (0.00 sec)

在程式以批量方式處理資料的時候，如果事先對資料排序，保證每個線程用固定的順序來處理記錄，也可以大大降低死鎖的機率。
在事務中，如果要更新記錄，應該申請足夠級别的鎖，即排它鎖，而不應先申請共享鎖，更新時在申請排它鎖，防止其他使用者也申請了共享鎖，進而産生鎖沖突甚至死鎖。
在REPEATABLE-READ隔離級别下，如果兩個線程同時對相同條件記錄用SELECT ... FOR UPDATE加排他鎖，在沒有符合條件的記錄的情況下，兩個線程都會加鎖成功。程式發現記錄不存在，如果師徒家兔一天記錄，如果兩個線程都這麼做，就會發生死鎖。這種情況下，把隔離級别改成READ COMMITTED就可以避免死鎖。
當隔離級别是READ COMMITTED時，如果兩個線程都先執行SELECT .. FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有即插入記錄。此時隻有一個線程能插入成功，另一個陷入鎖等待，當第一個線程送出成功後，第二發個線程也會因為主鍵重而出錯，但是，雖然這個線程出錯了，卻會獲得一個排他鎖。這時如果有第三個線程又來申請排他鎖，也會出現死鎖。

解決辦法是，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖。

措施

死鎖不可避免，在程式設計中總是捕獲并處理死鎖異常是很好的習慣。如果出現死鎖，可以用SHOW INNODB STATUS指令來确定最後一個死鎖産生的原因。傳回結果中包括死鎖相關事務的詳細資訊，如引發死鎖的SQL語句，事務已經獲得的鎖，正在等待什麼鎖，以及被復原的事務等。據此可以分析原因和改進措施。

總結

InnoDB的行鎖是基于索引實作的，若沒有通過索引通路資料，就會使用表鎖
Next-Key鎖機制，使用的原因
不同隔離級别下，InnoDB的一緻性讀政策和鎖機制不同
MySQL恢複和複制對InnoDB鎖機制和一緻性讀政策也有較大影響
鎖沖突甚至死鎖很難避免，在程式設計是捕捉并處理鎖異常是很好的程式設計習慣

通過設計和調整SQL減少鎖沖突或死鎖

盡量使用較低的隔離級别（一般READ COMMITTED就夠用了）
設計恰當的索引，盡量使用索引通路資料
選擇合理的事務大小，小事務發生鎖沖突的機率也小
給記錄級顯式加鎖時，盡量一次性申請足夠級别的鎖（排他鎖）。
不同的程式通路一組表時，盡量約定以相同的順序通路各表，對一個表而言，盡可能以固定的順序存取表中的行。
盡量用相等條件通路資料，避免Next-Key鎖對并發插入的影響。
不要申請超過實際需要的鎖級别
除非必須，否則查詢時不要顯式加鎖
對于特定的場景，表鎖的效率比行鎖高

MySQL InnoDB鎖問題

背景

擷取InnoDB行鎖争用情況

InnoDB的行鎖模式及加鎖方法

實作方式

恢複和複制的需要，對InnoDB鎖機制的影響

InnoDB在不同隔離級别下的一緻性讀及鎖的差異

何時使用表鎖

死鎖

避免死鎖的常用方法

措施

總結

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案