Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am using mysql 5.5.28.

I would like to ask a question about the nature of deadlocks in innodb engine.

I monitor deadlock with pt-deadlock-logger.

Recently I found deadlock of two transactions to two different tables. How it's possible? I thought deadlocks may happen only when two transactions try to get access to the same record of the same table. Both transactions tried to UPDATE field updated_at on the same time on the different tables.

How it's possible?

I also saved the output if SHOW ENGINE INNODB STATUS and can share with you any detail.

Queries:

UPDATE `tickets` 
SET `updated_at` = '2012-11-27 08:24:24' 
WHERE `id` = 123

UPDATE `ticket_info` 
SET `updated_at` = '2012-11-27 08:24:23', 
    `ticket_for` = 'reason'  
WHERE `id` = 456
share|improve this question
1  
Can you post details (the deadlocked transactions, the tables' definitions)? Are there any triggers on the two tables? – ypercube Nov 22 '12 at 13:02
there is no triggers – fog Dec 2 '12 at 16:24
Again: can you please add the tables definitions (use SHOW CREATE TABLE tickets; and SHOW CREATE TABLE ticket_info;). I suspect there is one (or more?) foreign keys between the 2 tables, right? – ypercube Dec 2 '12 at 17:47

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.