List:General Discussion« Previous MessageNext Message »
From:Anupam Karmarkar Date:January 29 2014 3:03pm
Subject:Index Query Tunning
View as plain text  
Hi All,

I have situation here about Innodb locking.
In  transaction, We select from XYZ transaction table values and then updates it
like below

SESSION 1:

START TRANSACTION;
SELECT ID INTO vID FROM XYZ WHERE FLAG = 1 ORDER BY TIME LIMIT 1 FOR UPDATE;
UPDATE XYZ SET FLAG=0 WHERE ID = vID;
COMMIT;

SESSION 2:

UPDATE XYZ SET FLAG=1 WHERE ID = 2;


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.

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;

--Anupam

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