Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
- 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
- 1 T1.@Amount = 0.00
- 2 T1.@Amount = 0.00
- 3 Deposits.Amount = 100.00
- 4 Deposits.Amount = 100.00
- 1 T1.@Amount = 0.00
- 2 T1.@Amount = 0.00
- 3 Deposits.Amount = 100.00
- 4 Deposits.Amount = 100.00
- 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
- 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
- UPDATE Deposits
- SET Amount = Amount + 100.0
- WHERE UserId = 123
- 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
- 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
Add Comment
Please, Sign In to add comment