mysql 死锁
LATEST DETECTED DEADLOCK
2018-04-19 11:12:36 2afede43e700
* (1) TRANSACTION:
TRANSACTION 104877736810, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 2936, 14 row lock(s), undo log entries 18
MySQL thread id 321467514, OS thread handle 0x2afedf834700, query id 398212894965 10.30.50.73 snz_app_1 updating
update location set RECORD_VERSION=3589, ALLOCATED_QTY=715.00000, UPDATED_DTM_LOC='2018-04-19 11:12:36' where location_ID=1795599055 and RECORD_VERSION=3588
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7889 page no 181697 n bits 96 index PRIMARY of table snz.location trx table locks 4 total table locks 6 trx id 104877736810 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
* (2) TRANSACTION:
TRANSACTION 104877736953, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
11 lock struct(s), heap size 2936, 11 row lock(s), undo log entries 14
MySQL thread id 321476058, OS thread handle 0x2afede43e700, query id 398212895160 10.30.50.73 snz_app_1 updating
update location set RECORD_VERSION=1447, ALLOCATED_QTY=187.00000, UPDATED_DTM_LOC='2018-04-19 11:12:36' where location_ID=1795894481 and RECORD_VERSION=1446
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7889 page no 181697 n bits 96 index PRIMARY of table snz.location trx table locks 4 total table locks 6 trx id 104877736953 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7889 page no 426162 n bits 104 index PRIMARY of table snz.location trx table locks 4 total table locks 6 trx id 104877736953 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
* WE ROLL BACK TRANSACTION (2)
TRANSACTIONS
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
The error message you've encountered describes a deadlock situation in your MySQL database, which is a common issue when two or more transactions are trying to acquire locks on the same resources in different orders, resulting in a circular dependency that prevents any transaction from proceeding. Here's a breakdown of the information provided:
Deadlock Explanation
-
Transaction 1 (trx id 104877736810):
- Intent: Updating a record in the
locationtable wherelocation_ID=1795599055andRECORD_VERSION=3588. - Lock Status: It's waiting to acquire an exclusive (X) lock on a specific record (primary key index entry) but can't because another transaction holds the lock.
- Intent: Updating a record in the
-
Transaction 2 (trx id 104877736953):
- Intent: Similarly, it's trying to update a different record in the
locationtable wherelocation_ID=1795894481andRECORD_VERSION=1446. - Lock Status: It currently holds an exclusive (X) lock on one record but is waiting to acquire another exclusive lock on a different record, which Transaction 1 is also waiting for indirectly.
- Intent: Similarly, it's trying to update a different record in the
Resolution
MySQL's InnoDB storage engine automatically detects such deadlocks and rolls back one of the transactions to break the deadlock, as indicated by "WE ROLL BACK TRANSACTION (2)". In this case, Transaction 2 was chosen to be rolled back.
Prevention Strategies
To minimize deadlocks in your application, consider implementing the following strategies:
-
Order Transactions Consistently: Ensure transactions access records in a consistent order to avoid conflicting lock acquisition sequences.
-
Keep Transactions Short: Minimize the amount of work done within a single transaction to reduce the likelihood and duration of locks being held.
-
Use Locking Hints: If applicable, use explicit locking hints to guide the locking behavior, though this should be done with caution and understanding of its implications.
-
Retry Logic: Implement retry logic in your application code to automatically re-attempt transactions that were rolled back due to deadlocks.
-
Optimize Indexes: Proper indexing can reduce lock contention by minimizing the number of rows accessed during queries.
-
Monitor and Analyze: Regularly monitor your database for deadlocks using tools like the Performance Schema in MySQL, and analyze the deadlock logs to understand patterns and potential areas for optimization.
Remember, while deadlocks are a normal part of database operations, proactive measures can significantly reduce their frequency and impact on your application's performance.
