Nobody can analyze how the deadlock completed, provide the solution, thank you for your bosses

CodePudding user response:

A data by multiple update process execution at the same time,
The best solution in the code level

CodePudding user response:

Looks like two transactions in terms of updating is red_packet_record_id query result is a range, uidx_userId_redPacketId corresponding range and clearance lock conflicts caused the update fails,
Should consider the where condition behind with primary key or unique index to avoid lock space,

Uidx_userId_redPacketId see names like a unique index (userId, redPacketId), mysql in the official document said is if the condition is used to update in the composite index only part of the field can also lead to add clearance lock, so you should also try the where the back with a userId and red_packet_record_id==XXX XXX do lock the only update record to avoid locking to space,

Finally: I will only basic database statement, due to the project scale, nor used transactions, index, lock what of, can say more than I compiled, no guarantee of,,,

CodePudding user response:

I feel like in A transaction to update multiple records, assuming A transaction need two records update 1, 2, B transaction two records need to be updated 2, 1


A lock 1 first, and update 1, and then to apply for A lock 2
2 B first lock, and update 2, and then to apply for a lock 1