Main > Databases | MySQL > MySQL deadlocks

MySQL deadlocks

25.06.2014 1 comment » Views: 3,666

MySQL

Посмотреть инфо о последнем DEADLOCK-е для InnoDB MySQL можно с помощью такой комманды:

она выведет что-то вроде..

------------------------
LATEST DETECTED DEADLOCK
------------------------
140625 23:25:02
*** (1) TRANSACTION:
TRANSACTION 15135E0, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 65508, OS thread handle 0x7f37e01ad700, query id 11115651 localhost dbname Sending data
DELETE ur FROM enterprise_url_rewrite AS ur
INNER JOIN enterprise_catalog_category_rewrite AS rc ON rc.url_rewrite_id = ur.url_rewrite_id WHERE (rc.category_id IN ('284'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 68051 n bits 184 index PRIMARY of table dbname.enterprise_url_rewrite trx id 15135E0 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 15135E1, ACTIVE 0 sec updating or deleting
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1248, 6 row lock(s), undo log entries 1
MySQL thread id 65509, OS thread handle 0x7f37e011a700, query id 11115652 localhost dbname deleting from reference tables
DELETE ur FROM enterprise_url_rewrite AS ur
INNER JOIN enterprise_catalog_category_rewrite AS rc ON rc.url_rewrite_id = ur.url_rewrite_id WHERE (rc.category_id IN ('284'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 68051 n bits 184 index PRIMARY of table dbname.enterprise_url_rewrite trx id 15135E1 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48343 n bits 552 index PRIMARY of table dbname.enterprise_catalog_category_rewrite trx id 15135E1 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

Здесь нас интересует то, что выделено жирным. А именно то, что транзакции одновременно выполняются в двух процессах, что и приводит к deadlock-у и как результат к откату транзакции. Именно это и нужно устранить.

Author: | Rating: 4/5 | Tags: , , ,

1 comment.

Write a comment
  1. Pavel Reply
    14.09.2022 в 2:55 pm
    Спасибо, все кратко и без лишней "воды"!

Leave a Reply

Your email address will not be published. Required fields are marked *

Allowed HTML-tags: <a>, <code>, <i>, <em>, <strong>, <b>, <u>, <strike>