CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresconcurrencydatabase-concurrency
Dio Phung
Dio Phung

Reputation: 6272

Avoiding deadlocks when running stored procedure on table WITH UPDLOCK, HOLDLOCK

I'm maintaining a stored procedure which will be executed whenever a user visits a web page.

-- Existing tables
CREATE TABLE SourceAccount   
(
    Id bigint NOT NULL, 
    Value varchar(50) NOT NULL, 
    UpdateTime datetime2 NULL
)

CREATE TABLE TargetAccount 
(
    Id bigint NOT NULL, 
    Value varchar(50) NOT NULL, 
    UpdateTime datetime2 NULL
)

CREATE TABLE UpdatedCustomers
(
     CustomerID bigint NOT NULL, 
     SyncTime datetime2 NOT NULL
)

The stored procedure:

CREATE PROCEDURE TriggerAccountSync (
    @CustId bigint,      
    @LastUpdate DATETIME2)
AS 
BEGIN
    --if customer is outdated
    IF EXISTS(SELECT 1 FROM UpdatedCustomers 
              WHERE CustomerID = @CustId AND SyncTime < @LastUpdate)
    BEGIN
      BEGIN TRY
          INSERT INTO TargetAccount(Id, Value)
              SELECT Id, Value 
              FROM SourceAccount 
              LEFT OUTER JOIN TargetAccount WITH (UPDLOCK, HOLDLOCK) ON TargetAccount.Id = SourceAccount.Id 
              WHERE SourceAccount.UpdateTime IS NULL

          DELETE FROM TargetAccount 
          FROM SourceAccount 
          INNER JOIN TargetAccount WITH (UPDLOCK) ON TargetAccount.Id = SourceAccount.Id 
          WHERE TargetAccount.UpdateTime < @TimeStamp

          UPDATE UpdatedCustomers 
          SET SyncTime = @LastUpdate 
          WHERE CustomerID = @CustId            
      END TRY
      BEGIN CATCH
        --there are multiple simultaneous calls, it can fail with deadlock
        --don't raise error
      END CATCH
    END
END

Can I throw exception using TRY CATCH THROW END CATCH and still avoid deadlocks on TargetAccount table ? I need to know when it fails to sync.

Is there a way to release table lock if the stored procedure failed to complete ?

Upvotes: 0

Views: 1091

Answers (1)

Luaan
Luaan

Reputation: 63722

Table locks are transaction-scoped. If you only have the implicit transaction associated with the stored procedure, as soon as your stored procedure ends, so will the locks, regardless of any errors.

When dealing with locks like this, it is generally useful to use explicit transactions, so that you have more control over the lifetime of the locks, and make it more explicit in the code (so that there is no confusion when transactions are nested).

Upvotes: 2

Related Questions