June 4, 2010 at 10:05 am
Good morning,
So I am getting these deadlocks and I think I have traced down the root cause but I am not sure of the best way to handle this. I am thinking setting the indexoption on one table to specify only row locks and not page locks but I am not really sure that is the right answer.
So what I have determined.. There are 2 queries in the deadlock that are different unrelated tables. What it seems from the deadlock XML is that one is doing a page lock. If I examine the pages using the DBCC IND command, I see the 2 tables share some of the same PAGEPID numbers. This leads me to believe that because of the page lock from the one query it is locking the other table when they reside in the same page. How best to handle this? DEADLOCK XML at bottom.
Thanks,
Mike
This SELECT gets a key lock:
KEY: 5:72057597165568000 (11044b42d882)
select polcod from poldat (UPDLOCK) where polcod = 123 and polvar = 123 and polval = 1234 and wh_id_tmpl = 1234 and srtseq = 12345
The Update gets a Pagelock:
waitresource="PAGE: 5:5:394956
update pckwrk set lodnum = 1234 where lodnum = 12345
<TextData>
<deadlock-list>
<deadlock victim="process12de4718">
<process-list>
<process id="process12de4718" taskpriority="0" logused="5996" waitresource="KEY: 5:72057597165568000 (11044b42d882)" waittime="8015" ownerId="1174201882" transactionname="implicit_transaction" lasttranstarted="2010-06-04T12:20:13.660" XDES="0x35a72a370" lockMode="U" schedulerid="10" kpid="5108" status="suspended" spid="51" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2010-06-04T12:20:13.830" lastbatchcompleted="2010-06-04T12:20:13.830" clientapp="jTDS" hostname="DUMMY" hostpid="123" loginname="EU\AdminRP" isolationlevel="read uncommitted (1)" xactid="1174201882" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="119866">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="162" sqlhandle="0x0200000098b44501c8300c5024fba8e4984f07909ec0f134">
select polcod from poldat (UPDLOCK) where polcod = @P0 and polvar = @P1 and polval = @P2 and wh_id_tmpl = @P3 and srtseq = @P4 </frame>
</executionStack>
<inputbuf>
(@P0 varchar(8000),@P1 varchar(8000),@P2 varchar(8000),@P3 varchar(8000),@P4 int)select polcod from poldat (UPDLOCK) where polcod = @P0 and polvar = @P1 and polval = @P2 and wh_id_tmpl = @P3 and srtseq = @P4 </inputbuf>
</process>
<process id="process230b049b8" taskpriority="0" logused="16332" waitresource="PAGE: 5:5:394956" waittime="4468" ownerId="1174126063" transactionname="implicit_transaction" lasttranstarted="2010-06-04T12:20:02.413" XDES="0x6940aea90" lockMode="U" schedulerid="1" kpid="10528" status="suspended" spid="151" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-04T12:20:17.113" lastbatchcompleted="2010-06-04T12:20:17.113" clientapp="jTDS" hostname="DUMMY1" hostpid="123" loginname="EU\AdminRP" isolationlevel="read uncommitted (1)" xactid="1174126063" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="119866">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x02000000946f1e1393f93eecfdcd2246a0d32af00f4cb2b0">
update pckwrk set lodnum = @P0 where lodnum = @P1 </frame>
</executionStack>
<inputbuf>
(@P0 varchar(8000),@P1 varchar(8000))update pckwrk set lodnum = @P0 where lodnum = @P1 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="5" pageid="394956" dbid="5" objectname="dbo.pckwrk" id="lock245d2d780" mode="IX" associatedObjectId="72057597164388352">
<owner-list>
<owner id="process12de4718" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process230b049b8" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<keylock hobtid="72057597165568000" dbid="5" objectname="dbo.poldat" indexname="poldat_pk" id="lock3d256da00" mode="U" associatedObjectId="72057597165568000">
<owner-list>
<owner id="process230b049b8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process12de4718" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
June 4, 2010 at 10:54 am
It is impossible for a page to be shared between two tables. Pages will only ever contain rows from a single table.
Can you post definitions and index definitions of the two tables involved please? (poldat, pckwrk)
Is there a trigger on the pckwrk table? Are there any foreign keys on it or referencing it?
You've got implicit transactions enabled, so once a data change occurs, locks are going to be held until an explicit COMMIT TRAN occurs. Can you trace what other SQL statements would have run prior to the ones listed in the deadlock graph, within the same transaction.
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
June 4, 2010 at 11:31 am
I thought that was my undertstanding as well. but looking at the dbcc ind command shows shared PAGEPID. I rechecked there are no FK's and no triggers to these tables or any that reference these tables, so i am at a loss as to why they would be deadlocking each other. At the bottom of this post is the table structures.
From the command DBCC IND('dbname',PCKWRK,-1)
3502852460876770817808117.20576E+16In-row data1035028533502851
3502853460876770817808117.20576E+16In-row data1035028543502852
3502854460876770817808117.20576E+16In-row data1035028553502853
3502855460876770817808117.20576E+16In-row data1035028563502854
3502856460876770817808117.20576E+16In-row data1035028573502855
3502857460876770817808117.20576E+16In-row data1035028583502856
3502858460876770817808117.20576E+16In-row data1054851383502857
3502859460876770817808117.20576E+16In-row data1035028604487766
3502860460876770817808117.20576E+16In-row data1035028613502859
3502861460876770817808117.20576E+16In-row data1035028623502860
3502862460876770817808117.20576E+16In-row data1035028633502861
3502863460876770817808117.20576E+16In-row data1035028643502862
3502864460876770817808117.20576E+16In-row data1035028653502863
3502865460876770817808117.20576E+16In-row data1035028663502864
3502866460876770817808117.20576E+16In-row data1014492803502865
3502867460876770817808117.20576E+16In-row data1035028686472423
From the second table:
From the command DBCC IND('dbname',POLDAT,-1)
5502851614949222623836117.20576E+16In-row data1055028526489528
5502852614949222623836117.20576E+16In-row data1055028535502851
5502853614949222623836117.20576E+16In-row data1043150085502852
5502854614949222623836117.20576E+16In-row data10550285512964
5502855614949222623836117.20576E+16In-row data1055028565502854
5502856614949222623836117.20576E+16In-row data1055028575502855
5502857614949222623836117.20576E+16In-row data1055028585502856
5502858614949222623836117.20576E+16In-row data1055028595502857
5502859614949222623836117.20576E+16In-row data1055028605502858
5502860614949222623836117.20576E+16In-row data1055028615502859
5502861614949222623836117.20576E+16In-row data1043177255502860
5502862614949222623836117.20576E+16In-row data1055028636489549
5502863614949222623836117.20576E+16In-row data1055028645502862
5502864614949222623836117.20576E+16In-row data1055028655502863
5502865614949222623836117.20576E+16In-row data1033448195502864
5502866614949222623836117.20576E+16In-row data1055028673344819
5502867614949222623836117.20576E+16In-row data1055028685502866
5502868614949222623836117.20576E+16In-row data1055028695502867
5502869614949222623836117.20576E+16In-row data1045049895502868
4504984574401222623836117.20576E+16In-row data1045049854317727
4504985574401222623836117.20576E+16In-row data1045049864504984
4504986574401222623836117.20576E+16In-row data1045049874504985
4504987574401222623836117.20576E+16In-row data1045049884504986
4504988574401222623836117.20576E+16In-row data1033447634504987
4504989574401222623836117.20576E+16In-row data1045049905502869
4504990574401222623836117.20576E+16In-row data1045049914504989
4504991574401222623836117.20576E+16In-row data1045049924504990
4504992574401222623836117.20576E+16In-row data1045049934504991
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
pckwrk dbo user table 2009-02-24 10:10:53.907
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
wrkref varchar no 10 no no no SQL_Latin1_General_CP1_CI_AS
wrktyp varchar no 1 no no no SQL_Latin1_General_CP1_CI_AS
schbat varchar no 32 yes no yes SQL_Latin1_General_CP1_CI_AS
srcloc varchar no 20 no no no SQL_Latin1_General_CP1_CI_AS
dstloc varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
srcare varchar no 10 no no no SQL_Latin1_General_CP1_CI_AS
dstare varchar no 10 yes no yes SQL_Latin1_General_CP1_CI_AS
ship_line_id varchar no 10 yes no yes SQL_Latin1_General_CP1_CI_AS
ship_id varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
client_id varchar no 32 yes no yes SQL_Latin1_General_CP1_CI_AS
wh_id varchar no 32 no no no SQL_Latin1_General_CP1_CI_AS
ordnum varchar no 35 yes no yes SQL_Latin1_General_CP1_CI_AS
ordlin varchar no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
ordsln varchar no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
stcust varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
rtcust varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
concod varchar no 10 no no no SQL_Latin1_General_CP1_CI_AS
cmbcod varchar no 10 no no no SQL_Latin1_General_CP1_CI_AS
lblbat varchar no 6 yes no yes SQL_Latin1_General_CP1_CI_AS
lblseq varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
devcod varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
pckqty int no 4 10 0 no (n/a) (n/a) NULL
pck_catch_qty numeric no 9 19 4 yes (n/a) (n/a) NULL
appqty int no 4 10 0 no (n/a) (n/a) NULL
app_catch_qty numeric no 9 19 4 yes (n/a) (n/a) NULL
pcksts varchar no 1 no no no SQL_Latin1_General_CP1_CI_AS
prtnum varchar no 30 no no no SQL_Latin1_General_CP1_CI_AS
prt_client_id varchar no 32 no no no SQL_Latin1_General_CP1_CI_AS
orgcod varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
revlvl varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
supnum varchar no 32 yes no yes SQL_Latin1_General_CP1_CI_AS
lotnum varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
invsts varchar no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
invsts_prg varchar no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
lodlvl varchar no 1 no no no SQL_Latin1_General_CP1_CI_AS
lodnum varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
subnum varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
dtlnum varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
untcas int no 4 10 0 no (n/a) (n/a) NULL
untpak int no 4 10 0 no (n/a) (n/a) NULL
ftpcod varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
ctncod varchar no 2 yes no yes SQL_Latin1_General_CP1_CI_AS
ctnnum varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
ctnseg varchar no 10 yes no yes SQL_Latin1_General_CP1_CI_AS
loducc varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
subucc varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
skip_cnt int no 4 10 0 yes (n/a) (n/a) NULL
visflg int no 4 10 0 yes (n/a) (n/a) NULL
splflg int no 4 10 0 yes (n/a) (n/a) NULL
locflg int no 4 10 0 yes (n/a) (n/a) NULL
lodflg int no 4 10 0 yes (n/a) (n/a) NULL
subflg int no 4 10 0 yes (n/a) (n/a) NULL
dtlflg int no 4 10 0 yes (n/a) (n/a) NULL
prtflg int no 4 10 0 yes (n/a) (n/a) NULL
orgflg int no 4 10 0 yes (n/a) (n/a) NULL
revflg int no 4 10 0 yes (n/a) (n/a) NULL
supflg int no 4 10 0 yes (n/a) (n/a) NULL
lotflg int no 4 10 0 yes (n/a) (n/a) NULL
qtyflg int no 4 10 0 yes (n/a) (n/a) NULL
catch_qty_flg int no 4 10 0 yes (n/a) (n/a) NULL
adddte datetime no 8 yes (n/a) (n/a) NULL
pckdte datetime no 8 yes (n/a) (n/a) NULL
cmpdte datetime no 8 yes (n/a) (n/a) NULL
refloc varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
wkonum varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
wkorev varchar no 10 yes no yes SQL_Latin1_General_CP1_CI_AS
wkolin varchar no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
ackdevcod varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
cur_cas int no 4 10 0 yes (n/a) (n/a) NULL
tot_cas_cnt int no 4 10 0 yes (n/a) (n/a) NULL
pipflg int no 4 10 0 yes (n/a) (n/a) NULL
frsflg int no 4 10 0 yes (n/a) (n/a) NULL
clst_seq int no 4 10 0 yes (n/a) (n/a) NULL
min_shelf_hrs int no 4 10 0 yes (n/a) (n/a) NULL
prtdte datetime no 8 yes (n/a) (n/a) NULL
list_id varchar no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
list_seqnum int no 4 10 0 yes (n/a) (n/a) NULL
lodtag varchar no 40 yes no yes SQL_Latin1_General_CP1_CI_AS
subtag varchar no 40 yes no yes SQL_Latin1_General_CP1_CI_AS
lm_assign_num varchar no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
lm_assign_seqnum int no 4 10 0 yes (n/a) (n/a) NULL
lm_goal_seconds int no 4 10 0 yes (n/a) (n/a) NULL
pallet_pos varchar no 8 yes no yes SQL_Latin1_General_CP1_CI_AS
pallet_id varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
unassign_flg int no 4 10 0 yes (n/a) (n/a) NULL
asset_typ varchar no 30 yes no yes SQL_Latin1_General_CP1_CI_AS
pallet_load_seq int no 4 10 0 yes (n/a) (n/a) NULL
bto_seqnum varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
slot varchar no 10 yes no yes SQL_Latin1_General_CP1_CI_AS
unique_pallet_id varchar no 20 yes no yes SQL_Latin1_General_CP1_CI_AS
palctlsts varchar no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
rowid uniqueidentifier no 16 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
rowid
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pckwrk_ackdevcod nonclustered located on PRIMARY ackdevcod, wh_id
pckwrk_dstloc nonclustered located on PRIMARY dstloc
pckwrk_idx1 nonclustered located on PRIMARY srcloc, wh_id, prtnum, pcksts, prt_client_id
pckwrk_idx2 nonclustered located on PRIMARY cmbcod, pckqty
pckwrk_idx3 nonclustered located on PRIMARY ship_line_id
pckwrk_idx4 nonclustered located on PRIMARY ctnnum
pckwrk_idx5 nonclustered located on PRIMARY subnum
pckwrk_idx6 nonclustered located on PRIMARY lblbat
pckwrk_idx7 nonclustered located on PRIMARY schbat, pcksts
pckwrk_list_id nonclustered located on PRIMARY list_id
pckwrk_lodtag nonclustered located on PRIMARY lodtag
pckwrk_loducc nonclustered located on PRIMARY loducc
pckwrk_ord_cli_wh nonclustered located on PRIMARY ordnum, client_id, wh_id
pckwrk_pcksts nonclustered located on PRIMARY pcksts
pckwrk_pk clustered, unique, primary key located on PRIMARY wrkref
pckwrk_prtnumcltid nonclustered located on PRIMARY prtnum, prt_client_id
pckwrk_rowid nonclustered, unique located on PRIMARY rowid
pckwrk_ship_id nonclustered located on PRIMARY ship_id, cmbcod
pckwrk_subtag nonclustered located on PRIMARY subtag
pckwrk_subucc nonclustered located on PRIMARY subucc
pckwrk_wko nonclustered located on PRIMARY wkonum, wkorev, wkolin, wh_id, client_id
pckwrk_wrktyppckst nonclustered located on PRIMARY wrktyp, pcksts
VAR_PCKWRK_IDX1 nonclustered located on PRIMARY palctlsts, wh_id
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT on column rowid DF__pckwrk__rowid__41049384 (n/a) (n/a) (n/a) (n/a) (newid())
DEFAULT on column splflg DF__pckwrk__splflg__2FDA0782 (n/a) (n/a) (n/a) (n/a) ((1))
DEFAULT on column supflg DF__pckwrk__supflg__386F4D83 (n/a) (n/a) (n/a) (n/a) ((0))
DEFAULT on column unassign_flg DF__pckwrk__unassign__3F1C4B12 (n/a) (n/a) (n/a) (n/a) ((0))
CHECK on column catch_qty_flg pckwrk_cqty_flg_ck (n/a) (n/a) Enabled Is_For_Replication ([catch_qty_flg]=(0) OR [catch_qty_flg]=(1))
CHECK on column dtlflg pckwrk_dtlflg_ck (n/a) (n/a) Enabled Is_For_Replication ([dtlflg]=(0) OR [dtlflg]=(1))
CHECK on column frsflg pckwrk_frsflg_ck (n/a) (n/a) Enabled Is_For_Replication ([frsflg]=(0) OR [frsflg]=(1))
CHECK on column locflg pckwrk_locflg_ck (n/a) (n/a) Enabled Is_For_Replication ([locflg]=(0) OR [locflg]=(1))
CHECK on column lodflg pckwrk_lodflg_ck (n/a) (n/a) Enabled Is_For_Replication ([lodflg]=(0) OR [lodflg]=(1))
CHECK on column lotflg pckwrk_lotflg_ck (n/a) (n/a) Enabled Is_For_Replication ([lotflg]=(0) OR [lotflg]=(1))
CHECK on column orgflg pckwrk_orgflg_ck (n/a) (n/a) Enabled Is_For_Replication ([orgflg]=(0) OR [orgflg]=(1))
CHECK on column pipflg pckwrk_pipflg_ck (n/a) (n/a) Enabled Is_For_Replication ([pipflg]=(0) OR [pipflg]=(1))
PRIMARY KEY (clustered) pckwrk_pk (n/a) (n/a) (n/a) (n/a) wrkref
CHECK on column prtflg pckwrk_prtflg_ck (n/a) (n/a) Enabled Is_For_Replication ([prtflg]=(0) OR [prtflg]=(1))
CHECK on column qtyflg pckwrk_qtyflg_ck (n/a) (n/a) Enabled Is_For_Replication ([qtyflg]=(0) OR [qtyflg]=(1))
CHECK on column revflg pckwrk_revflg_ck (n/a) (n/a) Enabled Is_For_Replication ([revflg]=(0) OR [revflg]=(1))
CHECK on column splflg pckwrk_splflg_ck (n/a) (n/a) Enabled Is_For_Replication ([splflg]=(0) OR [splflg]=(1))
CHECK on column subflg pckwrk_subflg_ck (n/a) (n/a) Enabled Is_For_Replication ([subflg]=(0) OR [subflg]=(1))
CHECK on column supflg pckwrk_supflg_ck (n/a) (n/a) Enabled Is_For_Replication ([supflg]=(0) OR [supflg]=(1))
CHECK on column unassign_flg pckwrk_unasgnflgck (n/a) (n/a) Enabled Is_For_Replication ([unassign_flg]=(0) OR [unassign_flg]=(1))
CHECK on column visflg pckwrk_visflg_ck (n/a) (n/a) Enabled Is_For_Replication ([visflg]=(0) OR [visflg]=(1))
No foreign keys reference table 'pckwrk', or you do not have permissions on referencing tables.
Table is referenced by views
--------------------------------------------------------------------------------------------------------------------------------
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
poldat dbo user table 2009-02-24 10:08:31.180
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
polcod varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
polvar varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
polval varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
wh_id_tmpl varchar no 32 no no no SQL_Latin1_General_CP1_CI_AS
srtseq int no 4 10 0 no (n/a) (n/a) NULL
rtstr1 varchar no 150 yes no yes SQL_Latin1_General_CP1_CI_AS
rtstr2 varchar no 150 yes no yes SQL_Latin1_General_CP1_CI_AS
rtnum1 int no 4 10 0 yes (n/a) (n/a) NULL
rtnum2 int no 4 10 0 yes (n/a) (n/a) NULL
rtflt1 numeric no 9 19 4 yes (n/a) (n/a) NULL
rtflt2 numeric no 9 19 4 yes (n/a) (n/a) NULL
moddte datetime no 8 yes (n/a) (n/a) NULL
mod_usr_id varchar no 40 yes no yes SQL_Latin1_General_CP1_CI_AS
grp_nam varchar no 40 yes no yes 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
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
poldat_pk clustered, unique, primary key located on PRIMARY polcod, polvar, polval, wh_id_tmpl, srtseq
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT on column wh_id_tmpl DF__poldat__wh_id_tm__0E391C95 (n/a) (n/a) (n/a) (n/a) ('----')
PRIMARY KEY (clustered) poldat_pk (n/a) (n/a) (n/a) (n/a) polcod, polvar, polval, wh_id_tmpl, srtseq
No foreign keys reference table 'poldat', or you do not have permissions on referencing tables.
No views with schema binding reference table 'poldat'.
June 4, 2010 at 11:47 am
mike mcneer (6/4/2010)
I thought that was my undertstanding as well. but looking at the dbcc ind command shows shared PAGEPID.From the command DBCC IND('dbname',PCKWRK,-1)
3502852460876770817808117.20576E+16In-row data1035028533502851
From the command DBCC IND('dbname',POLDAT,-1)
5502851614949222623836117.20576E+16In-row data1055028526489528
5502852614949222623836117.20576E+16In-row data1055028535502851
No it doesn't.
PCKWRK:
File ID 3, PageNo 502852
POLDAT:
File ID 5, PageNo 502852
The two tables are in different files. Page IDs are only unique per file, not overall in a database.
Any chance of getting the CREATE TABLE statements? The reams of info that you've posted is not formatted all that well, and it's going to be a mess to sort out.
You've got implicit transactions enabled, so once a data change occurs, locks are going to be held until an explicit COMMIT TRAN occurs. That's almost certainly why the two statements are deadlocking, because other statements earlier in the transactions still hold locks.
Can you trace what other SQL statements would have run prior to the ones listed in the deadlock graph, within the same transaction.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply