C Multithreading - Sqlite3 database access by 2 threads crash

Question

Here is a description of my problem:

I have 2 threads in my program. One is the main thread and the other one that i create using pthread_create

The main thread performs various functions on an sqlite3 database. Each function opens to perform the required actions and closing it when done.

The other thread simply reads from the database after a set interval of time and uploads it onto a server. The thread also opens and closes the database to perform its operation.

The problem occurs when both threads happen to open the database. If one finishes first, it closes the database thus causing the other to crash making the application unusable. Main requires the database for every operation.

Is there a way I can prevent this from happening? Mutex is one way but if I use mutex it will make my main thread useless. Main thread must remain functional at all times and the other thread runs in the background.

Any advice to make this work would be great. I did not provide snippets as this problem is a bit too vast for that but if you do not understand anything about the problem please do let me know.

EDIT:

static sqlite3 *db = NULL;

Code snippet for opening database

int open_database(char* DB_dir) // argument is the db path
        rc = sqlite3_open(DB_dir , &db); 

        if( rc )                
        {
//failed to open message
            sqlite3_close(db); 
            db = NULL;
            return SDK_SQL_ERR;
        }
        else
        {
            //success message
        }
    }
    return SDK_OK;

}

And to close db

int close_database()
{
    if(db!=NULL)
    {
        sqlite3_close(db);
        db = NULL;
        //success message
    }
    return 1;
}

EDIT: I forgot to add that the background thread performs one single write operation that updates 1 field of the table for each row it uploads onto the server

Why would it make your main thread useless? Compare it to what you have now, which is a whole program that can become useless, and maybe even corrupt the database. — Some programmer dude, Aug 18 '14 at 05:57
If i use mutex and if the 2nd thread gets ownership my main thread will not be able to use the database till the background thread is done and the user will not be able the use the main thread functions — bunni, Aug 18 '14 at 05:59
Does SQLite not provide multiple concurrent connections to the database from a single process, precisely for threading applications? If it does, you should make sure that the main thread and the other thread each use their own connection to the database. If it does not provide such separation, then your design with multiple opens and closes is wrong — and it may be that your design using multiple threads is wrong (you should really use two separate processes instead of two threads in a single process). — Jonathan Leffler, Aug 18 '14 at 06:01
To be honest I actually didnt think of looking up concurrent connections =) I will try to find information on that, thanks — bunni, Aug 18 '14 at 06:05
See: [SQLite and Multiple Threads](http://www.sqlite.org/threadsafe.html). — Jonathan Leffler, Aug 18 '14 at 06:18
I am guessing I have to use the multithreaded mode? I am sorry if I sound stupid, I am very new to this. Here is what I read somewhere: In multi-thread mode, this database handle mutex is omitted. So things run slightly faster because there is one less mutex grab each time an API call is made. But if you make simultaneous calls on a single database handle from multiple evilthreads, sqlite will crash or malfunction. — bunni, Aug 18 '14 at 06:34
IN this sample you have only one conenction variable: db. I was expecting to see two, a mainDb and workerThreadDb, so you would have two indepdent connections, then they can be opened and close at will. — djna, Aug 18 '14 at 12:58
In this example yes. I did make another one but the result was the same. I also forgot to mention that the 2nd thread does perform a single update query for each uploaded row. Could that be the issue? — bunni, Aug 18 '14 at 13:06
Yes, you need to compile SQLite for concurrent threads, and you need two handles, `sqlite3 *db1;` for the main thread and `sqlite3 *db2;` for the secondary thread (where the choice of names is up to you, of course). I think you should review why you open and close the database incessantly. You should find that you can keep each handle open while ensuring there is no pending activity on the handle at points where you currently close the handle. — Jonathan Leffler, Aug 18 '14 at 14:35
Wouldnt keeping the sql connection open at all times be prone to corruption of data? I have read that it is good practice to open and close the db when done — bunni, Aug 19 '14 at 04:39

score 2 · Answer 1 · answered Aug 18 '14 at 06:02

2

Have your threads each use their own database connection. There's no reason for the background thread to affect the main thread's connection.

Generally, I would want to be using connection pooling, so that I don't open and close database connections very frequently; connection opening is an expensive operation.

In application servers we very often have many threads, we find that a connection pool of a few tens of connections is sufficient to service requests on behalf of many hundreds of users.

answered Aug 18 '14 at 06:02

djna

54,992
14
74
117

To be honest I actually didnt think of looking up concurrent connections =) I will try to find information on that, thanks – bunni Aug 18 '14 at 06:05
Okay if I use the different connection for the second thread with sqlite_open_v2 I can't use sqlite_close_v2. It isnt there in my library? – bunni Aug 18 '14 at 06:58
It's a documented method. Can't see how it can be missing. You'll have to ask the sqliite folks. – djna Aug 18 '14 at 07:07
I use a different connection for the background thread but the problem is using sqlite_close closes the connection here causing the main thread to crash with it =( So problem is still the same – bunni Aug 18 '14 at 07:25
@user3598530 The your `sqlite3_close` call is wrong. – CL. Aug 18 '14 at 07:48
Which portion do you require snippets of? – bunni Aug 18 '14 at 09:20
the sections where you open and close seem to be where you have the problems – djna Aug 18 '14 at 09:23
Updated original question with snippets! – bunni Aug 18 '14 at 09:43

score 0 · Answer 2 · answered Aug 18 '14 at 14:01

0

Basically built into sqlite3 there are mechanisms to provide locking... BEGIN EXCLUSIVE then you can also register a sleep callback so that the other thread can do other things...

see sqlite3_busy_handler()

answered Aug 18 '14 at 14:01

Grady Player

14,399
2
48
76

C Multithreading - Sqlite3 database access by 2 threads crash

2 Answers2