Multithreading data writing in SQLite

Question

I'm confused a little bit when tried to establish a multithreading work with my SQLite db, so I have a service which periodically load a data from server and insert it in different db tables, also any user in any time can store his own data in db when pressed "save button" in app windows, so based on the rules that only one thread at time can write a data in db I'd'd like to make a thread-cooperation. At first I have created a singleton which have only one db instance, and all goes pretty good with my read-db methods, cause all threads can read data in the same time, but what about writing? I use a thread inside write-function, and don't give a start another until previous thread has finished it work.(also I do it for calls from ui thread when user press save button)

Question: All I want to do is consider two situations - first is when threads call's same function to write data then I used synchronized, second - when threads call different write functions I should use a lock, right? So now I came to decision, but is it correct and right to do like that?

Code (Updated):

// Sync method for processing 1st situation
public synchronized void addPoints(final ArrayList<Point> points, final OnDBOperationListener listener) {
    if (listener != null) {
        // Lock for others write-threads in 2nd situaton
        synchronized (mWriteLock) {
            while (mWriteWait) {
                try {
                    mWriteLock.wait();
                } catch (InterruptedException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            mWriteWait = true;

                    try {
                        SQLiteDatabase db = getDatabase();
                        // write a data

                        listener.onSuccess();
                    } catch (Exception e) {
                        e.printStackTrace();
                        listener.onError();
                    } finally {
                        closeDatabase();
                        synchronized (mWriteLock) {
                            mWriteWait = false;
                            mWriteLock.notifyAll();
                        }
                    }

        }

    }
}

Each connection *should* be created on its own thread and *not* shared. If that is done then the threads can otherwise both use SQLite. Transactions can (and should be) used to control atomicity of actions wrt the database - transactions will take care of all the required locking at the database level. That being said, if the number of threads can be reduced or bound, then the problem is simplified to begin with. (Also, SQLite has poor multi-writer support due to the lock model.) — user2864740, Dec 07 '13 at 07:55
@user2864740 Did you mean that y should use transactions instead db.insert? and they take care about multi-writing, right? — whizzzkey, Dec 07 '13 at 08:06
Not "instead of". The actual operation being done needs to be looked at, such that all the database work is done atomically - this can be done with the use of transactions. In this particular case though, creating a new thread seems very complicated and unnecessary. — user2864740, Dec 07 '13 at 08:09
@user2864740 Ok, i agree that a thread in this function is excess, i've just read this http://www.sqlite.org/threadsafe.html, it seems to be that sqllite can work with many threads and it turns out that i can write a data from any thread in same time — whizzzkey, Dec 07 '13 at 08:33
Here is a wrapper I wrote/use which makes it slightly easier to use SQLite well in the context of android - [SqlDb for Android](https://github.com/kashifrazzaqui/sqldb) — keios, Dec 31 '13 at 10:37

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

After a long search i finally found a gret answer for my broblem, so anyone who want to creat e multithreading acess to db should read this first What are the best practices for SQLite on Android?

edited May 23 '17 at 11:57

Community

1
1

answered Dec 07 '13 at 10:19

whizzzkey

926
3
21
52

Multithreading data writing in SQLite

1 Answers1