>-----Original Message----- >From: Paul McCullagh [mailto:paul.mccull...@primebase.com] >Sent: Monday, March 09, 2009 6:34 PM >To: Mattia Merzi >Cc: MySql >Subject: Re: InnoDB deadlocks > >Hi Mattia, > >On Mar 9, 2009, at 6:21 PM, Mattia Merzi wrote: > >> 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? > >Yes. > >But transactions will also no longer run in parallel which will reduce >the throughput of the server if it is accessed by multiple clients. > [JS] There is no free lunch, but sometimes you get a free appetizer. Within limits, you will get better throughput if you have multiple transactions running in parallel rather than running them serially. The problem is to determine those limits. If you have the luxury, you run stress tests and examine the queue lengths for the various bits: disk, memory, cpu, network. That will give you some idea of what your system can tolerate, as well as telling you where to put your money. In reality, most of us run stress tests during production. :<(
Of the various resources, memory is the one with the sharpest "knee in the curve" because either you have enough or you don't. If you have enough memory, then more will not help. Remember, there is always exactly one bottleneck in a system at any given moment. By the way, if transactions are constantly presented to a resource faster than the resource can service, the queue length will grow to infinity. That will cause performance problems. ;<) >> >> 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? > >Yes, this will work the same. > >> 'Cause the last sentence of the manual says: >> "With MySQL table-level locks, the timeout method must be used to >> resolve deadlocks" > >This is true, but is only a problem if deadlocks are possible. >However, deadlocks are not possible if you start every transaction >with a "lock table .... write". >> >> will this mean that if I use this LOCK TABLE method I can get >> timeouts instead of deadlocks >> on the very same transactions? > >Yes, this can happen. But, only if a deadlock is possible. By >exclusive locking a single resource (a row or a table), at the start >of each transaction, you explicitly make deadlocks impossible. > >However, it is recommended to UPDATE a single row in the new table, >instead of using "lock table .... write". > >This has the same affect, but with the benefit that deadlock detection >will still work in the case that you do not modify all transactions as >suggested. > >This might happen because: > >- your code is vast, and you miss one, or >- you add a new transaction and forget to add the exclusive locking >UPDATE, or >- you leave out certain transaction on purpose because you have never >had deadlocking problems with them before. > >All "good" reasons not to serialize all transactions, and therefore a >good reason to use the method that continues to support deadlock >detection. > >Best regards, > >Paul > > >-- >Paul McCullagh >PrimeBase Technologies >www.primebase.org >www.blobstreaming.org >pbxt.blogspot.com > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
