February 26, 2013 at 11:44 am
Hi,
I am seeing lots dead lock. I am working on it to resolve the same. Need your help on this to understand the graph.
deadlock-list
deadlock victim=processc59288
process-list
process id=processc59288 taskpriority=0 logused=0 waitresource=PAGE: 10:1:1190365 waittime=27806 ownerId=83601549 transactionname=SELECT lasttranstarted=2013-02-26T06:02:36.803 XDES=0x1b4b8bb30 lockMode=IS schedulerid=2 kpid=2160 status=suspended spid=84 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-02-26T06:02:32.870 lastbatchcompleted=2013-02-26T06:02:32.870 clientapp=.Net SqlClient Data Provider hostname=MMMMM hostpid=3064 loginname=XXXX isolationlevel=read committed (2) xactid=83601549 currentdb=10 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=iQuoteRetail.dbo.spUpdateQuoteItemProductValues line=81 stmtstart=4986 stmtend=5814 sqlhandle=0x03000a001d1462294c422201cf9f00000100000000000000
SELECT @StartListPrice = convert(Decimal(13,2),PRC_LST_SU ),
@StartCurrency = currency,
@ListPriceAsOf = convert(datetime,mtListPrice_view.ZVALIDFROM)
FROM
mtListPrice_view
WHERE MATERIAL = @ProductSKU AND PRICE_LIST = @priceListType AND PRICE_GRP = @priceListGrp
AND @ValidFrom BETWEEN convert(datetime,mtListPrice_view.ZVALIDFROM) AND convert(datetime,mtListPrice_view.ZVALIDTO )
frame procname=iQuoteRetail.dbo.spRefreshQuoteItems line=66 stmtstart=2766 stmtend=2906 sqlhandle=0x03000a007d70cb107bca2301899f00000100000000000000
EXEC [dbo].[spUpdateQuoteItemProductValues] @QuoteItemID
frame procname=iQuoteRetail.dbo.spGetQuoteItemDirectRetail line=20 stmtstart=1212 stmtend=1370 sqlhandle=0x03000a00ab049e1be3491e01c2a000000100000000000000
EXEC [dbo].[spRefreshQuoteItems] @QuoteID -- April 20 2011 updating values
inputbuf
Proc [Database Id = 10 Object Id = 463340715]
process id=processc73948 taskpriority=0 logused=347350816 waitresource=OBJECT: 10:2137058649:0 waittime=4947 ownerId=83590209 transactionguid=0x896855ad90aa7c4894c8091c5e877df0 transactionname=DTCXact lasttranstarted=2013-02-26T06:02:24.237 XDES=0x34ea15620 lockMode=X schedulerid=3 kpid=1592 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2013-02-26T06:02:59.660 lastbatchcompleted=2013-02-26T06:02:59.620 clientapp=Microsoft SQL Server hostname=MMMMM hostpid=7040 loginname=XXXXX isolationlevel=read uncommitted (1) xactid=83590209 currentdb=10 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x02000000154ea90333e9178220bc511a6370c13f6420b291
insert bulk [dbo].[mtListPrice]([MATERIAL] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PRICE_LIST] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PRICE_GRP] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[BIC ZCONTYPE] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PRC_LST_SU] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[CURRENCY] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[ZVALIDTO] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[ZVALIDFROM] varchar(50) collate SQL_Latin1_General_CP1_CI_AS)with(TABLOCK,CHECK_CONSTRAINTS)
inputbuf
insert bulk [dbo].[mtListPrice]([MATERIAL] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PRICE_LIST] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PRICE_GRP] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[BIC ZCONTYPE] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[PRC_LST_SU] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[CURRENCY] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[ZVALIDTO] varchar(50) collate SQL_Latin1_General_CP1_CI_AS,[ZVALIDFROM] varchar(50) collate SQL_Latin1_General_CP1_CI_AS)with(TABLOCK,CHECK_CONSTRAINTS)
resource-list
pagelock fileid=1 pageid=1190365 dbid=10 objectname=iQuoteRetail.dbo.mtListPrice id=lock1724dae00 mode=X associatedObjectId=72057594054508544
owner-list
owner id=processc73948 mode=X
waiter-list
waiter id=processc59288 mode=IS requestType=wait
objectlock lockPartition=0 objid=2137058649 subresource=FULL dbid=10 objectname=iQuoteRetail.dbo.mtListPrice id=lock1723bfa80 mode=IX associatedObjectId=2137058649
owner-list
owner id=processc59288 mode=IS
waiter-list
waiter id=processc73948 mode=X requestType=convert
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 26, 2013 at 11:59 am
1. Select statement : owner id=processc59288 is a 'SELECT ... FROM dbo.mtListPrice_view
' statement taking a shared lock on BASE table [dbo].[mtListPrice]
2. Insert statement on BASE table [dbo].[mtListPrice] is taking an exclusive X lock.
3. An X lock can be taken only when S or IS lock is released. This looks like a problem of conversion lock.
4. Do you have SNAPSHOT isolation on your DB?
hope this helps.
February 27, 2013 at 7:43 am
asiaindian (2/26/2013)
1. Select statement : owner id=processc59288 is a 'SELECT ... FROM dbo.mtListPrice_view' statement taking a shared lock on BASE table [dbo].[mtListPrice]
2. Insert statement on BASE table [dbo].[mtListPrice] is taking an exclusive X lock.
3. An X lock can be taken only when S or IS lock is released. This looks like a problem of conversion lock.
4. Do you have SNAPSHOT isolation on your DB?
hope this helps.
asiaindian,Thank you for taking your valuable time to answering me.
Reading all dead lock graph.Planning to do that once done.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply