((Your post misses a detail which changes the answers to your questions: are these threads each using their own connection or do they share one connection ? )) Please find my test program below. I haven't used any such advanced features in my code unless it is already configured in my prebuilt library which I am using. I have only verified that Sqlite3 works in serialized threading mode.. Also all threads share same db connection assuming sqlite3_exec() handles all serialization for me.. please share your comments..
#include <stdio.h> #include <stdlib.h> #include <sqlite3.h> #include <string.h> #include <pthread.h> #include <stdbool.h> #include <unistd.h> #include <time.h> sqlite3 *db = NULL; static void * threadFunc(void *arg) { int rc; int r; char *errmsg; char sqlcmd[500]= "\0"; unsigned int i = 0; static bool alt = true; while(1) { //sprintf(sqlcmd,"update demo set age = %d where name='Tom'; update demo set age = %d where name='Chris'; ", ++i, i); if (alt) { rc = sqlite3_exec(db, "update demo set age = 21 where name='Tom'; update demo set age = 31 where name='Chris'; ", NULL, NULL, &errmsg); alt = false; } else { rc = sqlite3_exec(db, "update demo set age = 41 where name='Tom'; update demo set age = 51 where name='Chris'; ", NULL, NULL, &errmsg); alt = true; } if (rc != SQLITE_OK) { printf("T0: Error: %s\n", errmsg); //goto out; } //usleep(100); } } static void * threadFunc1(void *arg) { int rc; int r; char *errmsg; char sqlcmd[500]= "\0"; unsigned int i = 0; static bool alt = true; while(1) { //sprintf(sqlcmd,"update demo set age = %d where name='Tom'; update demo set age = %d where name='Chris'; ", ++i, i); if (alt) { rc = sqlite3_exec(db, "SELECT age FROM demo WHERE name = 'Tom';", NULL, NULL, &errmsg); alt = false; } else { rc = sqlite3_exec(db, "SELECT age FROM demo WHERE name = 'Chris'; ", NULL, NULL, &errmsg); alt = true; } if (rc != SQLITE_OK) { printf("T1 Error: %s\n", errmsg); //goto out; } //usleep(200); } } static void * threadFunc3(void *arg) { int rc; int r; char *errmsg; char sqlcmd[500]= "\0"; unsigned int i = 0; static bool alt = true; while(1) { //sprintf(sqlcmd,"update demo set age = %d where name='Tom'; update demo set age = %d where name='Chris'; ", ++i, i); if (alt) { rc = sqlite3_exec(db, "BEGIN TRANSACTION; update demo set age = 44 where name='Tom'; update demo set age = 33 where name='Chris'; COMMIT; ", NULL, NULL, &errmsg); alt = false; } else { rc = sqlite3_exec(db, "BEGIN TRANSACTION; update demo set age = 50 where name='Tom'; update demo set age = 72 where name='Chris'; COMMIT; ", NULL, NULL, &errmsg); alt = true; } if (rc != SQLITE_OK) { printf("T2 Error: %s\n", errmsg); //goto out; } //usleep(300); } } int main() { sqlite3_stmt *stmt; int rc; char *errmsg; pthread_t t1[100], t2[100], t3; void *res; int s; /* * open SQLite database file test.db * use ":memory:" to use an in-memory database */ rc = sqlite3_open("test.db", &db); if (rc != SQLITE_OK) { printf("ERROR opening SQLite DB in memory: %s\n", sqlite3_errmsg(db)); goto out; } int threadsafe = sqlite3_threadsafe(); printf("Sqlite thread safety value: %d", threadsafe); srand(time(NULL)); // Initialization, should only be called once. rc = sqlite3_exec(db, "create table demo (name text, age, integer);", NULL, NULL, &errmsg); if (rc != SQLITE_OK) { printf("Error: %s\n", errmsg); goto out; } for(int i = 0; i < 100; i++) { s = pthread_create(&t1[i], NULL, threadFunc, NULL); if (s != 0) { printf("\n ERROR: pthread_create"); goto out; } s = pthread_create(&t2[i], NULL, threadFunc1, NULL); if (s != 0) { printf("\n ERROR: pthread_create"); goto out; } } #if 0 s = pthread_create(&t3, NULL, threadFunc3, NULL); if (s != 0) { printf("\n ERROR: pthread_create"); goto out; } #endif printf("Message from main()\n"); for(int i = 0; i < 100; i++) { s = pthread_join(t1[i], &res); if (s != 0) printf("\n ERROR: pthread_join"); s = pthread_join(t2[i], &res); if (s != 0) printf("\n ERROR: pthread_join"); } #if 0 /* * select using the convencience wrapper sqlite3_exec() (exec uses a callback function) */ int callback(void *arg, int argc, char **argv, char **colName) { int i; for(i=0; i<argc; i++){ printf("%s = %s\t", colName[i], argv[i] ? : "NULL"); } printf("\n"); return 0; } rc = sqlite3_exec(db, "select count(*), avg(age) from demo; select distinct name, age from demo order by 1,2;", callback, NULL, &errmsg); if (errmsg != NULL) { printf("Error in sqlite3_exec: %s\n", errmsg); sqlite3_free(errmsg); } #endif out: sqlite3_close(db); } On Mon, Apr 29, 2019, 4:27 AM Rowan Worth <row...@dug.com> wrote: > On Mon, 29 Apr 2019 at 01:22, Lullaby Dayal <lullaby.tec...@gmail.com> > wrote: > > > > > Considering all this, I have written a test application running on Linux > > with sqlite3 library in serialized mode. My test application has 200 > > parallel threads in which 100 threads are executing SELECT * operation > from > > a table and 100 are executing update table (alternate fields in alternate > > run) command in auto-commit mode (while(1)). I haven't verified the data > > correctly written in database as I only rely on return code and I was > > stress testing. I expect at some point it should produce SQLITE_BUSY > > command at some point of time. But it didn't. > > > > Only thing I got is:- while the test application is running, in a > separate > > SQLite command prompt I open the same database and executed .tables > > command. This time, I got a database locked error in my test > application. > > > > So my questions are:- > > > > 1. In auto-commit mode in serialized threading mode, how command queueing > > works? > > > 2. Multiple simultaneous calls to sqlite_exec() performing Multiple write > > commands or read commands while write is in progress - will this be > handled > > by sqlite_exec() itself? Or does the application need to do some kind of > > locking to avoid such situation as mentioned in the FAQ? In serialized > > mode, sqlite3 implements its own locking, right? Do application need to > do > > a high level locking beyond this? > > > > In serialized threading mode using sqlite3_exec, I don't believe you'll get > any DB concurrency between threads -- rather each thread will take turns to > run sqlite3_exec (which holds a connection-level mutex while it executes). > This is why you never see SQLITE_BUSY. > > To allow different threads to access the DB concurrently, they need to use > separate connections. However given that a lot of what sqlite does is i/o > bound you won't necessarily find any performance benefits from > multi-threading. If your DB is small enough and no other processes are > updating the DB, you could think about upping the cache_size PRAGMA and > using shared-cache mode for the connections to minimise i/o. > > -Rowan > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users