trigger and deadlock - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger and deadlock

Author

Topic

anichin
Starting Member

4 Posts

Posted - 2008-12-04 : 10:32:19

I have an after trigger for insert, update, delete defined on a Customers table. When the trigger is fired it updates another table that holds information from Customers, Address and Phone tables. There is a view defined on top of these 3 tables that is used to populate the table. And I got deadlock. I suspect it is because the trigger tries to access Customers table through the view and the table has changes that are not yet committed.

What can I do to avoid these deadlocks?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:10:13

why are you trying to update through the view?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 11:21:54

Can we see whole deadlock information through SQL profiler(Deadlock graph) or -T 1204 so we know what is main issue?

anichin
Starting Member

4 Posts

Posted - 2008-12-04 : 12:37:21

quote:
Originally posted by visakh16

why are you trying to update through the view?

The view gathers information from several tables. It uses a user defined function to concatenate strings from several records and represent it as one string. It also has different fields for business phone, home phone that come from one table - Address. The same is for Phone table. It will be too complicated to put all this logic inside the trigger. So basically the trigger is doing:

insert info Contact_Info
select * from My_View
inner join
(
-- Get contacts that are affected
select con_contact_id from inserted
) as c -- CON_Contact
on c.con_contact_id = My_View.ContactGuid

If I replace this code with

declare @id uniqueidentifier
select @id = top 1 con_contact_id from inserted

insert info Contact_Info
select * from My_View
where My_View.ContactGuid = @id

there are no more deadlocks. Most of the time it will work since the application works with one contact at a time. The only time it will have a problem is when I try to update several records from Contact table in Query Analyzer.

anichin
Starting Member

4 Posts

Posted - 2008-12-04 : 12:39:20

quote:
Originally posted by sodeep

Can we see whole deadlock information through SQL profiler(Deadlock graph) or -T 1204 so we know what is main issue?

For some reason I could not replicate the problem today. I will try to find older log information and will post it here. I am using SQL 2000, so the information will not be as descriptive if it was SQL 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 12:40:51

are you interested in table values before update or after update in the view? if you want only before values, you could try using NOLOCK hint in place you select from table.

anichin
Starting Member

4 Posts

Posted - 2008-12-04 : 13:15:47

quote:
Originally posted by visakh16

are you interested in table values before update or after update in the view? if you want only before values, you could try using NOLOCK hint in place you select from table.

I am interested in table values after update. For example if email address field in Contacts table is changed I would like to update the Contact_Info table with the new value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources