Prevent Race Conditions by Locking a Row in Oracle
Several times we may need to make a lock/release at a database row level. Let me expose a simple situation so you can then relate it to your particular problem in our web service or software in general.
Suppose we have a web service and one of our features lets our subscribers buy Pay Per View (PPV) events. Then suppose our subscriber John has enough credit to buy only one PPV event. If our web service receives two petitions to buy 2 PPV events from subscriber John and the petitions are so close that the second one comes before the first one ends, we’ll have a problem, because both purchases will be accepted by our web service.
It becomes very clear we need to hold the second petition until the first one ends. Think also that we may act on John’s account from different services that don’t communicate with each other, so a solution at an application code level may not be a solution.
Fortunately, ORACLE gives us a solution. The SELECT FOR UPDATE sentence lets us make a lock/release of a table row. Let’s see an example of usage:
SELECT subscribers WHERE id = 23 FOR UPDATE WAIT 30
This query makes a lock of the registry with id = 23 in table ‘subscribers’. This lock has a timeout of 30 seconds but it can be released before that by committing or rollbacking the transaction.
Let’s see now in pseudo code an example of usage in our situation.
// lock the particular subscriber
LOCK = SELECT subscriber WHERE id = 23 WAIT 30 FOR UPDATE
if (LOCK) then
…
#all necessary code to add PPV event to the subscriber
…
#releasing the subscriber
commit;
end if
When the first petition of purchase arrives from our subscriber, the row on the table is locked. The second petition will also try to lock the row, but as it is already locked, the query will remain in a status of hold until the row is released. If the 30-second timeout is over, and the first petition has not released the row, ORACLE returns an error to all the sessions waiting for the release.
In this manner we can synchronize our services and forget all the race conditions even if our services are consumed by different systems acting on the same set of subscribers.
As a final comment, there are other interesting uses of the Oracle SELECT FOR UPDATE. There is a very useful way of using it when we have a queue of tasks to do (stored in a database table) and we have many workers popping them with a query. As we don’t want two workers to process the same row, we can use something like this:
First select some unconsumed tasks.
SELECT id FROM transaction_queue WHERE status = ‘ready’ AND rownum <= 10
Suppose the result was 15, 16, 17, … 24. Then we are going to try to lock one of the rows retrieved and the first one that provides the row that we are querying will be our available row to process.
SELECT * FROM transaction_queue WHERE id = 15 FOR UPDATE SKIP LOCKED
Response: empty, it means the row was already locked by other worker, then continue…
SELECT * FROM transaction_queue WHERE id = 16 FOR UPDATE SKIP LOCKED
Response: empty, it means the row was already locked by other worker, then continue…
SELECT * FROM transaction_queue WHERE id = 17 FOR UPDATE SKIP LOCKED
Response: empty, it means the row was already locked by other worker, then continue…
SELECT * FROM transaction_queue WHERE id = 18 FOR UPDATE SKIP LOCKED
Response: the row, so we just locked the row and are able to process the task
Then the row 18 is the one we process.
In the first query, you need to select a number of tasks which are larger than the active workers available in your pool. This is to prevent starvation of a worker when all the retrieved tasks are locked by other processes. We can decrease the number of queries to make by not trying to lock the rows in order but randomly.
This is a very simple way of synchronizing our workers and preventing collisions among themselves!
This two were very simple approaches of the use of the Oracle SELECT FOR UPDATE feature and deserves a more extended explanation. Maybe in another opportunity we can give it a little more time.
If you need further information, please contact me at lucas.veljacic@intraway.com
