For long time, I thought setting some sort of SQL timeout on database client side was always a good idea, as it protected threads from hanging up, or released connections when not needed. For example, in WebService environment, if you know your customers quit within 30 seconds, why have a database connection servicing the customer working longer than that time? Why not just have the connection timeout in 30 seconds too.

Several hard lessons have learned that things are not that straight forward, especially with RAC, which is what we are using. As far as I know, there are two ways to set timeout from client side. One is to set it at Statement level, which is a straight JDBC feature. Internally Oracle sends a "Cancel" signal, via a different connection, to the server to cancel the execution, and after cancelling, the connection is returned to the pool.The other way is to use oracle.jdbc.ReadTimeout, which is supported by Oracle driver out of box. Internal implementation of this setting is actually to rely on TCP/IP read_time_out. Oracle just blindly set the value to TCP/IP connection and if no message during the time period, TCP/IP throws a ReadTimeOut exception. And when it happens, Orace throw the connection alway and not returning it to the pool.

I first implemented query timeout, because it is more "graceful" and not killing the connection. The way I did it is that before issuing a SELECT statement, I set against the Statement a query timeout which is the longest time my customer is willing to wait. Everything worked out fine during testing, but in production, we slowly noticed a connection timed out with ORA-1013: User Requested Cancel, which is expected. However, sometimes and not always, following ORA-1013, we see ORA-01006, bind variable doesn't exist, or ORA-01008: not all variables bound. And trust me, I know how to set bind variables so they are not true errors. It indicates to me that cached prepared statement pool for the connection that had timeout actually corrupted and it tries bind variables to a wrong prepared statement, so as long as number of variables not matching, there would be a bind error. And worse yet, as long as 1006 and 1008 happen, the connection cannot recover and we have to bounce the application to bring the application back to normal.

So query timeout didn't work and we tried ReadTimeout, and none of us could image that setting the value, with purpose of protecting servers and clients, actually killed the servers. What happened was that my application uses a lot of connections, let's say 2000 or more. And in one day, for whatever reason, the server paused, or slowed down, for a while. I don't know for how long exactly, but long enough so a number of connections experienced ReadTimeout. And because the implementation is at TCP/IP level, it is suspected that the server didn't know immediately the connections had quit so it was still trying to process those timed out sqls, although doing it slowly which made the matter worse. And because the timeout connections were thrown alway from the pool on the client side, the pool had room to create more connections. So new connections were created/logged in and started processing new requests. And because of the new and extra load, the server became even slower, hence more connections timed out, more new connections created and more load on server... And eventally the poor server crashed.

So what we do now? We leave both query timeout and ReadTimeout unset, so we just don't timeout. It is better to loss one or two threads, and hope they eventually come back, then keep on bouncing applications, or database servers.