Coupon Code: santa14
Hurry up, offer expires soon!
Blog

A deadlock I do not understand.

I am not an expert in InnoDB internals and have only little experience with using transactions actually. But I have started learning basics and understanding SHOW ENGINE INNODB STATUS (and the Information_Schema tables exposing same information in later versions). I stumbled across a different behavior between MySQL 5.0 and later and also find other transactional engines exposing yet another behavior. This is related to SELECT .. LOCK IN SHARE MODE primarily. But let us take SELECT .. FOR UPDATE as well for completness. Case(s) 1 below is about SELECT .. FOR UPDATE and case(s) 2 is about SELECT .. LOCK IN SHARE MODE.

– Case 1a (all InndoDB/XtraDB versions + PBXT)

– From connection 1: Execute the following
USE test;
CREATE TABLE blah(a INT PRIMARY KEY) ENGINE=INNODB;
INSERT INTO blah(a) VALUES(1);
INSERT INTO blah(a) VALUES(0);
START TRANSACTION;
SELECT * FROM blah FOR UPDATE;

– From connection 2: Execute the following
USE test;
DELETE FROM blah;

– from connection 1:
DELETE FROM blah; — 2 row(s) affected

– connection 2 still waiting for commit/rollback/lock_wait_timeout as expected

– case 1 behave identically with all server versions and storage engines supporting transactions (with the exception of ‘Aria’ engine – see case 1b).

– —————————————————————————————————————–

– Case 1b (Aria engine as of MariaDB 5.2.2)

– From connection 1: Execute the following
USE test;
CREATE TABLE blah(a INT PRIMARY KEY) ENGINE=ARIA TRANSACTIONAL = 1;
INSERT INTO blah(a) VALUES(1);
INSERT INTO blah(a) VALUES(0);
START TRANSACTION;
SELECT * FROM blah FOR UPDATE;

– From connection 2: Execute the following
USE test;
DELETE FROM blah; — 2 rows affected so no LOCK on table

– from connection 1:
DELETE FROM blah; — 0 row(s) affected

– —————————————————————————————————————–

– Case 2a – MySQL servers/InnoDB 5.1.51 and 5.5.6 as well as XtraDB as of MariaDB 5.2.2:
– From connection 1:
USE test;
CREATE TABLE blah(a INT PRIMARY KEY) ENGINE=INNODB;
INSERT INTO blah(a) VALUES(1);
INSERT INTO blah(a) VALUES(0);
START TRANSACTION;
SELECT * FROM blah LOCK IN SHARE MODE;

– from connection 2:
USE test;
DELETE FROM blah; — waiting

– from connection 1:
DELETE FROM blah; — 2 rows affected

– connection 2 now instantaneously pops up message
/*
Error Code : 1213
Deadlock found when trying to get lock; try restarting transaction
*/

– —————————————————————————————————————–

– Case 2b – server 5.0.90:
– From connection 1:
USE test;
CREATE TABLE blah(a INT PRIMARY KEY) ENGINE=INNODB;
INSERT INTO blah(a) VALUES(1);
INSERT INTO blah(a) VALUES(0);
START TRANSACTION;
SELECT * FROM blah LOCK IN SHARE MODE;

– from connection 2:
USE test;
DELETE FROM blah;

– from connection 1:
DELETE FROM blah; — 2 rows affected

– connection 1 now pops up message
/*
Error Code : 1213
Deadlock found when trying to get lock; try restarting transaction
*/

– —————————————————————————————————————–

– Case 2c – MariaDB 5.5.2 with PBXT and ARIA engines (they behave the same here)

– From connection 1:
USE test;
CREATE TABLE blah(a INT PRIMARY KEY) ENGINE=INNODB; — ARIA is the same
INSERT INTO blah(a) VALUES(1);
INSERT INTO blah(a) VALUES(0);
START TRANSACTION;
SELECT * FROM blah LOCK IN SHARE MODE;

– from connection 2:
USE test;
DELETE FROM blah; — 2 rows affected, so no LOCK on table

– from connection 1:
DELETE FROM blah; — 0 rows affected

What I do not understand is:

1) What is the explanation of this different server (ie. InnoDB) behaviour between 5.0 and 5.1? Is it a plain bug in MySQL/InnoDB 5.0? Or is it happening due to different (default – I have not changed InnoDB defaults with this example) configuration settings?

2) Why cannot connection 2 in case 2a simply wait and execute DELETE (what will then be against an empty table in this case) once connection 1 has committed and why will there need to be a deadlock with this case? The deadlock prompts instantaneously after the other connection has executed DELETE (and not committed). Bad timing? Server/storage engine interface problem? Or only a Peter-problem? :-)

Documentation at

http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

and

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

.. are not identical (but ‘content-wise’ I do not see much difference between them, so this does not help me).

It further looks like PBXT does not handle SELECT .. LOCK IN SHARE MODE (but SELECT .. FOR UPDATE seems to work fine and identically to InnoDB). I do not know if PBXT is supposed to handle SELECT .. LOCK IN SHARE MODE – now or one day in the future (but if not I’d prefer a plain error returned!). Also I am not sure what the tested version of the ARIA engine is supposed to do in its current stage (it is documented that “TRANSACTIONAL = 1″ does not mean it has full transactions support but it should ‘gain’ it over time, I understand). With ARIA neither SELECT .. LOCK IN SHARE MODE nor SELECT .. FOR UPDATE seem to have any effect even if table is declared TRANSACTIONAL = 1.

So trying neither PBXT nor ARIA helped me to understand the difference between MySQL 5.0 and later versions with SELECT …LOCK IN SHARE MODE . And FALCON I do not find it interesting to spend time with now.

5 thoughts on “A deadlock I do not understand.

  1. The reason you get a deadlock is because InnoDB doesn’t do lock escalation or priorities.

    So in your first transaction you get a shared lock a row. Then in the second you get an exclusive lock which is now pending. The first then tries to get an exclusive lock which has to wait on the pending lock in the second transaction. This is an A waiting on B waiting on A situation which is a deadlock.

    When a deadlock occurs, InnoDB needs to decide which one to kill. It uses the amount of locks held to be an indicator of how much ‘work’ a transaction has done and tries to kill those with less locks.

    As far as why it changed, I believe a change was made to count only X locks and not S locks. I could be completely making this up, but I seem to remember something like that being changed.

  2. Hi Peter,

    Some good analysis done here.

    You are right. PBXT does not support SELECT .. LOCK IN SHARE MODE, and it is currently not planned. The reason is: for the most part, when you lock, you need an UPDATE lock.

    The shared lock is usually not required for just reading because MVCC provides consistent lock-less reading.

    But, you are right. I should probably return an error.

    Another reason why the SHARED LOCK is not much good you demonstrated in 2a above. This is the correct behavior in this situation and looks like a bug fix (the 5.0 implementation is wrong).

    This is a variation of the problem called “lock escalation”, and it always leads to a deadlock. There is actually nothing the engine can do about it.

    Lock escalation deadlock generally goes like this:

    T1: SHARED LOCK x – GRANTED
    T2: SHARED LOCK x – GRANTED
    T1: UPDATE LOCK x – HANG (because T2 has a SHARED LOCK)
    T2: UPDATE LOCK x – DEADLOCK

    In other words of 2 transaction both first SHARE LOCK and then UPDATE lock the same resource then they will hit a deadlock (because each thread is holding a resource that the other thread needs). A deadlock occurs when transactions are waiting for each other.

    So in general. It is not a good idea to first SHARE LOCK a resource which you later plan to UPDATE. This will always lead to a deadlock. So this means that a SELECT .. FOR UPDATE must be used.

    Which is why I think that SELECT .. LOCK IN SHARE MODE is not much use. It does make sense, however, if the engine does not support MVCC. Because then read locking is the only way to get a repeatable read.

  3. Thanks for comments and explanations.

    I realize that an UPDATE LOCK is the right thing to do if you want to UPDATE (+ DELETE FROM + INSERT INTO) a table with no FOREIGN KEYs. But as I understand the InnoDB documentation LOCK IN SHARE MODE is still relevant with tables having FOREIGN KEYS in order to protect a ‘parent’ table to the ‘child’ table you want to UPDATE/INSERT INTO from changes from other clients/transactions during first transaction’s ‘time scope’.

    I think I will have to experiment more to understand this!

  4. Hi,

    The reason for the difference between case 2a and 2b is due to the fix for
    this http://bugs.mysql.com/bug.php?id=49047. Harrison filed and wrote
    a patch for this bug too :-).

    The difference is due to to change in the way we traverse the lock list, prior
    to the patch we traversed the lock list backwards and with the fix we now
    traverse the lock list forward.

    I confirmed this by reverting the patch.

    Regards,
    -sunny

  5. Not an expert on InnoDB internals? You better become one quickly as Oracle has (finally!) decided to set InnoDB as the default table engine in MySQL – yeeehaa!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>