CODEWITHSUNDEEP
sqlsql-server-2008
Innodel
Innodel

Reputation: 1426

Deadlock in SQL Server 2008

I have two stored procedures, both insert rows into the same table.

Once stored procedure call regular time interval and another stored procedure call by user event. Sometimes both stored procedure are called together and at this time deadlock occurs.

How can I solve this problem?

Upvotes: 0

Views: 312

Answers (3)

Hugh Jones
Hugh Jones

Reputation: 2694

You might also consider detecting the condition and retrying. Have each procedure back off for a short random amount of time

Upvotes: 0

evpo
evpo

Reputation: 2531

You can lock the table at the beginning of your both sprocs. This way there will be no deadlocks because data modification will have to wait until the other sproc finishes. See the following command:

select 1 from theTable with (tablock, holdlock) where 1=0;

It also needs to be done inside a transaction. The table will be editable when the transaction finishes.

Upvotes: 0

Louis
Louis

Reputation: 2900

Lock at the beginning of the SP and unlock at the end.

http://msdn.microsoft.com/en-us/library/ms187749.aspx

and

http://msdn.microsoft.com/en-us/library/ms190345.aspx

Upvotes: 0

Related Questions