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 ?