Hi all
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian
"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J
>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]
|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
David G.
|||Thanks to all of you
That is great
"Ian" <ian@.NoWhere.com> wrote in message
news:eYrRNVkmEHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I keep getting and error message that says that I am a Deadlock Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order then
> the trigger runs an update statement to make sure that the numbers are all
> hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group at
the
> same time. I have coded this in to the application by disabling the save
> button if another user has the group of records open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was fare
> to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
>
Showing posts with label causing. Show all posts
Showing posts with label causing. Show all posts
Sunday, March 25, 2012
Deadlock Victum Error Message
Hi all
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian
"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J
>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]
|||as well as J's posted url, i would suggest switching on -T1204 (You can do
this if you are the DBA)
and investigate the output in sql error log doing this will help identify
what the other process(spid) in the deadlock was. and what both processes
were doing to result in a deadlock.
U might have to address the order in which these processes run and/or
re-tune your code.
"Jan Hyde" wrote:
> "Ian" <ian@.NoWhere.com>'s wild thoughts were released on
> Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>
> http://www.sql-server-performance.com/deadlocks.asp
> J
>
> Jan Hyde (VB MVP)
> --
> Did you hear about the guy who blamed arithmetic for his divorce?
> His wife put two and two together.
> (Art. Moger)
> [Abolish the TV License - http://www.tvlicensing.biz/]
>
|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
David G.
sql
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian
"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J
>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]
|||as well as J's posted url, i would suggest switching on -T1204 (You can do
this if you are the DBA)
and investigate the output in sql error log doing this will help identify
what the other process(spid) in the deadlock was. and what both processes
were doing to result in a deadlock.
U might have to address the order in which these processes run and/or
re-tune your code.
"Jan Hyde" wrote:
> "Ian" <ian@.NoWhere.com>'s wild thoughts were released on
> Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>
> http://www.sql-server-performance.com/deadlocks.asp
> J
>
> Jan Hyde (VB MVP)
> --
> Did you hear about the guy who blamed arithmetic for his divorce?
> His wife put two and two together.
> (Art. Moger)
> [Abolish the TV License - http://www.tvlicensing.biz/]
>
|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
David G.
sql
Deadlock Victum Error Message
Hi all
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J
>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
--
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
--
David G.|||Thanks to all of you
That is great
"Ian" <ian@.NoWhere.com> wrote in message
news:eYrRNVkmEHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I keep getting and error message that says that I am a Deadlock Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order then
> the trigger runs an update statement to make sure that the numbers are all
> hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group at
the
> same time. I have coded this in to the application by disabling the save
> button if another user has the group of records open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was fare
> to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
>
I keep getting and error message that says that I am a Deadlock Victim.
Is there anything that I can do about this.
What is causing this.
I have a VB6 application using ADO to connect to a MS SQL Server 2000
Database
This always happens when accessing the same table.
I am not sure but it seems to happen on updates and inserts.
My record set details are this
rsMain.CursorLocation = adUseClient
rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
The record set remains open for the life of the form
On this table I do have a triggers for Insert, Update and Delete.
In my table I have and order column that is used to make sure that the
records load into a list in the correct order.
When ever a change is made if it is something that changes the order then
the trigger runs an update statement to make sure that the numbers are all
hole numbers and in sync.
So
1
2
4.5
6
Will become
1
2
3
4
This is managed by a group ID and no 2 people can edit the same group at the
same time. I have coded this in to the application by disabling the save
button if another user has the group of records open.
But One person can be editing and another viewing.
I tried doing the reordering of numbers on the client side but it was fare
to slow.
Is the problem that the second person to open the group of records the
recorded set should be opened read only as well as disabling the Save
button. This is what I will try next.
Many thanks
Ian"Ian" <ian@.NoWhere.com>'s wild thoughts were released on
Tue, 14 Sep 2004 11:25:46 +0100 bearing the following fruit:
>Hi all
>I keep getting and error message that says that I am a Deadlock Victim.
>Is there anything that I can do about this.
>What is causing this.
http://www.sql-server-performance.com/deadlocks.asp
J
>I have a VB6 application using ADO to connect to a MS SQL Server 2000
>Database
>This always happens when accessing the same table.
>I am not sure but it seems to happen on updates and inserts.
>My record set details are this
>rsMain.CursorLocation = adUseClient
>rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
>The record set remains open for the life of the form
>On this table I do have a triggers for Insert, Update and Delete.
>In my table I have and order column that is used to make sure that the
>records load into a list in the correct order.
>When ever a change is made if it is something that changes the order then
>the trigger runs an update statement to make sure that the numbers are all
>hole numbers and in sync.
>So
>1
>2
>4.5
>6
>Will become
>1
>2
>3
>4
>This is managed by a group ID and no 2 people can edit the same group at the
>same time. I have coded this in to the application by disabling the save
>button if another user has the group of records open.
>But One person can be editing and another viewing.
>I tried doing the reordering of numbers on the client side but it was fare
>to slow.
>Is the problem that the second person to open the group of records the
>recorded set should be opened read only as well as disabling the Save
>button. This is what I will try next.
>
>Many thanks
>Ian
>
Jan Hyde (VB MVP)
--
Did you hear about the guy who blamed arithmetic for his divorce?
His wife put two and two together.
(Art. Moger)
[Abolish the TV License - http://www.tvlicensing.biz/]|||Ian wrote:
> Hi all
> I keep getting and error message that says that I am a Deadlock
> Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order
> then the trigger runs an update statement to make sure that the
> numbers are all hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group
> at the same time. I have coded this in to the application by
> disabling the save button if another user has the group of records
> open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was
> fare to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
Keep you transactions as short as possible.
Always access tables in the same order from all stored procedures.
Determine a predefined order and try to abide by these rules in all
procedures.
Use the NOLOCK hint on SELECT statements where it's OK to potentially
read dirty data.
Use Profiler and track the SQL:StmtStarting, SP:StmtStarting events,
plus all the Deadlock events and wait for the problem to occur again. If
you can force the problem quickly, that would be ideal. See what other
transactions are involved int hedeadlock with you and see if their
queries need to be tuned.
You said: "The record set remains open for the life of the form" using
the LockOptimistic option. This is probably the root cause of the
problem. What lock level are you using when users can only read the
data? You shouls use adLockReadOnly for those transactions.
The other issue with a LockOptimistic is you are likely locking pages on
the server, which can potentially block other users.
--
David G.|||Thanks to all of you
That is great
"Ian" <ian@.NoWhere.com> wrote in message
news:eYrRNVkmEHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I keep getting and error message that says that I am a Deadlock Victim.
> Is there anything that I can do about this.
> What is causing this.
>
> I have a VB6 application using ADO to connect to a MS SQL Server 2000
> Database
> This always happens when accessing the same table.
> I am not sure but it seems to happen on updates and inserts.
> My record set details are this
> rsMain.CursorLocation = adUseClient
> rsMain.Open sSQL, dbMain, adOpenForwardOnly, adLockOptimistic
> The record set remains open for the life of the form
> On this table I do have a triggers for Insert, Update and Delete.
> In my table I have and order column that is used to make sure that the
> records load into a list in the correct order.
> When ever a change is made if it is something that changes the order then
> the trigger runs an update statement to make sure that the numbers are all
> hole numbers and in sync.
> So
> 1
> 2
> 4.5
> 6
> Will become
> 1
> 2
> 3
> 4
> This is managed by a group ID and no 2 people can edit the same group at
the
> same time. I have coded this in to the application by disabling the save
> button if another user has the group of records open.
> But One person can be editing and another viewing.
> I tried doing the reordering of numbers on the client side but it was fare
> to slow.
> Is the problem that the second person to open the group of records the
> recorded set should be opened read only as well as disabling the Save
> button. This is what I will try next.
>
> Many thanks
> Ian
>
deadlock victim
h
im the below output from profiler..processes r getting deadlock and most of the time the below processes spid is causing blocking..but im unable to get procedure associated with that...
RPC Event 0 sp_executesql;
wot is sp_executesql;1 ?There are some trace falgs you can turn on to help resolve deadlocking
problems.
DBCC TRACEON (1204, 3605,-1)
will output info into your SQL Server ErrorLog.
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:B1BF5BC8-8889-4054-9FDD-1FE3B52418B9@.microsoft.com...
> hi
> im the below output from profiler..processes r getting deadlock and most
of the time the below processes spid is causing blocking..but im unable to
get procedure associated with that...
> RPC Event 0 sp_executesql;1
> wot is sp_executesql;1 ?
>|||h
thnks for yr reply..but i failied to identify which the procedure associated with this...RPC Event 0 sp_executesql;
this "RPC Event 0 sp_executesql;1" im in getting profiler as deadlock processes
im the below output from profiler..processes r getting deadlock and most of the time the below processes spid is causing blocking..but im unable to get procedure associated with that...
RPC Event 0 sp_executesql;
wot is sp_executesql;1 ?There are some trace falgs you can turn on to help resolve deadlocking
problems.
DBCC TRACEON (1204, 3605,-1)
will output info into your SQL Server ErrorLog.
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:B1BF5BC8-8889-4054-9FDD-1FE3B52418B9@.microsoft.com...
> hi
> im the below output from profiler..processes r getting deadlock and most
of the time the below processes spid is causing blocking..but im unable to
get procedure associated with that...
> RPC Event 0 sp_executesql;1
> wot is sp_executesql;1 ?
>|||h
thnks for yr reply..but i failied to identify which the procedure associated with this...RPC Event 0 sp_executesql;
this "RPC Event 0 sp_executesql;1" im in getting profiler as deadlock processes
Wednesday, March 21, 2012
Deadlock on SQL SELECT statement
I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.
Monday, March 19, 2012
Deadlock Issue - Cannot pinpoint resource causing contention
All,
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
Value:0x2e7feac0 Cost0/80)
Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
Value:0x2e893d60 Cost0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
Value:0x2e893d60 Cost0/80)
End deadlock search 75296 ... a deadlock was found.
Hi
Have you seen
http://msdn.microsoft.com/library/de...abse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
http://msdn.microsoft.com/library/de...netchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default...b;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540)
> Value:0x2e7feac0 Cost0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>
|||Well - I think I answered my own question after seeing other similar threads.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun.
"John" wrote:
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0 Cost0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>
|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:
> Hi
> Have you seen
> http://msdn.microsoft.com/library/de...abse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> http://msdn.microsoft.com/library/de...netchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default...b;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
>
>
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec
0x2ED0F590) Value:0x2e893d60Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec
0x2ED0F590) Value:0x2e893d60 Cost0/80)Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)Value:0x2e7feac0 Cost
0/80)Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)Value:0x2e893d60 Cost
0/80)Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)Value:0x2e893d60 Cost
0/80)End deadlock search 75296 ... a deadlock was found.
Hi
Have you seen
http://msdn.microsoft.com/library/de...abse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
http://msdn.microsoft.com/library/de...netchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default...b;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60 Cost0/80)> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540)> Value:0x2e7feac0 Cost
0/80)> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> End deadlock search 75296 ... a deadlock was found.
> --
>
|||Well - I think I answered my own question after seeing other similar threads.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun.
"John" wrote:
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60 Cost0/80)> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)> Value:0x2e7feac0 Cost
0/80)> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> End deadlock search 75296 ... a deadlock was found.
> --
>
|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:
> Hi
> Have you seen
> http://msdn.microsoft.com/library/de...abse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> http://msdn.microsoft.com/library/de...netchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default...b;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
>
>
Deadlock Issue - Cannot pinpoint resource causing contention
All,
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec:(0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
Value:0x2e7feac0 Cost:(0/80)
Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
Value:0x2e893d60 Cost:(0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
Value:0x2e893d60 Cost:(0/80)
End deadlock search 75296 ... a deadlock was found.
--Hi
Have you seen
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540)
> Value:0x2e7feac0 Cost:(0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>|||Well - I think I answered my own question after seeing other similar threads.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun.
"John" wrote:
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> Value:0x2e7feac0 Cost:(0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:
> Hi
> Have you seen
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> > All,
> > Have a deadlock issue - see 1204 trace info below.
> >
> > After reviewing BOL I cannot ascertain the exact resource (table, page,
> > index, etc) that is causing the deadlock. I believe it might be a row in
> > a
> > table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> > this
> > and I cannot determine the exact table.
> >
> > Question - is there something in the trace data that can tell me the exact
> > resource down to the specific table - assuming it is a table.
> >
> > Starting deadlock search 75296
> >
> > Target Resource Owner:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> > Value:0x2e7feac0
> > Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0
> > Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> > Ec:(0x2ED0F590) Value:0x2e893d60
> > Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0
> >
> >
> > Deadlock cycle was encountered ... verifying cycle
> > Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> > Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> > Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> > Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> >
> >
> > Deadlock encountered ... Printing deadlock information
> >
> > Wait-for graph
> >
> > Node:1
> > RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> > Grant List 0::
> > Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> > ECID:0
> > SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> > Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> > Requested By:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540)
> > Value:0x2e7feac0 Cost:(0/80)
> >
> > Node:2
> > RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> > Grant List 1::
> > Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:185
> > ECID:0
> > SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> > Input Buf: RPC Event: sp_executesql;1
> > Requested By:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> > Value:0x2e893d60 Cost:(0/80)
> > Victim Resource Owner:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> > Value:0x2e893d60 Cost:(0/80)
> >
> > End deadlock search 75296 ... a deadlock was found.
> > --
> >
>
>
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec:(0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
Value:0x2e7feac0 Cost:(0/80)
Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
Value:0x2e893d60 Cost:(0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
Value:0x2e893d60 Cost:(0/80)
End deadlock search 75296 ... a deadlock was found.
--Hi
Have you seen
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540)
> Value:0x2e7feac0 Cost:(0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>|||Well - I think I answered my own question after seeing other similar threads.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun.
"John" wrote:
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> Value:0x2e7feac0 Cost:(0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> Value:0x2e893d60 Cost:(0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:
> Hi
> Have you seen
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenetchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> > All,
> > Have a deadlock issue - see 1204 trace info below.
> >
> > After reviewing BOL I cannot ascertain the exact resource (table, page,
> > index, etc) that is causing the deadlock. I believe it might be a row in
> > a
> > table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> > this
> > and I cannot determine the exact table.
> >
> > Question - is there something in the trace data that can tell me the exact
> > resource down to the specific table - assuming it is a table.
> >
> > Starting deadlock search 75296
> >
> > Target Resource Owner:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec:(0xB611B540)
> > Value:0x2e7feac0
> > Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0
> > Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> > Ec:(0x2ED0F590) Value:0x2e893d60
> > Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0
> >
> >
> > Deadlock cycle was encountered ... verifying cycle
> > Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> > Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> > Ec:(0x2ED0F590) Value:0x2e893d60 Cost:(0/80)
> > Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540) Value:0x2e7feac0 Cost:(0/80)
> >
> >
> > Deadlock encountered ... Printing deadlock information
> >
> > Wait-for graph
> >
> > Node:1
> > RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> > Grant List 0::
> > Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> > ECID:0
> > SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> > Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> > Requested By:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> > Ec:(0xB611B540)
> > Value:0x2e7feac0 Cost:(0/80)
> >
> > Node:2
> > RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> > Grant List 1::
> > Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:185
> > ECID:0
> > SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> > Input Buf: RPC Event: sp_executesql;1
> > Requested By:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> > Value:0x2e893d60 Cost:(0/80)
> > Victim Resource Owner:
> > ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x2ED0F590)
> > Value:0x2e893d60 Cost:(0/80)
> >
> > End deadlock search 75296 ... a deadlock was found.
> > --
> >
>
>
Deadlock Issue - Cannot pinpoint resource causing contention
All,
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
Value:0x2e7feac0 Cost0/80)
Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
Value:0x2e893d60 Cost0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
Value:0x2e893d60 Cost0/80)
End deadlock search 75296 ... a deadlock was found.
--Hi
Have you seen
tabse_5xrn.asp?" target="_blank">http://msdn.microsoft.com/library/d...tabse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
etchapt14.asp" target="_blank">http://msdn.microsoft.com/library/d...
etchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/defaul...kb;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540)
> Value:0x2e7feac0 Cost0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>|||Well - I think I answered my own question after seeing other similar threads
.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun
.
"John" wrote:
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about thi
s
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec0x2ED0F590) Value:0x2e893d60 Cost0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec0xB611B540) Value:0x2e7feac0 Cost0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:5
4
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec0xB611B54
0)
> Value:0x2e7feac0 Cost0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:1
85
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590
)
> Value:0x2e893d60 Cost0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x2ED0F590)
> Value:0x2e893d60 Cost0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:
> Hi
> Have you seen
> databse_5xrn.asp?" target="_blank">http://msdn.microsoft.com/library/d...tabse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> enetchapt14.asp" target="_blank">http://msdn.microsoft.com/library/d...enetchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/defaul...kb;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
>
>
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec
0x2ED0F590) Value:0x2e893d60Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec
0x2ED0F590) Value:0x2e893d60 Cost0/80)Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)Value:0x2e7feac0 Cost
0/80)Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)Value:0x2e893d60 Cost
0/80)Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)Value:0x2e893d60 Cost
0/80)End deadlock search 75296 ... a deadlock was found.
--Hi
Have you seen
tabse_5xrn.asp?" target="_blank">http://msdn.microsoft.com/library/d...tabse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
etchapt14.asp" target="_blank">http://msdn.microsoft.com/library/d...
etchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/defaul...kb;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60 Cost0/80)> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540)> Value:0x2e7feac0 Cost
0/80)> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> End deadlock search 75296 ... a deadlock was found.
> --
>|||Well - I think I answered my own question after seeing other similar threads
.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun
.
"John" wrote:
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about thi
s
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec
0x2ED0F590) Value:0x2e893d60 Cost0/80)> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec
0xB611B540) Value:0x2e7feac0 Cost0/80)>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:5
4
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0xB611B540)
> Value:0x2e7feac0 Cost
0/80)> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:1
85
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)
> Value:0x2e893d60 Cost
0/80)> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec
0x2ED0F590)> Value:0x2e893d60 Cost
0/80)> End deadlock search 75296 ... a deadlock was found.
> --
>|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:
> Hi
> Have you seen
> databse_5xrn.asp?" target="_blank">http://msdn.microsoft.com/library/d...tabse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> enetchapt14.asp" target="_blank">http://msdn.microsoft.com/library/d...enetchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/defaul...kb;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)