9

Our product is a TCP listening transaction processor. Incoming connections are assigned a thread to handle the connection and a DB connection to work with.

Rather than costly approach of establishing new DB connection for each incoming client connection, we maintain a pool of database connections.

The database connection pool fairly configurable: min / max sizes, growth rates, etc.

Some details:

  • Platform is Windows 2003 / 2008 R2
  • DB is SQL Server 2005 / 2008 R2
  • Connection method is ODBC
  • Programming language is C++

Finally, the question:

As the service could be running for several months without a restart, there's a real chance that some of the database connections in the pool become invalid. I want to have as quick a way as possible to test the validity of a given connection before assigning it to an incoming connection.

Currently, I do this by executing the simple SQL statement "SELECT 123;", however I've found that this has significant negative performance impacts when parallel execution plans are used.

Very briefly in code, what I'm doing is:

// ... at some point we decide pool needs another connection...

// Set up database connection
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
SQLDriverConnect(conn, 0, in_str, in_len, out_str, DIM(out_str), &out_len, SQL_DRIVER_NOPROMPT);

// 'conn' is placed in DB connection pool

// ... some time later a new client connection comes in ...

// Execute simple statement to test if 'conn' is still OK
SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);
SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1;", SQL_NTS);

// If 'conn' is OK, give it to incoming connection;
// if not, get another connection from pool

Cheers,
Dave

user390935
  • 255
  • 1
  • 4
  • 9
  • I think your only option here may be to simply check the connections more often, but not as often as every time. Perhaps every 5 minutes or so? – rlb.usa Sep 14 '11 at 23:49

1 Answers1

11

Well the official way is SQLGetConnectAttr( SQL_ATTR_CONNECTION_DEAD ) which tests if the connection was working when last attempted.

Or SQLGetConnectAttr(conn, SQL_COPT_SS_CONNECTION_DEAD, ...) which tests if the connection is working now.

a1an
  • 3,526
  • 5
  • 37
  • 57
Nick Gorham
  • 1,118
  • 8
  • 9
  • 1
    Hi Nick, A better way is: SQLGetConnectAttr(conn, SQL_COPT_SS_CONNECTION_DEAD, ...) as this tests if the connection is working now. SQL_ATTR_CONNECTION_DEAD only tests if the connection was working when last attempted. Thanks for the initial pointer though - it got me on the right track. Cheers, Dave. – user390935 Sep 19 '11 at 16:17
  • 3
    I think the second one is windows specific since in unixODBC there is no such definition and I can find only "SQL_ATTR_CONNECTION_DEAD". Is it expected to be that way (maybe a newer ODBC spec version)? – a1an Jul 20 '12 at 10:42
  • @a1an it's MS SQL Server driver specific – OwnageIsMagic Mar 27 '20 at 12:38