Geek Answers Handbook

How does SQL Server Lock work in this scenario?

Note 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 starts on 2 threads, in order:

  • stream 1 - select
  • stream 2 - select
  • stream 1 - update
  • stream 2 - update

Assume that before execution the sum is 0.

What will happen in this case in different SQL Server settings (read unlimited, read, read, repeatable read, serializable), what will happen at the end, will there be a dead end?

+4
source share
5 answers

Well-known script. I decided to check it out.

Here is 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 is 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 this test script into two windows of the query analyzer and completed each part as described in the question.

All readings occur before any writing, so all threads / scripts will read the value 0 in @amount.

Here are the results:

Reading completed

 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

Repeat reading

 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

It explains each type that can be used to achieve these results through modeling thinking.

Read Committed and Read Uncommined , both do not block data that has been read with changes by other users. The difference is that reading uncommitted will allow you to see data that has not yet been committed (minus), and will not block your reading if there is data blocked by others against reading (up), which really says the same thing twice.

Repeatable reading and Serializable , both behave as reads committed to reading. To lock, lock data that has been read against modification by other users. The difference is that the serializable blocks are larger than the read line, it also blocks inserts that will enter records that were not previously.

Thus, with repeated reading, you can see new records (called phantom records) in subsequent readings. With serializable, you block the creation of these records until commit.

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

+2
source

Others have already addressed the issue of using REPEATABLE READ.

So, I'll call you back with another piece of advice ...

Why use two operators, not just one operator like the following?

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

Also, your real transactions go away from something more than UserID, right? If not, you run the risk of working with more records than originally intended.

+2
source

Yes, you probably want to repeat the reading.

I would probably deal with this with an optimistic lock in which you only update if the existing value is the same as when reading (test-and-set). If the value is not 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
+1
source

Otherwise, you can use the hint to block to avoid deadlocks (if you have a server in read 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 particular procedure, of course, one statement (for example, Kevin Fairchild published) is preferable and does not cause side effects, but in more complex situations, the UPDLOCK prompt may become convenient.

+1
source

I believe that you want to use re-reading, which locks the records, the first choice will get a value, then it will update the thread lock until it is complete. So the final result is 200 in your example

Reading uncommitted will cause both entries to set to 100.

Reading completed may have a slightly interesting result, depending on the time of the two threads ....

Here is a good article I found about Repeatable Read that gives a good example

0
source

More articles:


All Articles