Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been testing a system application and have noticed that it is slow when it connects to the database overseas. This is to be expected to some extent, but there is a big difference with the domestic version, and after investigation realized that after each function the connection is closed. So, for one operation the connection is opened and closed around 30 times causing considerable lag. I asked why can't the connection be left open and closed at the end. The reply was, "It causes deadlocks".

How true is this, and if there is any advice please provide some. (System application is used by few hundred people but not constantly in use by everyone.)

share|improve this question
    
What RDBMS are you working with? –  Shawn Melton Jul 13 at 4:14
    
Sorry, SQL Server. –  Damien Golding Jul 13 at 5:04
    
So they are trying to say that connection pooling is always not viable? That's a controversial statement... –  usr Jul 13 at 12:08

2 Answers 2

up vote 2 down vote accepted

I can only speak toward SQL Server.

A database connection itself is not going to cause deadlocks. If it did connection pooling would be useless.

How they are handling and managing transactions against the database can have an impact on deadlocks. There are plenty of scripts online that can be found on how to force a deadlock using SSMS, most are with two query windows that are separate sessions over one connection.

share|improve this answer
    
Connection pooling bit makes sense. Thanks. –  Damien Golding Jul 13 at 13:52

The answer depends on what is really going on the wire (that is, in client session).

First, what is a deadlock? It is a cycle in lock dependency graph. Whatever it means, to make a deadlock occur, you need to have some locks first.

Locks normally do not live outside of transactions/queries.

A connection itself cannot cause any deadlocks if it does not put locks on database objects. (Shared object access is main purpose of locks)

If the connection opens a transaction, does some queries (effectively locking some database objects), and then still executes or sleeps in middle of the transaction (without commit/rollback), it COULD cause a deadlock - when sibling transactions access same resources.

If it is just an idle connection - there's no risk of deadlock.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.