全面瞭解mysql鎖機制（InnoDB）與問題排查

MySQL/InnoDB的加鎖，一直是一個常見的話題。例如，資料庫如果有高併發請求，如何保證資料完整性？產生死鎖問題如何排查並解決？下面是不同鎖等級的區別

• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高 ，併發度最低。
• 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。
• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。

檢視資料庫擁有的儲存引擎型別
SHOW ENGINES

樂觀鎖

用資料版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式。何謂資料版本？即為資料增加一個版本標識，一般是通過為資料庫表增加一個數字型別的 “version” 欄位來實現。當讀取資料時，將version欄位的值一同讀出，資料每更新一次，對此version值加1。當我們提交更新的時候，判斷資料庫表對應記錄的當前版本資訊與第一次取出來的version值進行比對，如果資料庫表當前版本號與第一次取出來的version值相等，則予以更新，否則認為是過期資料。

舉例：

1、資料庫表三個欄位，分別是id、value、version
select id,value,version from TABLE where id = #{id}
2、每次更新表中的value欄位時，為了防止發生衝突，需要這樣操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}
複製程式碼

悲觀鎖

與樂觀鎖相對應的就是悲觀鎖了。悲觀鎖就是在運算元據時，認為此操作會出現資料衝突，所以在進行每次操作時都要通過獲取鎖才能進行對相同資料的操作，這點跟java中的synchronized很相似，所以悲觀鎖需要耗費較多的時間。另外與樂觀鎖相對應的，悲觀鎖是由資料庫自己實現了的，要用的時候，我們直接呼叫資料庫的相關語句就可以了。

說到這裡，由悲觀鎖涉及到的另外兩個鎖概念就出來了，它們就是共享鎖與排它鎖。共享鎖和排它鎖是悲觀鎖的不同的實現，它倆都屬於悲觀鎖的範疇。

共享鎖

共享鎖又稱讀鎖 (read lock)，是讀取操作建立的鎖。其他使用者可以併發讀取資料，但任何事務都不能對資料進行修改（獲取資料上的排他鎖），直到已釋放所有共享鎖。當如果事務對讀鎖進行修改操作，很可能會造成死鎖。如下圖所示。

如果事務T對資料A加上共享鎖後，則其他事務只能對A再加共享鎖，不能加排他鎖。獲得共享鎖的事務只能讀資料，不能修改資料

開啟第一個查詢視窗

begin;/begin work;/start transaction;  (三者選一就可以)
#(lock in share mode 共享鎖)
SELECT * from TABLE where id = 1  lock in share mode;
複製程式碼

然後在另一個查詢視窗中，對id為1的資料進行更新
update TABLE set name="www.souyunku.com" where id =1;
此時，操作介面進入了卡頓狀態，過了很久超時，提示錯誤資訊
如果在超時前，第一個視窗執行commit，此更新語句就會成功。

[SQL]update test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
複製程式碼

加上共享鎖後，也提示錯誤資訊

update test_one set name="www.souyunku.com" where id =1 lock in share mode;
[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1
複製程式碼

在查詢語句後面增加 LOCK IN SHARE MODE ，Mysql會對查詢結果中的每行都加共享鎖，當沒有其他執行緒對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。 其他執行緒也可以讀取使用了共享鎖的表，而且這些執行緒讀取的是同一個版本的資料。

加上共享鎖後，對於update，insert，delete語句會自動加排它鎖。

排它鎖

排他鎖 exclusive lock（也叫writer lock）又稱寫鎖。
名詞解釋：若某個事物對某一行加上了排他鎖，只能這個事務對其進行讀寫，在此事務結束之前，其他事務不能對其進行加任何鎖，其他程式可以讀取,不能進行寫操作，需等待其釋放。 排它鎖是悲觀鎖的一種實現，在上面悲觀鎖也介紹過。

若事務 1 對資料物件A加上X鎖，事務 1 可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到事物 1 釋放A上的鎖。這保證了其他事務在事物 1 釋放A上的鎖之前不能再讀取和修改A。排它鎖會阻塞所有的排它鎖和共享鎖

讀取為什麼要加讀鎖呢？防止資料在被讀取的時候被別的執行緒加上寫鎖。 排他鎖使用方式：在需要執行的語句後面加上for update就可以了 select status from TABLE where id=1 for update;

排他鎖，也稱寫鎖，獨佔鎖，當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

排它鎖-舉例：

要使用排他鎖，我們必須關閉mysql資料庫的自動提交屬性，因為MySQL預設使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果進行提交。

我們可以使用命令設定MySQL為非autocommit模式：

set autocommit=0;
# 設定完autocommit後，我們就可以執行我們的正常業務了。具體如下：
# 1. 開始事務
begin;/begin work;/start transaction; (三者選一就可以)
# 2. 查詢表資訊（for update加鎖）
select status from TABLE where id=1 for update;
# 3. 插入一條資料
insert into TABLE (id,value) values (2,2);
# 4. 修改資料為
update TABLE set value=2 where id=1;
# 5. 提交事務
commit;/commit work
複製程式碼

行鎖

總結：多個事務操作同一行資料時，後來的事務處於阻塞等待狀態。這樣可以避免了髒讀等資料一致性的問題。後來的事務可以操作其他行資料，解決了表鎖高併發效能低的問題。

# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;

# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;
Query OK, 1 row affected (37.51 sec)
複製程式碼

現實：當執行批量修改資料指令碼的時候，行鎖升級為表鎖。其他對訂單的操作都處於等待中，，， 原因：nnoDB只有在通過索引條件檢索資料時使用行級鎖，否則使用表鎖！ 而模擬操作正是通過id去作為檢索條件，而id又是MySQL自動建立的唯一索引，所以才忽略了行鎖變表鎖的情況

總結：InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。並且該索引不能失效，否則都會從行鎖升級為表鎖。

• 行鎖的劣勢：開銷大；加鎖慢；會出現死鎖
• 行鎖的優勢：鎖的粒度小，發生鎖衝突的概率低；處理併發的能力強
• 加鎖的方式：自動加鎖。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖；對於普通SELECT語句，InnoDB不會加任何鎖；當然我們也可以顯示的加鎖：

從上面的案例看出，行鎖變表鎖似乎是一個坑，可MySQL沒有這麼無聊給你挖坑。這是因為MySQL有自己的執行計劃。 當你需要更新一張較大表的大部分甚至全表的資料時。而你又傻乎乎地用索引作為檢索條件。一不小心開啟了行鎖(沒毛病啊！保證資料的一致性！)。可MySQL卻認為大量對一張表使用行鎖，會導致事務執行效率低，從而可能造成其他事務長時間鎖等待和更多的鎖衝突問題，效能嚴重下降。所以MySQL會將行鎖升級為表鎖，即實際上並沒有使用索引。 我們仔細想想也能理解，既然整張表的大部分資料都要更新資料，在一行一行地加鎖效率則更低。其實我們可以通過explain命令檢視MySQL的執行計劃，你會發現key為null。表明MySQL實際上並沒有使用索引，行鎖升級為表鎖也和上面的結論一致。

注意：行級鎖都是基於索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。

間隙鎖

當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。 舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;
複製程式碼

是一個範圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的“間隙”加鎖。

InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對於上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大於100的任何記錄，那麼本事務如果再次執行上述語句，就會發生幻讀；另外一方面，是為了滿足其恢復和複製的需要。有關其恢復和複製對鎖機制的影響，以及不同隔離級別下InnoDB使用間隙鎖的情況，在後續的章節中會做進一步介紹。

很顯然，在使用範圍條件檢索並鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍內鍵值的併發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是併發插入比較多的應用，我們要儘量優化業務邏輯，儘量使用相等條件來訪問更新資料，避免使用範圍條件。

還要特別說明的是，InnoDB除了通過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

例子：假如emp表中只有101條記錄，其empid的值分別是1,2,......,100,101。
InnoDB儲存引擎的間隙鎖阻塞例子

session_1	session_2
mysql> select @@tx_isolation;	mysql> select @@tx_isolation;
+-----------------+	+-----------------+
@@tx_isolation	@@tx_isolation
+-----------------+	+-----------------+
REPEATABLE-READ	REPEATABLE-READ
+-----------------+	+-----------------+
1 row in set (0.00 sec)	1 row in set (0.00 sec)
mysql> set autocommit = 0;	mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)	Query OK, 0 rows affected (0.00 sec)
當前session對不存在的記錄加for update的鎖：
mysql> select * from emp where empid = 102 for update;
Empty set (0.00 sec)
	這時，如果其他session插入empid為201的記錄（注意：這條記錄並不存在），也會出現鎖等待：
	mysql>insert into emp(empid,...) values(201,...);
	阻塞等待
Session_1 執行rollback：
mysql> rollback;
Query OK, 0 rows affected (13.04 sec)
	由於其他session_1回退後釋放了Next-Key鎖，當前session可以獲得鎖併成功插入記錄：
	mysql>insert into emp(empid,...) values(201,...);
	Query OK, 1 row affected (13.35 sec)

危害(坑)：若執行的條件是範圍過大，則InnoDB會將整個範圍內所有的索引鍵值全部鎖定，很容易對效能造成影響。

表鎖

如何加表鎖？ innodb 的行鎖是在有索引的情況下，沒有索引的表是鎖定全表的。

Innodb中的行鎖與表鎖

前面提到過，在Innodb引擎中既支援行鎖也支援表鎖，那麼什麼時候會鎖住整張表，什麼時候只鎖住一行呢？ 只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響併發效能。

行級鎖都是基於索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。行級鎖的缺點是：由於需要請求大量的鎖資源，所以速度慢，記憶體消耗大。

死鎖

死鎖（Deadlock） 所謂死鎖：是指兩個或兩個以上的程式在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。此時稱系統處於死鎖狀態或系統產生了死鎖，這些永遠在互相等待的程式稱為死鎖程式。由於資源佔用是互斥的，當某個程式提出申請資源後，使得有關程式在無外力協助下，永遠分配不到必需的資源而無法繼續執行，這就產生了一種特殊現象死鎖。

解除正在死鎖的狀態有兩種方法：
第一種：

1. 查詢是否鎖表 show OPEN TABLES where In_use > 0;
2. 查詢程式（如果您有SUPER許可權，您可以看到所有執行緒。否則，您只能看到您自己的執行緒）
   show processlist
3. 殺死程式id（就是上面命令的id列）
   kill id

第二種：

1. 檢視當前的事務
   SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2. 檢視當前鎖定的事務
   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3. 檢視當前等鎖的事務
   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
   殺死程式
   kill 程式ID

如果系統資源充足，程式的資源請求都能夠得到滿足，死鎖出現的可能性就很低，否則就會因爭奪有限的資源而陷入死鎖。其次，程式執行推進順序與速度不同，也可能產生死鎖。 產生死鎖的四個必要條件：

1. 互斥條件：一個資源每次只能被一個程式使用。
2. 請求與保持條件：一個程式因請求資源而阻塞時，對已獲得的資源保持不放。
3. 不剝奪條件:程式已獲得的資源，在末使用完之前，不能強行剝奪。
4. 迴圈等待條件:若干程式之間形成一種頭尾相接的迴圈等待資源關係。

雖然不能完全避免死鎖，但可以使死鎖的數量減至最少。將死鎖減至最少可以增加事務的吞吐量並減少系統開銷，因為只有很少的事務回滾，而回滾會取消事務執行的所有工作。由於死鎖時回滾的操作由應用程式重新提交。

下列方法有助於最大限度地降低死鎖：

1. 按同一順序訪問物件。
2. 避免事務中的使用者互動。
3. 保持事務簡短並在一個批處理中。
4. 使用低隔離級別。
5. 使用繫結連線。

---

MyISAM儲存引擎

InnoDB和MyISAM的最大不同點有兩個：

1. InnoDB支援事務(transaction)；MyISAM不支援事務
2. Innodb 預設採用行鎖， MyISAM 是預設採用表鎖。加鎖可以保證事務的一致性，可謂是有人(鎖)的地方，就有江湖(事務)
3. MyISAM不適合高併發

共享讀鎖

對MyISAM表的讀操作（加讀鎖），不會阻塞其他程式對同一表的讀操作，但會阻塞對同一表的寫操作。只有當讀鎖釋放後，才能執行其他程式的寫操作。在鎖釋放前不能讀其他表。

獨佔寫鎖

對MyISAM表的寫操作（加寫鎖），會阻塞其他程式對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其他程式的讀寫操作。在鎖釋放前不能寫其他表。

總結：

1. 表鎖，讀鎖會阻塞寫，不會阻塞讀。而寫鎖則會把讀寫都阻塞。
2. 表鎖的加鎖/解鎖方式：MyISAM 在執行查詢語句(SELECT)前,會自動給涉及的所有表加讀鎖,在執行更新操作 (UPDATE、DELETE、INSERT 等)前，會自動給涉及的表加寫鎖，這個過程並不需要使用者干預，因此，使用者一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。

如果使用者想要顯示的加鎖可以使用以下命令：

鎖定表：

LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…] 
複製程式碼

解鎖表：

UNLOCK TABLES 
複製程式碼

在用 LOCK TABLES 給表顯式加表鎖時,必須同時取得所有涉及到表的鎖。 在執行 LOCK TABLES 後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表;

如果加的是讀鎖，那麼只能執行查詢操作，而不能執行更新操作。

在自動加鎖的情況下也基本如此，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖。這也正是 MyISAM 表不會出現死鎖(Deadlock Free)的原因。

對錶test_table增加讀鎖：

LOCK TABLES test_table READ 
UNLOCK test_table
複製程式碼

對錶test_table增加寫鎖

LOCK TABLES test_table WRITE
UNLOCK test_table
複製程式碼

當使用 LOCK TABLES 時，不僅需要一次鎖定用到的所有表,而且,同一個表在 SQL 語句中出現多少次，就要通過與 SQL 語句中相同的別名鎖定多少次，否則也會出錯！

比如如下SQL語句：

select a.first_name,b.first_name, from actor a,actor b where a.first_name = b.first_name;
複製程式碼

該Sql語句中，actor表以別名的方式出現了兩次，分別是a,b，這時如果要在該Sql執行之前加鎖就要使用以下Sql:

lock table actor as a read,actor as b read;
複製程式碼

併發插入

上文說到過 MyISAM 表的讀和寫是序列的,但這是就總體而言的。在一定條件下,MyISAM表也支援查詢和插入操作的併發進行。 MyISAM儲存引擎有一個系統變數concurrent_insert,專門用以控制其併發插入的行為,其值分別可以為0、1或2。

• 當concurrent_insert設定為0時,不允許併發插入。
• 當concurrent_insert設定為1時,如果MyISAM表中沒有空洞(即表的中間沒有被刪除的 行),MyISAM允許在一個程式讀表的同時,另一個程式從表尾插入記錄。這也是MySQL 的預設設定。
• 當concurrent_insert設定為2時,無論MyISAM表中有沒有空洞,都允許在表尾併發插入記錄。

可以利用MyISAM儲存引擎的併發插入特性,來解決應用中對同一表查詢和插入的鎖爭用。

MyISAM的鎖排程

前面講過，MyISAM 儲存引擎的讀鎖和寫鎖是互斥的，讀寫操作是序列的。那麼，一個程式請求某個 MyISAM 表的讀鎖，同時另一個程式也請求同一表的寫鎖，MySQL 如何處理呢?

答案是寫程式先獲得鎖。

不僅如此，即使讀請求先到鎖等待佇列，寫請求後到，寫鎖也會插到讀鎖請求之前！這是因為 MySQL 認為寫請求一般比讀請求要重要。這也正是 MyISAM 表不太適合於有大量更新操作和查詢操作應用的原因，因為大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！

幸好我們可以通過一些設定來調節 MyISAM 的排程行為。

通過指定啟動引數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。

• 通過執行命令SET LOWPRIORITYUPDATES=1,使該連線發出的更新請求優先順序降低。
• 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性,降低該語句的優先順序。
• 另外,MySQL也 供了一種折中的辦法來調節讀寫衝突,即給系統引數max_write_lock_count 設定一個合適的值,當一個表的讀鎖達到這個值後,MySQL就暫時將寫請求的優先順序降低, 給讀程式一定獲得鎖的機會。

總結

• 資料庫中的鎖從鎖定的粒度上分可以分為行級鎖、頁級鎖和表級鎖。
• MySQL的MyISAM引擎支援表級鎖。
• 表級鎖分為兩種：共享讀鎖、互斥寫鎖。這兩種鎖都是阻塞鎖。
• 可以在讀鎖上增加讀鎖，不能在讀鎖上增加寫鎖。在寫鎖上不能增加寫鎖。
• 預設情況下，MySql在執行查詢語句之前會加讀鎖，在執行更新語句之前會執行寫鎖。
• 如果想要顯示的加鎖/解鎖的花可以使用LOCK TABLES和UNLOCK來進行。
• 在使用LOCK TABLES之後，在解鎖之前，不能操作未加鎖的表。
• 在加鎖時，如果顯示的指明是要增加讀鎖，那麼在解鎖之前，只能進行讀操作，不能執行寫操作。
• 如果一次Sql語句要操作的表以別名的方式多次出現，那麼就要在加鎖時都指明要加鎖的表的別名。
• MyISAM儲存引擎有一個系統變數concurrent_insert,專門用以控制其併發插入的行為,其值分別可以為0、1或2。
• 由於讀鎖和寫鎖互斥，那麼在排程過程中，預設情況下，MySql會本著寫鎖優先的原則。可以通過low-priority-updates來設定。

實踐解決

分析行鎖定

通過檢查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: 當前正在等待鎖定的數量
• innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度；非常重要的引數，
• innodb_row_lock_time_avg: 每次等待所花平均時間；非常重要的引數，
• innodb_row_lock_time_max: 從系統啟動到現在等待最常的一次所花的時間；
• innodb_row_lock_waits: 系統啟動後到現在總共等待的次數；非常重要的引數。直接決定優化的方向和策略。

行鎖優化

1. 儘可能讓所有資料檢索都通過索引來完成，避免無索引行或索引失效導致行鎖升級為表鎖。
2. 儘可能避免間隙鎖帶來的效能下降，減少或使用合理的檢索範圍。
3. 儘可能減少事務的粒度，比如控制事務大小，而從減少鎖定資源量和時間長度，從而減少鎖的競爭等，提供效能。
4. 儘可能低階別事務隔離，隔離級別越高，併發的處理能力越低。

表鎖優化

檢視加鎖情況 how open tables; 1表示加鎖，0表示未加鎖。

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+
複製程式碼

分析表鎖定

可以通過檢查table_locks_waited 和 table_locks_immediate 狀態變數分析系統上的表鎖定：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: 表示立即釋放表鎖數。
• table_locks_waited: 表示需要等待的表鎖數。此值越高則說明存在著越嚴重的表級鎖爭用情況。

此外，MyISAM的讀寫鎖排程是寫優先，這也是MyISAM不適合做寫為主表的儲存引擎。因為寫鎖後，其他執行緒不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永久阻塞。

什麼場景下用表鎖

第一種情況：全表更新。事務需要更新大部分或全部資料，且表又比較大。若使用行鎖，會導致事務執行效率低，從而可能造成其他事務長時間鎖等待和更多的鎖衝突。

第二種情況：多表查詢。事務涉及多個表，比較複雜的關聯查詢，很可能引起死鎖，造成大量事務回滾。這種情況若能一次性鎖定事務涉及的表，從而可以避免死鎖、減少資料庫因事務回滾帶來的開銷。

1. InnoDB 支援表鎖和行鎖，使用索引作為檢索條件修改資料時採用行鎖，否則採用表鎖。
2. InnoDB 自動給修改操作加鎖，給查詢操作不自動加鎖
3. 行鎖可能因為未使用索引而升級為表鎖，所以除了檢查索引是否建立的同時，也需要通過explain執行計劃查詢索引是否被實際使用。
4. 行鎖相對於表鎖來說，優勢在於高併發場景下表現更突出，畢竟鎖的粒度小。
5. 當表的大部分資料需要被修改，或者是多表複雜關聯查詢時，建議使用表鎖優於行鎖。
6. 為了保證資料的一致完整性，任何一個資料庫都存在鎖定機制。鎖定機制的優劣直接影響到一個資料庫的併發處理能力和效能。

mysql 5.6 在 update 和 delete 的時候，where 條件如果不存在索引欄位，那麼這個事務是否會導致表鎖？ 有人回答： 只有主鍵和唯一索引才是行鎖，普通索引是表鎖。

結果發現普通索引並不一定會引發表鎖，在普通索引中，是否引發表鎖取決於普通索引的高效程度。

上文提及的“高效”是相對主鍵和唯一索引而言，也許“高效”並不是一個很好的解釋，只要明白在一般情況下，“普通索引”效率低於其他兩者即可。 屬性值重複率高

屬性值重複率

當“值重複率”低時，甚至接近主鍵或者唯一索引的效果，“普通索引”依然是行鎖；當“值重複率”高時，MySQL 不會把這個“普通索引”當做索引，即造成了一個沒有索引的 SQL，此時引發表鎖。

同 JVM 自動優化 java 程式碼一樣，MySQL 也具有自動優化 SQL 的功能。低效的索引將被忽略，這也就倒逼開發者使用正確且高效的索引。

屬性值重複率高

---

為了突出效果，我將“普通索引”建立在一個“值重複率”高的屬性下。以相對極端的方式，擴大對結果的影響。

我會建立一張“分數等級表”，屬性有“id”、“score（分數）”、“level（等級）”，模擬一個半自動的業務——“分數”已被自動匯入，而“等級”需要手工更新。

操作步驟如下：

1. 取消 MySQL 的 事務自動提交
2. 建表，id自增，並給“score（分數）”建立普通索引
3. 插入分數值，等級為 null
4. 開啟兩個事務 session_1、session_2，兩個事務以“score”為條件指定不同值，鎖定資料
5. session_1 和 session_2 先後更新各自事務鎖定內容的“level”
6. 觀察資料庫對兩個事務的響應

取消 事務自動提交：

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

mysql> show variables like "autocommit";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| autocommit               | OFF   |
+--------------------------+-------+
1 rows in set (0.01 sec)
複製程式碼

建表、建立索引、插入資料：

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`ID`  int(5) NOT NULL AUTO_INCREMENT ,
`SCORE`  int(3) NOT NULL ,
`LEVEL`  int(2) NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `test2` ADD INDEX index_name ( `SCORE` );

INSERT INTO `test1`(`SCORE`) VALUE (100);
……
INSERT INTO `test1`(`SCORE`) VALUE (0);
複製程式碼

"SCORE" 屬性的“值重複率”奇高，達到了 50%，劍走偏鋒：

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
|  5 |   100 | NULL  |
|  6 |   100 | NULL  |
|  7 |   100 | NULL  |
|  8 |   100 | NULL  |
|  9 |   100 | NULL  |
| 10 |   100 | NULL  |
| 11 |   100 | NULL  |
| 12 |   100 | NULL  |
| 13 |   100 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 24 |   100 | NULL  |
| 25 |     0 | NULL  |
| 26 |   100 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
25 rows in set
複製程式碼

開啟兩個事務（一個視窗對應一個事務），並選定資料：

-- SESSION_1，選定 SCORE = 100 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  5 |   100 | NULL  |
|  6 |   100 | NULL  |
|  7 |   100 | NULL  |
|  8 |   100 | NULL  |
|  9 |   100 | NULL  |
| 10 |   100 | NULL  |
| 11 |   100 | NULL  |
| 12 |   100 | NULL  |
| 13 |   100 | NULL  |
| 24 |   100 | NULL  |
| 26 |   100 | NULL  |
+----+-------+-------+
12 rows in set
複製程式碼

再開啟一個視窗：

-- SESSION_2，選定 SCORE = 0 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 25 |     0 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
13 rows in set
複製程式碼

session_1 視窗，更新“LEVEL”失敗：

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
1205 - Lock wait timeout exceeded; try restarting transaction
複製程式碼

在之前的操作中，session_1 選擇了 SCORE = 100 的資料，session_2 選擇了 SCORE = 0 的資料，看似兩個事務井水不犯河水，但是在 session_1 事務中更新自己鎖定的資料失敗，只能說明在此時引發了表鎖。彆著急，剛剛走向了一個極端——索引屬性值重複性奇高，接下來走向另一個極端。

屬性值重複率低

---

  還是同一張表，將資料刪除只剩下兩條，“SCORE” 的 “值重複率” 為 0：

mysql> delete from test1 where id > 2;
Query OK, 23 rows affected

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
+----+-------+-------+
2 rows in set
複製程式碼

關閉兩個事務操作視窗，重新開啟 session_1 和 session_2，並選擇各自需要的資料：

-- SESSION_1，選定 SCORE = 100 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
+----+-------+-------+
1 row in set

-- -----------------新視窗----------------- --

-- SESSION_2，選定 SCORE = 0 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
+----+-------+-------+
1 row in set
複製程式碼

session_1 更新資料成功：

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings:0
複製程式碼

相同的表結構，相同的操作，兩個不同的結果讓人出乎意料。第一個結果讓人覺得“普通索引”引發表鎖，第二個結果推翻了前者，兩個操作中，唯一不同的是索引屬性的“值重複率”。根據 單一變數 證明法，可以得出結論：當“值重複率”低時，甚至接近主鍵或者唯一索引的效果，“普通索引”依然是行鎖；當“值重複率”高時，MySQL 不會把這個“普通索引”當做索引，即造成了一個沒有索引的 SQL，此時引發表鎖。

舉個栗子：

1. 使用者A在銀行卡有100元錢，某一刻使用者B向A轉賬50元（稱為B操作），同時有使用者C向A轉賬50元（稱為C操作);
2. B操作從資料庫中讀取他此時的餘額100，計算新的餘額為100+50=150
3. C操作也從資料庫中讀取他此時的餘額100，計算新的餘額為100+50=150
4. B操作將balance=150寫入資料庫,之後C操作也將balance=150寫入資料庫
5. 最終A的餘額變為150

上面的例子，A同時收到兩筆50元轉賬，最後的餘額應該是200元，但卻因為併發的問題變為了150元，原因是B和C向A發起轉賬請求時，同時開啟了兩個資料庫會話，進行了兩個事務，後一個事務拿到了前一個事務的中間狀態資料，導致更新丟失。
常用的解決思路有兩種：

• 加鎖同步執行
• update前檢查資料一致性

要注意悲觀鎖和樂觀鎖都是業務邏輯層次的定義，不同的設計可能會有不同的實現。在mysql層常用的悲觀鎖實現方式是加一個排他鎖。

然而實際上並不是這樣，實際上加了排他鎖的資料，在釋放鎖（事務結束）之前其他事務不能再對該資料加鎖 排他鎖之所以能阻止update,delete等操作是因為update，delete操作會自動加排他鎖， 也就是說即使加了排他鎖也無法阻止select操作。而select XX for update 語法可以對select操作加上排他鎖。 所以為了防止更新丟失可以在select時加上for update加鎖 這樣就可以阻止其餘事務的select for update (但注意無法阻止select)

樂觀鎖example：

begin;
select balance from account where id=1;
-- 得到balance=100;然後計算balance=100+50=150
update account set balance = 150 where id=1 and balance = 100;
commit;
複製程式碼

如上，如果sql在執行的過程中發現update的affected為0 說明balance不等於100即該條資料有被其餘事務更改過，此時業務上就可以返回失敗或者重新select再計算

回滾的話，為什麼只有部分 update 語句失敗，而不是整個事務裡的所有 update 都失敗？

這是因為我們們的 innodb 預設是自動提交的：
需要注意的是，通常還有另外一種情況也可能導致部分語句回滾，需要格外留意。在 innodb 裡有個引數叫：innodb_rollback_on_timeout

show VARIABLES LIKE 'innodb_rollback_on_timeout'
+----------------------------+---------+
| Variable_name              | Value   |
|----------------------------+---------|
| innodb_rollback_on_timeout | OFF     |
+----------------------------+---------+
複製程式碼

官方手冊裡這樣描述：
In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

解釋：這個引數關閉或不存在的話遇到超時只回滾事務最後一個Query，開啟的話事務遇到超時就回滾整個事務。

注意：

• MySQL insert、update、replace into 死鎖回滾預設情況下不會記錄該條 DML 語句到 binlog，也不會有回滾日誌、error ，如果不對 jdbc 返回碼做處理 Mapreduce、hive 等大資料計算任務會顯示 success 造成插入、更新部分成功部分失敗，但是可以從 SHOW ENGINE INNODB STATUS\G 看到資料庫的死鎖回滾日誌。這種情況下建議根據 jdbc 錯誤碼或者 SQLException 增加重試機制或者 throw exception/error。
• 在一個事務系統中，死鎖是確切存在並且是不能完全避免的。 InnoDB會自動檢測事務死鎖，立即回滾其中某個事務，並且返回一個錯誤。它根據某種機制來選擇那個最簡單（代價最小）的事務來進行回滾。偶然發生的死鎖不必擔心，但死鎖頻繁出現的時候就要引起注意了。InnoDB儲存引擎有一個後臺的鎖監控執行緒，該執行緒負責檢視可能的死鎖問題，並自動告知使用者。

怎樣降低 innodb 死鎖機率？

死鎖在行鎖及事務場景下很難完全消除，但可以通過表設計和SQL調整等措施減少鎖衝突和死鎖，包括：

• 儘量使用較低的隔離級別，比如如果發生了間隙鎖，你可以把會話或者事務的事務隔離級別更改為 RC(read committed)級別來避免，但此時需要把 binlog_format 設定成 row 或者 mixed 格式
• 精心設計索引，並儘量使用索引訪問資料，使加鎖更精確，從而減少鎖衝突的機會；
• 選擇合理的事務大小，小事務發生鎖衝突的機率也更小；
• 給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改資料的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖；
• 不同的程式訪問一組表時，應儘量約定以相同的順序訪問各表，對一個表而言，儘可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；

例子：

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
至
DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
複製程式碼

儘量用相等條件訪問資料，這樣可以避免間隙鎖對併發插入的影響； 不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖； 對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能。

詭異的 Lock wait timeout

# 預設 lock 超時時間 50s，這個時間真心不短了
show variables like 'innodb_lock_wait_timeout';
+--------------------------+---------+
| Variable_name            |   Value |
|--------------------------+---------|
| innodb_lock_wait_timeout |      50 |
+--------------------------+---------+
複製程式碼

而且這次 SHOW ENGINE INNODB STATUS\G 也沒出現任何死鎖資訊，然後又將目光轉向 MySQL-server 日誌，希望能從日誌裡看一看那個時刻前後資料究竟在做什麼操作。這裡先簡單的介紹下MySQL日誌檔案系統的組成：

1. error 日誌：記錄啟動、執行或停止 mysqld 時出現的問題，預設開啟。
2. general 日誌：通用查詢日誌，記錄所有語句和指令，開啟資料庫會有 5% 左右效能損失。
3. binlog 日誌：二進位制格式，記錄所有更改資料的語句，主要用於 slave 複製和資料恢復。
4. slow 日誌：記錄所有執行時間超過 long_query_time 秒的查詢或不使用索引的查詢，預設關閉。
5. Innodb日誌：innodb redo log、undo log，用於恢復資料和撤銷操作。

從上面的介紹可以看到，目前這個問題的日誌可能在 2 和 4 中，看了下 4 中沒有，那就只能開啟 2 了，但 2 對資料庫的效能有一定損耗，由於是全量日誌，量非常巨大，所以開啟一定要謹慎：

-- general_log 日誌預設關閉，開啟會影響資料庫 5% 左右效能：
show variables like 'general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
|------------------+---------------------------------|
| general_log      | OFF                             |
| general_log_file | /opt/data/mysql/tjtx-103-26.log |
+------------------+---------------------------------+

-- 全域性 session 級別開啟：
set global general_log=1

-- 如果需要對當前 session 生效需要：
set general_log=1

-- set 指令設定的動態引數在 MySQL 重啟後失效，如果需要永久生效需要在 /etc/my.cnf 中配置靜態變數/引數。
-- 如果不知道 my.cnf 位置，可以根據 mysql -? | grep ".cnf" 查詢
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
複製程式碼

set 指令設定的動態引數在 MySQL 重啟後失效，如果需要永久生效需要在 /etc/my.cnf 中配置靜態變數/引數。

更多內容請參考
強烈推薦-何登成的技術部落格
何登成 資深技術專家 阿里巴巴資料庫核心團隊負責人，文章很有深度

mysql死鎖問題分析
mysql中插入，更新，刪除鎖
MySQL InnoDB 鎖——官方文件

總結

解除正在死鎖的狀態有兩種方法：

1. 查詢是否鎖表 show OPEN TABLES where In_use > 0;
2. 查詢程式（如果您有SUPER許可權，您可以看到所有執行緒。否則，您只能看到您自己的執行緒）
   show processlist
3. 殺死程式id（就是上面命令的id列）
   kill id

第二種：

1. 檢視當前的事務
   SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2. 檢視當前鎖定的事務
   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3. 檢視當前等鎖的事務
   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
   殺死程式
   kill 程式ID

# 通過檢查 InnoDB_row_lock 狀態變數分析系統上中行鎖的爭奪情況
show status like 'innodb_row_lock%';```

# 檢視加鎖情況 
show open tables where in_use > 0;

#具體使用說明可檢視上文內容
show status like 'table_locks%';
show VARIABLES LIKE 'innodb_rollback_on_timeout';
show variables like 'general%';
複製程式碼