Take the 2-minute tour ×
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.

What could be the reason behind 2 inserts causing a deadlock? Here is what I get from log file:

*** (1) TRANSACTION:
TRANSACTION 6CA06B9, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 30 lock struct(s), heap size 3112, 20 row lock(s), undo log entries 29
MySQL thread id 2363287, OS thread handle 0x7f5a0cec0700, query id 220715881 xx.xx.xx.xx yyy update
INSERT INTO `sales_payment_transaction` (`order_id`, `payment_id`, `txn_id`, `txn_type`, `is_closed`, `additional_information`, `created_at`) 
VALUES ('1054784', '797219', '6292592389', 'authorization', '0', NULL, '2014-06-28 16:56:42')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25553 page no 92 n bits 568 index `UNQ_SALES_PAYMENT_TRANSACTION_ORDER_ID_PAYMENT_ID_TXN_ID` of table `yyy`.`sales_payment_transaction` trx id 6CA06B9 lock_mode X insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 6CA06D3, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
35 lock struct(s), heap size 6960, 22 row lock(s), undo log entries 24
MySQL thread id 2363298, OS thread handle 0x7f62884f2700, query id 220715945 xx.xx.xx.xx yyy update
INSERT INTO `sales_payment_transaction` (`order_id`, `payment_id`, `txn_id`, `txn_type`, `is_closed`, `additional_information`, `created_at`) 
VALUES ('1054785', '797220', '6292592426', 'authorization', '0', NULL, '2014-06-28 16:56:43')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25553 page no 92 n bits 568 index `UNQ_SALES_PAYMENT_TRANSACTION_ORDER_ID_PAYMENT_ID_TXN_ID` of table `yyy`.`sales_payment_transaction` trx id 6CA06D3 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25553 page no 92 n bits 568 index `UNQ_SALES_PAYMENT_TRANSACTION_ORDER_ID_PAYMENT_ID_TXN_ID` of table `yyy`.`sales_payment_transaction` trx id 6CA06D3 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

The unique index UNQ_SALES_PAYMENT_TRANSACTION_ORDER_ID_PAYMENT_ID_TXN_ID is built from 3 fields of the table - order_id, payment_id, txn_id. I can not figure out, how these 2 inserts could cause a deadlock (but I am really new to this). Please, help!

share|improve this question

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.