Select only unlocked rows mysql
mysql, transactions
Solution
MySQL does not have a way to ignore locked rows in a SELECT. You'll have to find a different way to set a row aside as "already processed".
The simplest way is to lock the row briefly in the first query just to mark it as "already processed", then unlock it and lock it again for the rest of the processing - the second query will wait for the short "marker" query to complete, and you can add an explicit WHERE condition to ignore already-marked rows. If you don't want to rely on the first operation being able to complete successfully, you may need to add a bit more complexity with timestamps and such to clean up after those failed operations.
Problem
I have locked one row in one transaction by following query ``` START TRANSACTION; SELECT id FROM children WHERE id=100 FOR UPDATE; ``` And in another transaction i have a query as below ``` START TRANSACTION; SELECT id FROM children WHERE id IN (98,99,100) FOR UPDATE; ``` It gives error lock wait timeout exceeded. Here 100 is already locked (in first transaction ) But the ids 98,99 are not locked.Is there any possibility return records of 98,99 if only 100 is row locked in above query.So result should be as below Id === 98 99 === Id 100 should be ignored because 100 is locked by a transaction.