How to select only unlocked rows
Posted on 2006-07-07
I have a long-running transaction that runs concurrently from multiple sessions. Each transaction needs to update a randomly selected row from a table of 100 rows. It needs to do this without waiting for the other transactions to release their update locks. In other words, it needs to randomly select for update any of the rows which is not locked for update by another transaction. If all the rows are locked, it's ok to fail with a resource busy error, however, if any row is not locked, then I want to select one of them for update (don't care which one).
As it stands now, the application does a "Select random_row for update NOWAIT", but the incidence of resource busy errors has become unacceptable. I can remove the NOWAIT, but then the delay waiting for resources to free up is unacceptable.