Since we upgraded from SQLServer 7 to 2000 we get deadlocks that we didnt
get before. After upgrading we did a transfer of the database, rebuild
indexes, update statistics. As much as I know, nothing has changed in our
application code. Maybe someone can help?
Here is an example:
spid4 Node:1
spid4 PAG: 8:1:2412 CleanCnt:1 Mode: SIU Flags: 0x2
spid4 Grant List 2::
spid4 Owner:0x292d8520 Mode: S Flg:0x0 Ref:0 Life:00000001
SPID:59 ECID:0
spid4 SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
spid4 Input Buf: Language Event: UPDATE Import SET
import_record_status = 0 ,import_record_text = NULL WHERE import_record_inde
x
BETWEEN 0 AND 50 AND import_record_status > 0
spid4 Requested By:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:56 ECID:0
Ec
0x4FF839C8) Value:0x291ff8c0 Cost0/0)spid4
spid4 Node:2
spid4 PAG: 8:1:45964 CleanCnt:1 Mode: SIU Flags: 0x2
spid4 Grant List 2::
spid4 Owner:0x29400060 Mode: S Flg:0x0 Ref:0 Life:00000001
SPID:56 ECID:0
spid4 SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 1
spid4 Input Buf: Language Event: UPDATE Import SET
import_record_status = 0 ,import_record_text = NULL WHERE import_record_inde
x
BETWEEN 0 AND 50 AND import_record_status > 0
spid4 Requested By:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec
0x5A11F9C8) Value:0x291adac0 Cost0/0)spid4 Victim Resource Owner:
spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec
0x5A11F9C8) Value:0x291adac0 Cost0/0)spid4
This looks like a resource order conflict, where SPID 59 holds a Shared lock
on page A and request a IX lock on page B, while SPID 56 holds a Shared lock
on Page B and requests IX on Page A. As much as I could see using Profiler,
the statements are not within a transaction, transaction isolation level is
READ COMMITTED. How can this happen? And how can I avoid this?
Page information (ObjId 1892917815 = Import):
m_pageId = (1:2412) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:89846)
m_nextPage = (1:60805) pminlen = 52 m_slotCnt = 9
m_pageId = (1:45964) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1892917815 m_indexId = 0 m_prevPage = (1:2075)
m_nextPage = (1:62881) pminlen = 52 m_slotCnt = 12
Table definition:
CREATE TABLE [Import] (
[import_record_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ID
] DEFAULT
(newid()),
[import_record_index] [int] NOT NULL CONSTRAINT [DF_import_idx]
DEFAULT (0),
[import_id] [uniqueidentifier] NULL ,
[import_record_date] [datetime] NULL CONSTRAINT [DF_import_date]
DEFAULT
(getdate()),
[import_record_status] [int] NULL CONSTRAINT [DF_import_status]
DEFAULT (1),
[import_record_text] [varchar] (50) NULL ,
[field1] [varchar] (10) NULL ,
[field2] [varchar] (1) NULL ,
[field3] [varchar] (2) NULL ,
..
CONSTRAINT [PK_Import] PRIMARY KEY CLUSTERED
(
[import_record_id]
)
One problem maybe could be the clustered index on uniqueidentifier? There
are no other indexes on this table. I tried to change the PK to be a non
clustered index and defined a clustered one over date/index, but cant tell
yet if this helps or not. I have more examples if this could be useful.
Thanks,
PatrickPatrick wrote:
> Hi,
> Since we upgraded from SQLServer 7 to 2000 we get deadlocks that we
> didnt get before. After upgrading we did a transfer of the database,
> rebuild indexes, update statistics. As much as I know, nothing has
> changed in our application code. Maybe someone can help?
> <SNIP>
Well, you're correct that a clustered index on a UNIQUEIDENTIFIER can
cause problems on a busy table. it will likely cause page splitting as
each row is inserted into the table, which will slow down the
transaction, cause excessive page reads and writes, increase blocking
times, and make it more likely that a deadlock can occur. If you've made
the change in the clustered index already, then you may just want to
wait and see if it helps. If not, examine the performance of your
queries to make sure they are running in an optimized fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.comsql
