> are keep on getting deadlock due to index locking, there is index on
> FLAG, we can allow phantom read in session 1, we tried with READ
> COMMITTED but still same, I think issue with next-key locking.
Did you try setting binlog-format=ROW as well?
I have a brief explanation of this here under 'Write scalability of
> If i do following in SESSION 1 would that help in locking or still it would lock
> index. Any suggestion.
> SESSION 1:
> START TRANSACTION;
> UPDATE XYZ SET FLAG=0,ID=(@oID:=ID) ORDER BY TIME LIMIT 1;
> SELECT @oID AS ID;
Locks are held for the duration of the transaction, so I don't think it
will help here.