Strange database deadlock

[Maggie Pong]

Hi,

I had a deadlock situation on SQLServer 2000. When I look at the
Locks / process ID screen on Enterprise Manager, for one of the
session, I see the object being one table and the index is on another
totally unrelated table's primary key. This is really odd since there
are no foreign keys tying these two tables together. They don't even
reference the same parent tables. These two tables are quite
dis-joint. Does anyone know why I will get this kind of lock?

Thanks,

Maggie :)

[Alexey Aksyonenko]

Re: Strange database deadlock

Maggie - run DBCC CHECKDB (msdb) to make sure you don't have
structural issues. Other than that - verify that the 2 really aren't
related somewhere.


mpong_imail@yah oo.com (Maggie Pong) wrote in message news:<a38e01d9. 0309251922.2bad c484@posting.go ogle.com>...[color=blue]
> Hi,
>
> I had a deadlock situation on SQLServer 2000. When I look at the
> Locks / process ID screen on Enterprise Manager, for one of the
> session, I see the object being one table and the index is on another
> totally unrelated table's primary key. This is really odd since there
> are no foreign keys tying these two tables together. They don't even
> reference the same parent tables. These two tables are quite
> dis-joint. Does anyone know why I will get this kind of lock?
>
> Thanks,
>
> Maggie :)[/color]

[Erland Sommarskog]

Re: Strange database deadlock

[posted and mailed, please reply in news]

Maggie Pong (mpong_imail@ya hoo.com) writes:[color=blue]
> I had a deadlock situation on SQLServer 2000. When I look at the
> Locks / process ID screen on Enterprise Manager, for one of the
> session, I see the object being one table and the index is on another
> totally unrelated table's primary key. This is really odd since there
> are no foreign keys tying these two tables together. They don't even
> reference the same parent tables. These two tables are quite
> dis-joint. Does anyone know why I will get this kind of lock?[/color]

The tables may have been accessed in the same user-defined transaction.

Now, if you say that these two tables are not accessed in the same
transaction anywhere in the system - or at least should not be, this
could be an indication that you have transactions that you fail to
commit. There are a couple of nasty ways where this could happen in
conjunction with command timeouts in client code, or some errors in
stored procedures.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp