Deadlocks in custom changelog

fufikk

Hi,

I've created a custom changelog based on SQL triggers (Navision 3.70B, SQL2k). Log table has an autoincremet primary key and during testing concurrent transactions were able to add records without locking each other on this resource. The main "logged" tables are Sales Header, Sales Line, Reservation Entry and Item Ledger Entry. After the triggers were placed in production database users started to complain about being the deadlock victims. These deadlocks are caused by different tables. It seems that system instead of waiting for a lock to be realeased interprets it as deadlock and I have no idea how to check if that's really the case.

Any ideas?

kine

If the functionality is based on SQL triggers, it can be problem that the triggers are running in different transaction/process and locking the NAV process... but it is just shot into dark...

DenSter

a 'lock' is something entirely different from a 'deadlock'. When a deadlock occurs, there are two resources that are locked out by eachother. They're both waiting for eachother's lock to be released, which won't happen. The lock cannot be resolved, and that is what is called a 'deadlock'. There is no 'interpretation' of locks, it's quite literal.

I won't comment on your choice to implement this using SQL Server triggers, but you will have to make sure that your process locks resources in the same order, so that two resources can't lock eachother out, and they are forced to wait until the regular lock is released.

kriki

[Topic moved from Navision forum to SQL General forum]