Concurrent Triggers: unique INSERTED table for each SPID ?

[travis909]

Hello,

I have multiple processes (SPIDs) simultaneously INSERTING rows into a single table, each time firing a trigger which modifies the inserted row of the base table (and a corresponding row in another table).

I am getting DEADLOCK about 10% of the time.
[tt]
--SELECT initial values from INSERTED
SELECT @incomingconfid=h323incomingconfid,
@acctsessionid=acctsessionid
FROM INSERTED

UPDATE TblRadiusSTART WITH (UPDLOCK)
SET CallActive = 0
WHERE TblRadiusSTART.h323incomingconfid = @incomingconfid
AND TblRadiusSTART.AcctSessionid = @acctsessionid

UPDATE TblRadiusSTOP WITH (UPDLOCK)
SET StartRecordFound = 1
WHERE TblRadiusSTOP.h323incomingconfid = @incomingconfid
AND TblRadiusSTOP.AcctSessionid = @acctsessionid
[/tt]
Question: Does each SPID have a it's own copy of the INSERTED table ? From what I am observing, it seems their is an INSERTED table for each base table, and not a separate INSERTED table for each SPID.

If my observation is correct, can anyone tell me how to process the 2 UPDATE statements for the firing INSERT ?

I think I could use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" and make the entire trigger procedure one big transaction, but that removes nearly all concurrency from the application.

I would greatly appreciate help and will supply my email to help you in the future.

Thanks,
Travis

 

[MinusM]

Instead of retrieving vlaues into variables have you tried Update with INNER JOIN between TblRadiusSTOP and INSERTED tables on
h323incomingconfid and AcctSessionid
I haven't tried this but guessing. If not you can try your hand at inserting rows from INSERTED into a temporary table and running UPDATE by INNER JOIN with the same

Best of luck