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