Re: count concurrent locking transactions ?

[James Day]

You could try using SHOW INNODB STATUS or SHOW PROCESSLIST.

Consider using GET_LOCK calls around the transaction to serialise them. Possibly multiple different lock names if the transactions can operate on clearly different parts of the table and won't conflict when doing so.

You might also consider settting the transaction isolation mode to read uncommitted and using select count(*) whatever to ensure that the needed indexes and rows are in the InnoDB buffer pool before you go back to your normal transaction isolation level and carry out the main transaction and possible get_lock. This can decrease the total duration of the lock and hence the time interval during which a deadlock is possible.

If you do use get_lock, be sure to use timeouts in a sensibly short range - seconds rather than minutes or hours - and retry as necessary. You wouldn't want a crashed client to lock others out of the database for a long time.