How to exclusively lock and wait in SQL Server 2005 - avoiding deadlocks!

Using SQL Server 2005;
We have a complex set of chained stored procedures and triggers that are being run by concurrent users.
We have been experiencing problems related to 'ident_current' (versus 'scope_identity') and deadlocking.

NB because our requirement means we must use triggers 'instead of' and 'for' insert, we are unable to use 'scope_identity' to "get the current ID" from the table into which the concurrent users are inserting new records, hence our (incorrect?) use of 'ident_current'.

Here is a test procedure that attempts to replicate our problem in a simplified form.

/**************************************************************************/
/*** create table *********************************************************/
/**************************************************************************/
go
if object_id('testing') is not null drop table testing
go
create table testing (test_id int not null primary key identity, data varchar(24) not null)
go
/**************************************************************************/
/*** stored proc **********************************************************/
/**************************************************************************/
if object_id('testproc') is not null drop procedure testproc
go
create procedure testproc (
      @data int
) as
begin
      set transaction isolation level serializable
      begin try
            begin transaction      
            declare @cnt int
            
            select @cnt = count(*) from testing
            select 'ident_current: ' + cast(ident_current('testing') as varchar(max)) + '  ' + cast(@cnt as varchar(max))
            
            insert into testing (data) values (@data)                   
            waitfor delay '00:00:03.000'
            --now use new ID to do stuff (in this example just select it)
            
            select @cnt = count(*) from testing
            select 'ident_current: ' + cast(ident_current('testing') as varchar(max)) + '  ' + cast(@cnt as varchar(max))

            commit transaction
            return 1
      end try
      begin catch      
            declare @err_message nvarchar(4000);
            declare @err_severity int;
            declare @err_state int;
            
          select @err_message = error_message(), @err_severity = error_severity(), @err_state = error_state();
          raiserror (@err_message, @err_severity, @err_state);
            rollback transaction
            return 0
      end catch
end
go
grant execute on testproc to public
go

Having created the table and stored procedure (see preceding code), we simply open up 3 query windows, each one executing the stored procedure with a unique qualifieras follows;

Query Window 1;
exec testproc @data = 1

Query Window 2;
exec testproc @data = 2

Query Window 3;
exec testproc @data = 3 etc.

The result we expected was that, in each case, the 'ident_current' would return an incorrect ID, i.e. not necessarily the ID created by the Query Window running it, but perhaps the ID just created by whichever Query Window got there first. Ok, so this is the result we partially got.

However, the unexpected result was that, sometimes, one of the Query Windows would return with a deadlock error, as follows;

Msg 50000, Level 13, State 48, Procedure testproc, Line 29
Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The reason we did not expect this is because we thought that, by explicitly stating "set transaction isolation level serializable" in our stored procedure, we were forcing the transaction to be exclusive and therefore force other instances of that procedure's execution to WAIT rather than deadlock and return.

Clearly we are misunderstadning the application of "transaction isolation".
(1)  What should we be doing here to ensure that each instantiation of our procedure waits its turn then completes correctly?
(2) Is there a solution to the 'ident_current' situation, given the fact we cannot use 'scope_identity', as previously explained?
ddl_ex5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ddl_ex5Author Commented:
NB
if we run "alter database this_db set allow_snapshot_isolation on", we still get the same deadlock error, whether we explicitly set the transaction isolation level to serializable or not. Again, this is not what we'd understood should happen.
0
ddl_ex5Author Commented:
NB #2
should we simply be declaring the select statements in the stored procedure as "with (nolock)" and what is the impact of this...?
0
ddl_ex5Author Commented:
NB #3
...or (!) should we be setting "allow_snapshot_isolation on" AND specifying "transaction isolation level snapshot"...?

In any case, the other part of the question relating to "ident_current" still remains.

Thanks.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>NB because our requirement means we must use triggers 'instead of' and 'for' insert, we are unable to use 'scope_identity' to "get the current

huh?
a INSTEAD OF trigger should not change your use of scope_identity.
ident_current shall NEVER be used in such developments!!!

so, can you set up a showcase that demonstrates why you cannot use SCOPE_IDENTITY?



anyhow, the issue raising the deadlock in your code is, from what I can see, that you don't "hold" your locks on the tables, so the second transaction will happily start into the same lines ...


select @cnt = count(*) from testing

should be something like this:
select @cnt = count(*) from testing WITH (HOLDLOCK, TABLOCKX)

0
ddl_ex5Author Commented:
My statement was a little incomplete - we are unable t use 'scope_identity' because in the scenario I detailed, it returns 'null'.

Here is the showcase to demonstrate why we cannot use scope_identity, as requested;

Firstly, I create a trigger for the table that amends some property of the inserted data (which is what we have to do in our more complex real-life story);

if object_id('tr_testing') is not null drop trigger tr_testing
go

CREATE TRIGGER tr_testing
   ON  testing
   instead of INSERT
AS
BEGIN
      declare @data varchar(24)

      select @data = [data] from INSERTED
      set @data = @data + 'xx'

      insert into testing (data) values (@data)
            
END
GO

Now I alter the procedure so that we can view 'scope_identity' (NB for the purpose of simplicity I have commented out the transaction scoping, but you could leave this in and get the same result);

if object_id('testproc') is not null drop procedure testproc
go
create procedure testproc (
      @data int
) as
begin
      --set transaction isolation level snapshot
      begin try
            --begin transaction      
            declare @cnt int
                  declare @scope int
           
            select @cnt = count(*) from testing
            --select 'ident_current: ' + cast(ident_current('testing') as varchar(max)) + '  ' + cast(@cnt as varchar(max))
                  select scope_identity()
                  insert into testing (data) values (@data)                  
                  waitfor delay '00:00:03.000'
                  --now use new ID to do stuff (in this example just select it)    
                  select @cnt = count(*) from testing
                  --select 'ident_current: ' + cast(ident_current('testing') as varchar(max)) + '  ' + cast(@cnt as varchar(max))
                  select scope_identity()
            --commit transaction
            return 1
      end try
      begin catch      
            declare @err_message nvarchar(4000);
            declare @err_severity int;
            declare @err_state int;
           
          select @err_message = error_message(), @err_severity = error_severity(), @err_state = error_state();
          raiserror (@err_message, @err_severity, @err_state);
            --rollback transaction
            return 0
      end catch
end
go
grant execute on testproc to public
go

Scope_identity now returns null.

With regards to the second part of your response, perhaps my initial question was unclear. We do not wish to lock the table to prevent concurrent inserts, otherwise our system will grind to a halt. We instead wish to prevent deadlocking and instead promote a wait-my-turn-if-I-need-to-access-a-locked-record scenario.

Since posting this question, we've done further testing and now think this should be possible by setting 'allow_snapshot_isolation on' and specifying 'set transaction isolation level snapshot' in the relevant procedures. This still doesn't solve the ident_current vs scope_identity scenario, however. But we now think that using 'select top 1 id from testing' within the private scope of a snapshot transaction may solve the problem...



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Theory

From novice to tech pro — start learning today.