CODEWITHSUNDEEP
mysqldeadlock
GTCrais
GTCrais

Reputation: 2077

MySQL - Deadlock found when trying to get lock

I have table tt_users which has id as primary key, and columns state (CHAR(1)), which can be "x" or "y", and state_position (INT). There are no indexes on these columns. Storage engine is innodb.

state_positions must always be consecutive, and there may never be a duplicate position for a certain state, i.e. if I have 5 users with state 'x', their state_positions must be 1,2,3,4,5

This is the query I'm running that causes deadlocks:

insert into `tt_users` (`state`, `state_position`) 
values ('x',
   (SELECT MAX(state_position) AS maxStatePosition
    FROM tt_users AS u2
    WHERE u2.state='x') + 1
)

For testing, I inserted a large amount of users simultaneously, and each time I got the deadlock error.

I read this post - How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction' but I didn't understand what I should do with my query to prevent deadlocks, if that is at all possible, since the answer to this question - Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction" - says deadlocks can happen no matter what.

The only way I managed to get this to work, and work consistently, is this (language is PHP):

PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Then locking the table manually, and running my query:

SET autocommit=0;

LOCK TABLES
tt_users WRITE,
tt_users AS u2 WRITE;

insert into `tt_users` (`state`, `state_position`) 
values ('x',
   (SELECT MAX(state_position) AS maxStatePosition
    FROM tt_users AS u2
    WHERE u2.state='x') + 1
);

COMMIT;
UNLOCK TABLES;

And then:

PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

With that method I ran the code 4 times simultaneously, each instance inserting 2500 users, without problems.

Is that the only way to get this to work, or can I with 100% certainty prevent deadlocks without having to manually lock the table?

UPDATE:

Per @wallyk's answer, I tried the following:

1) Putting the query in a transaction - still deadlock error
2) Starting the transaction with WITH CONSISTENT SNAPSHOT, READ WRITE, READ ONLY. All 3 options required PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true); to be set. WITH CONSISTENT SNAPSHOT and READ WRITE still gave me deadlock error, while READ ONLY naturally didn't even allow me to execute the INSERT.

So for now it appears manually locking the table is the only thing that works.

Upvotes: 1

Views: 3106

Answers (1)

wallyk
wallyk

Reputation: 57764

I don't know if it will help, but surrounding the SQL statement with a transaction may provide the correct locking sequence internally:

start transaction;
insert into `tt_users` (`state`, `state_position`) 
values ('x',
   (SELECT MAX(state_position) AS maxStatePosition
    FROM tt_users AS u2
    WHERE u2.state='x') + 1
);
commit;

If that doesn't work, there are a few options which can be added to the start transaction statement. See here.

Upvotes: 1

Related Questions