October 25, 2011 at 11:21 pm
Hi,
I have been getting deadlock between a SELECT statement and an INSERT statement - the deadlock graph displays:
exec dbo.get_customer_search_records @mobilenumber='XXXXXX',@sortby='active' ->Request Mode S -> KeyLock (Index Name : idxCustomerSurname ) -> Owner Mode X -> INSERT INTO customer_contact (CUSTOMERCODE,CONTACTTYPEID,CONTACTVALUE,ISPRIMARYCONTACT,DATEEFFECTIVE) VALUES ('436738','1','XXXXXX','1','2011-10-25 16:32:07')
What I cannot understand is that the the idxCustomerSurname index is on the customer table and contains the customer.Surname field - so I do not know why an INSERT into
the customer_contact would make any difference....
I'm a bit new to locking so maybe I am missing somthing from the deadlock trace below - would be great if anyone could help......note I have masked the mobile number with XXXX
Thanks!
2011-10-25 16:32:09.960spid18sINSERT INTO customer_contact (CUSTOMERCODE,CONTACTTYPEID,CONTACTVALUE,ISPRIMARYCONTACT,DATEEFFECTIVE) VALUES ('436738','1','XXXXXXX','1','2011-10-25 16:32:07')
2011-10-25 16:32:09.960spid18s resource-list
2011-10-25 16:32:09.960spid18s keylock hobtid=72057594126598144 dbid=5 objectname=Swag_prod.dbo.customer indexname=idxCustomerSurname id=lock2338e0980 mode=X associatedObjectId=72057594126598144
2011-10-25 16:32:09.960spid18s owner-list
2011-10-25 16:32:09.960spid7sDeadlock encountered .... Printing deadlock information
2011-10-25 16:32:09.960spid7sWait-for graph
2011-10-25 16:32:09.960spid7s
2011-10-25 16:32:09.960spid7sNode:1
2011-10-25 16:32:09.960spid7sKEY: 5:72057594126598144 (2360b6903002) CleanCnt:2 Mode:X Flags: 0x1
2011-10-25 16:32:09.960spid7s Grant List 1:
2011-10-25 16:32:09.960spid7s Owner:0x0000000174F824C0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:64 ECID:0 XactLockInfo: 0x000000058DEA5990
2011-10-25 16:32:09.960spid7s SPID: 64 ECID: 0 Statement Type: INSERT Line #: 1
2011-10-25 16:32:09.960spid7s Input Buf: Language Event: INSERT INTO customer_contact (CUSTOMERCODE,CONTACTTYPEID,CONTACTVALUE,ISPRIMARYCONTACT,DATEEFFECTIVE) VALUES ('436738','1','xxxxxx','1','2011-10-25 16:32:07')
2011-10-25 16:32:09.960spid7sRequested by:
2011-10-25 16:32:09.960spid7s ResType:LockOwner Stype:'OR'Xdes:0x00000004A21579F0 Mode: S SPID:69 BatchID:0 ECID:0 TaskProxy:(0x0000000182E52538) Value:0xde80f000 Cost:(0/0)
2011-10-25 16:32:09.960spid7s
2011-10-25 16:32:09.960spid7sNode:2
2011-10-25 16:32:09.960spid7sOBJECT: 5:917578307:0 CleanCnt:2 Mode:S Flags: 0x1
2011-10-25 16:32:09.960spid7s Grant List 1:
2011-10-25 16:32:09.960spid7s Owner:0x000000044E37CC40 Mode: S Flg:0x40 Ref:2 Life:00000001 SPID:69 ECID:0 XactLockInfo: 0x00000004A2157A30
2011-10-25 16:32:09.960spid7s SPID: 69 ECID: 0 Statement Type: SELECT Line #: 1
2011-10-25 16:32:09.960spid7s Input Buf: Language Event: exec dbo.get_customer_search_records @mobilenumber=''xxxxxx',@sortby='active'
2011-10-25 16:32:09.960spid7sRequested by:
2011-10-25 16:32:09.960spid7s ResType:LockOwner Stype:'OR'Xdes:0x000000058DEA5950 Mode: IX SPID:64 BatchID:0 ECID:0 TaskProxy:(0x000000020E278538) Value:0x61550500 Cost:(0/1556)
2011-10-25 16:32:09.960spid7s
2011-10-25 16:32:09.960spid7sVictim Resource Owner:
2011-10-25 16:32:09.960spid7s ResType:LockOwner Stype:'OR'Xdes:0x00000004A21579F0 Mode: S SPID:69 BatchID:0 ECID:0 TaskProxy:(0x0000000182E52538) Value:0xde80f000 Cost:(0/0)
2011-10-25 16:32:09.960spid18sdeadlock-list
2011-10-25 16:32:09.960spid18s deadlock victim=process2421562c8
2011-10-25 16:32:09.960spid18s process-list
2011-10-25 16:32:09.960spid18s process id=process2421562c8 taskpriority=0 logused=0 waitresource=KEY: 5:72057594126598144 (2360b6903002) waittime=730 ownerId=31066221852 transactionname=INSERT EXEC lasttranstarted=2011-10-25T16:32:01.223 XDES=0x4a21579f0 lockMode=S schedulerid=4 kpid=4388 status=suspended spid=69 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2011-10-25T16:32:01.223 lastbatchcompleted=2011-10-25T16:32:01.223 clientapp=PHP MSSQL_70 hostpid=3440 loginname=orders_user isolationlevel=read committed (2) xactid=31066221852 currentdb=5 lockTimeout=4294967295 clientoption1=136314912 clientoption2=16416
2011-10-25 16:32:09.960spid18s executionStack
2011-10-25 16:32:09.960spid18s frame procname=adhoc line=1 stmtstart=728 sqlhandle=0x02000000717d540cd1c91b3313c5ad151c17f61884341932
2011-10-25 16:32:09.960spid18sSelect TOP 50 c.customercode
2011-10-25 16:32:09.960spid18sfrom customer as c
2011-10-25 16:32:09.960spid18sjoin customer_address as ca on c.DeliveryAddressID = ca.AddressID
2011-10-25 16:32:09.960spid18sjoin customer_status_history as csh on c.CustomerStatusHistoryID = csh.CustomerStatusHistoryID
2011-10-25 16:32:09.960spid18sjoin customer_status_lookup as csl on csh.StatusID = csl.CustomerStatusID
2011-10-25 16:32:09.960spid18sleft join customer_business as cb on c.BusinessID = cb.BusinessID left join customer_contact cc on c.CustomerCode = cc.CustomerCode where c.FirstName like @xfirstname + '%'
2011-10-25 16:32:09.960spid18s and c.Surname like @xsurname + '%'
2011-10-25 16:32:09.960spid18s and (ca.Postcode = @xpostcode or @xpostcode = '')and csh.statusid != @xcuststatusid and (cb.Businessname like @xcompanyname + '%' or @xcompanyname = '')
2011-10-25 16:32:09.960spid18s and ca.StreetName like @xstreetname + '%' and cc.Contactvalue like @xmobilenumber + '%' order by csh.statusid asc
2011-10-25 16:32:09.960spid18s frame procname=mssqlsystemresource.sys.sp_executesql line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000
2011-10-25 16:32:09.960spid18ssp_executesql
2011-10-25 16:32:09.960spid18s frame procname=Swag_prod.dbo.get_customer_search_records line=145 stmtstart=8720 stmtend=9234 sqlhandle=0x03000500570de95f1eae2601729f00000100000000000000
2011-10-25 16:32:09.960spid18sinsert into #searchresults
2011-10-25 16:32:09.960spid18sexec sp_executesql @sql,
2011-10-25 16:32:09.960spid18s@paramlist, @customerid,@companyname,@firstname, @surname, @streetname, @postcode, @phonenumber,@mobilenumber, @emailaddress, @rounds, @custstatusid,@ext_user_id,@promo_code,@promo_validation_code
2011-10-25 16:32:09.960spid18s frame procname=adhoc line=1 sqlhandle=0x01000500675d031660edc0d2010000000000000000000000
2011-10-25 16:32:09.960spid18sexec dbo.get_customer_search_records @mobilenumber=''xxxxxx',@sortby='active'
2011-10-25 16:32:09.960spid18s inputbuf
2011-10-25 16:32:09.960spid18sexec dbo.get_customer_search_records @mobilenumber=''xxxxxx',@sortby='active'
2011-10-25 16:32:09.960spid18s process id=process188ddf288 taskpriority=0 logused=1556 waitresource=OBJECT: 5:917578307:0 waittime=2525 ownerId=31066235912 transactionname=user_transaction lasttranstarted=2011-10-25T16:32:07.413 XDES=0x58dea5950 lockMode=IX schedulerid=1 kpid=7352 status=suspended spid=64 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-10-25T16:32:07.433 lastbatchcompleted=2011-10-25T16:32:07.430 clientapp=PHP MSSQL_70 hostpid=1952 loginname=orders_user isolationlevel=read committed (2) xactid=31066235912 currentdb=5 lockTimeout=4294967295
2011-10-25 16:32:09.960spid18s executionStack
2011-10-25 16:32:09.960spid18s frame procname=adhoc line=1 stmtstart=172 sqlhandle=0x02000000d4cda8302570261961df449d276de31ff0415af0
2011-10-25 16:32:09.960spid18sinsert into customer_contact ( CUSTOMERCODE , CONTACTTYPEID , CONTACTVALUE , ISPRIMARYCONTACT , DATEEFFECTIVE ) values ( @0 , @1 , @2 , @3 , @4 )
2011-10-25 16:32:09.960spid18s frame procname=adhoc line=1 sqlhandle=0x02000000bc21cf2f019698180ce88d5bb1e4eb8156a3756b
2011-10-25 16:32:09.960spid18sINSERT INTO customer_contact (CUSTOMERCODE,CONTACTTYPEID,CONTACTVALUE,ISPRIMARYCONTACT,DATEEFFECTIVE) VALUES ('436738','1','XXXXXXXXX','1','2011-10-25 16:32:07')
2011-10-25 16:32:09.960spid18s inputbuf
2011-10-25 16:32:09.960spid18s owner id=process188ddf288 mode=X
2011-10-25 16:32:09.960spid18s waiter-list
2011-10-25 16:32:09.960spid18s waiter id=process2421562c8 mode=S requestType=wait
2011-10-25 16:32:09.960spid18s objectlock lockPartition=0 objid=917578307 subresource=FULL dbid=5 objectname=Swag_prod.dbo.customer_contact id=lock5775f2500 mode=S associatedObjectId=917578307
2011-10-25 16:32:09.960spid18s owner-list
2011-10-25 16:32:09.960spid18s owner id=process2421562c8 mode=S
2011-10-25 16:32:09.960spid18s waiter-list
2011-10-25 16:32:09.960spid18s waiter id=process188ddf288 mode=IX requestType=wait
October 26, 2011 at 12:37 am
Please post relevant DDL statements (in this case definitely including indexes), it would help us a lot to see the whole picture.
I've seen similar scenarios, where a strange index has been used, since it is the smallest index containing the requested columns, but it is hard to tell without the DDL.
October 26, 2011 at 1:09 am
Hi JayK,
What I can see from it, table customer_contact is definitely used in your dynamic query
left join customer_contact cc on c.CustomerCode = cc.CustomerCode AND
and cc.Contactvalue like @xmobilenumber + '%'
Moreover, it seems like there could be referential integrity to be maintained as well between customer_contact and customer (if there is foreign key). Hence, you are facing this deadlock situation in a read-comitted isolation level.
As far as idxCustomerSurname is concerned, your and c.Surname like @xsurname + '%' part of the query is making an index hit. The solution for your problem is more than one and can be done iteratively. You must optimize your dynamic query to avoid the deadlock. Since you are making multiple joins in your query, efficient indexing is definitely required and that can be done according to the data stored in your database. One immediate index came to my mind to try would be on customer_contact (Contactvalue, customer code) where the order and number of the indexed columns should be decided according to your data and clustered index. But this could only work for this specific query. For bigger picture you may need different solution. For instance, if history table is huge, then you might be missing proper index on that table. So only you are in the best position to sort this out and optimize your dynamic query.
One other way to avoid this specific deadlock only (which may not be the best) is to divide the logic into two steps. First step would be to apply all the filters and joins before making join with customer_contact and application of the filter cc.Contactvalue like @xmobilenumber + '%' and store the data into a temporary table. Second Step is to make join with customer_contact and apply the required filter.
Another solution could be to set the isolation level to READ UNCOMMITTED for this query. But this I only use for the reports where I am sure I can get away with dirty reads etc.
October 26, 2011 at 1:15 am
For the community to help JayK more easily, below are the queries extracted from the log
Dynamic SQL
SELECT TOP 50
c.customercode
FROM customer AS c
JOIN customer_address AS ca
ON c.DeliveryAddressID = ca.AddressID
JOIN customer_status_history AS csh
ON c.CustomerStatusHistoryID = csh.CustomerStatusHistoryID
JOIN customer_status_lookup AS csl
ON csh.StatusID = csl.CustomerStatusID
LEFT JOIN customer_business AS cb
ON c.BusinessID = cb.BusinessID
LEFT JOIN customer_contact cc
ON c.CustomerCode = cc.CustomerCode
WHERE c.FirstName LIKE @xfirstname + '%'
AND c.Surname LIKE @xsurname + '%'
AND (
ca.Postcode = @xpostcode
OR @xpostcode = ''
)
AND csh.statusid != @xcuststatusid
AND (
cb.Businessname LIKE @xcompanyname + '%'
OR @xcompanyname = ''
)
AND ca.StreetName LIKE @xstreetname + '%'
AND cc.Contactvalue LIKE @xmobilenumber + '%'
ORDER BY csh.statusid ASC
INSERT QUERY
INSERT INTO customer_contact (CUSTOMERCODE,CONTACTTYPEID,CONTACTVALUE,ISPRIMARYCONTACT,DATEEFFECTIVE) VALUES ('436738','1','xxxxxx','1','2011-10-25 16:32:07')
October 26, 2011 at 3:23 pm
Hey - Thanks for the heads up - I have now masked that contact value....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply