Deadlock in SQL Server 2008

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

Once stored procedure call regular time interv开发者_JAVA技巧al 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?

---

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

---

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.

---

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