| 導購 | 订阅 | 在线投稿
分享
 
 
 

巧用MySQL InnoDB引擎鎖機制解決死鎖問題

來源:互聯網  2008-06-01 03:05:24  評論

最近,在項目開發過程中,碰到了數據庫死鎖問題,在解決問題的過程中,筆者對MySQL InnoDB引擎鎖機制的理解逐步加深。

案例如下:

在使用Show innodb status檢查引擎狀態時,發現了死鎖問題:

*** (1) TRANSACTION:

TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 320

MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update

update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting

Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

*** (2) TRANSACTION:

TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499

mysql tables in use 1, locked 1

3 lock struct(s), heap size 320, undo log entries 1

MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating

update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap

Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting

Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;

*** WE ROLL BACK TRANSACTION (1)

此死鎖問題涉及TSK_TASK表,該表用于保存系統監測任務,以下是相關字段及索引:

ID:主鍵;

MON_TIME:監測時間;

STATUS_ID:任務狀態;

索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

分析,涉及的兩條語句應該不會涉及相同的TSK_TASK記錄,那爲什麽會造成死鎖呢?

查詢MySQL官網文檔,發現這跟MySQL的索引機制有關。MySQL的InnoDB引擎是行級鎖,我原來的理解是直接對記錄進行鎖定,實際上並不是這樣的。

要點如下:

不是對記錄進行鎖定,而是對索引進行鎖定;

在UPDATE、DELETE操作時,MySQL不僅鎖定WHERE條件掃描過的所有索引記錄,而且會鎖定相鄰的鍵值,即所謂的next-key locking;

如語句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000會鎖定所有主鍵大于等于1000的所有記錄,在該語句完成之前,你就不能對主鍵等于10000的記錄進行操作;

當非簇索引(non-cluster index)記錄被鎖定時,相關的簇索引(cluster index)記錄也需要被鎖定才能完成相應的操作。

再分析一下發生問題的兩條SQL語句,就不難找到問題所在了:

當「update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME

假設「update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)」幾乎同時執行時,本語句首先鎖定簇索引(主鍵),由于需要更新STATUS_ID的值,所以還需要鎖定KEY_TSKTASK_MONTIME2的某些索引記錄。

這樣第一條語句鎖定了KEY_TSKTASK_MONTIME2的記錄,等待主鍵索引,而第二條語句則鎖定了主鍵索引記錄,而等待KEY_TSKTASK_MONTIME2的記錄,在此情況下,死鎖就産生了。

筆者通過拆分第一條語句解決死鎖問題:

先查出符合條件的ID:select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute);然後再更新狀態:update TSK_TASK set STATUS_ID=1064 where ID in (….)

至此,死鎖問題徹底解決。

最近,在項目開發過程中,碰到了數據庫死鎖問題,在解決問題的過程中,筆者對MySQL InnoDB引擎鎖機制的理解逐步加深。 案例如下: 在使用Show innodb status檢查引擎狀態時,發現了死鎖問題: *** (1) TRANSACTION: TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;; *** (2) TRANSACTION: TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;; *** WE ROLL BACK TRANSACTION (1) 此死鎖問題涉及TSK_TASK表,該表用于保存系統監測任務,以下是相關字段及索引: ID:主鍵; MON_TIME:監測時間; STATUS_ID:任務狀態; 索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。 分析,涉及的兩條語句應該不會涉及相同的TSK_TASK記錄,那爲什麽會造成死鎖呢? 查詢MySQL官網文檔,發現這跟MySQL的索引機制有關。MySQL的InnoDB引擎是行級鎖,我原來的理解是直接對記錄進行鎖定,實際上並不是這樣的。 要點如下: 不是對記錄進行鎖定,而是對索引進行鎖定; 在UPDATE、DELETE操作時,MySQL不僅鎖定WHERE條件掃描過的所有索引記錄,而且會鎖定相鄰的鍵值,即所謂的next-key locking; 如語句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000會鎖定所有主鍵大于等于1000的所有記錄,在該語句完成之前,你就不能對主鍵等于10000的記錄進行操作; 當非簇索引(non-cluster index)記錄被鎖定時,相關的簇索引(cluster index)記錄也需要被鎖定才能完成相應的操作。 再分析一下發生問題的兩條SQL語句,就不難找到問題所在了: 當「update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME 假設「update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)」幾乎同時執行時,本語句首先鎖定簇索引(主鍵),由于需要更新STATUS_ID的值,所以還需要鎖定KEY_TSKTASK_MONTIME2的某些索引記錄。 這樣第一條語句鎖定了KEY_TSKTASK_MONTIME2的記錄,等待主鍵索引,而第二條語句則鎖定了主鍵索引記錄,而等待KEY_TSKTASK_MONTIME2的記錄,在此情況下,死鎖就産生了。 筆者通過拆分第一條語句解決死鎖問題: 先查出符合條件的ID:select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute);然後再更新狀態:update TSK_TASK set STATUS_ID=1064 where ID in (….) 至此,死鎖問題徹底解決。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有