CODEWITHSUNDEEP
sqloracle-database
user1886876
user1886876

Reputation:

Are update statements safe from race conditions?

I want to make sure that an email is sent exactly once, so I'm using the following statement in Oracle SQL:

update mytable set mail_sent = 't' where id = ? and mail_sent = 'f'

and examining the number of rows modified. If no rows were modified, then another process did the same thing first and will send the mail. If 1 row was modified, I send the mail. (Of course, if sending the mail fails, I reset mail_sent. There's a tiny chance of the process crashing and leaving mail_sent at 't', so no mail is sent. I'll live with it.)

I can't quite convince myself this is safe against race conditions (Process 1 reads 'f' and process 2 reads 'f' before process 1 has written 't', so both processes think they modified the row and 2 emails are sent. I'm setting the isolation level to SERIALIZABLE to avoid the problem, but is this actually necessary, or am I safe without it?

Upvotes: 7

Views: 2822

Answers (3)

David Aldridge
David Aldridge

Reputation: 52336

One safe way to do this is to select the row for update, which takes an exclusive lock on the row, send the email, then update the record to 't' and commit.

The locking of the record is a deliberate design aim of this method. Until the email is confirmed to be sent you do not want to indicate that you have sent it, otherwise you need a recovery process to indicate that transmission actually failed. Similarly when you have started the process of sending an email you do not want another session to start that process.

If it is necessary to avoid a longer term lock then I'd suggest breaking out the process into two steps -- setting a flag to confirm that the email transmission process has started (and actually I'd timestamp that), and setting it again (or setting another) to confirm transmission. That's not a bad method in itself, as it allows monitoring of how long it took to get the confirmation, and in my experience some internet requests can be a significant proportion of application time.

Upvotes: 2

Kirill Leontev
Kirill Leontev

Reputation: 10931

There's a set of Tom Kyte's excellent articles about what happens during a concurrent update, which are worth reading:

Long story short, in case of two statements doing a concurrent update, the latter one:

  1. performs a consistent read (a row's version as of moment of a statement start)
  2. checks if the row fits the where condition of your update
  3. if it does, it does current mode read - gets the latest committed version of the row - and checks if it's still the same row as in step 1(!), so we're no updating something we didn't intend to
  4. If it is not, then the row doesn't get updated and the whole update statement gets restarted, but that's whole other story.

As a result, in case your first update commits 't', the second update will never update this row again. You can check it with sql%rowcount.

A simple test case (36 and 37 are two concurrent sessions here):

-- first session updates, locks the row
00:41:44 LKU@sandbox(36)> update mail set mail_sent = 't' where id = 1 and mail_sent = 'f';

1 row updated.

Elapsed: 00:00:00.21

-- second session tries to update the same row, it hangs as the row is locked
00:58:13 LKU@sandbox(37)> update mail set mail_sent = 't' where id = 1 and mail_sent = 'f';

-- first session commits
00:58:27 LKU@sandbox(36)> commit;

Commit complete.

Elapsed: 00:00:00.00

-- no rows updated in second!
00:58:13 LKU@sandbox(37)> update mail set mail_sent = 't' where id = 1 and mail_sent = 'f';

0 rows updated.

Elapsed: 00:00:33.12 -- time of me switching between sqlplus tabs and copy-pasting text here ;)

So, I can conclude that in case you check the amount of rows updated by a session after you perform the update - you are safe.

Upvotes: 6

Richard Newman
Richard Newman

Reputation: 630

This sounds like a job for transactions.

BEGIN TRANSACTION

UPDATE mytable 
SET mail_sent = 't' 
WHERE id = @id 
AND mail_sent = 'f'

send the email

IF (@emailSent = 0)
    ROLLBACK TRANSACTION
    RAISERROR('Email not sent', 1, 16);
ELSE
    COMMIT TRANSACTION

Upvotes: 0

Related Questions