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;
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!