0

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dio Phung
  • 5,944
  • 5
  • 37
  • 55
  • Why `HOLDLOCK`? From my understanding, that changes the semantics for that table to effectively the `SERIALIZABLE` isolation level. It reduces concurrency for sure, so make sure you need those isolation semantics. – Ben Thul Sep 06 '16 at 20:52
  • @BenThul: the TargetAccount table can be updated by the same customer concurrently (from multiple web pages), or by various customers at the same time. Hence, we want to ensure no dirty read/write to that table. – Dio Phung Sep 08 '16 at 00:47
  • You won't get dirty reads by default in SQL. As for "dirty writes", I assume that you mean multiple people updating the same row(s) at the same time. For that, I'd put the whole thing in an explicit transaction rather than use query hints that probably don't really do what you're looking to do anyways. But I'm not you. – Ben Thul Sep 08 '16 at 02:01

1 Answers1

2

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).

Luaan
  • 62,244
  • 7
  • 97
  • 116