From e470d9172c6cb819691eb5a00831f01d5eeed8ce Mon Sep 17 00:00:00 2001 From: Nathan Bird Date: Fri, 20 Feb 2009 16:03:12 -0500 Subject: [PATCH] Where possible use a reset command against the DB to verify a pooled connection. This is related to 0a29c2fafbfb7ed42c54208cea576c6b0d600d82, when getting a connection out of the pool we issue a simple query on that connection to ensure that it is still valid. Several databases have reset commands for use with connection pools that also reset connection variables back to their default states so that connections coming from the pool behave as if they were new connections. This patch uses db specific commands to do this for * mssql - sp_reset_connection * postgres - "RESET ALL" --- sql/pool.lisp | 14 ++++++++++++-- 1 file changed, 12 insertions(+), 2 deletions(-) diff --git a/sql/pool.lisp b/sql/pool.lisp index 7aa3c99..d1c0a23 100644 --- a/sql/pool.lisp +++ b/sql/pool.lisp @@ -32,15 +32,25 @@ :initform (make-process-lock "Connection pool")))) (defun acquire-from-conn-pool (pool) + "Try to find a working database connection in the pool or create a new +one if needed. This performs 1 query against the DB to ensure it's still +valid. When possible (postgres, mssql) that query will be a reset +command to put the connection back into its default state." (or (with-process-lock ((conn-pool-lock pool) "Acquire from pool") (when (plusp (length (free-connections pool))) (let ((pconn (vector-pop (free-connections pool)))) ;; test if connection still valid. ;; (e.g. db reboot -> invalid connection ) (handler-case - (case (database-type pconn) - (:mysql + (case (database-underlying-type pconn) + (:postgresql + (database-execute-command "RESET ALL" pconn)) + (:mysql (database-query "SHOW ERRORS LIMIT 1" pconn nil nil)) + (:mssql + ;; rpc escape sequence since this can't be called as a normal sp. + ;;http://msdn.microsoft.com/en-us/library/aa198358%28SQL.80%29.aspx + (database-execute-command "{rpc sp_reset_connection}" pconn)) (T (database-query "SELECT 1;" pconn '(integer) nil))) (sql-database-error (e) -- 2.34.1