mysql(innodb)死锁

smdnsysy  于 2021-06-17  发布在  Mysql
关注(0)|答案(0)|浏览(247)

我们正在与一个死锁作斗争,这个死锁每天在我们的生产环境中发生多次。

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2018-12-27 19:07:34 7fcef1959700
  5. ***(1) TRANSACTION:
  6. TRANSACTION 2125001468, ACTIVE 2 sec starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  9. MySQL thread id 42190185, OS thread handle 0x7fcffc0b1700, query id 918842488 --- updating
  10. UPDATE synchronization SET service_synchronized_at = NULL WHERE id = 116212
  11. ***(1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 615 page no 288 n bits 528 index `PRIMARY` of table `app`.`synchronization` trx table locks 1 total table locks 2 trx id 2125001468 lock_mode X locks rec but not gap waiting lock hold time 2 wait time before grant 0
  13. ***(2) TRANSACTION:
  14. TRANSACTION 2125001355, ACTIVE 5 sec fetching rows
  15. mysql tables in use 2, locked 2
  16. 25216 lock struct(s), heap size 3683880, 5297668 row lock(s), undo log entries 94
  17. MySQL thread id 42189517, OS thread handle 0x7fcef1959700, query id 918842042 --- updating
  18. UPDATE synchronization s SET s.service_synchronized_at = now() WHERE s.service_synchronized_at IS NULL AND s.user_id IN (* time consuming select to determine which users should be updated *)
  19. ***(2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 615 page no 288 n bits 528 index `PRIMARY` of table `app`.`synchronization` trx table locks 2 total table locks 2 trx id 2125001355 lock_mode X lock hold time 3 wait time before grant 0
  21. ***(2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 615 page no 2313 n bits 472 index `PRIMARY` of table `app`.`synchronization` trx table locks 2 total table locks 2 trx id 2125001355 lock_mode X waiting lock hold time 0 wait time before grant 0
  23. ***WE ROLL BACK TRANSACTION (1)

在我想设置的一个查询中 service_synchronized_at = NULL . 在更新之前的第一次查询中,服务\u synchronized \u at始终不为空。
在第二个查询中,我添加了一个where条件 s.service_synchronized_at IS NULL 认为这将导致非锁定行的非空值。我想我错了。
表上只有主索引 id 唯一的约束 user_id (当然,外键在 user_id ).
欢迎任何帮助。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题