Database locking issue

We have a table with an update trigger that we seem to be having unintended
deadlock issues on. The trigger, among other things, updates the same row
that was updated to spawn the trigger. In our examples where we are
encountering the deadlocks we are always doing single row updates on a uniqu
e
clustered index.
Originally, we seemed to be having a lot of unnecessary lock escalation
occurring to the page and table level. We added a lock hint to the table to
prevent page locks (sp_indexoption 'osc_play.cylmas.uq_cylmas_barcod',
'disallowpagelocks',TRUE ). This has helped minimize the deadlocks on that
table considerably.
However, we do still occasionally get them, but they are now Key locks that
are deadlocking. The 2 update statements are attempting to update 2 differen
t
rows, but we suspect they happen to be on the same Key page. Are the update
triggers coming in to play here? Has the initial lock from our SQL update
been devalued before the trigger fires – generating a second lock, allowin
g a
window for the second SQL update to grab its lock?If you are wanting to update the contents of the same row being updated (for
example, updating a column called updated_by with a userid), then consider
using an INSTEAD OF trigger or updating the special [inserted] table rather
than trying to perform yet another update. As it is, the problem may be the
result of the update trigger being called multiple times recursively.
Nested Triggers
http://msdn.microsoft.com/library/d...>
_08_6nw3.asp
Using the inserted and deleted Tables
http://msdn2.microsoft.com/en-us/library/ms191300.aspx
INSTEAD OF Triggers
http://msdn.microsoft.com/library/d...>
NSTEADOF.asp
"cu_blenge" <cu_blenge@.discussions.microsoft.com> wrote in message
news:58C0E063-7F0A-4157-ADFC-AD2E1939603E@.microsoft.com...
> We have a table with an update trigger that we seem to be having
> unintended
> deadlock issues on. The trigger, among other things, updates the same row
> that was updated to spawn the trigger. In our examples where we are
> encountering the deadlocks we are always doing single row updates on a
> unique
> clustered index.
> Originally, we seemed to be having a lot of unnecessary lock escalation
> occurring to the page and table level. We added a lock hint to the table
> to
> prevent page locks (sp_indexoption 'osc_play.cylmas.uq_cylmas_barcod',
> 'disallowpagelocks',TRUE ). This has helped minimize the deadlocks on that
> table considerably.
> However, we do still occasionally get them, but they are now Key locks
> that
> are deadlocking. The 2 update statements are attempting to update 2
> different
> rows, but we suspect they happen to be on the same Key page. Are the
> update
> triggers coming in to play here? Has the initial lock from our SQL update
> been devalued before the trigger fires - generating a second lock,
> allowing a
> window for the second SQL update to grab its lock?|||Good link , however "or updating the special [inserted] table rather
> than trying to perform yet another update." is not possible. See below
> from the same link
DML trigger statements use two special tables: the deleted table and the
inserted tables. SQL Server 2005 automatically creates and manages these
tables. You can use these temporary, memory-resident tables to test the
effects of certain data modifications and to set conditions for DML trigger
actions. You cannot directly modify the data in the tables or perform data
definition language (DDL) operations on the tables, such as CREATE INDEX.
Thanks
Farmer
"JT" <someone@.microsoft.com> wrote in message
news:ebvtWQq%23FHA.3676@.tk2msftngp13.phx.gbl...
> If you are wanting to update the contents of the same row being updated
> (for example, updating a column called updated_by with a userid), then
> consider using an INSTEAD OF trigger or updating the special [inserted]
> table rather than trying to perform yet another update. As it is, the
> problem may be the result of the update trigger being called multiple
> times recursively.
> Nested Triggers
> http://msdn.microsoft.com/library/d...
es_08_6nw3.asp
> Using the inserted and deleted Tables
> http://msdn2.microsoft.com/en-us/library/ms191300.aspx
> INSTEAD OF Triggers
> http://msdn.microsoft.com/library/d...
/INSTEADOF.asp
>
> "cu_blenge" <cu_blenge@.discussions.microsoft.com> wrote in message
> news:58C0E063-7F0A-4157-ADFC-AD2E1939603E@.microsoft.com...
>