September 26, 2014 at 4:21 am
Hi Guys,
I have a table where email is the PK and am continually getting deadlocks on updates on this table.
The table has rough 2 million rows. There are no indexes on this table.
lock mode: Intent exclusive (IX), file id: 1, page no. 5711974
update cust set address1='asdasdasdasdasd',address2='asdasdasdvasvf',address3='asdasdasdasd',last_modify_date=getdate()
where email='Dan.Doe@Amaze.com'
lock mode: Update (U), file id: 1, page no. 5496760
--DEADLOCK VICTIM
if not exists (select 1 from comm where email = 'John.Smith@fanmail.co.uk')
begin
insert into cust (email, firstname, surname, country,gender, agerange)
values ('John.Smith@fanmail.co.uk', 'John', 'Smith', 'United Kingdom', 'Male', '20-25');
select 0 as noinsert
end
else begin
update cust set firstname = 'John', surname='Smith', country='United Kingdom',
gender='Male', agerange='20-25'
where email = 'John.Smith@fanmail.co.uk';
select 1 as noinsert
end
If i replaced the 2nd statement( the victim) with a SQL merge statement would this fix the issue?
OR should I change the PK email to a unique non clustered index? And setup a new primary (autoincrement) key?
Could I create a Composite Index with includes to try rectify this as well?
Any suggestions appreciated !
Thanks
B
September 26, 2014 at 4:37 am
Can you post the deadlock XML (if you have it)?
September 26, 2014 at 5:08 am
DBA From The Cold (9/26/2014)
Can you post the deadlock XML (if you have it)?
Trying to capture it in SQL Profiler but typically there are no deadlocks happening at the moment.
September 26, 2014 at 8:43 am
OK, it's difficult to provide a resolution with just the information you've provided I'm afraid.
Warning, shameless self plug coming up!
I wouldn't recommend running profiler to capture the deadlock, have a look here:-
http://dbafromthecold.wordpress.com/2014/02/11/who-are-your-victims/
This post will run you through using Event Notifications and Service Broker to capture deadlocks.
September 26, 2014 at 1:31 pm
Use system_health event session to get deadlock graphs.
September 29, 2014 at 2:43 am
Here's the deadlock XML - looking through it I wonder if the trigger (last_date_modify) is causing the issue?
deadlock-list
deadlock victim=process4db9048
process-list
process id=process4db9048 taskpriority=0 logused=268 waitresource=PAGE: 9:1:7669491 waittime=3403 ownerId=8499272252 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:01.750 XDES=0x800dda70 lockMode=U schedulerid=10 kpid=8920 status=suspended spid=76 sbid=0 ecid=5 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:01.750 lastbatchcompleted=2014-09-27T04:56:01.750 clientapp=PHP 5 hostname=web02 hostpid=145729 isolationlevel=read committed (2) xactid=8499272252 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=78 sqlhandle=0x02000000f1b3642eacaeaaee4b366a48284c7678f5ea5494
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[last_modify_date] = getdate() WHERE %%=%%@4
frame procname=adhoc line=1 sqlhandle=0x0200000024555608ee2bb0e56f81e78175e0280c9a5719b7
update comm set newsletter=1,newbie=1,snailmail=1,last_modify_date=getdate()
where email='vanessa@mail.com'
inputbuf
process id=process1ccbb6748 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5491675 waittime=3360 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x21d251950 lockMode=U schedulerid=12 kpid=7076 status=suspended spid=60 sbid=0 ecid=16 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process913ec988 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5494022 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x157f31950 lockMode=U schedulerid=2 kpid=7448 status=suspended spid=60 sbid=0 ecid=2 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process92d048 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5489656 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x89ddb2b0 lockMode=U schedulerid=3 kpid=5656 status=suspended spid=60 sbid=0 ecid=1 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process53e988 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5492976 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x8670edd0 lockMode=U schedulerid=9 kpid=7012 status=suspended spid=60 sbid=0 ecid=13 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process109a594c8 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5497583 waittime=3493 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x2272cdb80 lockMode=U schedulerid=5 kpid=10224 status=suspended spid=60 sbid=0 ecid=3 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process38e9654c8 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5494429 waittime=3493 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x2250f7a10 lockMode=U schedulerid=8 kpid=9544 status=suspended spid=60 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process23e2794c8 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5498057 waittime=3493 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x1b50b0930 lockMode=U schedulerid=4 kpid=7468 status=suspended spid=60 sbid=0 ecid=14 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process1e98ec748 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5496760 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x800276e0 lockMode=U schedulerid=6 kpid=2416 status=suspended spid=60 sbid=0 ecid=6 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process49a4748 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5495669 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x117a03640 lockMode=U schedulerid=7 kpid=7292 status=suspended spid=60 sbid=0 ecid=7 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process19912d288 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5494950 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0xc991c450 lockMode=U schedulerid=14 kpid=7136 status=suspended spid=60 sbid=0 ecid=8 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process5055b88 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5490967 waittime=3495 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x813782f0 lockMode=U schedulerid=16 kpid=7584 status=suspended spid=60 sbid=0 ecid=10 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process4fd3948 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5492300 waittime=3495 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x3a40bd310 lockMode=U schedulerid=11 kpid=2336 status=suspended spid=60 sbid=0 ecid=11 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process4db9948 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5490380 waittime=3495 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x2219b98b0 lockMode=U schedulerid=10 kpid=7476 status=suspended spid=60 sbid=0 ecid=9 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process5007048 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5499190 waittime=3491 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0xbe11f640 lockMode=U schedulerid=13 kpid=4016 status=suspended spid=60 sbid=0 ecid=15 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process503a988 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5493592 waittime=3495 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0x3a1387c80 lockMode=U schedulerid=15 kpid=3984 status=suspended spid=60 sbid=0 ecid=12 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=process1f95154c8 taskpriority=0 logused=604 waitresource=PAGE: 9:1:5498562 waittime=3494 ownerId=8499273233 transactionname=UPDATE lasttranstarted=2014-09-27T04:56:03.143 XDES=0xe99d58b0 lockMode=U schedulerid=1 kpid=3976 status=suspended spid=60 sbid=0 ecid=5 priority=0 trancount=0 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
process id=processbd0a2988 taskpriority=0 logused=10000 waittime=997 schedulerid=7 kpid=2468 status=suspended spid=76 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-09-27T04:56:01.750 lastbatchcompleted=2014-09-27T04:56:01.750 clientapp=PHP 5 hostname=web02 hostpid=145729 loginname=fusqldbuser isolationlevel=read committed (2) xactid=8499272252 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=78 sqlhandle=0x02000000f1b3642eacaeaaee4b366a48284c7678f5ea5494
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[last_modify_date] = getdate() WHERE %%=%%@4
frame procname=adhoc line=1 sqlhandle=0x0200000024555608ee2bb0e56f81e78175e0280c9a5719b7
update comm set newsletter=1,newbie=1,snailmail=1,last_modify_date=getdate()
where email='vanessa@mail.com'
inputbuf
update comm set newsletter=1,newbie=1,snailmail=1,last_modify_date=getdate()
where email='vanessa@mail.com'
process id=process276e6a508 taskpriority=0 logused=10000 waittime=3495 schedulerid=4 kpid=6712 status=suspended spid=60 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-09-27T04:56:03.143 lastbatchcompleted=2014-09-27T04:56:03.143 clientapp=PHP 5 hostname=web02 hostpid=8773 loginname=fusqldbuser isolationlevel=read committed (2) xactid=8499273233 currentdb=9 lockTimeout=4294967295 clientoption1=136445984 clientoption2=16416
executionStack
frame procname=monkey_DB.dbo.tgr_last_modify_date line=12 stmtstart=714 stmtend=924 sqlhandle=0x0300090040e62e38db9200016fa300000000000000000000
UPDATE [comm]
SET last_modify_date = GETDATE()
WHERE id IN (SELECT id FROM inserted);
frame procname=adhoc line=1 stmtstart=92 sqlhandle=0x02000000fa62a20c7e20f30a3988761c880dd4a14216231b
UPDATE [comm] set
= @1,[newbie] = @2,[snailmail] = @3,[custid] = @4,[last_modify_date] = getdate() WHERE %%=%%@5
frame procname=adhoc line=1 sqlhandle=0x020000004a1f8b08ccfeb62e7ab34e7c0e5cc90830bd2a59
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
inputbuf
update comm set newsletter=1,newbie=1,snailmail=1,custid=1904533,last_modify_date=getdate()
where email='emily@mail.com'
resource-list
pagelock fileid=1 pageid=7669491 dbid=9 objectname=monkey_DB.dbo.comm id=lock27c76de00 mode=IX associatedObjectId=72057626092240896
owner-list
owner id=process276e6a508 mode=IX
waiter-list
waiter id=process4db9048 mode=U requestType=wait
pagelock fileid=1 pageid=5491675 dbid=9 objectname=monkey_DB.dbo.comm id=lock39eeef180 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process1ccbb6748 mode=U requestType=wait
pagelock fileid=1 pageid=5494022 dbid=9 objectname=monkey_DB.dbo.comm id=lock2d549ac80 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process913ec988 mode=U requestType=wait
pagelock fileid=1 pageid=5489656 dbid=9 objectname=monkey_DB.dbo.comm id=lock114ae5680 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process92d048 mode=U requestType=wait
pagelock fileid=1 pageid=5492976 dbid=9 objectname=monkey_DB.dbo.comm id=lock1e0122a00 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process53e988 mode=U requestType=wait
pagelock fileid=1 pageid=5497583 dbid=9 objectname=monkey_DB.dbo.comm id=lock2d6a1fe80 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process109a594c8 mode=U requestType=wait
pagelock fileid=1 pageid=5494429 dbid=9 objectname=monkey_DB.dbo.comm id=lock2d52dcf80 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process38e9654c8 mode=U requestType=wait
pagelock fileid=1 pageid=5498057 dbid=9 objectname=monkey_DB.dbo.comm id=lock1bf100300 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process23e2794c8 mode=U requestType=wait
pagelock fileid=1 pageid=5496760 dbid=9 objectname=monkey_DB.dbo.comm id=lock2dc08f880 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process1e98ec748 mode=U requestType=wait
pagelock fileid=1 pageid=5495669 dbid=9 objectname=monkey_DB.dbo.comm id=lock2d6dfac80 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process49a4748 mode=U requestType=wait
pagelock fileid=1 pageid=5494950 dbid=9 objectname=monkey_DB.dbo.comm id=lock2d40c9800 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process19912d288 mode=U requestType=wait
pagelock fileid=1 pageid=5490967 dbid=9 objectname=monkey_DB.dbo.comm id=locka7bb0480 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=process4db9048 mode=U
waiter-list
waiter id=process5055b88 mode=U requestType=wait
pagelock fileid=1 pageid=5492300 dbid=9 objectname=monkey_DB.dbo.comm id=lock13556d200 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process4fd3948 mode=U requestType=wait
pagelock fileid=1 pageid=5490380 dbid=9 objectname=monkey_DB.dbo.comm id=lock392d54b80 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process4db9948 mode=U requestType=wait
pagelock fileid=1 pageid=5499190 dbid=9 objectname=monkey_DB.dbo.comm id=lock3bcb07980 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process5007048 mode=U requestType=wait
pagelock fileid=1 pageid=5493592 dbid=9 objectname=monkey_DB.dbo.comm id=lock1bef0c280 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process503a988 mode=U requestType=wait
pagelock fileid=1 pageid=5498562 dbid=9 objectname=monkey_DB.dbo.comm id=lock2d5173180 mode=U associatedObjectId=72057626092240896
owner-list
owner id=processbd0a2988 mode=U
owner id=processbd0a2988 mode=U
waiter-list
waiter id=process1f95154c8 mode=U requestType=wait
exchangeEvent id=Pipe217f06480 WaitType=e_waitPipeGetRow nodeId=4
owner-list
owner id=process4db9048
waiter-list
waiter id=processbd0a2988
exchangeEvent id=Pipe230a10480 WaitType=e_waitPipeGetRow nodeId=4
owner-list
owner id=process1f95154c8
owner id=process913ec988
owner id=process109a594c8
owner id=process38e9654c8
owner id=process23e2794c8
owner id=process1e98ec748
owner id=process92d048
owner id=process49a4748
owner id=process19912d288
owner id=process5055b88
owner id=process4fd3948
owner id=process4db9948
owner id=process5007048
owner id=process503a988
owner id=process53e988
owner id=process1ccbb6748
waiter-list
waiter id=process276e6a508
September 29, 2014 at 4:16 am
From my first impression of looking at the deadlocks, the trigger does seem to be causing the issue.
You mentioned that you have no indexes on the table, do you have the option of creating them if required?
Also, I take it the trigger is there to handle functionality that the application should be? Do you have the option of including an update to the last_modify_date in the code coming from the app?
September 29, 2014 at 4:58 am
Hi, actually there is one index on the table, solely on custid.
Yes, I can change the frontend app code to add the last_modify_date and remove the trigger from table.
I also have the ability to create any required indexes .. i am the DBA 😀 (didn't create this table or index though)
Thanks
September 29, 2014 at 6:18 am
bugg (9/29/2014)
Hi, actually there is one index on the table, solely on custid.Yes, I can change the frontend app code to add the last_modify_date and remove the trigger from table.
I also have the ability to create any required indexes .. i am the DBA 😀 (didn't create this table or index though)
Thanks
Just checking 🙂 I've seen these issues in apps which void service agreements by changes being made to the back end databases (think Sharepoint).
I'd definitely get the app code updated to remove the trigger. Then I'd look at an index on the comm table, on the id column (unless that's the one you mentioned.
September 30, 2014 at 1:22 am
DBA From The Cold (9/29/2014)
bugg (9/29/2014)
Hi, actually there is one index on the table, solely on custid.Yes, I can change the frontend app code to add the last_modify_date and remove the trigger from table.
I also have the ability to create any required indexes .. i am the DBA 😀 (didn't create this table or index though)
Thanks
Just checking 🙂 I've seen these issues in apps which void service agreements by changes being made to the back end databases (think Sharepoint).
I'd definitely get the app code updated to remove the trigger. Then I'd look at an index on the comm table, on the id column (unless that's the one you mentioned.
I plan on removing the trigger first and checking if that helps. There is an index on "custid". The "id" column is an auto-increment identity column which is the PK.
Thanks
September 30, 2014 at 1:28 am
I take it the PK on the table also has a clustered index?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply