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 victim. Show all posts
Showing posts with label victim. 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 even using temp table
Hi. I am struggling to understand why I get the following error in
using the stored procedure noted below. I recently starting using a
temp table as a way of providing custom paging in asp.net and this
problem has occured ever since (maybe 10 times per day with an average
of 30 users on all day).
Here is the error: "Transaction (Process ID ##) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction"
The client code is below. It uses a DataAdapter to fill a dataset that
is used to populate a datagrid. The long store procedure is below
that. It essentialy fills the temp table with records that are chosen
then retrieves all the necessary fields for the datagrid using whatever
page that is selected. There is code in there to support sorting and
hopefully it's not too confusing.
I apologize for the long post, I didn't want to remove parts of the SP
to make it shorter in case I removed an important element. I admit, I
am only an intermediate programmer so I may be missing some
fundamentals. Hopefully this is obvious to someone.
Thanks in advance.
Jeff
-- client code --
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter("tochange",
myConnection)
'Dim myCommand As New SqlDataAdapter
'myCommand.SelectCommand.Connection = myConnection
myCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
myCommand.SelectCommand.CommandText =
"dbo.csp_cGeneral_GetRequests2"
myCommand.SelectCommand.Parameters.Add("@.PortalID",
SqlDbType.Int).Value = iPortalID
myCommand.SelectCommand.Parameters.Add("@.Status",
SqlDbType.VarChar, 10).Value = Status
myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
SqlDbType.Int).Value = RequestSeqID
myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
SqlDbType.VarChar, 30).Value = BorrowerLastName
myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
SqlDbType.VarChar, 30).Value = LoanOfficerCompany
myCommand.SelectCommand.Parameters.Add("@.BegDate",
SqlDbType.VarChar, 25).Value = BegDate
myCommand.SelectCommand.Parameters.Add("@.EndDate",
SqlDbType.VarChar, 25).Value = EndDate
myCommand.SelectCommand.Parameters.Add("@.ContactName",
SqlDbType.VarChar, 30).Value = ContactName
myCommand.SelectCommand.Parameters.Add("@.AgentID",
SqlDbType.Int).Value = AgentID
myCommand.SelectCommand.Parameters.Add("@.HasDocs",
SqlDbType.Int).Value = HasDocs
myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
SqlDbType.Int).Value = iStaffSearchID
myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
SqlDbType.Int).Value = _currentPageNumber
myCommand.SelectCommand.Parameters.Add("@.PageSize",
SqlDbType.Int).Value = iPagesize
myCommand.SelectCommand.Parameters.Add("@.SortField",
SqlDbType.VarChar, 30).Value = strSortColumn
myCommand.SelectCommand.Parameters.Add("@.UserID",
SqlDbType.Int).Value = UserID
myCommand.SelectCommand.Parameters.Add("@.Role",
SqlDbType.VarChar, 20).Value = Role
myCommand.SelectCommand.Parameters.Add("@.Maps",
SqlDbType.VarChar, 20).Value = sMaps
' Create and Fill the DataSet
Dim myDataSet As New DataSet
myCommand.Fill(myDataSet, "Requests")
Dim myTable As DataTable = myDataSet.Tables("Requests")
If Not myTable Is Nothing Then
If myTable.Rows.Count > 0 Then
Dim dr As DataRow = myTable.Rows(0)
_TotalRecords = dr.Item("TotalRecords")
Else
_TotalRecords = 0
End If
End If
' Return the DataSet
-- stored procedure --
ALTER procedure dbo.csp_cGeneral_GetRequests2
@.PortalID int,
@.Status varchar(10) = "-1",
@.RequestSeqID int = -1,
@.BorrowerLastName varchar(30) = "-1",
@.LoanOfficerCompany varchar(30) = "-1",
@.BegDate varchar(25) = "-1",
@.EndDate varchar(25) = "-1",
@.ContactName varchar(30) = "-1",
@.AgentID Int = Null,
@.UserID int = 0,
@.Role varchar(20) = 'None',
@.HasDocs decimal = -1,
@.CurrentPage int,
@.PageSize int,
@.SortField varchar(30),
@.Maps varchar(20),
@.AssignedStaffID Int --(-1 all, -2 not in list)
as
--if @.AssignedStaffID = -2
--begin
--
--end
Declare @.TotalRecords int
Declare @.Status1 int
Declare @.Status2 int
Declare @.AssignedAgentID int
Declare @.CustomerID int
set @.AssignedAgentID = 0
set @.CustomerID = 0
if @.Role = 'NotaryAgent'
Begin
set @.AssignedAgentID = @.UserID
set @.CustomerID = Null
end
if @.Role = 'Customer'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = @.UserID
end
if @.Role = 'ServiceOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
if @.Role = 'AgentOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
set @.Status1 = -1
set @.Status2 = -1
if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
begin
set @.Status1 = cast(@.Status as int)
set @.Status2 = cast(@.Status as int)
end
else
begin
if @.Status = '123'
Begin
set @.Status1 = 1
set @.Status2 = 3
end
if @.Status = '45'
Begin
set @.Status1 = 4
set @.Status2 = 5
end
if @.Status = '123456'
Begin
set @.Status1 = 1
set @.Status2 = 6
end
if @.Status = '1234569'
Begin
set @.Status1 = 1
set @.Status2 = 10
end
end
Declare @.BegDate2 as smalldatetime
Declare @.EndDate2 as smalldatetime
if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
isdate(@.EndDate) = 0
begin
set @.BegDate2 = Null
Set @.EndDate2 = Null
end
else
begin
set @.BegDate2 = cast(@.BegDate as smalldatetime)
Set @.EndDate2 = cast(@.EndDate as smalldatetime)
end
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
RequestID int,
FileSize int,
DocCount int
)
INSERT INTO #TempTable
(
RequestID,
FileSize,
DocCount
)
SELECT
RequestID,
FileSize,
DocCount
>From (
Select SR.RequestID, isnull(FileSize,0) as FileSize,
isnull(temp1.DocCount,0) as DocCount,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN SRS.StatusOrder
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN 0
WHEN 'HasCust' THEN SR.UserID
WHEN 'StaffOrder' THEN Staff.StaffOrder
ELSE SRS.StatusOrder
END AS sortcol0,
CASE @.SortField
WHEN 'Request' THEN ''
WHEN 'Status' THEN ''
WHEN 'Borrower' THEN SR.BorrowerLastName
WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Location' THEN isnull(SR.SigningCity,'')
WHEN 'ContactInfo' THEN SR.ContactName
WHEN 'Agent' THEN Users.LastName
WHEN 'FileSize' THEN ''
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN Staff.StaffInitials
ELSE ''
END AS sortcol1,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Borrower' THEN SR.BorrowerFirstName
WHEN 'Date' THEN SR.SigningTime
WHEN 'Location' THEN isnull(SR.SigningState,'')
WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
WHEN 'Agent' THEN Users.FirstName
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE convert(varchar(20),SR.SigningDate,112)
END AS sortcol2,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN SR.SigningTime
WHEN 'Borrower' THEN '0'
WHEN 'Date' THEN '0'
WHEN 'Location' THEN '0'
WHEN 'ContactInfo' THEN '0'
WHEN 'Agent' THEN '0'
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE SR.SigningTime
END AS sortcol3,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN 0
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN FileSize
WHEN 'HasCust' THEN 0
WHEN 'StaffOrder' THEN 0
ELSE 0
END AS sortcol4,
SR.RequestSeqID AS sortcol5
>From dbo.ctbl_SigningRequests SR
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join (
Select ctbl_Docs.RequestID,
case when sum(Case when ctbl_Docs.LoanDocs = 0 and
ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as
DocCount,
cast(sum(ctbl_Docs.filesize) as decimal)/1000000 as filesize from
ctbl_Docs
Where ctbl_Docs.PortalID = @.PortalID
Group by ctbl_Docs.RequestID
) as temp1 ON SR.RequestID = temp1.RequestID
Where
SR.PortalID = @.PortalID
and SR.InActiveDate is null
and SR.BorrowerLastName like
IsNull(nullif('%'+@.BorrowerLastName+'%',
'%-1%'),'%'+SR.BorrowerLastName+'%')
and SR.LoanOfficerCompany like
IsNull(nullif('%'+@.LoanOfficerCompany+'%
','%-1%'),'%'+SR.LoanOfficerCompany+
'%')
and SR.ContactName like
IsNull(nullif('%'+@.ContactName+'%','%-1%'),'%'+SR.ContactName+'%')
and SR.RequestSeqID =
isnull(Nullif(@.RequestSeqID,-1),SR.RequestSeqID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AgentID,-1),IsNull(SR.AssignedAgent,-1))
and SR.SigningStatusID Between
IsNull(Nullif(@.Status1,-1),SR.SigningStatusID) and
IsNull(NullIf(@.Status2,-1),SR.SigningStatusID)
and SR.SigningDate Between IsNull(@.BegDate2,SR.SigningDate) and
IsNull(@.EndDate2,SR.SigningDate)
and SR.DeleteDate Is Null
and isnull(temp1.FileSize,0) > cast(@.HasDocs as decimal)
and SR.UserID = IsNull(@.CustomerID,SR.UserID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AssignedAgentID,-1),IsNull(SR.AssignedAgent,-1))
and IsNull(SR.AssignedStaffID,-1) =
isnull(Nullif(@.AssignedStaffID,-1),IsNull(SR.AssignedStaffID,-1))
) as t1
order by sortcol0, sortcol1, sortcol2, sortcol3, sortcol4 DESC,
sortcol5
--Create variable to identify the first and last record that should be
selected
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
if @.CurrentPage > ceiling(cast(@.TotalRecords as float)/cast (@.PageSize
as float))
set @.CurrentPage = isnull(ceiling(@.TotalRecords / @.PageSize),1)
--select ceiling(cast(@.TotalRecords as float)/cast (@.PageSize as
float))
--select ceiling(cast(31/10 as float))
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
--Return the total number of records available as an output parameter
--Select one page of data based on the record numbers above
Select SR.RequestID, SR.RequestSeqID, SRS.StatusNameShort, SR.UserID,
SRS.StatusOrder, SR.SigningDate, SR.SigningTime, SR.LoanNumber,
Case When SR.InvoiceCreated is Null then 0 else 1 end as
InvoiceCreated,
Case When SR.Invoiced is Null then 0 else 1 end as Invoiced,
Case When SR.InvoicePaid is Null then 0 else 1 end as CustomerPaid,
Case When SR.NotaryPaid is Null then 0 else 1 end as NotaryPaid,
Case When SR.Invoiced is Null then '0' else '1' end + Case When
SR.InvoicePaid is Null then '0' else '1' end
+ Case When SR.NotaryPaid is Null then '0' else '1' end as IconSort,
SR.ContactName, Isnull(SR.ContactEmail,'') as ContactEmail,
Isnull(SR.ContactPhone,'') as ContactPhone,
-- SR.ContactName + '' + left(SR.LoanOfficerCompany,10) as
ContactInfo, SR.BorrowerLastName, SR.BorrowerFirstName,
Left(SR.ContactName,15) as ContactInfo, SR.BorrowerLastName,
SR.BorrowerFirstName,
SR.LoanOfficerCompany, left(SR.LoanOfficerCompany,10) as
LoanOfficerCompanyShort,
Case When Users.UserID Is Null then '(Assign Notary)' else
Users.FirstName + ' ' + Users.LastName end as AssignedAgentName,
IsNull(Users.UserID,0) as AssignedAgentID, isnull(SR.SigningCity,'')
+ ', ' + isnull(SR.SigningState,'') as CityState,
isnull(SR.SigningZip,'') as SigningZip,
SR.LastChangedByMobile,
cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1)) as HudDocsFlag,
Case cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1))
When '001' then 'Loan Docs and Title are not in. Other docs exist.'
When '011' then 'Loan Docs are not in. Title is in. Other docs
exist.'
When '101' then 'Loan Docs are in. Title is not in. Other docs
exist.'
When '111' then 'Loan Docs and Title are in. Other docs exist.'
When '000' then 'Loan Docs and Title are not in.'
When '010' then 'Loan Docs are not in. Title is in.'
When '100' then 'Loan Docs are in. Title is not in.'
When '110' then 'Loan Docs and Title are in.'
else 'Desc error.'
end as HudDocsFlagDesc,
#TempTable.FileSize,
Case When SR.UserID = 0 then '-' else 'C' end as CustomerFlag,
Case When SR.AssignedAgent = 0 and @.Maps='MSN' then
''
When SR.AssignedAgent <> 0 and @.Maps='MSN' then
replace(replace('http://maps.msn.com/directionsFind.aspx?strt1=' +
isnull(Users.Street,'') + '&city1=' + isnull(Users.City,'') + '&zipc1='
+ isnull(Users.PostalCode,'')
+ '&cnty1=0&strt2=' + isnull(SR.SigningAddress1,'') + '&city2=' +
isnull(SR.SigningCity,'') + '&zipc2=' + isnull(SR.SigningZip,'') +
'&cnty2=0&rtyp=1&unit=0',' ','%20'),'#','')
When SR.AssignedAgent = 0 and @.Maps='QUEST' then
''
When SR.AssignedAgent <> 0 and @.Maps='QUEST' then
replace(replace('http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=
1&un=m&cl=EN&ct=NA&rsres=1&1a='
+ isnull(Users.Street,'') + '&1c=' + isnull(Users.City,'') + '&1s=' +
isnull(Users.Region,'') + '&1z=' + isnull(Users.PostalCode,'')
+ '&2a=' + isnull(SR.SigningAddress1,'') + '&2c=' +
isnull(SR.SigningCity,'') + '&2s=' + isnull(SR.SigningState,'') +
'&2z=' + isnull(SR.SigningZip,''),' ','%20'),'#','')
else ''
end as MapLink,
cast(isnull(ctbl_PortalData.FileSSLActivate,1) as varchar(1)) as
FileSSLActivate,
(isnull(SR.PriceQty_Agent1,0) * isnull(SR.PriceAmt_Agent1,0))
+(isnull(SR.PriceQty_Agent2,0) * isnull(SR.PriceAmt_Agent2,0))
+(isnull(SR.PriceQty_Agent3,0) * isnull(SR.PriceAmt_Agent3,0))
+(isnull(SR.PriceQty_Agent4,0) * isnull(SR.PriceAmt_Agent4,0))
+(isnull(SR.PriceQty_Agent5,0) * isnull(SR.PriceAmt_Agent5,0)) as
NotaryInvoiceTotal, @.TotalRecords as TotalRecords,
isnull(Staff.StaffInitials, Isnull(Staff.FirstName,'***')) as Staff,
isnull(Staff.StaffOrder,0) as StaffOrder
>From dbo.ctbl_SigningRequests SR
inner join #TempTable
on #TempTable.RequestID = SR.RequestID
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
WHERE
ID > @.FirstRec
AND
ID < @.LastRec
order by #TempTable.[ID]Check the transaction isolation level: you can probably live with READ
COMMITTED. Also make sure that the procedure isn't running in the context o
f
a transaction. If that isn't the problem, then make sure that indexes exist
on the columns joined and that the execution plan uses them. You may have t
o
coerce the optimizer with a hint or two.
"jhonz@.etsmail.com" wrote:
> Hi. I am struggling to understand why I get the following error in
> using the stored procedure noted below. I recently starting using a
> temp table as a way of providing custom paging in asp.net and this
> problem has occured ever since (maybe 10 times per day with an average
> of 30 users on all day).
> Here is the error: "Transaction (Process ID ##) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction"
> The client code is below. It uses a DataAdapter to fill a dataset that
> is used to populate a datagrid. The long store procedure is below
> that. It essentialy fills the temp table with records that are chosen
> then retrieves all the necessary fields for the datagrid using whatever
> page that is selected. There is code in there to support sorting and
> hopefully it's not too confusing.
> I apologize for the long post, I didn't want to remove parts of the SP
> to make it shorter in case I removed an important element. I admit, I
> am only an intermediate programmer so I may be missing some
> fundamentals. Hopefully this is obvious to someone.
> Thanks in advance.
> Jeff
>
> -- client code --
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
> Dim myCommand As New SqlDataAdapter("tochange",
> myConnection)
> 'Dim myCommand As New SqlDataAdapter
> 'myCommand.SelectCommand.Connection = myConnection
> myCommand.SelectCommand.CommandType =
> CommandType.StoredProcedure
> myCommand.SelectCommand.CommandText =
> "dbo.csp_cGeneral_GetRequests2"
> myCommand.SelectCommand.Parameters.Add("@.PortalID",
> SqlDbType.Int).Value = iPortalID
> myCommand.SelectCommand.Parameters.Add("@.Status",
> SqlDbType.VarChar, 10).Value = Status
> myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
> SqlDbType.Int).Value = RequestSeqID
> myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
> SqlDbType.VarChar, 30).Value = BorrowerLastName
> myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
> SqlDbType.VarChar, 30).Value = LoanOfficerCompany
> myCommand.SelectCommand.Parameters.Add("@.BegDate",
> SqlDbType.VarChar, 25).Value = BegDate
> myCommand.SelectCommand.Parameters.Add("@.EndDate",
> SqlDbType.VarChar, 25).Value = EndDate
> myCommand.SelectCommand.Parameters.Add("@.ContactName",
> SqlDbType.VarChar, 30).Value = ContactName
> myCommand.SelectCommand.Parameters.Add("@.AgentID",
> SqlDbType.Int).Value = AgentID
> myCommand.SelectCommand.Parameters.Add("@.HasDocs",
> SqlDbType.Int).Value = HasDocs
> myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
> SqlDbType.Int).Value = iStaffSearchID
> myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
> SqlDbType.Int).Value = _currentPageNumber
> myCommand.SelectCommand.Parameters.Add("@.PageSize",
> SqlDbType.Int).Value = iPagesize
> myCommand.SelectCommand.Parameters.Add("@.SortField",
> SqlDbType.VarChar, 30).Value = strSortColumn
> myCommand.SelectCommand.Parameters.Add("@.UserID",
> SqlDbType.Int).Value = UserID
> myCommand.SelectCommand.Parameters.Add("@.Role",
> SqlDbType.VarChar, 20).Value = Role
> myCommand.SelectCommand.Parameters.Add("@.Maps",
> SqlDbType.VarChar, 20).Value = sMaps
> ' Create and Fill the DataSet
> Dim myDataSet As New DataSet
> myCommand.Fill(myDataSet, "Requests")
> Dim myTable As DataTable = myDataSet.Tables("Requests")
> If Not myTable Is Nothing Then
> If myTable.Rows.Count > 0 Then
> Dim dr As DataRow = myTable.Rows(0)
> _TotalRecords = dr.Item("TotalRecords")
> Else
> _TotalRecords = 0
> End If
> End If
> ' Return the DataSet
>
> -- stored procedure --
> ALTER procedure dbo.csp_cGeneral_GetRequests2
> @.PortalID int,
> @.Status varchar(10) = "-1",
> @.RequestSeqID int = -1,
> @.BorrowerLastName varchar(30) = "-1",
> @.LoanOfficerCompany varchar(30) = "-1",
> @.BegDate varchar(25) = "-1",
> @.EndDate varchar(25) = "-1",
> @.ContactName varchar(30) = "-1",
> @.AgentID Int = Null,
> @.UserID int = 0,
> @.Role varchar(20) = 'None',
> @.HasDocs decimal = -1,
> @.CurrentPage int,
> @.PageSize int,
> @.SortField varchar(30),
> @.Maps varchar(20),
> @.AssignedStaffID Int --(-1 all, -2 not in list)
> as
> --if @.AssignedStaffID = -2
> --begin
> --
> --end
> Declare @.TotalRecords int
> Declare @.Status1 int
> Declare @.Status2 int
> Declare @.AssignedAgentID int
> Declare @.CustomerID int
> set @.AssignedAgentID = 0
> set @.CustomerID = 0
> if @.Role = 'NotaryAgent'
> Begin
> set @.AssignedAgentID = @.UserID
> set @.CustomerID = Null
> end
> if @.Role = 'Customer'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = @.UserID
> end
> if @.Role = 'ServiceOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> if @.Role = 'AgentOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> set @.Status1 = -1
> set @.Status2 = -1
> if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
> begin
> set @.Status1 = cast(@.Status as int)
> set @.Status2 = cast(@.Status as int)
> end
> else
> begin
> if @.Status = '123'
> Begin
> set @.Status1 = 1
> set @.Status2 = 3
> end
> if @.Status = '45'
> Begin
> set @.Status1 = 4
> set @.Status2 = 5
> end
> if @.Status = '123456'
> Begin
> set @.Status1 = 1
> set @.Status2 = 6
> end
> if @.Status = '1234569'
> Begin
> set @.Status1 = 1
> set @.Status2 = 10
> end
> end
> Declare @.BegDate2 as smalldatetime
> Declare @.EndDate2 as smalldatetime
> if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
> isdate(@.EndDate) = 0
> begin
> set @.BegDate2 = Null
> Set @.EndDate2 = Null
> end
> else
> begin
> set @.BegDate2 = cast(@.BegDate as smalldatetime)
> Set @.EndDate2 = cast(@.EndDate as smalldatetime)
> end
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> RequestID int,
> FileSize int,
> DocCount int
> )
> INSERT INTO #TempTable
> (
> RequestID,
> FileSize,
> DocCount
> )
> SELECT
> RequestID,
> FileSize,
> DocCount
> Select SR.RequestID, isnull(FileSize,0) as FileSize,
> isnull(temp1.DocCount,0) as DocCount,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN SRS.StatusOrder
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN 0
> WHEN 'HasCust' THEN SR.UserID
> WHEN 'StaffOrder' THEN Staff.StaffOrder
> ELSE SRS.StatusOrder
> END AS sortcol0,
> CASE @.SortField
> WHEN 'Request' THEN ''
> WHEN 'Status' THEN ''
> WHEN 'Borrower' THEN SR.BorrowerLastName
> WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Location' THEN isnull(SR.SigningCity,'')
> WHEN 'ContactInfo' THEN SR.ContactName
> WHEN 'Agent' THEN Users.LastName
> WHEN 'FileSize' THEN ''
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN Staff.StaffInitials
> ELSE ''
> END AS sortcol1,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Borrower' THEN SR.BorrowerFirstName
> WHEN 'Date' THEN SR.SigningTime
> WHEN 'Location' THEN isnull(SR.SigningState,'')
> WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
> WHEN 'Agent' THEN Users.FirstName
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE convert(varchar(20),SR.SigningDate,112)
> END AS sortcol2,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN SR.SigningTime
> WHEN 'Borrower' THEN '0'
> WHEN 'Date' THEN '0'
> WHEN 'Location' THEN '0'
> WHEN 'ContactInfo' THEN '0'
> WHEN 'Agent' THEN '0'
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE SR.SigningTime
> END AS sortcol3,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN 0
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN FileSize
> WHEN 'HasCust' THEN 0
> WHEN 'StaffOrder' THEN 0
> ELSE 0
> END AS sortcol4,
> SR.RequestSeqID AS sortcol5
> Left Outer Join dbo.ctbl_SigningRequestStatus SRS
> ON SR.SigningStatusID = SRS.SigningStatusID
> Left Outer Join dbo.ctbl_UserData UD
> ON SR.AssignedAgent = UD.UserID
> Left Outer Join dbo.Users Users
> ON SR.AssignedAgent = Users.UserID
> Left Outer Join ctbl_PortalData
> On ctbl_PortalData.PortalID = @.PortalID
> Left Outer Join dbo.Users Staff
> ON SR.AssignedStaffID = Staff.UserID
> Left Outer Join (
> Select ctbl_Docs.RequestID,
> case when sum(Case when ctbl_Docs.LoanDocs = 0 and
> ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as|||Thanks, Brian. Couldn't I use WITH (NOLOCK) on the select that fills
the temptable and then later selects from it? READ COMMITTED seems to
be SQL 2000 default and is probably arleady set. I image the locks or
on the select a temp table should not be shared amongts users. In
ASP.Net's connection pooling, do you think things could get crossed
there.
I am not running a transaction so I think I am safe there.
I ran the execution plan and I don't see any table scans. Is that
sufficient indication that things are OK there?
Jeff
using the stored procedure noted below. I recently starting using a
temp table as a way of providing custom paging in asp.net and this
problem has occured ever since (maybe 10 times per day with an average
of 30 users on all day).
Here is the error: "Transaction (Process ID ##) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction"
The client code is below. It uses a DataAdapter to fill a dataset that
is used to populate a datagrid. The long store procedure is below
that. It essentialy fills the temp table with records that are chosen
then retrieves all the necessary fields for the datagrid using whatever
page that is selected. There is code in there to support sorting and
hopefully it's not too confusing.
I apologize for the long post, I didn't want to remove parts of the SP
to make it shorter in case I removed an important element. I admit, I
am only an intermediate programmer so I may be missing some
fundamentals. Hopefully this is obvious to someone.
Thanks in advance.
Jeff
-- client code --
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter("tochange",
myConnection)
'Dim myCommand As New SqlDataAdapter
'myCommand.SelectCommand.Connection = myConnection
myCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
myCommand.SelectCommand.CommandText =
"dbo.csp_cGeneral_GetRequests2"
myCommand.SelectCommand.Parameters.Add("@.PortalID",
SqlDbType.Int).Value = iPortalID
myCommand.SelectCommand.Parameters.Add("@.Status",
SqlDbType.VarChar, 10).Value = Status
myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
SqlDbType.Int).Value = RequestSeqID
myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
SqlDbType.VarChar, 30).Value = BorrowerLastName
myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
SqlDbType.VarChar, 30).Value = LoanOfficerCompany
myCommand.SelectCommand.Parameters.Add("@.BegDate",
SqlDbType.VarChar, 25).Value = BegDate
myCommand.SelectCommand.Parameters.Add("@.EndDate",
SqlDbType.VarChar, 25).Value = EndDate
myCommand.SelectCommand.Parameters.Add("@.ContactName",
SqlDbType.VarChar, 30).Value = ContactName
myCommand.SelectCommand.Parameters.Add("@.AgentID",
SqlDbType.Int).Value = AgentID
myCommand.SelectCommand.Parameters.Add("@.HasDocs",
SqlDbType.Int).Value = HasDocs
myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
SqlDbType.Int).Value = iStaffSearchID
myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
SqlDbType.Int).Value = _currentPageNumber
myCommand.SelectCommand.Parameters.Add("@.PageSize",
SqlDbType.Int).Value = iPagesize
myCommand.SelectCommand.Parameters.Add("@.SortField",
SqlDbType.VarChar, 30).Value = strSortColumn
myCommand.SelectCommand.Parameters.Add("@.UserID",
SqlDbType.Int).Value = UserID
myCommand.SelectCommand.Parameters.Add("@.Role",
SqlDbType.VarChar, 20).Value = Role
myCommand.SelectCommand.Parameters.Add("@.Maps",
SqlDbType.VarChar, 20).Value = sMaps
' Create and Fill the DataSet
Dim myDataSet As New DataSet
myCommand.Fill(myDataSet, "Requests")
Dim myTable As DataTable = myDataSet.Tables("Requests")
If Not myTable Is Nothing Then
If myTable.Rows.Count > 0 Then
Dim dr As DataRow = myTable.Rows(0)
_TotalRecords = dr.Item("TotalRecords")
Else
_TotalRecords = 0
End If
End If
' Return the DataSet
-- stored procedure --
ALTER procedure dbo.csp_cGeneral_GetRequests2
@.PortalID int,
@.Status varchar(10) = "-1",
@.RequestSeqID int = -1,
@.BorrowerLastName varchar(30) = "-1",
@.LoanOfficerCompany varchar(30) = "-1",
@.BegDate varchar(25) = "-1",
@.EndDate varchar(25) = "-1",
@.ContactName varchar(30) = "-1",
@.AgentID Int = Null,
@.UserID int = 0,
@.Role varchar(20) = 'None',
@.HasDocs decimal = -1,
@.CurrentPage int,
@.PageSize int,
@.SortField varchar(30),
@.Maps varchar(20),
@.AssignedStaffID Int --(-1 all, -2 not in list)
as
--if @.AssignedStaffID = -2
--begin
--
--end
Declare @.TotalRecords int
Declare @.Status1 int
Declare @.Status2 int
Declare @.AssignedAgentID int
Declare @.CustomerID int
set @.AssignedAgentID = 0
set @.CustomerID = 0
if @.Role = 'NotaryAgent'
Begin
set @.AssignedAgentID = @.UserID
set @.CustomerID = Null
end
if @.Role = 'Customer'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = @.UserID
end
if @.Role = 'ServiceOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
if @.Role = 'AgentOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
set @.Status1 = -1
set @.Status2 = -1
if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
begin
set @.Status1 = cast(@.Status as int)
set @.Status2 = cast(@.Status as int)
end
else
begin
if @.Status = '123'
Begin
set @.Status1 = 1
set @.Status2 = 3
end
if @.Status = '45'
Begin
set @.Status1 = 4
set @.Status2 = 5
end
if @.Status = '123456'
Begin
set @.Status1 = 1
set @.Status2 = 6
end
if @.Status = '1234569'
Begin
set @.Status1 = 1
set @.Status2 = 10
end
end
Declare @.BegDate2 as smalldatetime
Declare @.EndDate2 as smalldatetime
if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
isdate(@.EndDate) = 0
begin
set @.BegDate2 = Null
Set @.EndDate2 = Null
end
else
begin
set @.BegDate2 = cast(@.BegDate as smalldatetime)
Set @.EndDate2 = cast(@.EndDate as smalldatetime)
end
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
RequestID int,
FileSize int,
DocCount int
)
INSERT INTO #TempTable
(
RequestID,
FileSize,
DocCount
)
SELECT
RequestID,
FileSize,
DocCount
>From (
Select SR.RequestID, isnull(FileSize,0) as FileSize,
isnull(temp1.DocCount,0) as DocCount,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN SRS.StatusOrder
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN 0
WHEN 'HasCust' THEN SR.UserID
WHEN 'StaffOrder' THEN Staff.StaffOrder
ELSE SRS.StatusOrder
END AS sortcol0,
CASE @.SortField
WHEN 'Request' THEN ''
WHEN 'Status' THEN ''
WHEN 'Borrower' THEN SR.BorrowerLastName
WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Location' THEN isnull(SR.SigningCity,'')
WHEN 'ContactInfo' THEN SR.ContactName
WHEN 'Agent' THEN Users.LastName
WHEN 'FileSize' THEN ''
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN Staff.StaffInitials
ELSE ''
END AS sortcol1,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Borrower' THEN SR.BorrowerFirstName
WHEN 'Date' THEN SR.SigningTime
WHEN 'Location' THEN isnull(SR.SigningState,'')
WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
WHEN 'Agent' THEN Users.FirstName
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE convert(varchar(20),SR.SigningDate,112)
END AS sortcol2,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN SR.SigningTime
WHEN 'Borrower' THEN '0'
WHEN 'Date' THEN '0'
WHEN 'Location' THEN '0'
WHEN 'ContactInfo' THEN '0'
WHEN 'Agent' THEN '0'
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE SR.SigningTime
END AS sortcol3,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN 0
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN FileSize
WHEN 'HasCust' THEN 0
WHEN 'StaffOrder' THEN 0
ELSE 0
END AS sortcol4,
SR.RequestSeqID AS sortcol5
>From dbo.ctbl_SigningRequests SR
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join (
Select ctbl_Docs.RequestID,
case when sum(Case when ctbl_Docs.LoanDocs = 0 and
ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as
DocCount,
cast(sum(ctbl_Docs.filesize) as decimal)/1000000 as filesize from
ctbl_Docs
Where ctbl_Docs.PortalID = @.PortalID
Group by ctbl_Docs.RequestID
) as temp1 ON SR.RequestID = temp1.RequestID
Where
SR.PortalID = @.PortalID
and SR.InActiveDate is null
and SR.BorrowerLastName like
IsNull(nullif('%'+@.BorrowerLastName+'%',
'%-1%'),'%'+SR.BorrowerLastName+'%')
and SR.LoanOfficerCompany like
IsNull(nullif('%'+@.LoanOfficerCompany+'%
','%-1%'),'%'+SR.LoanOfficerCompany+
'%')
and SR.ContactName like
IsNull(nullif('%'+@.ContactName+'%','%-1%'),'%'+SR.ContactName+'%')
and SR.RequestSeqID =
isnull(Nullif(@.RequestSeqID,-1),SR.RequestSeqID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AgentID,-1),IsNull(SR.AssignedAgent,-1))
and SR.SigningStatusID Between
IsNull(Nullif(@.Status1,-1),SR.SigningStatusID) and
IsNull(NullIf(@.Status2,-1),SR.SigningStatusID)
and SR.SigningDate Between IsNull(@.BegDate2,SR.SigningDate) and
IsNull(@.EndDate2,SR.SigningDate)
and SR.DeleteDate Is Null
and isnull(temp1.FileSize,0) > cast(@.HasDocs as decimal)
and SR.UserID = IsNull(@.CustomerID,SR.UserID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AssignedAgentID,-1),IsNull(SR.AssignedAgent,-1))
and IsNull(SR.AssignedStaffID,-1) =
isnull(Nullif(@.AssignedStaffID,-1),IsNull(SR.AssignedStaffID,-1))
) as t1
order by sortcol0, sortcol1, sortcol2, sortcol3, sortcol4 DESC,
sortcol5
--Create variable to identify the first and last record that should be
selected
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
if @.CurrentPage > ceiling(cast(@.TotalRecords as float)/cast (@.PageSize
as float))
set @.CurrentPage = isnull(ceiling(@.TotalRecords / @.PageSize),1)
--select ceiling(cast(@.TotalRecords as float)/cast (@.PageSize as
float))
--select ceiling(cast(31/10 as float))
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
--Return the total number of records available as an output parameter
--Select one page of data based on the record numbers above
Select SR.RequestID, SR.RequestSeqID, SRS.StatusNameShort, SR.UserID,
SRS.StatusOrder, SR.SigningDate, SR.SigningTime, SR.LoanNumber,
Case When SR.InvoiceCreated is Null then 0 else 1 end as
InvoiceCreated,
Case When SR.Invoiced is Null then 0 else 1 end as Invoiced,
Case When SR.InvoicePaid is Null then 0 else 1 end as CustomerPaid,
Case When SR.NotaryPaid is Null then 0 else 1 end as NotaryPaid,
Case When SR.Invoiced is Null then '0' else '1' end + Case When
SR.InvoicePaid is Null then '0' else '1' end
+ Case When SR.NotaryPaid is Null then '0' else '1' end as IconSort,
SR.ContactName, Isnull(SR.ContactEmail,'') as ContactEmail,
Isnull(SR.ContactPhone,'') as ContactPhone,
-- SR.ContactName + '' + left(SR.LoanOfficerCompany,10) as
ContactInfo, SR.BorrowerLastName, SR.BorrowerFirstName,
Left(SR.ContactName,15) as ContactInfo, SR.BorrowerLastName,
SR.BorrowerFirstName,
SR.LoanOfficerCompany, left(SR.LoanOfficerCompany,10) as
LoanOfficerCompanyShort,
Case When Users.UserID Is Null then '(Assign Notary)' else
Users.FirstName + ' ' + Users.LastName end as AssignedAgentName,
IsNull(Users.UserID,0) as AssignedAgentID, isnull(SR.SigningCity,'')
+ ', ' + isnull(SR.SigningState,'') as CityState,
isnull(SR.SigningZip,'') as SigningZip,
SR.LastChangedByMobile,
cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1)) as HudDocsFlag,
Case cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1))
When '001' then 'Loan Docs and Title are not in. Other docs exist.'
When '011' then 'Loan Docs are not in. Title is in. Other docs
exist.'
When '101' then 'Loan Docs are in. Title is not in. Other docs
exist.'
When '111' then 'Loan Docs and Title are in. Other docs exist.'
When '000' then 'Loan Docs and Title are not in.'
When '010' then 'Loan Docs are not in. Title is in.'
When '100' then 'Loan Docs are in. Title is not in.'
When '110' then 'Loan Docs and Title are in.'
else 'Desc error.'
end as HudDocsFlagDesc,
#TempTable.FileSize,
Case When SR.UserID = 0 then '-' else 'C' end as CustomerFlag,
Case When SR.AssignedAgent = 0 and @.Maps='MSN' then
''
When SR.AssignedAgent <> 0 and @.Maps='MSN' then
replace(replace('http://maps.msn.com/directionsFind.aspx?strt1=' +
isnull(Users.Street,'') + '&city1=' + isnull(Users.City,'') + '&zipc1='
+ isnull(Users.PostalCode,'')
+ '&cnty1=0&strt2=' + isnull(SR.SigningAddress1,'') + '&city2=' +
isnull(SR.SigningCity,'') + '&zipc2=' + isnull(SR.SigningZip,'') +
'&cnty2=0&rtyp=1&unit=0',' ','%20'),'#','')
When SR.AssignedAgent = 0 and @.Maps='QUEST' then
''
When SR.AssignedAgent <> 0 and @.Maps='QUEST' then
replace(replace('http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=
1&un=m&cl=EN&ct=NA&rsres=1&1a='
+ isnull(Users.Street,'') + '&1c=' + isnull(Users.City,'') + '&1s=' +
isnull(Users.Region,'') + '&1z=' + isnull(Users.PostalCode,'')
+ '&2a=' + isnull(SR.SigningAddress1,'') + '&2c=' +
isnull(SR.SigningCity,'') + '&2s=' + isnull(SR.SigningState,'') +
'&2z=' + isnull(SR.SigningZip,''),' ','%20'),'#','')
else ''
end as MapLink,
cast(isnull(ctbl_PortalData.FileSSLActivate,1) as varchar(1)) as
FileSSLActivate,
(isnull(SR.PriceQty_Agent1,0) * isnull(SR.PriceAmt_Agent1,0))
+(isnull(SR.PriceQty_Agent2,0) * isnull(SR.PriceAmt_Agent2,0))
+(isnull(SR.PriceQty_Agent3,0) * isnull(SR.PriceAmt_Agent3,0))
+(isnull(SR.PriceQty_Agent4,0) * isnull(SR.PriceAmt_Agent4,0))
+(isnull(SR.PriceQty_Agent5,0) * isnull(SR.PriceAmt_Agent5,0)) as
NotaryInvoiceTotal, @.TotalRecords as TotalRecords,
isnull(Staff.StaffInitials, Isnull(Staff.FirstName,'***')) as Staff,
isnull(Staff.StaffOrder,0) as StaffOrder
>From dbo.ctbl_SigningRequests SR
inner join #TempTable
on #TempTable.RequestID = SR.RequestID
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
WHERE
ID > @.FirstRec
AND
ID < @.LastRec
order by #TempTable.[ID]Check the transaction isolation level: you can probably live with READ
COMMITTED. Also make sure that the procedure isn't running in the context o
f
a transaction. If that isn't the problem, then make sure that indexes exist
on the columns joined and that the execution plan uses them. You may have t
o
coerce the optimizer with a hint or two.
"jhonz@.etsmail.com" wrote:
> Hi. I am struggling to understand why I get the following error in
> using the stored procedure noted below. I recently starting using a
> temp table as a way of providing custom paging in asp.net and this
> problem has occured ever since (maybe 10 times per day with an average
> of 30 users on all day).
> Here is the error: "Transaction (Process ID ##) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction"
> The client code is below. It uses a DataAdapter to fill a dataset that
> is used to populate a datagrid. The long store procedure is below
> that. It essentialy fills the temp table with records that are chosen
> then retrieves all the necessary fields for the datagrid using whatever
> page that is selected. There is code in there to support sorting and
> hopefully it's not too confusing.
> I apologize for the long post, I didn't want to remove parts of the SP
> to make it shorter in case I removed an important element. I admit, I
> am only an intermediate programmer so I may be missing some
> fundamentals. Hopefully this is obvious to someone.
> Thanks in advance.
> Jeff
>
> -- client code --
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
> Dim myCommand As New SqlDataAdapter("tochange",
> myConnection)
> 'Dim myCommand As New SqlDataAdapter
> 'myCommand.SelectCommand.Connection = myConnection
> myCommand.SelectCommand.CommandType =
> CommandType.StoredProcedure
> myCommand.SelectCommand.CommandText =
> "dbo.csp_cGeneral_GetRequests2"
> myCommand.SelectCommand.Parameters.Add("@.PortalID",
> SqlDbType.Int).Value = iPortalID
> myCommand.SelectCommand.Parameters.Add("@.Status",
> SqlDbType.VarChar, 10).Value = Status
> myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
> SqlDbType.Int).Value = RequestSeqID
> myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
> SqlDbType.VarChar, 30).Value = BorrowerLastName
> myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
> SqlDbType.VarChar, 30).Value = LoanOfficerCompany
> myCommand.SelectCommand.Parameters.Add("@.BegDate",
> SqlDbType.VarChar, 25).Value = BegDate
> myCommand.SelectCommand.Parameters.Add("@.EndDate",
> SqlDbType.VarChar, 25).Value = EndDate
> myCommand.SelectCommand.Parameters.Add("@.ContactName",
> SqlDbType.VarChar, 30).Value = ContactName
> myCommand.SelectCommand.Parameters.Add("@.AgentID",
> SqlDbType.Int).Value = AgentID
> myCommand.SelectCommand.Parameters.Add("@.HasDocs",
> SqlDbType.Int).Value = HasDocs
> myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
> SqlDbType.Int).Value = iStaffSearchID
> myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
> SqlDbType.Int).Value = _currentPageNumber
> myCommand.SelectCommand.Parameters.Add("@.PageSize",
> SqlDbType.Int).Value = iPagesize
> myCommand.SelectCommand.Parameters.Add("@.SortField",
> SqlDbType.VarChar, 30).Value = strSortColumn
> myCommand.SelectCommand.Parameters.Add("@.UserID",
> SqlDbType.Int).Value = UserID
> myCommand.SelectCommand.Parameters.Add("@.Role",
> SqlDbType.VarChar, 20).Value = Role
> myCommand.SelectCommand.Parameters.Add("@.Maps",
> SqlDbType.VarChar, 20).Value = sMaps
> ' Create and Fill the DataSet
> Dim myDataSet As New DataSet
> myCommand.Fill(myDataSet, "Requests")
> Dim myTable As DataTable = myDataSet.Tables("Requests")
> If Not myTable Is Nothing Then
> If myTable.Rows.Count > 0 Then
> Dim dr As DataRow = myTable.Rows(0)
> _TotalRecords = dr.Item("TotalRecords")
> Else
> _TotalRecords = 0
> End If
> End If
> ' Return the DataSet
>
> -- stored procedure --
> ALTER procedure dbo.csp_cGeneral_GetRequests2
> @.PortalID int,
> @.Status varchar(10) = "-1",
> @.RequestSeqID int = -1,
> @.BorrowerLastName varchar(30) = "-1",
> @.LoanOfficerCompany varchar(30) = "-1",
> @.BegDate varchar(25) = "-1",
> @.EndDate varchar(25) = "-1",
> @.ContactName varchar(30) = "-1",
> @.AgentID Int = Null,
> @.UserID int = 0,
> @.Role varchar(20) = 'None',
> @.HasDocs decimal = -1,
> @.CurrentPage int,
> @.PageSize int,
> @.SortField varchar(30),
> @.Maps varchar(20),
> @.AssignedStaffID Int --(-1 all, -2 not in list)
> as
> --if @.AssignedStaffID = -2
> --begin
> --
> --end
> Declare @.TotalRecords int
> Declare @.Status1 int
> Declare @.Status2 int
> Declare @.AssignedAgentID int
> Declare @.CustomerID int
> set @.AssignedAgentID = 0
> set @.CustomerID = 0
> if @.Role = 'NotaryAgent'
> Begin
> set @.AssignedAgentID = @.UserID
> set @.CustomerID = Null
> end
> if @.Role = 'Customer'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = @.UserID
> end
> if @.Role = 'ServiceOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> if @.Role = 'AgentOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> set @.Status1 = -1
> set @.Status2 = -1
> if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
> begin
> set @.Status1 = cast(@.Status as int)
> set @.Status2 = cast(@.Status as int)
> end
> else
> begin
> if @.Status = '123'
> Begin
> set @.Status1 = 1
> set @.Status2 = 3
> end
> if @.Status = '45'
> Begin
> set @.Status1 = 4
> set @.Status2 = 5
> end
> if @.Status = '123456'
> Begin
> set @.Status1 = 1
> set @.Status2 = 6
> end
> if @.Status = '1234569'
> Begin
> set @.Status1 = 1
> set @.Status2 = 10
> end
> end
> Declare @.BegDate2 as smalldatetime
> Declare @.EndDate2 as smalldatetime
> if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
> isdate(@.EndDate) = 0
> begin
> set @.BegDate2 = Null
> Set @.EndDate2 = Null
> end
> else
> begin
> set @.BegDate2 = cast(@.BegDate as smalldatetime)
> Set @.EndDate2 = cast(@.EndDate as smalldatetime)
> end
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> RequestID int,
> FileSize int,
> DocCount int
> )
> INSERT INTO #TempTable
> (
> RequestID,
> FileSize,
> DocCount
> )
> SELECT
> RequestID,
> FileSize,
> DocCount
> Select SR.RequestID, isnull(FileSize,0) as FileSize,
> isnull(temp1.DocCount,0) as DocCount,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN SRS.StatusOrder
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN 0
> WHEN 'HasCust' THEN SR.UserID
> WHEN 'StaffOrder' THEN Staff.StaffOrder
> ELSE SRS.StatusOrder
> END AS sortcol0,
> CASE @.SortField
> WHEN 'Request' THEN ''
> WHEN 'Status' THEN ''
> WHEN 'Borrower' THEN SR.BorrowerLastName
> WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Location' THEN isnull(SR.SigningCity,'')
> WHEN 'ContactInfo' THEN SR.ContactName
> WHEN 'Agent' THEN Users.LastName
> WHEN 'FileSize' THEN ''
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN Staff.StaffInitials
> ELSE ''
> END AS sortcol1,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Borrower' THEN SR.BorrowerFirstName
> WHEN 'Date' THEN SR.SigningTime
> WHEN 'Location' THEN isnull(SR.SigningState,'')
> WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
> WHEN 'Agent' THEN Users.FirstName
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE convert(varchar(20),SR.SigningDate,112)
> END AS sortcol2,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN SR.SigningTime
> WHEN 'Borrower' THEN '0'
> WHEN 'Date' THEN '0'
> WHEN 'Location' THEN '0'
> WHEN 'ContactInfo' THEN '0'
> WHEN 'Agent' THEN '0'
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE SR.SigningTime
> END AS sortcol3,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN 0
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN FileSize
> WHEN 'HasCust' THEN 0
> WHEN 'StaffOrder' THEN 0
> ELSE 0
> END AS sortcol4,
> SR.RequestSeqID AS sortcol5
> Left Outer Join dbo.ctbl_SigningRequestStatus SRS
> ON SR.SigningStatusID = SRS.SigningStatusID
> Left Outer Join dbo.ctbl_UserData UD
> ON SR.AssignedAgent = UD.UserID
> Left Outer Join dbo.Users Users
> ON SR.AssignedAgent = Users.UserID
> Left Outer Join ctbl_PortalData
> On ctbl_PortalData.PortalID = @.PortalID
> Left Outer Join dbo.Users Staff
> ON SR.AssignedStaffID = Staff.UserID
> Left Outer Join (
> Select ctbl_Docs.RequestID,
> case when sum(Case when ctbl_Docs.LoanDocs = 0 and
> ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as|||Thanks, Brian. Couldn't I use WITH (NOLOCK) on the select that fills
the temptable and then later selects from it? READ COMMITTED seems to
be SQL 2000 default and is probably arleady set. I image the locks or
on the select a temp table should not be shared amongts users. In
ASP.Net's connection pooling, do you think things could get crossed
there.
I am not running a transaction so I think I am safe there.
I ran the execution plan and I don't see any table scans. Is that
sufficient indication that things are OK there?
Jeff
Deadlock victim (-2147467259)
Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communitie...qlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communitie...qlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....
Deadlock victim (-2147467259)
Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities...lserver.server
Try to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities...lserver.server
Try to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....
Deadlock victim (-2147467259)
Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
--
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=cb2097f1-e3c0-49b8-bf62-533a51932bad&dg=microsoft.public.sqlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....sql
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
--
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=cb2097f1-e3c0-49b8-bf62-533a51932bad&dg=microsoft.public.sqlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....sql
deadlock victim
Here is the exception I am getting, any idea why this might be happening:
Exception: ThisMethod is done: let this go: Exception:
Transaction (Process ID 74) was deadlocked on lock |
communication buffer resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.Stack Trace:at
Jim,Please check the following article for some insight:
http://support.microsoft.com/kb/224453|||
deadlock is a contention by two process on a resource
in a deadlock situtation one is choose as a deadlock victim and are rolled back
and the other rolls forward
you just ave to rerun the deadlock victim
|||
Thanks for the reply.
While users are working in the user interface I am inserting data to the table with another that is back end for the user interface. Although we do insert and update I do not think that user are dealing with the same data row, so I am assuming I should not be receiving deadlock. Does SQL server do RowLock or TableLock. How can I get my insert and update work on the RowLock. Can you give me sample sql statement.
|||sqlserver will escalate the lock (from row/page to table) as it seesfit. While deadlock is not desired, it's necessary in order to allow
the system to proceed forward. Imagine a case where the system just
sits in blocking/contention. That would not be good for either user,
would it. So, the best you can do is to follow the recommendations in
the KB I've posted.
Here is some info on sqlserver locking:
http://msdn2.microsoft.com/en-us/library/ms190615.aspx
And some example of implementing rowlock (again, sql can escalate the lock if it wants to):
http://groups.google.com/groups?q=sqlserver+oj+rowlock+update
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
deadlock victim
Here is the exception I am getting, any idea why this might be happening:
Exception: ThisMethod is done: let this go: Exception:
Transaction (Process ID 74) was deadlocked on lock |
communication buffer resources with another process and has been chosen as the deadlock victim.
Rerun the transaction. Stack Trace: at
This previous thread on the deadlock topic, along with the links it contains, might help you: Deadlock|||Thanks for the reply.
While users are working in the user interface I am inserting data to the table with another that is back end for the user interface. Although we do insert and update I do not think that user are dealing with the same data row, so I am assuming I should not be receiving deadlock. Does SQL server do RowLock or TableLock. How can I get my insert and update work on the RowLock. Can you give me sample sql statement.
Thursday, March 22, 2012
Deadlock Problems
Hi all
Now a day I am facing too-many deadlock problems, is there any way or
possibility that I make automated job, for diagnose the deadlock victim and
kill that process.
Any advice will be greatly appreciated.
Farhan Iqbal
Why would you want to kill the victim? The application should be able to
recover from a deadlock situation and either retry or gracefully handle it
some how. That really isn't a database issue. You would be best served by
spending that time fixing the reason why deadlocks occur in the first place.
Andrew J. Kelly SQL MVP
"Farhan Iqbal" <mr_farhaniqbal@.hotmail.com> wrote in message
news:eR0mzUrTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim
and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
|||Shouldn't SQL automatically be killing the victim, rolling back it's transaction? There really isn't a need to write a job to do this for you.
Have the application constantly checking for the 1205 message.
"Farhan Iqbal" wrote:
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
>
Now a day I am facing too-many deadlock problems, is there any way or
possibility that I make automated job, for diagnose the deadlock victim and
kill that process.
Any advice will be greatly appreciated.
Farhan Iqbal
Why would you want to kill the victim? The application should be able to
recover from a deadlock situation and either retry or gracefully handle it
some how. That really isn't a database issue. You would be best served by
spending that time fixing the reason why deadlocks occur in the first place.
Andrew J. Kelly SQL MVP
"Farhan Iqbal" <mr_farhaniqbal@.hotmail.com> wrote in message
news:eR0mzUrTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim
and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
|||Shouldn't SQL automatically be killing the victim, rolling back it's transaction? There really isn't a need to write a job to do this for you.
Have the application constantly checking for the 1205 message.
"Farhan Iqbal" wrote:
> Hi all
>
> Now a day I am facing too-many deadlock problems, is there any way or
> possibility that I make automated job, for diagnose the deadlock victim and
> kill that process.
>
> Any advice will be greatly appreciated.
> Farhan Iqbal
>
>
>
Sunday, March 11, 2012
deadlock and error code returned
The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ? be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ? --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNum
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ? be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ? --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>
|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-536X45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
> - Show quoted text -
thankyou
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ? be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ? --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNum
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ? be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ? --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>
|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-536X45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
> - Show quoted text -
thankyou
deadlock and error code returned
The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
> > The question is: if the below sproc execution transaction becomes a
> > deadlock victim, would it give a error? If yes, what kind of error
> > would it give.
> > In the below example, what should ' be?
> > /*
> > Sample Call:
> > declare @.ret int
> > @.ret =3D exec usp_testSproc
> > if @.ret =3D ' --
> > Print "The sproc is a deadlock victim"
> > */
> > create proc usp_testSproc
> > as
> > declare @.errNum int
> > set @.errNum =3D 0
> > select name, dept
> > from department
> > where name =3D"xyz"
> > set @.errNum =3D @.@.ERROR
> > if @.errNum <> 0
> > print cast(@.errNum as varchar(10) + ": Error occured"
> > return @.errNum- Hide quoted text -
> - Show quoted text -
thankyou
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
> > The question is: if the below sproc execution transaction becomes a
> > deadlock victim, would it give a error? If yes, what kind of error
> > would it give.
> > In the below example, what should ' be?
> > /*
> > Sample Call:
> > declare @.ret int
> > @.ret =3D exec usp_testSproc
> > if @.ret =3D ' --
> > Print "The sproc is a deadlock victim"
> > */
> > create proc usp_testSproc
> > as
> > declare @.errNum int
> > set @.errNum =3D 0
> > select name, dept
> > from department
> > where name =3D"xyz"
> > set @.errNum =3D @.@.ERROR
> > if @.errNum <> 0
> > print cast(@.errNum as varchar(10) + ": Error occured"
> > return @.errNum- Hide quoted text -
> - Show quoted text -
thankyou
deadlock and error code returned
The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5364
5181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=
36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
>
>
>
> - Show quoted text -
thankyou
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5364
5181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:
> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=
36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
>
>
>
> - Show quoted text -
thankyou
Thursday, March 8, 2012
Deadlock
I get the message "Transaction (Process ID 67) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
Yannis
Hi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
Yannis
Hi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>
Deadlock
I get the message "Transaction (Process ID 67) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
YannisHi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
YannisHi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>
Deadlock
I get the message "Transaction (Process ID 67) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
YannisHi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction".
I think I know what it means but my question is what is a resource ? Can it
be a specific record or it must be a whole table?
Thanks
YannisHi Yannis
Lockable resources include tables, indexes, pages, rows etc.
To find out WHICH resource was involved, you need to collect an output
report from the server called a "deadlock graph" which is written to the SQL
Log in the event a deadlock occurs if you have the following trace flags on:
dbcc traceon (3605, 1204, -1)
You simply run that command in the Query Analyser when logged in as an admin
and any time a deadlock occurs, you inspect the SQL Log (In the Enterprise
Manager under Management). To work out which resources were deadlocking, you
get the object id from the graph & look it up in the database's sysobjects
system table.
This post might be a bit technical, but try & work through it & post back
with any further questions if anything doesn't make sense / isn't clear
enough.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OAiNCNwUEHA.1952@.TK2MSFTNGP12.phx.gbl...
> I get the message "Transaction (Process ID 67) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction".
> I think I know what it means but my question is what is a resource ? Can
it
> be a specific record or it must be a whole table?
> Thanks
> Yannis
>
Subscribe to:
Posts (Atom)