CODEWITHSUNDEEP
sql-servermultithreadingsql-server-2008transactionssql-server-2012
Vitalii
Vitalii

Reputation: 11071

Multythread safety of SQL stored procedure

I need to store some data into database and mark it as 'used' if it was read once. And it should work correctly with simultaneous calls of 20+ threads.

The idea is that one thread can get only it's unique value but 2 threads cannot get the same value. Access to list of values is by some int key and thread should take any value with this key but not several threads the same value

I created a table with next structure

Table structure .

So stored procedure for adding is quite simple 

ALTER PROCEDURE [dbo].[StorageAdd]
@key int,
@value nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        INSERT INTO Storage(k, value, isUsed) VALUES (@key, @value, 0)
        SELECT 1  as 'result', '' as 'message'
    END TRY
    BEGIN CATCH
        SELECT 0 as 'result', 'Operation failed. Message = ' + ERROR_MESSAGE() as 'message'
    END CATCH   
END

But it is not so evident for getting where I should perform SELECT and UPDATE after it

CREATE PROCEDURE StorageGet
    @key int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRAN 
BEGIN TRY
    -- create variable for value
    DECLARE @value nvarchar(100)
    -- get first value from database
    SET @value = (SELECT TOP 1 Storage.value FROM Storage WHERE Storage.k = @key AND Storage.isUsed = 0)

    IF(@value IS NULL OR @value = '')
        BEGIN
            ROLLBACK
            SELECT 0 as 'result', 'No items found' as 'message', '' as 'value'
            RETURN
        END

    UPDATE Storage SET Storage.isUsed = 1 WHERE  Storage.k = @key AND Storage.value = @value 

    IF @@ERROR <> 0
        BEGIN
            -- in case of error roll back this transaction
            ROLLBACK
            -- return 0
            SELECT 0 as 'result', 'Insert article ID failed. Error code = ' + CONVERT(varchar(5), @@ERROR) as 'message', '' as 'value'
            RETURN
        END
    COMMIT TRAN
    SELECT 1 as 'result', '' as 'message', @value as 'value'

END TRY
BEGIN CATCH
    -- in case of error roll back this transaction
    ROLLBACK
    -- return 0
    SELECT 0 as 'result', 'Operation failed. Message = ' + ERROR_MESSAGE() as 'error'
    RETURN
END CATCH   

END
GO

So will my transaction be multithread safe?

First I select the top value for the table that it is not used before and after I make update of this row that it is used. Possible problem is that if I run 100 executions simultaneously 2 threads can at the same time make select and get the same value and after it both will update this row.

In a result I'll have 2 threads with the same value and no error.

Is my transaction thread safe or not? I use MS SQL Server 2008 and 2012.

Upvotes: 1

Views: 38

Answers (1)

Dan
Dan

Reputation: 10680

Just add a hint to the very first SELECT-statement (the one where you read out the value), to ensure that the row is locked until the end of the transaction:

SELECT TOP 1 Storage.value FROM Storage 
WITH (XLOCK, ROWLOCK)
WHERE Storage.k = @key AND Storage.isUsed = 0

This should ensure, that no other transactions can read the row, until the current transaction has ended.

Upvotes: 2

Related Questions