MySQL鎖問題最佳實踐

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

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

(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 rows

mysql tables in use 3, locked 3

LOCK 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 update

UPDATE 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 waiting

Record 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 4980

mysql 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 update

UPDATE 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 gap

Record 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：https://bugs.mysql.com/bug.php?id=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 \G

waiting_thread: 318984063

waiting_trx_id: 26432631

waiting_query: UPDATE test_warning SET …….. WHERE test_id = ’46a9b’

blocking_trx_id: 26432630

blocking_query: NULL

blocking_thread: 235202017

trx_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=0

235202017 (13:54:39) Query UPDATE test_warning SET …. WHERE test_id = ’46a9b’

235202017 Query commit

thread id=318984063 的SQL上下文：

318984063 Query SET autocommit=1

318984063 (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 t

Query |4 | Waiting for table metadata lock | alter table t add column gmt_create datetime

Query |2 | Waiting for table metadata lock | select * from t where id=1

Query |1 | Waiting for table metadata lock | select * from t where id=2

Query |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 掉即可。

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

設計開發階段

• 表設計要避免使用myisam存儲引擎，改用innodb引擎；
• 為SQL創建合適的索引，避免多個單列索引執行出錯；
• 避免大事務，長事務，複雜事務導致事務在數據庫中的運行時間加長；

管理運維階段：

• 在業務低峰期執行上述操作，比如創建刪除索引；
• 在結構變更前，觀察數據庫中是否存在長時間運行的SQL，未提交的事務；
• 結構變更期間，監控數據庫的線程狀態是否存在lock wait。

閱讀更多 IT生涯 的文章

關鍵字: 技術 情況 連接數