【MySQL】鎖問題最佳實務

標籤：需要   style   技術   air   thread   並發   charset   執行計畫   刪除索引   

最近一段時間處理了較多鎖的問題，包括鎖等待導致Business Connectivity堆積或逾時，死結導致業務失敗等，這類問題對業務可能會造成嚴重的影響，沒有處理經驗的使用者往往無從下手。下面將從整個資料庫設計，開發，營運階段介紹如何避免鎖問題的發生，提供一些最佳實務供讀者參考。

設計階段

在資料庫設計階段，引擎選擇和索引設計不當可能導致後期業務上線後出現較為嚴重的鎖或者死結問題。

1. 表引擎選擇使用myisam，引發table level lock wait。

從5.5版本開始，MySQL官方就把預設引擎由myisam轉為innodb，這兩種引擎的主要區別：

由於myisam引擎只支援table lock，在使用myisam引擎表過程中，當資料庫中出現執行時間較長的查詢後就會堵塞該表上的更新動作，所以經常會碰到線程會話處於表級鎖等待（Waiting for table level lock）的情況，嚴重的情況下會出現由於執行個體串連數被佔滿而應用無法正常串連的情況

CREATE TABLE `t_myisam` (`id` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8；Query |111 | User sleep              | select id,sleep(100) from t_myisam   |Query |108 | Waiting for table level lock | update t_myisam set id=2 where id=1|Query |  3 | Waiting for table level lock | update t_myisam set id=2 where id=1|

從上述的案例中可以看到，t_myisam表為myisam儲存引擎，當該表上有執行時間較長的查詢語句在執行的時候，該表上其他的更新全被堵塞住了，這個時候應用或者資料庫的串連很快耗完，導致應用請求失敗。這個時候快速的恢複方法為將線程id：111 kill掉即可（可以執行show processlist查看到當前資料庫所有串連狀態）。另外myisam儲存引擎的表索引在執行個體異常關閉的情況下會導致索引損壞，這個時候必須要對錶進行repair操作，該操作同樣會阻塞該表上的所有請求。

2. 表索引設計不當，導致資料庫出現死結。

索引設計是資料庫設計非常重要的一環，不僅僅關係到後續業務的效能，如果設計不當還可導致業務上的死結。下面的一則案例就出現線上上系統，資料庫在並發更新的時候出現了死結，通過排查定位於update更新使用了兩個索引導致，死結資訊如下：

*** (1) TRANSACTION:TRANSACTION 29285454235, ACTIVE 0.001 sec fetching rowsmysql tables in use 3, locked 3LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s)MySQL thread id 6641616, OS thread handle 0x2b165c4b1700, query id 28190427937 10.103.180.86 test_ebs Searching rows for updateUPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4580605*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454235 lock_mode X locks rec but not gap waitingRecord lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format; info bits 0*** (2) TRANSACTION:TRANSACTION 29285454237, ACTIVE 0.001 sec fetching rows, thread declared inside InnoDB 4980mysql tables in use 3, locked 3 5 lock struct(s), heap size 1184, 3 row lock(s)MySQL thread id 6639213, OS thread handle 0x2b1694cc2700, query id 28190427939 10.103.180.113 test_ebs Searching rows for updateUPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454237 lock_mode X locks rec but not gapRecord lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format;

表結構：

CREATE TABLE `test` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主鍵’,`test_id` bigint(20) DEFAULT NULL,`group_id` bigint(20) DEFAULT NULL COMMENT ‘Id，對應test_group.id’,`gmt_created` datetime DEFAULT NULL COMMENT ‘建立時間’,`gmt_modified` datetime DEFAULT NULL COMMENT ‘修改時間’,`is_deleted` tinyint(4) DEFAULT ‘0’ COMMENT ‘刪除。’,PRIMARY KEY (`id`),KEY `idx_testid` (`test_id`),KEY `idx_groupid` (`group_id`)) ENGINE=InnoDB AUTO_INCREMENT=7429111 ;

SQL執行計畫：

mysql>explain UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra || 1 | SIMPLE | test | index_merge | idx_testid,idx_groupid | idx_testid,idx_groupid | 9,9 | | 1 | Using intersect(idx_testid,idx_groupid); Using where; Using temporary |

所以第一個事務先根據group_id索引，已經鎖住primary id，然後再根據test_id索引，鎖定primary id；第二個事務先根據test_id索引，已經鎖住primary id，然後再根據group_id索引，去鎖primary id；所以這樣並發更新就可能出現死索引。

MySQL官方也已經確認了此bug：77209，解決方案有兩種：

• 加test_id + group_id的複合式索引，這樣就可以避免掉index merge；
• 將最佳化器的index merge最佳化關閉。

開發階段

交易處理時間過長，導致並發出現鎖等待。

並發交易處理在資料庫中經常看到的應用情境，在這種情境下，需要避免大事務，長事務，複雜事務導致事務在資料庫中的已耗用時間加長，事務時間變長則導致事務中鎖的持有時間變長，影響整體的資料庫輸送量。下面的一則案例中，使用者的業務資料庫中出現大量的update等待，導致大量的業務逾時報錯：

問題排查

1. 通過show processlist確定出現鎖等待的線程以及SQL資訊:

|Query|37|updating|UPDATE test_warning SET ... WHERE test_id = ‘46a9b‘

2. 通過innodb的information_schema資料庫中的鎖等待以及事務試圖，查出相關的鎖資訊：

select r.trx_mysql_thread_id waiting_thread,       r.trx_id waiting_trx_id,       r.trx_query waiting_query,       b.trx_id blocking_trx_id,       b.trx_query blocking_query,       b.trx_mysql_thread_id blocking_thread,       b.trx_ started,       b.trx_wait_started  from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id= w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id= w.requesting_trx_id \Gwaiting_thread: 318984063waiting_trx_id: 26432631waiting_query: UPDATE test_warning SET ........ WHERE test_id = ‘46a9b‘blocking_trx_id: 26432630blocking_query: NULLblocking_thread: 235202017trx_started: 2016-03-01 13:54:39

從述的鎖等待資訊中發現，事務26432631被26432630阻塞了，那麼我們就可以從general log中去排查一下事務26432630做了哪些操作。

3. 從提前開啟資料庫的general log中尋找到上述被blcok 的update語句在日誌中的位置，發現了update被blcok的根本原因： 日誌中有兩條不同的SQL同時並發更新同一條記錄，所以後更新的SQL會等待前更新的SQL，如果SQL1所在的事務1直沒有提交，那麼事務2將會一直等待，這樣就出現上述updating的狀態

235202017 Query UPDATE test_warning ..... WHERE test_id = ‘46a9b‘318984063 Query UPDATE test_warning ..... task_order_id = ‘‘ WHERE test_id = ‘46a9b‘

所以我們就可以在看一下這個事務的上下文：

thread id=235202017 的SQL上下文：

235202017 Query SET autocommit=0235202017 (13:54:39) Query UPDATE test_warning SET .... WHERE test_id = ‘46a9b‘235202017 Query commit

thread id=318984063 的SQL上下文：

318984063 Query SET autocommit=1318984063 (13:54:39)Query SELECT .... FROM test_waybill WHERE (test_id IN (‘46a9b‘))318984063 Query SELECT......FROM test_waybill WHERE test_id = ‘46a9b‘318984063 Query UPDATE test_warning SET ..... WHERE test_id = ‘46a9b‘318984063 (13:55:31)Query UPDATE test_waybill_current t ..... WHERE t.test_id IN (‘46a9b‘)318984063 Query SET autocommit=0

可以看到事務1 從13:54:39開始，直到13:55:30結束，事務2 中有更新事務1中的同一條記錄，所以直到事務1 提交後，事務2才得以執行完畢，有了這樣的日誌，將此資訊發給使用者很快就找到了問題，在事務1中由於還存在其他的商務邏輯，導致事務1的提交遲遲沒有完成，進而導致了其他業務鎖的發生。

維護階段

DDL操作被大查詢block。

當應用上線進入維護階段，則開始會有較多的資料庫變更操作，比如：添加欄位，添加索引等操作，這一類操作導致的鎖故障也是非常頻繁的，下面將會介紹一則案例，一個DDL操作被查詢block，導致資料庫連接堆積：

Query |6 | User sleep                       | select id ,sleep(50) from tQuery |4 | Waiting for table metadata lock  | alter table t add column gmt_create datetimeQuery |2 | Waiting for table metadata lock  | select * from t where id=1Query |1 | Waiting for table metadata lock  | select * from t where id=2Query |1 | Waiting for table metadata lock  | update t set id =2 where id=1

Metadata lock wait 的含義：為了在並發環境下維護表中繼資料的資料一致性，在表上有活動事務（顯式或隱式）的時候，不可以對中繼資料進行寫入操作。因此 MySQL 引入了 metadata lock ，來保護表的中繼資料資訊。因此在對錶進行上述操作時，如果表上有活動事務（未提交或復原），請求寫入的會話會等待在 Metadata lock wait。

導致 Metadata lock wait 等待的常見因素包括：活動事務，當前有對錶的長時間查詢，顯示或者隱式開啟事務後未提交或復原，比如查詢完成後未提交或者復原，表上有失敗的查詢事務等。

上述案例中，查詢，更新和DDL操作的線程狀態都為Waiting for table metadata lock，對錶t的操作全部被阻塞，前端源源不斷的請求到達資料庫，這個時候資料庫的串連很容易被打滿，那我們來分析一下為什麼有這這些鎖等待：

• alter 操作的鎖等待：由於在表t上做了一個添加欄位的操作，該操作會在結束前對錶擷取一個metadata lock，但是該表上已經有一個查詢一直未結束，導致metadata lock一直擷取不到，所以添加欄位操作只能等待查詢結束，這就解釋了alter操作為什麼狀態為Waiting for table metadata lock。
• 查詢和更新的鎖等待：由於前面進行的alter操作已經在T表上試圖擷取metadata lock，所以後續對錶T的的查詢和更新操作在擷取metadata lock的時候會被alter操作所阻塞，進而導致這些線程狀態為Waiting for table metadata lock。

解決辦法則是將線程6 kill 掉即可。

總結

鎖問題是非常常見的問題，需要我們在資料庫開發、設計、管理的各個階段都需要注意，防範未然，做到心中有數。

設計開發階段:

1. 表設計要避免使用myisam儲存引擎，改用innodb引擎；
2. 為SQL建立合適的索引，避免多個單列索引執行出錯；
3. 避免大事務，長事務，複雜事務導致事務在資料庫中的已耗用時間加長。

管理營運階段：

1. 在業務低峰期執行上述操作，比如建立刪除索引；
2. 在結構變更前，觀察資料庫中是否存在長時間啟動並執行SQL，未提交的事務；
3. 結構變更期間，監控資料庫的線程狀態是否存在lock wait。

【MySQL】鎖問題最佳實務