Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on th
e
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:

> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > o
n the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks
.
Are there any indexes supporting the where clause and if so how selective ar
e
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:
>

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apf
What isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||The default - Read Committed
|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed
|||Can you post the deadlock trace?
"apf" wrote:

> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
> Can you post the deadlock trace?
>
> "apf" wrote:
> > Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> > same table can cause a deadlock?
> >
> > The output to trace flag 1204 indicates that deadlocking in occuring on 2
> > statments that look like this:
> >
> > select count(*) from table_1 where ...
> >
> > The where criteria is different for the 2 SELECTS.
> >
> > Thanks in advance for any ideas or guesses!!
> >
> > apf

Deadlocks

I have in a table with some 2000 records with a Document ID which is a Unique Seq Number and details with a Status Flag. Multiple Users access the Table. When a user accesses a particular Document ID the Status flag changes from "N" to "W". Once he finishes working with data on that particular Document ID the Status Changes to "C". When another user tries to pickup a Record the next record in the Seq with the Status as "N" will get fetched. I use a Stored Procedure to assign a Document id to a user and update the Record/status details in the Table. I have used the No lock clause in the SP while retrieving a particular Document ID. It was working fine till sometime. Now it has started giving the following error

Error: Run-time error '-2147467259(80004005)'

[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.

Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL

Good luck !

Deadlock: Trace flag 1205, 1204

I want to log deadlocks. In query analyzer I ran dbcc
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

Deadlock problem

Hello,
I am battling various deadlocks on our system and have the trace flag
1204 turned on so that I get information on what was involved in the
deadlock. Today, however, I saw something that I've never seen before
(see below). What does 'Port' mean? How can I figure out what exactly
was involved in the deadlock.
Node:2
Port: 0x42c30a80 Xid Slot: 0, EC: 0x668115a0, ECID: 0 (Coordinator),
Exchange Wait Type :e_etypeCXPacket
Coordinator: EC = 0x668115a0, SPID: 98, ECID: 0, Not Blocking
Consumer List::
Consumer: Xid Slot: 0, EC = 0x668115a0, SPID: 98, ECID: 0, Not Blocking
Producer List::
Producer: Xid Slot: 1, EC = 0x3f25c0c0, SPID: 98, ECID: 3, Blocking
Producer: Xid Slot: 2, EC = 0x47b0e0c0, SPID: 98, ECID: 1, Blocking
Producer: Xid Slot: 3, EC = 0x3d1280c0, SPID: 98, ECID: 2, Blocking
Producer: Xid Slot: 4, EC = 0x3f8d40c0, SPID: 98, ECID: 4, Blocking
Thanks.Just curious how to read it outside the port as well ? ;) Would be nice to
know what that translates to .
"Frank Rizzo" <none@.none.com> wrote in message
news:emogpuFKGHA.1320@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am battling various deadlocks on our system and have the trace flag 1204
> turned on so that I get information on what was involved in the deadlock.
> Today, however, I saw something that I've never seen before (see below).
> What does 'Port' mean? How can I figure out what exactly was involved in
> the deadlock.
>
>
> Node:2
> Port: 0x42c30a80 Xid Slot: 0, EC: 0x668115a0, ECID: 0 (Coordinator),
> Exchange Wait Type :e_etypeCXPacket
> Coordinator: EC = 0x668115a0, SPID: 98, ECID: 0, Not Blocking
> Consumer List::
> Consumer: Xid Slot: 0, EC = 0x668115a0, SPID: 98, ECID: 0, Not Blocking
> Producer List::
> Producer: Xid Slot: 1, EC = 0x3f25c0c0, SPID: 98, ECID: 3, Blocking
> Producer: Xid Slot: 2, EC = 0x47b0e0c0, SPID: 98, ECID: 1, Blocking
> Producer: Xid Slot: 3, EC = 0x3d1280c0, SPID: 98, ECID: 2, Blocking
> Producer: Xid Slot: 4, EC = 0x3f8d40c0, SPID: 98, ECID: 4, Blocking
> Thanks.

Deadlock Issue

Hi ,
I always face the Deadlock issue in our production DB.We are not running any
Profiler nor any the Error Flag is set ON.The job fails and we trace the LOG
file to check the error. We are not supposed to run any of these.Is there an
y
way to check the DEADLOCK Issue after it has occured,such as to Trace back
the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LOGs
or any other option.
KINDLY HELP ME ON THIS ASAP!
Thanks in advance.
Regards,
ShyamIf you haven't set anything up to capture the deadlock info, I'm afraid ther
e
is not much you can do to analyze the deadlocks that already took place. One
of the most effective ways to capture and analyze deadlocks is set up trace
falg 1204 at startup (i.e. add -T1204 as a startup parameter from Enterprise
Manager).

> We are not supposed to run any of these.
Well, I'm not sure who set the rule. But if you are expected to solve
problems, you've got to have access to proper tools.
Linchi
"Shyam" wrote:

> Hi ,
> I always face the Deadlock issue in our production DB.We are not running a
ny
> Profiler nor any the Error Flag is set ON.The job fails and we trace the L
OG
> file to check the error. We are not supposed to run any of these.Is there
any
> way to check the DEADLOCK Issue after it has occured,such as to Trace back
> the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LO
Gs
> or any other option.
> KINDLY HELP ME ON THIS ASAP!
> Thanks in advance.
> Regards,
> Shyam

Deadlock Issue

Hi ,
I always face the Deadlock issue in our production DB.We are not running any
Profiler nor any the Error Flag is set ON.The job fails and we trace the LOG
file to check the error. We are not supposed to run any of these.Is there any
way to check the DEADLOCK Issue after it has occured,such as to Trace back
the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LOGs
or any other option.
KINDLY HELP ME ON THIS ASAP!
Thanks in advance.
Regards,
ShyamIf you haven't set anything up to capture the deadlock info, I'm afraid there
is not much you can do to analyze the deadlocks that already took place. One
of the most effective ways to capture and analyze deadlocks is set up trace
falg 1204 at startup (i.e. add -T1204 as a startup parameter from Enterprise
Manager).
> We are not supposed to run any of these.
Well, I'm not sure who set the rule. But if you are expected to solve
problems, you've got to have access to proper tools.
Linchi
"Shyam" wrote:
> Hi ,
> I always face the Deadlock issue in our production DB.We are not running any
> Profiler nor any the Error Flag is set ON.The job fails and we trace the LOG
> file to check the error. We are not supposed to run any of these.Is there any
> way to check the DEADLOCK Issue after it has occured,such as to Trace back
> the issue.Like, from the SQL Mgmt option from Ent Manager,or SQL Server LOGs
> or any other option.
> KINDLY HELP ME ON THIS ASAP!
> Thanks in advance.
> Regards,
> Shyam

Deadlock : What the meaning of associatedObjectId

In 2005, when 1222 trace flag is activated, I get in the error log file, among other very useful informations, this kind of message
keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=... id=lock13c50480 mode=S associatedObjectId=72057594039304192
or
pagelock fileid=X pageid=xxxx dbid=XX objectname=.... id=lock5f69480 mode=IX associatedObjectId=72057594378518528
What is the meaning of associatedObjectId?
It always starts with 72057594xxxxx and does not look like it is related to the locked object
In my case, OBJECT_NAME and OBJECT_ID did not return any relevant information
Thanks
Med
Hi
This should match a waitresource in the process list and in the waiter list,
you should also should also see that database id for this which is probably 2
ie tempdb.
Check out Bart Duncan Blog
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
John
"Med Bouchenafa" wrote:

> In 2005, when 1222 trace flag is activated, I get in the error log file, among other very useful informations, this kind of message
> keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=... id=lock13c50480 mode=S associatedObjectId=72057594039304192
> or
> pagelock fileid=X pageid=xxxx dbid=XX objectname=.... id=lock5f69480 mode=IX associatedObjectId=72057594378518528
>
> What is the meaning of associatedObjectId?
> It always starts with 72057594xxxxx and does not look like it is related to the locked object
> In my case, OBJECT_NAME and OBJECT_ID did not return any relevant information
> Thanks
> Med
>
|||Med,
I don't know if the associatedObjectId will help you any, although you do
have the dbid and the objectname to focus your attention. From the Books
Online article "Detecting and Ending Deadlocks"
http://msdn2.microsoft.com/en-us/library/ms178104.aspx :
associatedObjectId. Represents the HoBT (heap or b-tree) ID.
RLF
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:%23znq0EVTIHA.5516@.TK2MSFTNGP02.phx.gbl...
In 2005, when 1222 trace flag is activated, I get in the error log file,
among other very useful informations, this kind of message
keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=...
id=lock13c50480 mode=S associatedObjectId=72057594039304192
or
pagelock fileid=X pageid=xxxx dbid=XX objectname=....
id=lock5f69480 mode=IX associatedObjectId=72057594378518528
What is the meaning of associatedObjectId?
It always starts with 72057594xxxxx and does not look like it is related to
the locked object
In my case, OBJECT_NAME and OBJECT_ID did not return any relevant
information
Thanks
Med
|||You're right...
That's the way it is documented in the books online.
To have the object name, I run a query like this
SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE partition_id =
@.associatedObjectId
and it worked
What surprised me is the fact that they all started with 72057594xxxxx
It looks like partion_id is a component of two or more values...
Thanks again
Med
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uSgP$qVTIHA.5404@.TK2MSFTNGP03.phx.gbl...
> Med,
> I don't know if the associatedObjectId will help you any, although you do
> have the dbid and the objectname to focus your attention. From the Books
> Online article "Detecting and Ending Deadlocks"
> http://msdn2.microsoft.com/en-us/library/ms178104.aspx :
> associatedObjectId. Represents the HoBT (heap or b-tree) ID.
> RLF
> "Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
> news:%23znq0EVTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> In 2005, when 1222 trace flag is activated, I get in the error log file,
> among other very useful informations, this kind of message
> keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=...
> id=lock13c50480 mode=S associatedObjectId=72057594039304192
> or
> pagelock fileid=X pageid=xxxx dbid=XX
> objectname=.... id=lock5f69480 mode=IX
> associatedObjectId=72057594378518528
>
> What is the meaning of associatedObjectId?
> It always starts with 72057594xxxxx and does not look like it is related
> to the locked object
> In my case, OBJECT_NAME and OBJECT_ID did not return any relevant
> information
> Thanks
> Med
>
>

Deadlock : What the meaning of associatedObjectId

In 2005, when 1222 trace flag is activated, I get in the error log file, amo
ng other very useful informations, this kind of message
keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=...
id=lock13c50480 mode=S associatedObjectId=72057594039304192
or
pagelock fileid=X pageid=xxxx dbid=XX objectname=....
id=lock5f69480 mode=IX associatedObjectId=7205759437851852
8
What is the meaning of associatedObjectId?
It always starts with 72057594xxxxx and does not look like it is related to
the locked object
In my case, OBJECT_NAME and OBJECT_ID did not return any relevant informatio
n
Thanks
MedHi
This should match a waitresource in the process list and in the waiter list,
you should also should also see that database id for this which is probably
2
ie tempdb.
Check out Bart Duncan Blog
http://blogs.msdn.com/bartd/archive...
-1.aspx
John
"Med Bouchenafa" wrote:

> In 2005, when 1222 trace flag is activated, I get in the error log file, a
mong other very useful informations, this kind of message
> keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=...
id=lock13c50480 mode=S associatedObjectId=72057594039304192
> or
> pagelock fileid=X pageid=xxxx dbid=XX objectname=...
. id=lock5f69480 mode=IX associatedObjectId=72057594378518
528
>
> What is the meaning of associatedObjectId?
> It always starts with 72057594xxxxx and does not look like it is related
to the locked object
> In my case, OBJECT_NAME and OBJECT_ID did not return any relevant informat
ion
> Thanks
> Med
>|||Med,
I don't know if the associatedObjectId will help you any, although you do
have the dbid and the objectname to focus your attention. From the Books
Online article "Detecting and Ending Deadlocks"
http://msdn2.microsoft.com/en-us/library/ms178104.aspx :
associatedObjectId. Represents the HoBT (heap or b-tree) ID.
RLF
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:%23znq0EVTIHA.5516@.TK2MSFTNGP02.phx.gbl...
In 2005, when 1222 trace flag is activated, I get in the error log file,
among other very useful informations, this kind of message
keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=...
id=lock13c50480 mode=S associatedObjectId=72057594039304192
or
pagelock fileid=X pageid=xxxx dbid=XX objectname=....
id=lock5f69480 mode=IX associatedObjectId=72057594378518528
What is the meaning of associatedObjectId?
It always starts with 72057594xxxxx and does not look like it is related to
the locked object
In my case, OBJECT_NAME and OBJECT_ID did not return any relevant
information
Thanks
Med|||You're right...
That's the way it is documented in the books online.
To have the object name, I run a query like this
SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE partition_id =
@.associatedObjectId
and it worked
What surprised me is the fact that they all started with 72057594xxxxx
It looks like partion_id is a component of two or more values...
Thanks again
Med
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uSgP$qVTIHA.5404@.TK2MSFTNGP03.phx.gbl...
> Med,
> I don't know if the associatedObjectId will help you any, although you do
> have the dbid and the objectname to focus your attention. From the Books
> Online article "Detecting and Ending Deadlocks"
> http://msdn2.microsoft.com/en-us/library/ms178104.aspx :
> associatedObjectId. Represents the HoBT (heap or b-tree) ID.
> RLF
> "Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
> news:%23znq0EVTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> In 2005, when 1222 trace flag is activated, I get in the error log file,
> among other very useful informations, this kind of message
> keylock hobtid=xxxxxxx dbid=XX indexname=...objectname=...
> id=lock13c50480 mode=S associatedObjectId=72057594039304192
> or
> pagelock fileid=X pageid=xxxx dbid=XX
> objectname=.... id=lock5f69480 mode=IX
> associatedObjectId=72057594378518528
>
> What is the meaning of associatedObjectId?
> It always starts with 72057594xxxxx and does not look like it is related
> to the locked object
> In my case, OBJECT_NAME and OBJECT_ID did not return any relevant
> information
> Thanks
> Med
>
>