code_junkie

How does SQL Server locking work in this scenario?

Nov 12th, 2011
57
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.03 KB | None | 0 0
  1. BEGIN TRANSACTION
  2. DECLARE MONEY @amount
  3. SELECT Amount AS @amount
  4. FROM Deposits
  5. WHERE UserId = 123
  6. UPDATE Deposits
  7. SET Amount = @amount + 100.0
  8. WHERE UserId = 123
  9. COMMIT
  10.  
  11. CREATE TABLE Deposits(Amount Money, UserID int)
  12. INSERT INTO Deposits (Amount, UserID)
  13. SELECT 0.0, 123
  14. --Reset
  15. UPDATE Deposits
  16. SET Amount = 0.00
  17. WHERE UserID = 123
  18.  
  19. SET TRANSACTION ISOLATION LEVEL Serializable
  20. ----------------------------------------
  21. -- Part 1
  22. ----------------------------------------
  23. BEGIN TRANSACTION
  24. DECLARE @amount MONEY
  25. SET @amount =
  26. (
  27. SELECT Amount
  28. FROM Deposits
  29. WHERE UserId = 123
  30. )
  31. SELECT @amount as Amount
  32. ----------------------------------------
  33. -- Part 2
  34. ----------------------------------------
  35. DECLARE @amount MONEY
  36. SET @amount = *value from step 1*
  37. UPDATE Deposits
  38. SET Amount = @amount + 100.0
  39. WHERE UserId = 123
  40. COMMIT
  41. SELECT *
  42. FROM Deposits
  43. WHERE UserID = 123
  44.  
  45. 1 T1.@Amount = 0.00
  46. 2 T1.@Amount = 0.00
  47. 3 Deposits.Amount = 100.00
  48. 4 Deposits.Amount = 100.00
  49.  
  50. 1 T1.@Amount = 0.00
  51. 2 T1.@Amount = 0.00
  52. 3 Deposits.Amount = 100.00
  53. 4 Deposits.Amount = 100.00
  54.  
  55. 1 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
  56. 2 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
  57. 3 Hangs until step 4. (due to lock in step 2)
  58. 4 Deadlock!
  59. Final result: Deposits.Amount = 100.00
  60.  
  61. 1 T1.@Amount = 0.00 (locks out changes by others on Deposit)
  62. 2 T1.@Amount = 0.00 (locks out changes by others on Deposit)
  63. 3 Hangs until step 4. (due to lock in step 2)
  64. 4 Deadlock!
  65. Final result: Deposits.Amount = 100.00
  66.  
  67. UPDATE Deposits
  68. SET Amount = Amount + 100.0
  69. WHERE UserId = 123
  70.  
  71. BEGIN TRANSACTION
  72. DECLARE MONEY @amount
  73. SELECT Amount AS @amount
  74. FROM Deposits
  75. WHERE UserId = 123
  76. UPDATE Deposits
  77. SET Amount = @amount + 100.0
  78. WHERE UserId = 123 AND Amount = @amount
  79. IF @@ROWCOUNT <> 1 BEGIN ROLLBACK; RAISERROR(...) END
  80. ELSE COMMIT END
  81.  
  82. BEGIN TRANSACTION
  83. DECLARE MONEY @amount
  84. SELECT Amount AS @amount
  85. FROM Deposits WITH(UPDLOCK)
  86. WHERE UserId = 123
  87. UPDATE Deposits
  88. SET Amount = @amount + 100.0
  89. WHERE UserId = 123
  90. COMMIT
Add Comment
Please, Sign In to add comment