Deadlocks after upgrade to SQLServer 2000

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?

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 Cost:(0/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 Cost:(0/0)
spid4     Victim Resource Owner:
spid4      ResType:LockOwner Stype:'OR' Mode: IX SPID:59 ECID:0
Ec:(0x5A11F9C8) Value:0x291adac0 Cost:(0/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] (
& #91;import_record_id
] & #91;uniqueidentifier
] NOT NULL CONSTRAINT [DF_ID
] DEFAULT
(newid()),
& #91;import_record_in
dex] [int] NOT NULL CONSTRAINT [DF_import_idx] 
DEFAULT (0),
[import_id] & #91;uniqueidentifier
] NULL ,
& #91;import_record_da
te] [datetime] NULL CONSTRAINT [DF_import_date]
 DEFAULT
(getdate()),
& #91;import_record_st
atus] [int] NULL CONSTRAINT & #91;DF_import_status
] 
DEFAULT (1),
& #91;import_record_te
xt] [varchar] (50) NULL ,
[field1] [varchar] (10) NULL ,
[field2] [varchar] (1) NULL ,
[field3] [varchar] (2) NULL ,
..
CONSTRAINT [PK_Import] PRIMARY KEY CLUSTERED
(
& #91;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,
Patrick

Patrick  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.com