CODEWITHSUNDEEP
c#sql-server
Trido
Trido

Reputation: 545

Locking a table while inserting and selecting

I am creating an asp.net site where a user submits some data which is inserted into table 1, and then I run a query to grab the ID of the entry that was then created from table 1 and then insert that ID into table 2. The issue is that if someone just happens to submit at the same time I could easily end up with incorrect data. I assume it is possible to lock table 1 until the data has been inserted into the table 2, but I am unsure how to do it. Can anyone provide some guidance? All my code is in a C# Code Behind file. I read that you could do it with something like the below, but if that works, how is it unlocked?

UPDATE table1 WITH (rowlock)

Upvotes: 0

Views: 886

Answers (1)

sisdog
sisdog

Reputation: 2719

A sample proc that your ADO could call.

/*
create table a (id int identity(1,1) primary key, name varchar(max))
create table b (id int identity(1,1) primary key, ParentID int)
GO

create proc Test as
begin
    declare @parentId int
    begin tran
    begin try
        insert into a(name) values('abc')
        select @parentId = SCOPE_IDENTITY()
        select 'ScopeID',@parentID
        insert into b(ParentID) values(@parentid)
        --Uncomment this to see the rollback happen
        --raiserror ('Testing what will happen if an error occurred to make sure rollback works.',
        --       16, -- Severity.
        --       1 -- State.
        --       );
        commit tran
    end try
    begin catch
        rollback tran
    end catch
end
go
*/

truncate table a --TRUNCATE RESET IDENTITY SEED VALUES (UNLIKE DELETE)
truncate table b
exec Test
select * from a
select * from b

Upvotes: 1

Related Questions