((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 <[email protected]> wrote:
> On Mon, 29 Apr 2019 at 01:22, Lullaby Dayal <[email protected]>
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
