All geek questions in one place

Are update operators safe from race conditions?

I want to make sure that the message was sent exactly once, so I use the following statement in Oracle SQL:

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

and consider the number of rows changed. If no line has been changed, the other process will be the first to do the same and send the mail. If 1 line has been changed, I send mail. (Of course, if sending the mail failed, I reset mail_sent. There is a tiny chance of the process crashing and leaving mail_sent on 't', so no mail is sent. I will live with it.)

I can’t completely convince myself that it’s safe against the conditions of the race (process 1 reads “f” and process 2 reads “f” before process 1 wrote “t”, so both processes believe that they modified the line and 2 letters I set the isolation level SERIALIZABLE to avoid the problem, but is it really necessary, or am I safe without it?

+4
source share
3 answers

There is a collection of great articles by Tom Kyte about what happens during the side-by-side upgrade, which are worth reading:

In short, in the case of two statements performing parallel updates, the latter:

  • performs sequential reading (version of the line from the moment the instruction was run)
  • checks if the string matches the conditions of your update.
  • if so, he reads the current mode - receives the last completed version of the line - and checks if it remains the same line as in step 1 (!), so we do not update what we did not intend to
  • If this is not so, then the line is not updated, and the whole update instruction is restarted, but this whole story.

As a result, if your first update fixes “t,” the second update will never update this line again. You can check it with sql%rowcount .

A simple test case (36 and 37 are two simultaneous 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 if you check the number of rows updated by the session after the update, you are safe.

+6
source

One safe way to do this is to select an update line that takes an exceptional lock on the line, send an email, and then update the entry to "t" and commit.

Write lock is the intentional design goal of this method. Until the message is sent, you do not want to indicate that you sent it, otherwise you will need a recovery process to show that the transmission really failed. Similarly, when you started the email sending process, you do not want another session to start this process.

If you need to avoid longer blocking, I would suggest splitting the process into two stages - by setting a flag to confirm that the email transfer process has started (and actually I will mark this time), and set it again (or set another) for transfer confirmation. This is not a bad method in itself, since it allows you to track how long it took to get confirmation, and, in my experience, some internet requests can be a significant part of the application’s time.

+2
source

This seems to work 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
0
source

Source: https://habr.com/ru/post/1501884/

More articles:


All Articles