Deadlocks

I was reading something in the "MySQL for Database Administrators" class
materials book that has me wondering. It's in the section on InnoDB
Locking - section 8.3.3. Maybe I'm reading this incorrectly but it seems
to be saying that deadlocks are only possible if a second storage engine
is involved. Is that true? I thought deadlocks could be caused by a
poorly designed application where two (or more) transactions lock two or
more resources but in a different order.

Here's the quote from the book...

"Deadlocks are possible because InnoDB does not acquire locks during a
transaction until they are needed. InnoDB uses graph detection for
deadlocks so that they can be detected instantly. When a deadlock is
detected, one of the transactions is automatically rolled back. This
only occurs if a second storage engine is involved in the deadlock."

TIA

Hi Chuck,

Chuck <skilover_nospam@bluebottle.com> wrote:

> Here's the quote from the book...
>
> "Deadlocks are possible because InnoDB does not acquire locks during a
> transaction until they are needed. InnoDB uses graph detection for
> deadlocks so that they can be detected instantly. When a deadlock is
> detected, one of the transactions is automatically rolled back. This
> only occurs if a second storage engine is involved in the deadlock."

This is all fine, except for the last sentence. I wonder if there is
some context missing.

What is true: a transaction can involve tables using different storage
engines, but some MySQL engines are not capable of transactions. In
case of a deadlock any changes done to a nontransactional table cannot
be rolled back. I am not perfectly sure if InnoDB takes this into
account when it choses the transaction to be rolled back. But I don't
think so.


XL
--
Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems

MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> Hi Chuck,
>
> Chuck <skilover_nospam@bluebottle.com> wrote:
>
>> Here's the quote from the book...
>>
>> "Deadlocks are possible because InnoDB does not acquire locks during a
>> transaction until they are needed. InnoDB uses graph detection for
>> deadlocks so that they can be detected instantly. When a deadlock is
>> detected, one of the transactions is automatically rolled back. This
>> only occurs if a second storage engine is involved in the deadlock."
>
> This is all fine, except for the last sentence. I wonder if there is
> some context missing.

That is the entire context. It's one entire bullet point pertaining to
InnoDB locking. It doesn't make sense to me and I'm wondering if it's a
misprint. Maybe it should say "if a second storage engine is NOT
involved.". I simply don't know. What I know of deadlocks from other
RDBMSes is that they are actually an application design issue and can be
completely prevented if the application always locks tables in the same
order. IOW don't write one part of the application to do "update master
; update line_items; commit;" and another part to do "update line_items
; update master ; commit;".

I'm guessing that the same is true with the InnoDB engine - that
deadlocks are 100% preventable with good application design.

Chuck <skilover_nospam@bluebottle.com> wrote:
> Axel Schwenke wrote:

>> This is all fine, except for the last sentence. I wonder if there is
>> some context missing.
>
> That is the entire context. It's one entire bullet point pertaining to
> InnoDB locking. It doesn't make sense to me and I'm wondering if it's a
> misprint.

What book is that?

> Maybe it should say "if a second storage engine is NOT involved.".

Still makes no sense. A deadlock can only happen if InnoDB tables are
involved in both transactions. And then the only solution is to roll
back one of the transactions.

> What I know of deadlocks from other
> RDBMSes is that they are actually an application design issue and can be
> completely prevented if the application always locks tables in the same
> order. IOW don't write one part of the application to do "update master
> ; update line_items; commit;" and another part to do "update line_items
> ; update master ; commit;".
>
> I'm guessing that the same is true with the InnoDB engine - that
> deadlocks are 100% preventable with good application design.

This is correct.

There are some more pitfalls connected to InnoDBs next-key locking
algorithm. But this is about locking in general, not deadlocks.


XL
--
Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems

MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
>
> There are some more pitfalls connected to InnoDBs next-key locking
> algorithm. But this is about locking in general, not deadlocks.

Can the next key locking induce a deadlock on it's own?

I am fairly new to MySQL/innodb. I've been and Oracle DBA for a long
time though. I am seeing deadlocks on one application running on mysql
every few minutes. The two transactions in the deadlock section of
'show engine innodb status' are almost always two DELETE's trying to
delete the same row. I've never seen anything like this in Oracle and
I'm wondering if it has to do with next key locking and the default
isolation level difference between Oracle and MySQL. MySQL uses
repeatable read whereas Oracle used read committed.

Chuck <skilover_nospam@bluebottle.com> wrote:
> Axel Schwenke wrote:
>>
>> There are some more pitfalls connected to InnoDBs next-key locking
>> algorithm. But this is about locking in general, not deadlocks.
>
> Can the next key locking induce a deadlock on it's own?

No.

Deadlocks can however happen with a single table, if two statements
access the table via different indexes. This happens because InnoDB
ties record locks to index entries (there is a good reason for doing
it this way, but this goes too much into details)

Example:

table t1 with indexes ix1 and ix2

tx1:
- update t1 via index ix1, some records in ix1 get locked

tx2:
- update t1 via index ix2, some records in ix2 get locked

tx1:
- update t1 via index ix2, hits a lock set by tx2, waits

tx2:
- update t1 via index ix1, hits a lock set by tx1, deadlock!


> I am fairly new to MySQL/innodb. I've been and Oracle DBA for a long
> time though. I am seeing deadlocks on one application running on mysql
> every few minutes. The two transactions in the deadlock section of
> 'show engine innodb status' are almost always two DELETE's trying to
> delete the same row. I've never seen anything like this in Oracle and
> I'm wondering if it has to do with next key locking and the default
> isolation level difference between Oracle and MySQL. MySQL uses
> repeatable read whereas Oracle used read committed.

Could all be. But I don't think we should go into details here. My boss
would not like if I do for free what I normally do for money.

Buy a support contract and meet me at support.mysql.com ;-)


XL
--
Axel Schwenke, MySQL Senior Support Engineer, Sun Microsystems

MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:

> Buy a support contract and meet me at support.mysql.com ;-)


Funny you should mention that. I've been pressing my boss, and he's
pressing his, to pony up for a support contract. :)

I suspect the deadlock may be what you suggested. The two transactions
in the deadlock are always deleting from the same table and often
running the same sql.

delete from table where keyvalue = 'x';

Sometimes the 2nd TX is running something like this.

delete from table where expiretime < now();

Keyvalue has a unique key index on it and expiretime has a nonunique index.

Chuck wrote:
> I'm wondering if it has to do with next key locking and the default
> isolation level difference between Oracle and MySQL. MySQL uses
> repeatable read whereas Oracle used read committed.

InnoDB offers all four transaction isolation levels
described by the SQL standard. The level can be set
globally or for a single session. See:
http://dev.mysql.com/doc/refman/5.0/...isolation.html


--
Charles Polisher

Charles Polisher wrote:
> Chuck wrote:
>> I'm wondering if it has to do with next key locking and the default
>> isolation level difference between Oracle and MySQL. MySQL uses
>> repeatable read whereas Oracle used read committed.
>
> InnoDB offers all four transaction isolation levels
> described by the SQL standard. The level can be set
> globally or for a single session. See:
> http://dev.mysql.com/doc/refman/5.0/...isolation.html
>
>

Yes but the locking method employed by InnoDB appears to be able to
introduce deadlocks on it's own regardless of the isolation level chosen.