How does SQL Server locking work in this scenario?

Question

Consider that I have a transaction:

BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
  FROM Deposits
  WHERE UserId = 123
UPDATE Deposits
  SET Amount = @amount + 100.0
  WHERE UserId = 123
COMMIT

And it gets executed on 2 threads, in the order:

1. thread 1 - select
2. thread 2 - select
3. thread 1 - update
4. thread 2 - update

Assume that before execution Amount is 0.

What will happen in this case in the different settings of SQL Server (read uncommited, read commited, repeatable read, serializable), what will be amount at the end, will there be a deadlock?

Answer 1

I believe that you would want to use Repeatable read, which would lock the records, the first select would get the value, then it would update blocking thread two until it was completed. Thus an end result of 200 in your example

Read uncommitted would result in both records setting the value to 100.

Read committed might have a bit of an intersting result, depending on timing of the two threads....

Here is a nice article I found about Repeatable Read as well, that provides a good example

Answer 2

Yes, you probably want repeatable read.

I'd probably handle this via optimistic locking wherein you only update if the existing value is the same as it was when you read (test-and-set). If the value isn't the same, raise an error. This allows you to run read-uncommitted, without deadlocks, and without data corruption.

BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
  FROM Deposits
  WHERE UserId = 123
UPDATE Deposits
  SET Amount = @amount + 100.0
  WHERE UserId = 123 AND Amount = @amount
IF @@ROWCOUNT <> 1 BEGIN ROLLBACK; RAISERROR(...) END
ELSE COMMIT END

Answer 3

Others already addressed the issue of using REPEATABLE READ.

So I'll chime in with a different piece of advice...

Why use two statements and not just one statement like the following?

UPDATE Deposits
SET Amount = Amount + 100.0
WHERE UserId = 123

Also, your real transactions are going off of something more than a UserID, right? If not, you run the risk of working with more records than you originally intended.

Answer 4

Nice well stated scenario. I decided to test it.

Here's my setup script:

CREATE TABLE Deposits(Amount Money, UserID int)
INSERT INTO Deposits (Amount, UserID)
SELECT 0.0, 123
--Reset
UPDATE Deposits
SET Amount = 0.00
WHERE UserID = 123

Here's my test script.

SET TRANSACTION ISOLATION LEVEL Serializable
----------------------------------------
-- Part 1
----------------------------------------
BEGIN TRANSACTION
DECLARE @amount MONEY
SET @amount =
(
SELECT Amount
FROM Deposits
WHERE UserId = 123
)
SELECT @amount as Amount
----------------------------------------
-- Part 2
----------------------------------------
DECLARE @amount MONEY
SET @amount =  *value from step 1*
UPDATE Deposits
SET Amount = @amount + 100.0
WHERE UserId = 123
COMMIT
SELECT *
FROM Deposits
WHERE UserID = 123

I loaded up this test script in two query analyzer windows and ran each part as described by the question.

All of the reading happens before any of the writing, so all threads/scenarios will read the value of 0 into @amount.

Here are the results:

Read committed

1 T1.@Amount = 0.00
2 T1.@Amount = 0.00
3 Deposits.Amount = 100.00
4 Deposits.Amount = 100.00

Read uncommitted

1 T1.@Amount = 0.00
2 T1.@Amount = 0.00
3 Deposits.Amount = 100.00
4 Deposits.Amount = 100.00

Repeatable Read

1 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
2 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
3 Hangs until step 4. (due to lock in step 2)
4 Deadlock!
Final result: Deposits.Amount = 100.00

Serializable

1 T1.@Amount = 0.00 (locks out changes by others on Deposit)
2 T1.@Amount = 0.00 (locks out changes by others on Deposit)
3 Hangs until step 4. (due to lock in step 2)
4 Deadlock!
Final result: Deposits.Amount = 100.00

Here's an explanation of each type which can be used to reach these results through thought simulations.

Read Committed and Read Uncommited, both do not lock the data that was read against modifications by other users. The difference is that read uncommitted will allow you to see data that is not yet committed (downside) and will not block your read if there is data locked by others against reading (upside), which is really saying the same thing twice.

Repeatable Read and Serializable, both behave like read committed for reading. For locking, both lock data which has been read against modification by other users. The difference is that serializable blocks more than the row which has been read, it also blocks inserts that would introduce records that were not present before.

So with repeatable read, you could see new records (termed : phantom records) in later reads. With serializable, you block the creation of those records until you commit.

The above explanations come from my interpretation of this msdn article.

Answer 5

Otherwise you can use locking hint to avoid deadlocks (in case you have server in read commited mode):

BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
  FROM Deposits WITH(UPDLOCK)
  WHERE UserId = 123
UPDATE Deposits
  SET Amount = @amount + 100.0
  WHERE UserId = 123
COMMIT

In this specific procedure of course single statement (like Kevin Fairchild posted) is preferred and doesn't cause side effects, but in more complex situations UPDLOCK hint may become handy.