I've read http://www.sqlite.org/cvstrac/wiki?p=MultiThreading under the "Case in point: a benchmark application I've written for this purpose" and found that current releases of SQLite do not appear to behave in this manner. I cannot find any documentation which clearly states the intended behavior.
It appears that if 2 threads start transactions at the same time, both inserting into the same table, neither thread can finish until one has rolled back. The first thread succeeds until the COMMIT is issued, then returns BUSY on the COMMIT. The second thread keeps returning BUSY on the INSERT statement. I've created a test case which will retry on BUSY (up to 25x to prevent infinite loops). You'll notice the test ultimately fails. Also in this test case, I've created a test which does a ROLLBACK when a BUSY is hit just to show that it does succeed. Is this intended functionality? It appears at least a release at some point in time did not behave this way (Jan 10, 2003 from the Wiki). Considering the second thread never gets a successful response to an INSERT statement, it would seem that it should not have tried to obtain a lock on that table, preventing the COMMIT from succeeding... but it is... It seems to be a bug to me. I have attached the test case. Any insight would be appreciated. Here are the results (for both RETRY_BUSY scenarios and ROLLBACK): $ gcc -Wall -D RETRY_BUSY=1 -W -o sqlitetest sqlitetest.c -l sqlite3 [EMAIL PROTECTED] ~ $ ./sqlitetest Creating a table 0 => Executing: CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT) 0 => started .... 1 => started .... all threads started 0 => Executing: BEGIN TRANSACTION 1 => Executing: BEGIN TRANSACTION 0 => Executing: INSERT INTO test_table VALUES(0, 0, 'test0_0') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 1, 'test0_1') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 2, 'test0_2') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 3, 'test0_3') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 4, 'test0_4') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 5, 'test0_5') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 6, 'test0_6') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 7, 'test0_7') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 8, 'test0_8') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: INSERT INTO test_table VALUES(0, 9, 'test0_9') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => BUSY 0 => Executing: COMMIT 0 => BUSY 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => MAX BUSY CNT 1 => thread failed ... 0 => Executing: COMMIT 0 => finished. exiting...(test failed) [EMAIL PROTECTED] ~ $ gcc -Wall -D RETRY_BUSY=0 -W -o sqlitetest sqlitetest.c -l sqlite3 [EMAIL PROTECTED] ~ $ ./sqlitetest Creating a table 0 => Executing: CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT) 0 => started .... 1 => started .... all threads started 0 => Executing: BEGIN TRANSACTION 1 => Executing: BEGIN TRANSACTION 0 => Executing: INSERT INTO test_table VALUES(0, 0, 'test0_0') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 0 => Executing: INSERT INTO test_table VALUES(0, 1, 'test0_1') 1 => Executing: ROLLBACK 1 => ROLLBACK succeeded 1 => Retrying transaction ... 0 => Executing: INSERT INTO test_table VALUES(0, 2, 'test0_2') 1 => Executing: BEGIN TRANSACTION 0 => Executing: INSERT INTO test_table VALUES(0, 3, 'test0_3') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 0 => Executing: INSERT INTO test_table VALUES(0, 4, 'test0_4') 1 => Executing: ROLLBACK 1 => ROLLBACK succeeded 1 => Retrying transaction ... 0 => Executing: INSERT INTO test_table VALUES(0, 5, 'test0_5') 1 => Executing: BEGIN TRANSACTION 0 => Executing: INSERT INTO test_table VALUES(0, 6, 'test0_6') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 0 => Executing: INSERT INTO test_table VALUES(0, 7, 'test0_7') 1 => Executing: ROLLBACK 1 => ROLLBACK succeeded 1 => Retrying transaction ... 0 => Executing: INSERT INTO test_table VALUES(0, 8, 'test0_8') 1 => Executing: BEGIN TRANSACTION 0 => Executing: INSERT INTO test_table VALUES(0, 9, 'test0_9') 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 0 => Executing: COMMIT 0 => finished. 1 => Executing: ROLLBACK 1 => ROLLBACK succeeded 1 => Retrying transaction ... 1 => Executing: BEGIN TRANSACTION 1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0') 1 => Executing: INSERT INTO test_table VALUES(1, 1, 'test1_1') 1 => Executing: INSERT INTO test_table VALUES(1, 2, 'test1_2') 1 => Executing: INSERT INTO test_table VALUES(1, 3, 'test1_3') 1 => Executing: INSERT INTO test_table VALUES(1, 4, 'test1_4') 1 => Executing: INSERT INTO test_table VALUES(1, 5, 'test1_5') 1 => Executing: INSERT INTO test_table VALUES(1, 6, 'test1_6') 1 => Executing: INSERT INTO test_table VALUES(1, 7, 'test1_7') 1 => Executing: INSERT INTO test_table VALUES(1, 8, 'test1_8') 1 => Executing: INSERT INTO test_table VALUES(1, 9, 'test1_9') 1 => Executing: COMMIT 1 => finished. exiting...(test succeeded) Thanks! -Brad
#include <stdio.h> #include <sqlite3.h> #include <stdlib.h> #include <string.h> #include <sys/stat.h> #include <pthread.h> #ifdef __Linux__ # define _XOPEN_SOURCE 500 #endif #include <unistd.h> #define SQLITEDB "test.db" #define NUM_THREADS 2 #define MAX_BUSY 25 #define NUM_INSERTS 10 #ifndef RETRY_BUSY #define RETRY_BUSY 1 #endif static void sync_thread(int thnum); typedef struct sql_result { int alloc_rows; int rows; int cols; char ***data; } sql_result; typedef struct th_st { int th_num; /* 0 = not started, 1 = started, 2 = done, 3 = error */ int state; } th_st; th_st *th_data = NULL; pthread_mutex_t global_mutex; int on_thread = 0; #define SQLITE_ALLOC_ROWS 1024 static int sqlite3_rowcallback(void *myresult, int argc, char **argv, char **colName) { sql_result *result = myresult; int i; (void)colName; if (result == NULL) return (SQLITE_ERROR); if (result->cols == 0) result->cols=argc; else if (result->cols != argc) return (SQLITE_ERROR); if (result->rows >= result->alloc_rows) { result->data=(char ***)realloc(result->data, (result->alloc_rows+SQLITE_ALLOC_ROWS)*sizeof(char **)); result->alloc_rows += SQLITE_ALLOC_ROWS; } result->data[result->rows] = (char **)malloc((result->cols)*sizeof(char *)); for (i=0; i<result->cols; i++) { if (argv[i] == NULL) result->data[result->rows][i] = NULL; else result->data[result->rows][i] = strdup(argv[i]); } result->rows++; return(SQLITE_OK); } static void sqlite3_printresult(sql_result *result) { int i, j; if (!result->rows) return; printf("Query Results:\n"); for (i=0; i<result->rows; i++) { for (j=0; j<result->cols; j++) { if (j != 0) printf(","); printf("%s", result->data[i][j]); } printf("\n"); } printf("\n"); } static void sqlite3_freeresult(sql_result *result) { int i, j; if (!result->rows) return; for (i=0; i<result->rows; i++) { for (j=0; j<result->cols; j++) { free(result->data[i][j]); } free(result->data[i]); } free(result->data); } /** sqlite3_execute * Description: Execute SQL statment, loop on busy * Return : 1 on success, 0 on failure, -1 on rollback/concurrency issue */ static int sqlite3_execute(sqlite3 *db_conn, const char *statement, void (*sync_thread_p)(int), int thnum) { int rc, retval; sql_result result; char *errmsg = NULL; #if RETRY_BUSY int busy_cnt = 0; #endif memset(&result, 0, sizeof(result)); do { errmsg = NULL; if (sync_thread_p != NULL) (*sync_thread_p)(thnum); printf("%d => Executing: %s\n", thnum, statement); rc = sqlite3_exec(db_conn, statement, sqlite3_rowcallback, &result, &errmsg); switch (rc) { case SQLITE_OK: retval = 1; break; case SQLITE_BUSY: #if RETRY_BUSY busy_cnt++; break; #endif case SQLITE_LOCKED: /* Issue a rollback if not in autocommit mode */ if (!sqlite3_get_autocommit(db_conn)) { if (sqlite3_execute(db_conn, "ROLLBACK", sync_thread_p, thnum) != 1) { printf("%d => ROLLBACK failed\n", thnum); } else { printf("%d => ROLLBACK succeeded\n", thnum); } } retval = -1; break; default: printf("%d => query failed: %s\n", thnum, errmsg); retval = 0; break; } if (errmsg != NULL) { sqlite3_free(errmsg); errmsg = NULL; } #if RETRY_BUSY /* Don't loop too fast */ if (rc == SQLITE_BUSY) { if (busy_cnt > MAX_BUSY) { rc = SQLITE_ERROR; retval = 0; printf("%d => MAX BUSY CNT\n", thnum); } else { printf("%d => BUSY\n", thnum); usleep(10000); } } } while (rc == SQLITE_BUSY); #else } while (0); #endif sqlite3_printresult(&result); sqlite3_freeresult(&result); return(retval); } static void sync_thread(int thnum) { /* Unassign from self, and assign to next thread */ pthread_mutex_lock(&global_mutex); if (on_thread == thnum) on_thread++; pthread_mutex_unlock(&global_mutex); while(1) { pthread_mutex_lock(&global_mutex); /* Thread could disappear */ if (on_thread < NUM_THREADS && th_data[on_thread].state >= 2) on_thread++; if (on_thread >= NUM_THREADS) on_thread = 0; if (on_thread == thnum) { pthread_mutex_unlock(&global_mutex); break; } pthread_mutex_unlock(&global_mutex); usleep(20000); } } void *testthread(void *arg) { int rc; int rollback = 0; sqlite3 *db_conn = NULL; th_st *th = arg; char temp[255]; int i; rc = sqlite3_open(SQLITEDB , &db_conn); if (rc) { printf("%d => sqlite3_open() failed: %s\n", th->th_num, sqlite3_errmsg(db_conn)); sqlite3_close(db_conn); th->state = 3; return(NULL); } printf("%d => started ....\n", th->th_num); th->state = 1; /* We want to synchronize accesses by threads, just to see what happens */ do { if (rollback) { printf("%d => Retrying transaction ...\n", th->th_num); usleep(50000); } rollback = 0; rc = sqlite3_execute(db_conn, "BEGIN TRANSACTION", sync_thread, th->th_num); if (rc == -1) { rollback = 1; continue; } else if (rc == 0) break; for (i=0; i<NUM_INSERTS; i++) { snprintf(temp, sizeof(temp), "INSERT INTO test_table VALUES(%d, %d, 'test%d_%d')", th->th_num, i, th->th_num, i); rc = sqlite3_execute(db_conn, temp, sync_thread, th->th_num); if (rc == -1) { rollback = 1; break;; } else if (rc == 0) break; } if (rc == 0) break; if (rollback) continue; rc = sqlite3_execute(db_conn, "COMMIT", sync_thread, th->th_num); if (rc == -1) { rollback = 1; continue; } else if (rc == 0) break; } while (rollback); sqlite3_close(db_conn); if (rc == 0) { printf("%d => thread failed ...\n", th->th_num); th->state = 3; } else { th->state = 2; printf("%d => finished.\n", th->th_num); } return(NULL); } int main() { sqlite3 *db_conn = NULL; int rc, i; int retval = 0; struct stat myst; int trigger = 0; pthread_t thread; /* If the database already exists, lets go ahead and delete it */ if (stat(SQLITEDB, &myst) != -1) { unlink(SQLITEDB); } rc = sqlite3_open(SQLITEDB , &db_conn); if (rc) { printf("sqlite3_open() failed: %s\n", sqlite3_errmsg(db_conn)); sqlite3_close(db_conn); return(1); } /* Pre-create a table */ printf("Creating a table\n"); rc = sqlite3_execute(db_conn, "CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT)", NULL, 0); if (rc != 1) { printf("failed to create table\n"); sqlite3_close(db_conn); return(1); } sqlite3_close(db_conn); pthread_mutex_init(&global_mutex, NULL); pthread_mutex_lock(&global_mutex); /* Start all threads */ th_data = malloc(sizeof(*th_data)*NUM_THREADS); for (i=0; i<NUM_THREADS; i++) { th_data[i].th_num = i; th_data[i].state = 0; pthread_create(&thread, NULL, testthread, &(th_data[i])); pthread_detach(thread); } /* Wait for all threads to start, then release mutex */ trigger = 0; while (!trigger) { trigger = 1; for (i=0; i<NUM_THREADS; i++) if (!th_data[i].state) trigger = 0; usleep(50000); } printf("all threads started\n"); pthread_mutex_unlock(&global_mutex); /* Wait for all threads to finish, then cleanup and exit */ trigger = 0; while (!trigger) { trigger = 1; for (i=0; i<NUM_THREADS; i++) if (th_data[i].state < 2) { trigger = 0; } else if (th_data[i].state > 2) { retval = 1; } usleep(50000); } pthread_mutex_destroy(&global_mutex); free(th_data); printf("exiting...(test %s)\n", (retval==0)?"succeeded":"failed"); return(retval); }
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------