List:General Discussion« Previous MessageNext Message »
From:Morgan Tocker Date:January 29 2014 3:17pm
Subject:Re: Index Query Tunning
View as plain text  
Hi Anupam,

> We
>   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 
certain statements':

http://www.tocker.ca/2013/09/04/row-based-replication.html

> 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;
> COMMIT;

Locks are held for the duration of the transaction, so I don't think it 
will help here.

- Morgan

Thread
Index Query TunningAnupam Karmarkar29 Jan 2014
  • Re: Index Query TunningMorgan Tocker29 Jan 2014