We are working on VB 6.0 as front-end tool and sqlserver 7.0 as back-end.
Currently we have started experiencing deadlock condition mostly when we are firing the update statements.
We haev tried closing all recordsets after using them and setting them to 'Nothing', but hasn't helped.
The number of users has nothing to do with this problem experienced.
As sometimes even with around 65-70 users we don't have this and sometimes even 1-2 people working on the network experience this.
It doesnt matter whether 50 users use the system simultaneously or no ... A deadlock can even occur when there are 2 users in the system. It depends on what tables of the database are being used and for what purpose.It is very likely that there are long running queries which are holding locks on the table while another user is either trying to query or update the same table.
There can be many reasons as to why a query/update suddenly starts running slowly all of a sudden.... the simples reasons can be that the table size has grown a lot larger than what it used to be or there can be external factors like CPU being used by another process which keeps the SQL server process to starve...
You will have to be very specific as to when u observe the deadlocks ...esp because u are saying that they dont occur all the time.
It will be really nice if you can provide more information
Oohhh, your problem is such general, that only general statements can be made.
Are you using DAO of ADO of ADO.NET, or are using Java or Borland technology?
First of all, I'm not sure whether you have a deadlock situation at all. In a deadlock situation, two transactions started, and one will be forced to roll-back. I guess, you have simply a locking problem, which occurs when you are updating a record, and a second process wants to read it.
Second thought: such a locking problem can een happen within 1 program, running by one user! I had that problem with two concurrent threads. So, the number of concurrent users isn't really an issue, if you have designed your application properly.
I don't have my old sources right here, but i remember that I had to set a kind of WaitForTransaction timeout, which was by default 0.
Make everything as simple as possible, but not simpler! - A. Einstein
DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool
Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:
Rolled back, undoing all the work performed by the transaction.
Resubmitted by applications because they were rolled back when deadlocked.
To help minimize deadlocks:
Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.