Insert on single table caused deadlock.

Question

I am using SQLServer 2005 and designed a DAO running insert sql like this:

INSERT INTO eventsources (recevied_event_time_stamp, recevied_event_curve_name, recevied_event_curve_value, recevied_event_quote_date, total_time_for_event_processing, number_of_published_events{0}, triggered_curve, recevied_event_day)

however, the system throws dead lock exception after running a while, which seems impossible to me, I think deadlock should only happen when using multiple resources in reverse order.

The insertion are multi-Thread, could that be a problem? but I am using Spring.Net's AdoTemplate which announced to be Thread-Safe.

I have created an trigger on the eventsources table

CREATE TRIGGER TRIGGER_EVENTSOURCES
ON eventsources
FOR INSERT
AS
DECLARE @newlyInertedFormulaName VARCHAR(100)
DECLARE @error_message varchar(10)
DECLARE @last_calculated_date datetime
DECLARE @timeframe datetime
DECLARE @publishedEvent int

SELECT @publishedEvent = (SELECT number_of_published_events FROM Inserted)
SELECT @newlyInertedFormulaName = (SELECT triggered_curve FROM Inserted)
SELECT @error_message = (SELECT error_message FROM Inserted)
SELECT @last_calculated_date = (SELECT recevied_event_time_stamp FROM Inserted)

if @publishedEvent > 0
BEGIN
    update formulaversions set last_calculated_date = @last_calculated_date where 

formulaname = @newlyInertedFormulaNam
e and lifecycle = 3;
END

if @error_message is not NULL
        BEGIN
                update formulaversions set status = 2 where formulaname = @newlyInertedFormulaName and lifecycle = 3;
        END
ELSE
        update formulaversions set status = 1 where formulaname = @newlyInertedFormulaName and lifecycle = 3 and (status <>
 2 or status is null);       

GO

Is there any problem of using this trigger?

Any comments will be appreciated.

Answer 1

the root cause is the three update statements in the trigger, it may lock on the same row in different order, which cause the problem, SQL Server Profiler is very helpful to figure this out.

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