! SQL Server 6.5 Deadlocks !

Have you determined via Current Activity in EM or via SQLTrace what processes are causing the deadlocks? Are these actually deadlocks or is someone just blocking someone else? A deadlock is a two-way street and SQL Server should pick a victom and rollback one of the conficting transactions.

I`m not sure if removing foreign key relationships will help out... the validation should be read only so unless there is an exclusive lock out there it should be pretty compatible.

I`d use SQLTrace first and find out what is going on and then I`d look at the applications (stored procedures, however they are accessing the database) to see if a less-than-optimal design is contributing to the problem. A lot of time the applications may be using an inappropriate cursor type that is causing a lot of locking. Dynamic recordsets can be extremely troublesome.

Another area to look at is to see if adding indexes to the tables could help in some cases but reducing table scans, etc.

Any further details might help out more,

Don

Here is an article on reducing locking from www.microsoft.com:

SQL Server Frequently Asked Questions
How can I avoid deadlocks in my application?
An unavoidable characteristic of any lock-based concurrent system is that blocking may occur under some conditions. Blocking happens when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection to either wait or block on the first.

For best scalability, performance, and concurrency, application and query design should emphasize keeping the transaction path length short and holding locks as briefly as possible. The foundation of most concurrency problems is laid when the application and database are designed. For this reason, it is critical that these issues be well understood at design time. Otherwise, a hidden performance limitation may be unintentionally engineered into the application, and this may not appear until full-scale stress testing.

Related Knowledge Base Articles
For more information about this topic and other related issues, see the Microsoft Knowledge Base. It contains thousands of articles that include answers to common questions about using Microsoft products. See the following Knowledge Base articles:

http://support.microsoft.com/support/kb/ARTICLES/Q162/3/61.asp

On 8/12/98 10:39:10 AM, Kalyan Seshu wrote:
> I need some help in reducing deadlocks in 6.5 I have tested with `Insert
> Row Locking` turned off and it reduced the number of deadlocks. What i need
> to know is if removing the foreign key relationships on tables
> reduces/eliminates Deadlocks. If any of you have any info on this please
> let me know.

Thanks
Kalyan