Low volume deadlocks?

Low volume deadlocks?

Post by Joel Wilso » Fri, 28 Sep 2001 04:38:12



We're doing testing on a new application. There's only about 10 testers
filling in mult-page applications so the database volume is relatively low.
Recently we started seeing the following error fairly frequently (several
times a day):

Transaction (Process ID 76) was deadlocked on {lock} resolving another
process and has been chosen as the deadlock victim. Rerun the transactin.
Number -2147467259

I don't understand how we could get so many deadlocks in such a low volume
test.

We're using VB objects and ADO to query. The objects are running under COM+
with the lowest possible transaction setting. For example all reads run
under "no transactions". Only multi-table writes require transactions. Are
we doing something wrong?

 
 
 

Low volume deadlocks?

Post by John Good » Fri, 28 Sep 2001 10:44:26


I assume by the message  you receive that you use SQL SERVER.

I can also guess that maybe you don't have a lot of data in the tables, if
so and due to the fact that  SQL SERVER uses page locking by default then
your data is locked by pages and so causes deadlocks.

you can solve this by
1) defining the locks as row level locking and not page or table level
locking.

2) defining the lock types - SQL Server can use something like (don't catch
me on the number) eight levels of locking beginning from read lock - which
enables some users in parallel read the same data  without causing dead
locks and up to full lcoks which you should set only when updating, I
suggest you upgrade the lock type during the session, e.g. set it first to
read and only if the user wants to edit then upgrade to update locks.  you
can de-grade the locks back to read when the user again won't change the
data but just browse through it.

I hope that would solve your problem.


Quote:> We're doing testing on a new application. There's only about 10 testers
> filling in mult-page applications so the database volume is relatively
low.
> Recently we started seeing the following error fairly frequently (several
> times a day):

> Transaction (Process ID 76) was deadlocked on {lock} resolving another
> process and has been chosen as the deadlock victim. Rerun the transactin.
> Number -2147467259

> I don't understand how we could get so many deadlocks in such a low volume
> test.

> We're using VB objects and ADO to query. The objects are running under
COM+
> with the lowest possible transaction setting. For example all reads run
> under "no transactions". Only multi-table writes require transactions. Are
> we doing something wrong?


 
 
 

Low volume deadlocks?

Post by Robin Sh » Fri, 28 Sep 2001 12:41:45


First, I think you should use SQL Server tools to find the cause of the
deadlock. You can use sp_lock, current activity window, trace and etc to
find the object and process and why it's locked with your own code.

After that you can take action accordingly, for example:
Use low level isolation level for SQL Server; make transaction short; use
resource with the same order and etc.

The following article should be useful for you:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/support/kb/articles/Q224/4/53.ASP

INFO: COM+ and MTS Always Use SERIALIZABLE Transaction Isolation Level
http://support.microsoft.com/support/kb/articles/Q215/5/20.ASP

Hope this helps,

Robin

 
 
 

Low volume deadlocks?

Post by Dan Holme » Fri, 28 Sep 2001 21:31:21



> I assume by the message  you receive that you use SQL SERVER.

> I can also guess that maybe you don't have a lot of data in the tables, if
> so and due to the fact that  SQL SERVER uses page locking by default then
> your data is locked by pages and so causes deadlocks.

> you can solve this by
> 1) defining the locks as row level locking and not page or table level
> locking.

How?

--
-------------------
Dan Holmes
Integrated Visual Systems, Inc.
voice 704-847-3379
fax   704-847-4655

work -> http://www.ivsi.com
play -> http://www.geocities.com/heartland/hollow/3097

Insert Disclaimer:
Most of the time i think for myself, at least that is what
they tell me.

 
 
 

1. Low volume deadlocks?

We're doing testing on a new application. There's only about 10 testers
filling in mult-page applications so the database volume is relatively low.
Recently we started seeing the following error fairly frequently (several
times a day):

Transaction (Process ID 76) was deadlocked on {lock} resolving another
process and has been chosen as the deadlock victim. Rerun the transactin.
Number -2147467259

I don't understand how we could get so many deadlocks in such a low volume
test.

We're using VB objects and ADO to query. The objects are running under COM+
with the lowest possible transaction setting. For example all reads run
under "no transactions". Only multi-table writes require transactions. Are
we doing something wrong?

2. Random access to DATA (database) in VB

3. Clients can query only low volume of informations

4. Visual Pick for Universe - WordMark

5. best low volume SQL database for web cgi ???

6. Installing SQL Server 7.0

7. lower and lower performance when restore logical log

8. US-SAN FRANCISCO, CA-ORACLE P/A NEEDED

9. Deadlocked by Deadlocks

10. Deadlock situtation (deadlock victim) - help needed!!

11. 1 Volume Raid 10 vs. n Volumes Raid 1

12. High-Speed/Volume Database

13. Sql Server 6.5 Data Volume