CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vanapandi Narayanan
Vanapandi Narayanan

Reputation: 109

How to avoid concurrent read access issue in SQL Server?

I am getting a date value from a tblinfo table. If the date is null, then I will insert into table, else I will update the row in the table.

I am facing the issue that 2 sessions accessing the table simultaneously with same information, both are getting Null, so both are trying to insert records into same table with same primary key value. We are getting the primary key constraints issue. How to stop this?

begin tran
     set @date=(select date from tblinfo where id=@id)
        if(@date is null) 
        --insert 
    else 
    --update 
commit tran

Upvotes: 0

Views: 2704

Answers (3)

FLICKER
FLICKER

Reputation: 6693

Right after "begin tran", update your table like below script

begin tran 

-- this update will lock the table so no other process can read data
update tblInfo with (tablock)
set date = date


---- do what ever you need to do here
set @date=(select * from tblinfo)

if(@date is null) 
    --insert 
else 
    --update 

commit tran

this will cause SQL Server lock the table and the second transaction will wait reading the data until the first process finish.

Upvotes: 1

Chris Steele
Chris Steele

Reputation: 1381

Relying on lock hints to get the desired outcome will perhaps prevent you from attempting to insert the value twice, but it won't prevent errors from happening. You'll just get an 'Unable to acquire lock' or deadlock error instead.

You should put a mutex in your code, in the minimally sized critical section. Using sp_getApplock, you can get a lock on a section of code with a wait period that you specify (subsequent threads will wait for the lock to clear and then continue). Here's some sample code:

declare @LockResource nvarchar(100) = 'VALUE_INSERT'    
begin transaction

    -- Fetch the lock:
    EXEC @res = sp_getapplock                 
                    @Resource = @LockResource,
                    @LockMode = 'Exclusive',
                    @LockOwner = 'Transaction',
                    @LockTimeout = 10000,
                    @DbPrincipal = 'public'

    if @res not in (0, 1)
    begin
        set @msg = 'Unable to acquire Lock for ' + @LockResource
        raiserror (@msg , 16, 1)
        rollback transaction
        return
    end 
    else
    begin
        begin try
            ---------------------------------------------
            -- Fetch value if it exists, insert if not.
            -- Both need to happen here.
            ---------------------------------------------
        end try
        begin catch
            select @msg = 'An error occurred:  ' + ERROR_MESSAGE()

            -- Release the lock.
            EXEC @res = sp_releaseapplock 
                    @Resource = @LockResource,
                    @DbPrincipal = 'public',
                    @LockOwner = 'Transaction' 

            rollback transaction
            raiserror(@msg, 11, 1)
            goto cleanup
        end catch

        -- Release the lock.
        EXEC @res = sp_releaseapplock 
                @Resource = @LockResource,
                @DbPrincipal = 'public',
                @LockOwner = 'Transaction' 
    end

    commit transaction

Upvotes: 0

JohnLBevan
JohnLBevan

Reputation: 24490

Using Locking

declare @Date DateTime
, @RowId bigint = 10

Begin Tran
select @Date = SomeDate 
from tblInfo 
with (RowLock) --this is the important point; during the transaction, this row will be locked, so other statements won't be able to read it
where Id = @RowId

if (@Date is Null)
begin
    insert SomeTable (TblInfoId, Date) 
    values (@RowId, GetUtcDate())
end
else
begin
    update SomeTable 
    set Date = @Date 
    where tblInfoId = @RowId
end
if @@TranCount > 0 Commit Tran

Technique Avoiding Locking

If preventing reads from occurring is an issue for some reason, the below is a lockless approach; but requires an extra field in your database.

declare @Date DateTime
, @RowId bigint = 10
, @DataAvailable bit

Begin Tran

update tblInfo
set session_lock = @@SPID
where Id = @RowId
and session_lock is null

select @Date = SomeDate 
, @DataAvailable = 1
from tblInfo 
where Id = @RowId
and session_lock = @@SPID

if (@DataAvailable = 1)
begin
    if (@Date is Null)
    begin
        insert SomeTable (TblInfoId, Date) 
        values (@RowId, GetUtcDate())
    end
    else
    begin
        update SomeTable 
        set Date = @Date 
        where tblInfoId = @RowId
    end
    update tblInfo
    set session_lock = null
    where Id = @RowId
    and session_lock = @@SPID
end
--optionally do something if the row was already "locked"
--else
--begin
    --...
--end
if @@TranCount > 0 Commit Tran

Upvotes: 4

Related Questions