How do RDBMS deal with stale connections ?

Question

Consider setting up a connection to a RDBMS, then yanking the network plug. (You'll get the same effect if the connection goes through a NAT gateway, and the gateway decides to purge that connection.)

At that point the RDBMS server is waiting for a query, or whatever, which will never occur. And the TCP connection won't be closed by tha client since that's not on that network anymore. Presumably the server will not close it either since it still believes the connection to be open.

Do typical RDBMS handle this with timeouts and/or implementing a simple keepalive mechanism over TCP ? Anyone have any experience with this for Oracle,SQL Server and MySQL ?

Edit; More digging suggests mysql purges connections after 8 hours of inactivity.

Answer 1

It's a pretty broad question. Let me give you a broad answer.

In the client/server days this would have been a bigger problem, since clients maintained persistent connections to the database. Nowadays, for scalability and other reasons, applications are generally written in a "disconnected" fashion, whereby they make a connection to the database to obtain their data, and then promptly disconnect. In other words, applications request connections on an as needed basis.

When an operation occurs that is "atomic" (i.e. the entire operation must complete successfully from beginning to end), that operation is wrapped in a transaction. If the database connection is severed at any time during the transaction, the database system will "roll back" the operation, placing any affected records in the same state they were before the transaction was started.

Timeouts insure that any stray connections to the database do not stay open forever.

Answer 2

When the underlying TCP connection is dropped either by the client or the server, the TCP/IP stack on the other side detects this and notifies the application. You can be sure the database server closes the connection (and rolls back any uncommitted transaction).

Update: A network failure just looks to each side like the other went away. Both sides would set socket timeouts, do retries, etc. so they could handle these failures gracefully. Jonathan brings up an interesting sub-issue in the comments, turns out the server does an implicit transaction commit if the client explicitly disconnects without terminating a transaction either way.

Answer 3

The specific situation you're talking about doesn't really happen. Just because a connection is established, doesn't imply that the RDBMS is "waiting" for a query to arrive. It may simply note the existence of the connection, start an asynchronous read operation, then go on about its business.

If it were a problem to have the async read never complete, then of course, the RDBMS could implement a periodic "ping" mechanism that would determine if the connection is still open. As I think you already know, a TCP/IP connection is only open if it's open on both ends, so if the RDBMS sent a "ping" to a client that is no longer there, then the write would time out the connection, and the server end would close. One would expect the RDBMS to notice that, and destroy any server-side resources associated with the connection.

Answer 4

Database connections are typically pooled so you don't have to incur the penalty of establishing the connection every time. Application servers provide this facility and they typically provide flexibility to flush the connection pool and re-establish all the connections in their pool when this stale connections state is detected.

Answer 5

The Oracle JDBC driver provides a proprietery ping() method on the connection class, which returns true if the connection is considered "active".

Unfortunately, ping() was implemented by an idiot. It sends a "select 'x' from dual" query to the server, which is far from being a low-impact query due to the presense of the literal 'x' which gives the query cache a hard time.

We used to call ping() from our app server every time a connection was borrowed from the pool, until it almost killed our oracle server.

Not recommended.

Answer 6

In SQL Server, the connection (you can see this with sp_who) is closed and uncommitted transactions are rolled back.

What I've seen on the client is that a subsequent attempt on the same connection from the client (in the case of the VPN or NAT being reconnected) usually results in a Transport Error at the client side and a new connection is created in the case of SSMS for subsequent batches in the same window.