I have a multi-threaded environment (clustered, so even multi-JVMs) with lots of threads quering a single db-table at the same time. All these threads are doing the exact same query, looking for an arbitrary entity with the state 'Available'. If a thread finds an 'Available' entity, it changes its state to 'Blocked' and dispatches it to the business-logic which does some subsequent work. This query for an 'Available' entity is a pessimistic-write-lock-query, thus the corresponding row in the db-table is locked for any other thread trying to access the same row simultaneously. All other threads will have to wait until the preceding thread releases the lock in the database.
Now my question is: does a thread that waits for a lock to be released on a certain entity receive this specific entity after the lock is released in any case? Even if the state of the entity does no longer meet the initial query criteria? In my scenario above, if a second thread queries for some entity with the state 'available' and finds an entity which is currently (write) locked by a first thread, does it mean the second thread will obtain this entity after its release in any case, even if the first thread changes its state to 'Blocked' in the meanwhile just before releasing the lock?
With pessimistic locking:
In your scenario, query 1 gets a row that has 'Available' status and then updates the status to 'Blocked'. If it does not commit (or rollback) this update, then when other queries searching for 'Available' rows try to read this row, they will wait –– they can't read the value of the row while it's locked.
If query 1 commits the update (i.e. the status is now 'Blocked'), then the other queries will will not have get the row.
If query 1 performs a rollback (the status of the row is reverted to 'Available'), then the other queries will get the row in their resultset.
To achieve the best concurrency in this situation, make sure the transaction performs this update as short as possible (i.e. separate the business logic that does the subsequent work into a separate transaction). When the business logic completes, then make a decision on its success/failure and then update the row in question's status again.