List:General Discussion« Previous MessageNext Message »
From:Mattia Merzi Date:March 9 2009 5:21pm
Subject:InnoDB deadlocks
View as plain text  
Hi everyone,

I've got some problems with deadlocks on InnoDB tables.
On paragraph 13.6.8.10. "How to Cope with Deadlocks"
of the mysql 5.1 version, the last sentence states:
--------------
Another way to serialize transactions is to create an auxiliary
“semaphore” table that contains just a single row.
Have each transaction update that row before accessing other tables.
In that way, all transactions happen in a serial fashion.
Note that the InnoDB instant deadlock detection algorithm also works
in this case, because the serializing lock is a row-level lock.
With MySQL table-level locks, the timeout method must be used to
resolve deadlocks.
--------------

Just two very simple questions:
- using this method, transactions will be serialized so the deadlock
problem will never come up again?
  This seems clear reading that sentence, the only thing that makes me
humble is the statement:
  "Note that the InnoDB instant deadlock detection algorithm also
works in this case" ... can someone
  briefly explain me this concept?
- if I create a "semaphore" table and I start any deadlock-prone
transaction issuing a "lock table .... write"
  on that table and an unlock tables immediately after the commit,
will the effect be the same?
  'Cause the last sentence of the manual says:
  "With MySQL table-level locks, the timeout method must be used to
resolve deadlocks"
  will this mean that if I use this LOCK TABLE method I can get
timeouts instead of deadlocks
  on the very same transactions?

Thank you very much,

Greetings,

Mattia.
Thread
InnoDB deadlocksMattia Merzi9 Mar
  • Re: InnoDB deadlocksPaul McCullagh9 Mar
    • RE: InnoDB deadlocksJerry Schwartz10 Mar
      • Re: InnoDB deadlocksMattia Merzi10 Mar