Database and connections

Question

I am interested in knowing how databases handle connections that are not explicitly closed by the entity that initiated the connection.

1. Say I have a database that can accept only 2 concurrent connections at a time. I have a piece of code that opens a connection and never closes it. This piece of code runs forever, but it uses the connection only once in it's lifetime but the connection object never goes out of scope, so it is not garbage collected. Say I run 2 instances of this code. Does it mean that until the program is terminated or the connection times out (due to inactivity) the database can accept no more connections?

2. In the above scenario if the connection object is garbage collected, then is the connection terminated automatically or does it depend on the database driver that I am using or the connection is not terminated at all until I close it explicitly?

3. If I open a connection in piece of code and I do not close the connection explicitly, but the program terminates then how does the database reclaim that connection?

Answer 1

To answer your questions in order:

1. Yes, probably. Unless by "concurrent connections" you actually mean "concurrent queries". If yo're holding the database connection open, it's open.

2. Garbage collection may or may not clean up the connection. Depends on the language and the database driver used. (Garbage collection may be limited to reclaiming memory, not resources like TCP connections.)

3. When your program terminates, generally the OS is responsible for cleaning up all resources it used. This includes closing TCP, etc. connections. So, for most connection types, the database will be notified that the other side closed the connection.

Answer 2

The underlying protocol for database connections is typically TCP/IP based. The connection can be terminated in one of several ways:

1. The server closes it gracefully and receives an acknowledgement from the client;
2. The client closes is gracefully and receives an acknowledgement from the server;
3. The connection times out. The client and server both separately are told by their respective operating systems that the connection has been closed;
4. The connection is forcefully closed by either side.

In the case of (3), TCP connections must be kept alive by sending dummy messages every so often to avoid a timeout. Your connection may timeout because neither side is doing this (and for a database connection it's not something you typically want to do).

It's entirely possible for a time for one side to think the connection is closed and hte other side to still believe it is open. Messages may get sent in those cases (and typically discarded).

Each connection ("socket") uses an operating system resource called a file descriptor (in UNIX parlance, your OS may call it something else), which is a handle to an I/O resource and the same thing used for an open file (again, OSs may vary).

The limit on connections on your database will be the lowest of:

• The configured limit for the OS;
• The maximum file descriptors allowed for that process (minus any being used for I/O activity); and
• (possibly) system settings or policies on connection limits.

If the connection isn't TCP based (eg a filesystem socket as is often used with MySQL on UNIX systems) the principles are in fact very simlar.

Anyway, the moral of the story to take from this is that a database connection--regardless of its form--involves an operating system resource of some kind. Your program hsa directly or indirectly asked for that resource. If the program dies, the operating system will reclaim it (perhaps not immediately but eventually). If the connection gets garbage collected then the resource will be released in much the same way as if it had been forcibly closed.

Its that external resource (and not the code your client is using) that holds the connection open and drives any limits.