Does leaving a connection open for a matter of seconds cause deadlocks?

Question

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

Answer 1

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.

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