0

I have a situation where I need to:

  1. Read the value of a row.
  2. If the value of some column is 'X', perform action A. Otherwise, perform action B.
  3. If we performed action A, update the column with the result of the action.

Action A is not a database operation, and may take a while to run, and it is not reversible. Action B is not a database operation, but is very fast to run. The sequence is performed on multiple threads, across multiple servers.

Currently we have no locking, and so occasionally we see action A being executed multiple times, when it should only happen once. I think my only solution here is to somehow wrap the sequence above with an acquire lock step and a release lock step, and I'm not sure how to do that.

I have seen a similar question, where the answer was to add 'locked' and 'acquiry time' columns to the row. However, in that situation, the OP wasn't worried about frequently re-acquiring the lock. If I had to spin-wait for the previous lock to expire every time I wanted to execute the sequence, my server's performance would probably go out the window.

Is there something built in to SQL that I can use here?

Andrew Williamson
  • 8,299
  • 3
  • 34
  • 62

1 Answers1

5

Update the "X" value to "pending".

On completion of action A, update "pending" to whatever.

No locking required.

tpdi
  • 34,554
  • 11
  • 80
  • 120
  • Is this not still going to have a chance that two threads query the row at the same time, and so both threads see the row as 'X', and both attempt to update it to pending? – Andrew Williamson Jun 20 '17 at 22:57
  • @AndrewWilliamson: If you did a select to the middle tier and and then an update. But if if you just do an update, that should be atomic (unless you're playing SET ISOLATION_LEVEL games). – tpdi Jun 26 '17 at 18:57