You understand the locking logic correctly, however I suspect the nested transactions are causing your problem. (Without seeing your actual code it is hard to be sure, but the following is a likely explanation.)
If your first SELECT
statement in a transaction is not the locking SELECT ... FOR UPDATE
then you are going to have issues, given that the default transaction isolation level is READ-REPEATABLE.
Assuming that you do have an earlier SELECT
in your transaction, then the whole transaction is taking a snapshot of the database state at the moment, and negating the benefit of the locking.
In detail, here is what I think is causing your race conditions:
Step 1. Request1: SELECT
statement without FOR UPDATE
(snapshot is taken here)
Step 2. Request1: SELECT ... FOR UPDATE
starts a lock
Step 3. Request1: SELECT processed=false
Step 4. Request2: SELECT
statement without FOR UPDATE
(snapshot is taken here)
Step 5. Request2: SELECT ... FOR UPDATE
waits for Request1 to finish
Step 6. Request1: UPDATE processed=true
Step 7. Request1: COMMIT
Step 8. Request2: SELECT processed=false
(will see the database at it was at step 4, BEFORE the processed rows were changed, so the query will return the same rows as Request1 did)
Creating a SAVEPOINT
will not prevent this issue.
I wrote about this issue in the past on stackoverflow.
You psuedocode says you aren't doing that, but the comment about nested transactions suggests that you may already have an open transaction at the time your psuedocode starts, which is causing an "incorrect" snapshot to be taken for your needs.
My advice is the same as in my linked post: Make sure your transaction starts with the SELECT ... FOR UPDATE
statement. That means committing the earlier transaction, rather than using the nesting logic, as simply making a SAVEPOINT as Doctrine does won't suffice to make sure you are working from the correct snapshot of data.
If you are using nested transactions in an attempt to wrap an entire HTTP request in a transaction, and then using nested transactions deeper down in the request, then this is an expected problem, and I suggest not trying to wrap an entire request like that. (I only mention that because I used to naively think it was a good idea to wrap an entire request like that... and then discovered the hard way issues like I outlined in this answer.)