Is a single SQL Server statement atomic and consistent?

by

I’ve been operating under the assumption that a single statement in SQL Server is consistent

That assumption is wrong. The following two transactions have identical locking semantics:

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

No difference at all. Single statements and auto-commits do not change anything.

So merging all logic into one statement does not help (if it does, it was by accident because the plan changed).

Let’s fix the problem at hand. SERIALIZABLE will fix the inconsistency you are seeing because it guarantees that your transactions behave as if they executed single-threadedly. Equivalently, they behave as if they executed instantly.

You will be getting deadlocks. If you are ok with a retry loop, you’re done at this point.

If you want to invest more time, apply locking hints to force exclusive access to the relevant data:

UPDATE Gifts  -- U-locked anyway
SET GivenAway = 1
WHERE GiftID = (
   SELECT TOP 1 GiftID
   FROM Gifts WITH (UPDLOCK, HOLDLOCK) --this normally just S-locks.
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)

You will now see reduced concurrency. That might be totally fine depending on your load.

The very nature of your problem makes achieving concurrency hard. If you require a solution for that we’d need to apply more invasive techniques.

You can simplify the UPDATE a bit:

WITH g AS (
   SELECT TOP 1 Gifts.*
   FROM Gifts
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)
UPDATE g  -- U-locked anyway
SET GivenAway = 1

This gets rid of one unnecessary join.

More Related Contents:

  1. Create a view with ORDER BY clause
  2. How to run the same query on all the databases on an instance?
  3. SQL Update with row_number()
  4. How to write UTF-8 characters using bulk insert in SQL Server?
  5. Connecting to MS SQL Server with Windows Authentication using Python?
  6. determining the character set of a table / database?
  7. How to query for Xml values and attributes from table in SQL Server?
  8. A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
  9. Should every User Table have a Clustered Index?
  10. cross apply xml query performs exponentially worse as xml document grows
  11. SQL Server Configuration Manager cannot be found
  12. Compute MD5 hash of a UTF8 string
  13. Sql Server replication requires the actual server name to make a connection to the server
  14. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  15. Convert from DateTime to INT
  16. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  17. SQL Server 2008 Spatial: find a point in polygon
  18. SQL Server – error when creating stored procedure
  19. Are GUID collisions possible?
  20. SQL Server Escape an Underscore
  21. Does a foreign key automatically create an index?
  22. best way to convert and validate a date string
  23. Why use the INCLUDE clause when creating an index?
  24. How can I make HTTP request from SQL server?
  25. SQL Server Management Studio alternatives to browse/edit tables and run queries [closed]
  26. Format SQL in SQL Server Management Studio
  27. SQL Server, the misleading XLOCK & optimizations
  28. Grant Select on a view not base table when base table is in a different database
  29. Update SQL with consecutive numbering
  30. Nested transactions in Sql Server

Leave a Comment