Re: hard to test select for update scenario

[Rick James]

> and the first completes in 20 milliseconds, the second will block only for 20 milliseconds, and never(!) deadlock.

Is 20ms too long?

> Your "probably" worries me.

Sorry, I don't know enough about the internals of InnoDB to be more certain.

> blockage only on the numbered lock items being discussed, and never deadlock??

Watch out for "gap" locks. That can cause blockages and/or deadlocks on seemingly distinct values. (But the values are in the same 'gap'.)

a)b)c)d) -- A single statement with 100 values in an IN clause will run 10 times as fast as 100 separate statements in a single BEGIN...COMMIT transaction. Faster means less chance of colliding.

If you don't care whether the 100 UPDATEs are done as a single transaction, and if you don't care how long they take, then perhaps autocommit=ON plus 100 UPDATEs is the least likely to hit a deadlock.

I would do the UPDATE...WHERE...IN(:sortedlist), and be prepared to rerun the update if it hit a deadlock. It would be fastest (when there is no deadlock). It would recover from a deadlock that 'probably' would never occur. Speed. Correctness. The only downside is the code to handle the deadlock.