Re: Transaction deadlock with many threads

[Rick James]

Various comments...

* I can't explain the deadlock

* It is probably unrealistic to do the LIMIT 1 without an ORDER BY -- you could be getting a random row (but probably are getting the same row).

* If there is no useful index, then all the SELECTs will be table scans.

* How many rows?

* If you ran one copy of this 'transaction', how long would it take? I am (indirectly) asking if you have a sleep() or lengthy operation inside the lock/begin.

* Are you trying to launch all the threads at exactly the same time? (I don't mean in the same second, I mean in the same millisecond.) If so, this is "unrealistic".

* In normal situations, you can easily do 200 such operations per second if they are coming in 'randomly'.

* Please provide
SHOW CREATE TABLE
SHOW TABLE STATUS