1

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.)

3
  • What RDBMS are you working with?
    – user507
    Jul 13, 2014 at 4:14
  • Sorry, SQL Server. Jul 13, 2014 at 5:04
  • So they are trying to say that connection pooling is always not viable? That's a controversial statement...
    – usr
    Jul 13, 2014 at 12:08

2 Answers 2

2

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.

1
  • Connection pooling bit makes sense. Thanks. Jul 13, 2014 at 13:52
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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