Hi there,
I am trying to sort through an occasional problem I am having with
deadlocks I am facing with a series of inoodb tables:
cases (PK id)
|___ cases_workcodes (PK id, case_id / FK case_id)
|___ cases_invoices (PK id, case_id / FK case_id)
|___ cases_additional (PK id, case_id / FK case_id)
|___ cases_alloys (PK id, case_id / FK case_id)
|___ cases_enclosures (PK id, case_id / FK case_id)
The cases table has a one-to-many relationship with the noted "child"
tables, maintained by fully cascading foreign keys.
When inserting or changing data in the "cases" and related tables I have
code that does something like this:
1. Create transaction
2. REPLACE data in a single case as identified by a primary key
* The choice of using a REPLACE statement is that I want
it to INSERT or DELETE and INSERT the case data. With
the cascades on DELETE, case sub table data gets cleaned
up for me automatically.
3. Loop through each case sub types and INSERT each
4. Close transaction
My problem is that once in a while (almost daily) I get the following
error:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction
The interesting thing is that I never get the deadlock on the initial
REPLACE statement on the case. The deadlock is always reported on the
INSERT statements on the sub tables.
I know I have no other processes that are locking data in the sub tables
for INSERT or UPDATE beyond the scope of that single identified case
record. However, I am not sure the scope of the transaction lock.
I also know that I need to recode to catch the deadlock and try again,
however I'm not sure why this would be happening in the first place.
Part of me believes if I understood the circumstances of the deadlock, I
might be able to solve this issue (in part or in whole) at the DB level.
Any tips? I've included below data from the Innodb status output.
Thanks,
Mike
------------------------
LATEST DETECTED DEADLOCK
------------------------
090522 19:51:54
*** (1) TRANSACTION:
TRANSACTION 0 3102355, ACTIVE 0 sec, process no 10134, OS thread id 1191344448
inserting
mysql tables in use 1, locked 1
LOCK WAIT 33 lock struct(s), heap size 6752, 22 row lock(s), undo log entries 9
MySQL thread id 141330, query id 3658119 x.x.x.x ddx update
INSERT INTO
ddx800020.cases_invoices
(id, case_id, statement_date, practice_id, invoice_date,
taxes, total, line_items, note, payment, payment_id)
VALUES
('263012', '310372', NULL, '221', '2009-05-22', '0.00',
'183.75', 'WORKCODES', NULL, '0', NULL)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6587 page no 54 n bits 1256 index `IDX_cases_invoices_1`
of table `ddx800020`.`cases_invoices` trx id 0 3102355 lock_mode X locks gap
before rec insert intention waiting
Record lock, heap no 983 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 0004bcbe; asc ;; 1: len 4; hex 000401ee; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 3102341, ACTIVE 0 sec, process no 10134, OS thread id 1192675648
inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
41 lock struct(s), heap size 6752, 38 row lock(s), undo log entries 18
MySQL thread id 141328, query id 3658088 x.x.x.x ddx update
INSERT INTO
ddx800020.cases_macros
(case_id, macro_id, dental_code, units, description,
teeth)
VALUES
('310332', 'P', '', '1', 'PFZ - PORCELAIN FUSED TO
ZIRCONIA', '26')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6587 page no 54 n bits 1256 index `IDX_cases_invoices_1`
of table `ddx800020`.`cases_invoices` trx id 0 3102341 lock mode S locks gap
before rec
Record lock, heap no 983 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 0004bcbe; asc ;; 1: len 4; hex 000401ee; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2056 page no 11 n bits 720 index `IDX_cases_macros_1` of
table `ddx800020`.`cases_macros` trx id 0 3102341 lock_mode X locks gap before
rec insert intention waiting
Record lock, heap no 436 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 0004bc6e; asc n;; 1: len 6; hex 0000000cc8cf; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
Please consider the environment before printing this email.
E-mail messages may contain viruses, worms, or other malicious code. By reading
the message and opening any attachments, the recipient accepts full
responsibility for taking protective action against such code. Henry Schein is
not liable for any loss or damage arising from this message.
The information in this email is confidential and may be legally privileged. It
is intended solely for the addressee(s). Access to this e-mail by anyone else
is unauthorized.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
