Trouble great god action, don't know why a single SQL, die lock!!!!!!

Background:
, a state table id are needed to modify status, the code is not enabled in the transaction, is a single update - SQL,

Two transactions, grammar is the update by ids
Difference is a change to "COST_ONGOING" state, an amended as "WAIT_UPDATE" state, an additional condition is voucher_acctgplt_trans_status_enum IN, an additional condition is voucher_acctgplt_trans_status_enum NOT IN, have tenant_id conditions,

This table has three indexes:
1. The primary key index id column
2. The only index tenant_id + A + B + C [no columns in the SQL]
3. The index of the only A + voucher_acctgplt_trans_status_enum

Transaction 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

Transaction 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


Deadlock logs:
Doesn * * * (1) THE LOCK (S) :
RECORD the 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 the lock, heap no 58 PHYSICAL Record: n_fields 14; Compact format. The info bits (0

Doesn * * * (2) THE LOCK (S) :
RECORD the 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 the lock, heap no 3 PHYSICAL Record: n_fields 14; Compact format. The info bits 0

CodePudding user response:

Did you see between different SQL public

CodePudding user response:

Deadlock log lock_mode X locks rec but not gap is record locks, lock the clearance

Have two affairs respectively carry out the following SQL statement
Transaction A: update the TB set A='XXX' where id in (1,3,7,12);
Transaction B: update the TB set B='XXX' where id in (7,4,5,1);

The transaction will be A press id=1 - & gt; 3 - & gt; 7-12 in turn lock
Transaction will be according to the id=7 B - & gt; 4 -- -- -- -- -- & gt; 5-1 in turn lock

This leads to A transaction A hold id=1 row locks, want to get id=7 row locks, found that B hold id=7 be affairs, affairs waiting transaction B releases the lock; Transaction B hold id=7 row locks, want to get id=1 by air, find id=1 be transaction, A transaction B is waiting for A lock is released, which entered into A deadlock state

CodePudding user response:

refer to the second floor alone xiang water friends reply:

deadlock log lock_mode X locks rec but not gap is record locks, lock the clearance

Have two affairs respectively carry out the following SQL statement
Transaction A: update the TB set A='XXX' where id in (1,3,7,12);
Transaction B: update the TB set B='XXX' where id in (7,4,5,1);

The transaction will be A press id=1 - & gt; 3 - & gt; 7-12 in turn lock
Transaction will be according to the id=7 B - & gt; 4 -- -- -- -- -- & gt; 5-1 in turn lock

This leads to A transaction A hold id=1 row locks, want to get id=7 row locks, found that B hold id=7 be affairs, affairs waiting transaction B releases the lock; Transaction B hold id=7 row locks, want to get id=1 by air, find id=1 be transaction, A transaction B is waiting for A lock is released, thus enter the deadlock state

Hello, this into account, id is a sequence of, but still a deadlock, so don't understand

CodePudding user response:

reference 1/f, singing sound Amadues response:

you look to the public between different SQL

Hello, this SQL study many times repeatedly, if according to the primary key to lock, should not be a deadlock, I like to know what is the mechanism caused the deadlock