麻煩大神支招，不知道為什么單一sql，會死鎖！！！
背景：
一個狀態表，需要通過id修改狀態，代碼中未啟用事務，都是單一update-sql。
兩個事務,語法都是update by ids
區別是一個修改為"COST_ONGOING"狀態,一個修改為"WAIT_UPDATE"狀態,一個附加條件是voucher_acctgplt_trans_status_enum IN ,一個附加條件是voucher_acctgplt_trans_status_enum NOT IN ，都有tenant_id 條件。
這個表有三個索引：
1.主鍵索引 id 列
2.唯一索引 tenant_id + A + B + C【無sql中的列】
3.非唯一索引 A + voucher_acctgplt_trans_status_enum
事務1:
UPDATE voucher_acctgplt_trans_track SET voucher_acctgplt_trans_status_enum = 'COST_ONGOING', voucher_acctgplt_trans_track.version_no = voucher_acctgplt_trans_track.version_no + 1, voucher_acctgplt_trans_track.last_updated_user_id = 61000391511, voucher_acctgplt_trans_track.last_updated_stamp = now() WHERE (id IN (1275221270331468, 1275221270331672, 1275222880944467, 1275223820468617, 1275226907476342, 1275227041693840, 1275228652306561, 1277286075859673, 1277288223342592, 1277288894431261, 1277289833955388, 1275221270331419, 1275221270331603, 1275223283597348, 1275223820468483, 1275226907476255, 1275227041693764, 1275228652306522, 1277287686471772, 1277288089125194, 1277288894431238, 1277289833955374) AND voucher_acctgplt_trans_status_enum NOT IN ('DELETED', 'ACCTG_TRANS_ON_GENERATING', 'ACCTG_TRANS_GENERATED')) AND voucher_acctgplt_trans_track.tenant_id = 1262172890595328
事務2:
UPDATE voucher_acctgplt_trans_track SET voucher_acctgplt_trans_status_enum = 'WAIT_UPDATE', voucher_acctgplt_trans_track.version_no = voucher_acctgplt_trans_track.version_no + 1, voucher_acctgplt_trans_track.last_updated_user_id = 61000391511, voucher_acctgplt_trans_track.last_updated_stamp = now() WHERE (id IN (1275203687809025, 1275203822026775, 1275221270331468, 1275221270331672, 1275222880944467, 1275223015161856, 1275223686250647, 1275223820468617, 1275223954686216, 1275226907476056, 1275226907476342, 1275227041693840, 1275227041693983, 1275227578564718, 1275228518088766, 1275228652306442, 1275228652306561, 1275228786524403, 1275228920741936, 1277251984556118, 1277251984556300, 1277252252991616, 1277252387209337, 1277252521427293, 1277261111361670, 1277261245579296, 1277278022795264, 1277278157012992, 1277278157013133, 1277278291230830, 1277278559666267, 1277278693883948, 1277278693884002) AND voucher_acctgplt_trans_status_enum IN ('WAIT_UPDATE', 'COST_ONGOING', 'NOT_SYNCHRONIZED','SYNCHRONIZED')) AND voucher_acctgplt_trans_track.tenant_id = 1262172890595328
死鎖日志：
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 691 page no 21585 n bits 208 index PRIMARY of table `voucher_acctgplt_trans_track` trx id 2369817381 lock_mode X locks rec but not gap
Record lock, heap no 58 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 691 page no 21587 n bits 216 index PRIMARY of table `voucher_acctgplt_trans_track` trx id 2369817363 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
uj5u.com熱心網友回復：
你看看不同的sql之間有沒有公共的東西uj5u.com熱心網友回復：
死鎖日志lock_mode X locks rec but not gap 顯示是記錄鎖，非間隙鎖有2個事務分別執行如下SQL陳述句
事務A：update tb set a='xxx' where id in (1,3,7,12);
事務B：update tb set b='xxx' where id in (7,4,5,1);
事務A會按id=1---> 3---->7---12 依次加鎖
事務B會按id=7---> 4----->5----1 依次加鎖
這就導致了事務A持有id=1的行鎖，想要獲取id=7的行鎖，發現id=7被事務B持有，事務A等待事務B釋放鎖；事務B持有id=7的行鎖，想要獲取id=1的航所，發現id=1被事務A持有，事務B就等待A釋放鎖，從而進入了死鎖狀態
uj5u.com熱心網友回復：
你好，這個考慮到了，id都排過序的，但是還是死鎖，所以理解不了
uj5u.com熱心網友回復：
你好，這個sql反復研究很多遍了，如果按主鍵來鎖的話，不應該死鎖的，我就像知道是什么機制導致了死鎖
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/246301.html
標籤：MySQL