Hi Martin,
Sorry my example wasn't clearer. I am doing a commit or rollback
depending on the success of the overall transaction. What I don't do is
retry parts of the transaction upon deadlock.
Thanks for pointing that out, though!
Best,
Mike
On Mon, 2009-05-25 at 16:46 -0400, Martin Gainty wrote:
> Mike-
>
> MySQL should ALWAYS perform a commit or rollback e.g.
> exec()
> commit() or rollback()
>
> > The reason for this is from Page 419 of the
> > MySQL 5.0 Certification Study Guide bullet point #3:
> > During the course of a transaction, InnoDB may acquire row locks
> > AS IT DISCOVERS THEM TO BE NECESSARY.
>
> and yes Deadlocks can be very tricky to resolve
> Martin Gainty
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de
> confidentialité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
> unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig.
> Diese Nachricht dient lediglich dem Austausch von Informationen und
> entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten
> Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den
> Inhalt uebernehmen.
>
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
> destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
> l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci
> est interdite. Ce message sert à l'information seulement et n'aura pas
> n'importe quel effet légalement obligatoire. Étant donné que les email
> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
> aucune responsabilité pour le contenu fourni.
>
>
>
>
>
> > Subject: Understanding Transaction Deadlocks with Innodb
> > From: michael.cap...@henryschein.com
> > To: mysql@lists.mysql.com
> > Date: Mon, 25 May 2009 15:18:34 -0300
> >
> > 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=mgai...@hotmail.com
> >
>
>
> ______________________________________________________________________
> Windows Live™: Keep your life in sync. Check it out.
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.
