CODEWITHSUNDEEP
sqlsql-servertransactions
nicomp
nicomp

Reputation: 4657

Why doesn't one transaction delay the other transaction?

I run this script:

BEGIN TRAN Tran1
DECLARE @HighBid AS Money
SET @HighBid = (SELECT CurrentHighBid FROM eBaySchema.tItem WHERE ItemID = 1)
SET @HighBid = @HighBid * 1.1
WAITFOR DELAY '0:00:30'
UPDATE eBaySchema.tItem SET HighBidderID=4, CurrentHighBid = @HighBid WHERE ItemID = 1
COMMIT TRAN Tran1

That will sleep for 30 seconds so it doesn't finish too quickly... Then I run immediately run this, which is the same logic and wants to update the same row in the same table...

BEGIN TRAN Tran1
DECLARE @HighBid AS Money
SET @HighBid = (SELECT CurrentHighBid FROM eBaySchema.tItem WHERE ItemID = 1)
SET @HighBid = @HighBid * 1.1
UPDATE eBaySchema.tItem SET HighBidderID=5, CurrentHighBid = @HighBid WHERE ItemID = 1
COMMIT TRAN Tran1

But the the second script shoots right through and doesn't wait for the first script to let go of the table in the UPDATE statement. What am I missing?

Upvotes: 1

Views: 182

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89424

While SERIALIZABLE or REPEATABLE READ will prevent the lost update, it will do it by causing a deadlock, which is pretty inconvenient. Under SERIALIZABLE both sessions will be able to run

SET @HighBid = (SELECT CurrentHighBid FROM eBaySchema.tItem with (UPDLOCK) WHERE ItemID = 1)

But will hold the Shared lock, and then neither will be able to get the Exclusive lock necessary to update the row, which will result in a deadlock, which SQL Server will resolve by failing one sessions.

To block one transaction until the other commits either use sp_getapplock as @RossBush suggests, or use the UPDLOCK table hint.

BEGIN TRAN Tran1
DECLARE @HighBid AS Money
SET @HighBid = (SELECT CurrentHighBid FROM eBaySchema.tItem with (UPDLOCK) WHERE ItemID = 1)
SET @HighBid = @HighBid * 1.1
UPDATE eBaySchema.tItem SET HighBidderID=5, CurrentHighBid = @HighBid WHERE ItemID = 1
COMMIT TRAN Tran1

Upvotes: 2

Ross Bush
Ross Bush

Reputation: 15185

If you would like to serialize access to an update then you can look into the lock hints posted in a comment above. That is probably the cleanest approach. If you need to define a wait time for your lock then you can create a "single point of truth" procedure that serialized access to a critical section with a configurable timeout. This still uses the internal locking mechanism much like -> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

CREATE MyCriticalUpdateProcedure()
AS
    DECLARE @LockRequestResult INT = 0
    DECLARE @MyTimeoutMiliseconds INT=5000--Wait only five seconds max then timeouit
    
    BEGIN TRAN
    EXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalUpdate','Exclusive','Transaction',@MyTimeoutMiliseconds
    IF(@LockRequestResult>=0)BEGIN
        --Critical Section
        DECLARE @HighBid AS Money
        SET @HighBid = (SELECT CurrentHighBid FROM eBaySchema.tItem WHERE ItemID = 1)
        SET @HighBid = @HighBid * 1.1
        UPDATE eBaySchema.tItem SET HighBidderID=5, CurrentHighBid = @HighBid WHERE ItemID = 1
        --Release the lock
        COMMIT TRAN
    END ELSE
    ROLLBACK TRAN

Use the currently configured Sql Server Timeout Settings

CREATE MyCriticalUpdateProcedure()
AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN
        --Critical Section
        DECLARE @HighBid AS Money
        SET @HighBid = (SELECT CurrentHighBid FROM eBaySchema.tItem WHERE ItemID = 1)
        SET @HighBid = @HighBid * 1.1
        UPDATE eBaySchema.tItem SET HighBidderID=5, CurrentHighBid = @HighBid WHERE ItemID = 1
    COMMIT TRAN

Upvotes: 0

Related Questions