Mailing-List: contact dev-help@apr.apache.org; run by ezmlm
Precedence: bulk
Received-SPF: pass (asf.osuosl.org: domain of bojan@rexursive.com designates
 203.171.74.242 as permitted sender)
Subject: [PATCH]: Make SQLite3 wait on BUSY inside dbd_sqlite3_query()
From: Bojan Smojver <bojan@rexursive.com>
To: APR Development List <dev@apr.apache.org>
Content-Type: multipart/mixed; boundary="=-Mc5hmZg9Giw5cItMHiv3"
Date: Mon, 17 Apr 2006 16:11:18 +1000
Message-Id: <1145254278.16371.37.camel@coyote.rexursive.com>
Mime-Version: 1.0


--=-Mc5hmZg9Giw5cItMHiv3
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

I tested this patch with 1.2.x, but I'd like people to check if all is
cool before it gets committed to any branch/trunk. Essentially, we're
not assuming any more that the sqlite3_step() call is always going to be
successful - this call can return SQLITE_BUSY just the same as the one
inside the dbd_sqlite3_select() function.

The gain from this patch was rather obvious in my testing, where I
hammered Apache at concurrency level 10 with requests (using ab) and
with APU DBD doing UPDATE to a single SQLite3 database. With this patch,
I had 0 failed queries out of 1000 requests. Without it, I'd get
anywhere between 750 and 1000 (!) failed queries out of 1000 requests in
this high contention scenario.

-- 
Bojan

--=-Mc5hmZg9Giw5cItMHiv3
Content-Disposition: attachment; filename=apr-util-SQLite3-query-busy.patch
Content-Type: text/x-patch; name=apr-util-SQLite3-query-busy.patch;
 charset=UTF-8
Content-Transfer-Encoding: 7bit

Index: apr_dbd_sqlite3.c
===================================================================
--- apr_dbd_sqlite3.c	(revision 394609)
+++ apr_dbd_sqlite3.c	(working copy)
@@ -253,12 +253,29 @@
     apr_dbd_mutex_lock();
 
     do {
+        int retry_count = 0;
+
         ret = sqlite3_prepare(sql->conn, query, length, &stmt, &tail);
         if (ret != SQLITE_OK) {
             sqlite3_finalize(stmt);
             break;
         }
-        ret = sqlite3_step(stmt);
+
+        while(retry_count++ <= MAX_RETRY_COUNT) {
+            ret = sqlite3_step(stmt);
+            if (dbd_sqlite3_is_success(ret))
+                break;
+
+            if (ret == SQLITE_BUSY){
+                apr_thread_mutex_unlock(sql->mutex);
+                apr_sleep(MAX_RETRY_SLEEP);
+                apr_thread_mutex_lock(sql->mutex);
+                continue;
+            } else {
+                break;
+            }
+        }
+
         *nrows = sqlite3_changes(sql->conn);
         sqlite3_finalize(stmt);
         length -= (tail - query);

--=-Mc5hmZg9Giw5cItMHiv3--