MySQL - Deadlock found when trying to get lock
<p>I have table <code>tt_users</code> which has <code>id</code> as primary key, and columns <code>state</code> (CHAR(1)), which can be "x" or "y", and <code>state_position</code> (INT). There are no indexes on these columns. Storage engine is innodb.</p>

<p>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</p>

<p>This is the query I'm running that causes deadlocks:</p>

<pre><code>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
)
</code></pre>

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

<p>I read this post - <a href="https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans">How to avoid mysql &#39;Deadlock found when trying to get lock; try restarting transaction&#39;</a> 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 - <a href="https://stackoverflow.com/questions/2596005/working-around-mysql-error-deadlock-found-when-trying-to-get-lock-try-restarti">Working around MySQL error &quot;Deadlock found when trying to get lock; try restarting transaction&quot;</a> - says deadlocks can happen no matter what.</p>

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

<pre><code>PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
</code></pre>

<p>Then locking the table manually, and running my query:</p>

<pre><code>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;
</code></pre>

<p>And then:</p>

<pre><code>PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
</code></pre>

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

<p>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?</p>

<p><strong>UPDATE:</strong></p>

<p>Per @wallyk's answer, I tried the following:</p>

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

<p>So for now it appears manually locking the table is the only thing that works.</p>