Implement Concurrency-Safe Stored Procedure for Update and Delete
Implement Concurrency-Safe Stored Procedure for Update and Delete
In SQL Server 2005
By : Kasim Wirama, MCDBA
Concurrency happens when two or more users access and modifies same resource. In this article, I would share would how you should develop UD (Update, Delete) stored procedure that is aware to concurrency so that your users ensure that changes that they make will be consistent.
Create 2 tables, first invoice and then invoiceLineItem as script shown below :
CREATE TABLE dbo.Invoice
(
InvoiceId int identity(1,1),
InvoiceDate datetime,
InvoiceNumber varchar(30) not null,
Constraint pk_invoice primary key (InvoiceId),
Constraint ak_invoice unique (InvoiceNumber)
);
GO
CREATE TABLE dbo.InvoiceLineItem
(
InvoiceLineItemId int identity(1,1) not null,
InvoiceId int not null,
Cost numeric (10,2),
Constraint pk_invoiceLineItem primary key (InvoiceLineItemId),
Constraint fk_invoiceLineItem foreign key (InvoiceId) References dbo.Invoice(InvoiceId)
);
GO
Now let’s say you create a stored procedure to delete one of the invoiceLineItem as follows :
CREATE PROCEDURE dbo.InvoiceLineItem_del
(@InvoiceLineItemId int)
As
BEGIN
DELETE FROM dbo.InvoiceLineItem
WHERE InvoiceLineItemId = @InvoiceLineItemId
END;
GO
It’s simple but I can say it’s not safe against concurrency issue. Let’s say, user A change cost at a InvoiceLineItem. With almost the same time, user B call the stored procedure to delete the InvoiceLineItem. Boom! User B successfully delete the invoiceLineItem without knowing before hand that there was a change in InvoiceLineItem made by other user, i.e. UserA. That’s the concurrency issue.
So the stored procedure above is not capable to handle concurrency issue, and unfortunately I saw many projects get used to with this kind of CRUD style which is not concurrency safe.
How to make it concurrency safe? Here I will tell you a trick. The tirck is that I add rowversion at Invoice and implement high performance server side transaction scope that binds Invoice and InvoiceLineItem into one transaction. So I add new column at Invoice table as following script shown:
ALTER TABLE dbo.Invoice
ADD ObjectVersion rowversion;
Now change the above stored procedure as follow :
ALTER PROCEDURE dbo.InvoiceLineItem_del
(
@InvoiceLineItemId int ,
@InvoiceId int ,
@objectVersion rowversion
)
As
BEGIN
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.Invoice
SET InvoiceNumber = InvoiceNumber
WHERE InvoiceId = @InvoiceId
AND ObjectVersion = @objectVersion;
IF @@ROWCOUNT = 0
RAISERROR (‘Changes has been happened prior deleting line item’, 16, 1) ;
DELETE dbo.InvoiceLineItem
WHERE InvoiceLineItemId = @InvoiceLineItemId;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF xact_state() <> 0
BEGIN
ROLLBACK TRAN;
END
DECLARE @errmsg VARCHAR(8000);
SET @errmsg = ERROR_MESSAGE();
RAISERROR ( @errmsg, 16, 1);
END CATCH;
END;
GO
With the concurrency-safe stored procedure above, your users will be aware changes made from others before hand.