Locking a table while inserting and selecting

Question

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)

If you are getting an identity value from the first `INSERT` you should use either `SCOPE_IDENTITY()` or an `OUTPUT` clause. It doesn't require an additional query and gives you "your" answer even if the table is being updated by others. Do learn about `OUTPUT` as it handles multiple rows, works with `INSERT`, `UPDATE`, `DELETE` and `MERGE`, and it provides access to all columns with before and after values (as applicable to the statement). — HABO, Jun 07 '13 at 01:30
And OUTPUT looks simpler to work with than Stored Procedures. Does anyone know if it is possible to output the OUTPUT in the query to a variable in C# so I can then find that ID in table 2 to update the remaining info? My SQL knowledge is woefully inadequate I must admit. — Trido, Jun 07 '13 at 01:39
Oh, I read another Question (http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) which said that if I use .ExecuteScalar() instead of .ExecuteNonQuery() it will read back the resulting ID. — Trido, Jun 07 '13 at 01:43

score 1 · Accepted Answer · answered Jun 08 '13 at 19:16

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

Locking a table while inserting and selecting

1 Answers1