July 20, 2009 at 5:10 am
Hi,
I am facing a strange issue.. where i have
Table A (Col1, Col2, Col3)
Col 1- PK - Clustered Index
Table B (Col1, Col2, Col3)
Col1 - PK - Identity column - Clustered Index
Col 2 - FK to Table A (Col 1) and a non-Clustered Index
when i try to insert into Table B with concurrent users - I see a dead lock on Table A
on pagelock
some info on the deadlock processes are
waiter id=process701deb8 mode=S requestType=convert
waiter id=process7031eb8 mode=S requestType=convert
owner id=process7031eb8 mode=IX
owner id=process701deb8 mode=IX
can anyone help??
July 20, 2009 at 6:21 am
Can you post the full definition of the tables and the entire deadlock graph please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2009 at 6:43 am
here is the deadlock info
ResType:LockOwner Stype:'OR'Xdes:0x0000000151736370 Mode: S SPID:114 BatchID:0 ECID:0 TaskProxy:(0x00000001FA2DE598) Value:0x4166a200 Cost:(0/22380)
Victim Resource Owner:
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
waiter id=process703bc18 mode=S requestType=convert
waiter id=process701d828 mode=S requestType=convert
waiter-list
owner id=process703bc18 mode=IX
owner id=process701d828 mode=IX
owner-list
pagelock fileid=1 pageid=374351 dbid=12 objectname=PerfTest.dbo.Policy id=lock1fa436380 mode=IX associatedObjectId=72057594551402496
resource-list
Proc [Database Id = 12 Object Id = 660405622]
inputbuf
)
TotalAmount
BankCharges
IsBankChargesApplicable
Pincode
City
Address
PayeeName
RefundAdjustedAmount
IsRefundAdjusted
CollectionBranchID
CreatedDate
CreatedBy
ExcessAmount
CollectionStatus
AmountAdjusted
AmountDue
ProductCode
EndorsementID
PolicyNumber
(
INSERT INTO PremiumCollection
frame procname=PerfTest.dbo.CreatePolicy line=186 stmtstart=24622 stmtend=32788 sqlhandle=0x03000c0076fd5c2758debc00499c00000100000000000000
executionStack
process id=process703bc18 taskpriority=0 logused=22380 waitresource=PAGE: 12:1:374351 waittime=3900 ownerId=62780226 transactionname=user_transaction lasttranstarted=2009-07-20T17:58:04.980 XDES=0x151736370 lockMode=S schedulerid=6 kpid=3268 status=suspended spid=114 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-20T17:58:04.977 lastbatchcompleted=2009-07-20T17:58:04.977 clientapp=.Net SqlClient Data Provider hostname=CIFE1002 hostpid=22984 loginname=sa isolationlevel=read committed (2) xactid=62780226 currentdb=12 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
Proc [Database Id = 12 Object Id = 660405622]
inputbuf
)
TotalAmount
BankCharges
IsBankChargesApplicable
Pincode
City
Address
PayeeName
RefundAdjustedAmount
IsRefundAdjusted
CollectionBranchID
CreatedDate
CreatedBy
ExcessAmount
CollectionStatus
AmountAdjusted
AmountDue
ProductCode
EndorsementID
PolicyNumber
(
INSERT INTO PremiumCollection
frame procname=PerfTest.dbo.CreatePolicy line=186 stmtstart=24622 stmtend=32788 sqlhandle=0x03000c0076fd5c2758debc00499c00000100000000000000
executionStack
process id=process701d828 taskpriority=0 logused=26580 waitresource=PAGE: 12:1:374351 waittime=3910 ownerId=62780371 transactionname=user_transaction lasttranstarted=2009-07-20T17:58:05.050 XDES=0x205d74ef0 lockMode=S schedulerid=3 kpid=7632 status=suspended spid=82 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-20T17:58:05.047 lastbatchcompleted=2009-07-20T17:58:05.047 clientapp=.Net SqlClient Data Provider hostname=CIFE1002 hostpid=22984 loginname=sa isolationlevel=read committed (2) xactid=62780371 currentdb=12 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
process-list
deadlock victim=process703bc18
deadlock-list
ResType:LockOwner Stype:'OR'Xdes:0x0000000151736370 Mode: S SPID:114 BatchID:0 ECID:0 TaskProxy:(0x00000001FA2DE598) Value:0x4166a200 Cost:(0/22380)
Requested By:
Grant List 2:
Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 660405622]
SPID: 82 ECID: 0 Statement Type: INSERT Line #: 186
Owner:0x00000001071C2900 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:82 ECID:0 XactLockInfo: 0x0000000205D74F28
Grant List 1:
PAGE: 12:1:374351 CleanCnt:4 Mode:IX Flags: 0x2
Node:2
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
ResType:LockOwner Stype:'OR'Xdes:0x0000000205D74EF0 Mode: S SPID:82 BatchID:0 ECID:0 TaskProxy:(0x000000011FEE0598) Value:0x8aeae00 Cost:(0/26580)
Requested By:
Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 660405622]
SPID: 114 ECID: 0 Statement Type: INSERT Line #: 186
Owner:0x0000000139213340 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:114 ECID:0 XactLockInfo: 0x00000001517363A8
Grant List 2:
Grant List 1:
PAGE: 12:1:374351 CleanCnt:4 Mode:IX Flags: 0x2
Node:1
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Wait-for graph
Deadlock encountered .... Printing deadlock information
July 20, 2009 at 6:57 am
Can you post the table and index definitions for the two tables please? I'm guessing it has something to do with the checks for matching foreign key records.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2009 at 7:14 am
Name Owner Type Created_datetime
Policy dbo user table 2008-06-30 21:42:48.217
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
PolicyNumber nvarchar no 80 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
QuoteNumber nvarchar no 80 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CustomerId int no 4 10 0 yes (n/a) (n/a) NULL
PartyId int no 4 10 0 yes (n/a) (n/a) NULL
RenewalFrequency nvarchar no 100 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
BranchId int no 4 10 0 no (n/a) (n/a) NULL
BusinessChannelId int no 4 10 0 no (n/a) (n/a) NULL
DevelopmentOfficerId int no 4 10 0 yes (n/a) (n/a) NULL
AgentId int no 4 10 0 yes (n/a) (n/a) NULL
BrokerId int no 4 10 0 yes (n/a) (n/a) NULL
ProductId int no 4 10 0 no (n/a) (n/a) NULL
CoverStartDate datetime no 8 yes (n/a) (n/a) NULL
CoverEndDate datetime no 8 yes (n/a) (n/a) NULL
CreatedBy nvarchar no 100 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CreatedDate datetime no 8 no (n/a) (n/a) NULL
LastUpdBy nvarchar no 100 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
LastUpdDate datetime no 8 yes (n/a) (n/a) NULL
VersionId int no 4 10 0 no (n/a) (n/a) NULL
LineOfBusinessId int no 4 10 0 no (n/a) (n/a) NULL
IRDASegment nvarchar no 100 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
IsActive tinyint no 1 3 0 no (n/a) (n/a) NULL
BusinessSource varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
IsVB64Complied bit no 1 yes (n/a) (n/a) NULL
Status varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
RenewalStopCode bit no 1 yes (n/a) (n/a) NULL
AutoDebit bit no 1 yes (n/a) (n/a) NULL
CollectionStatus varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
LoadId nvarchar no 40 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
PolicyXML nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
PolicySchedule nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CorporateAgentID int no 4 10 0 yes (n/a) (n/a) NULL
BankAssuranceID int no 4 10 0 yes (n/a) (n/a) NULL
IsCoInsurance tinyint no 1 3 0 yes (n/a) (n/a) NULL
CoInsuranceBusinessType nvarchar no 200 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
No identity column defined. NULL NULL NULL
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
index_name index_description index_keys
IX_Policy_Quote nonclustered, stats no recompute located on PRIMARY QuoteNumber, VersionId
PK__Policy__0C85DE4D clustered, unique, primary key, stats no recompute located on PRIMARY PolicyNumber
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
DEFAULT on column IsVB64Complied DF__Policy__IsVB64Complied (n/a) (n/a) (n/a) (n/a) ((0))
DEFAULT on column IsActive DF_Policy_IsActive (n/a) (n/a) (n/a) (n/a) ((1))
FOREIGN KEY FK__Policy__078C1F06 No Action Cascade Enabled Is_For_Replication QuoteNumber, VersionId
REFERENCES PerfTest.dbo.Quote (QuoteNumber, VersionId)
FOREIGN KEY FK__Policy__09746778 No Action No Action Enabled Is_For_Replication ProductId, IRDASegment
REFERENCES PerfTest.dbo.ProductMaster (ProductId, IRDASegment)
FOREIGN KEY FK__Policy__Customer__0880433F No Action No Action Enabled Is_For_Replication CustomerId
REFERENCES PerfTest.dbo.Customer (CustomerId)
FOREIGN KEY FK__Policy__LineOfBu__0697FACD No Action No Action Enabled Is_For_Replication LineOfBusinessId
REFERENCES PerfTest.dbo.LineOfBusiness (LineOfBusinessId)
FOREIGN KEY FK_Policy_Branch No Action No Action Enabled Is_For_Replication BranchId
REFERENCES PerfTest.dbo.Branch (BranchId)
FOREIGN KEY FK_Policy_Party No Action No Action Enabled Is_For_Replication PartyId
REFERENCES PerfTest.dbo.Party (PartyId)
PRIMARY KEY (clustered) PK__Policy__0C85DE4D (n/a) (n/a) (n/a) (n/a) PolicyNumber
Table is referenced by foreign key
PerfTest.dbo.PremiumCollection: FK__PremiumCo__Polic__0E391C95
No views with schema binding reference table 'Policy'.
Table 2
-------------
Name Owner Type Created_datetime
PremiumCollection dbo user table 2009-03-06 22:22:02.043
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
CollectionID int no 4 10 0 no (n/a) (n/a) NULL
PolicyNumber nvarchar no 80 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
EndorsementID int no 4 10 0 no (n/a) (n/a) NULL
AmountDue decimal no 9 18 2 no (n/a) (n/a) NULL
AmountAdjusted decimal no 9 18 2 yes (n/a) (n/a) NULL
ExcessAmount decimal no 9 18 2 yes (n/a) (n/a) NULL
ProductCode nvarchar no 400 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
DocumentType nvarchar no 200 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CreatedBy nvarchar no 100 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CreatedDate datetime no 8 yes (n/a) (n/a) NULL
LastUpdBy nvarchar no 100 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
LastUpdDate datetime no 8 yes (n/a) (n/a) NULL
CollectionStatus varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
DateOfRelaisation datetime no 8 yes (n/a) (n/a) NULL
CollectionBranchID int no 4 10 0 yes (n/a) (n/a) NULL
PayingSlipNo varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
PayingSlipDate datetime no 8 yes (n/a) (n/a) NULL
IsRefundAdjusted bit no 1 yes (n/a) (n/a) NULL
RefundAdjustedAmount decimal no 9 18 2 yes (n/a) (n/a) NULL
PayeeName varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
Address varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
City varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS
Pincode int no 4 10 0 yes (n/a) (n/a) NULL
ScrollNumber nvarchar no 160 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
IsScroll bit no 1 yes (n/a) (n/a) NULL
IsBankChargesApplicable bit no 1 yes (n/a) (n/a) NULL
BankCharges decimal no 9 18 2 yes (n/a) (n/a) NULL
TotalAmount decimal no 9 18 2 yes (n/a) (n/a) NULL
MovedToSAP bit no 1 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
CollectionID 1 1 0
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
index_name index_description index_keys
IX_CollectionBranchID nonclustered, stats no recompute located on PRIMARY CollectionBranchID
IX_CreatedDate nonclustered, stats no recompute located on PRIMARY CreatedDate
IX_PolicyNumber nonclustered, stats no recompute located on PRIMARY PolicyNumber
PK__PremiumCollectio__10566F31 clustered, unique, primary key, stats no recompute located on PRIMARY CollectionID
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
FOREIGN KEY FK__PremiumCo__Polic__0E391C95 No Action No Action Enabled Is_For_Replication PolicyNumber
REFERENCES PerfTest.dbo.Policy (PolicyNumber)
FOREIGN KEY FK__PremiumCollection_Branch No Action No Action Enabled Is_For_Replication CollectionBranchID
REFERENCES PerfTest.dbo.Branch (BranchId)
PRIMARY KEY (clustered) PK__PremiumCollectio__10566F31 (n/a) (n/a) (n/a) (n/a) CollectionID
Table is referenced by foreign key
PerfTest.dbo.BankGuaranteeTransaction: FK_BankGuaranteeTransaction_PremiumCollection
PerfTest.dbo.CashDepositTransaction: FK_CashDepositTransaction_PremiumCollection
PerfTest.dbo.DebitNote: FK_DebitNote_PremiumCol
PerfTest.dbo.PremiumBreakup: FK_PremiumBreakup_PremiumCollection
PerfTest.dbo.ReceiptDetails: FK_ReceiptDetails_PremiumCollection
No views with schema binding reference table 'PremiumCollection'.
July 21, 2009 at 12:54 am
Hi Gail,
Any observation?
Regards,
Ganesh
July 21, 2009 at 2:02 am
Be patient. You posted that just before 5pm my time yesterday. I'll take a look when I have several minutes to spend on it exclusively.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 2:30 am
Ok. Thanks Gail for your help.
July 21, 2009 at 3:57 pm
The only thing I can see at the moment that looks suspect is that all of your indexes have been set to not auto update stats. Is there a reason for that and how often do you manually update stats? How often are those indexes rebuilt?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2009 at 8:24 pm
Hi Gail,
Update stats & rebuild of indexes are happening on a daily basis.
July 22, 2009 at 12:19 am
I can't see any reason why that would be deadlocking then. Can you post the exec plan of one of those inserts? (saved as a .sqlplan file, zipped and attached)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2009 at 12:53 am
yep.. me too puzzled why this is happening.. here is the sqlplan & also the lock information when this insert is executed..
July 22, 2009 at 10:29 am
http://vinay-thakur.spaces.live.com/blog/cns!645E3FC14D5130F2!208.entry
my once cent...
HTH.
Vinay.
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
July 22, 2009 at 12:58 pm
One thing I forgot to ask...
Is this insert the only statement in the transaction, or is it in an explicit transaction along with other commands?
Is there a trigger on the PremiumCollection table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2009 at 9:13 pm
hi Gail, yes there are other transactions (one insert on the parent table policy) then there are few updates on the other child tables. After that this insert happens in premiumcollection table.. and there are no trigger in this table..
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply