Multithreading in C# sqlite

Question

I am programming in C# wpf application and using Sqlite database.Let's say I have 25 secondary threads all waiting to perform an insert operation on the database. Now The Main Thread performs a select operation on the database. I may be wrong, but this main thread will wait for some time. How do I ensure. So i get database locked in my log files. How do i ensure that main thread gets highest priority so that my UI is not blocked. I am using DBContext object to perform database operations.?

Answer 1

A clever use of ReaderWriterLockSlim will definitely help you improve performance.

    private ReaderWriterLockSlim _readerWriterLock = new ReaderWriterLockSlim();

    private DataTable RunSelectSQL(string Sql)
    {
        DataTable selectDataTable = null;
        try
        {
            _readerWriterLock.EnterReadLock();                
            //Function to acess your database and return the selected results
        }
        finally
        {
            _readerWriterLock.ExitReadLock();
        }
        return selectDataTable;
    }

    private DataTable RunInsertSQL(string Sql)
    {
        DataTable selectDataTable = null;
        bool isbreaked = false;
        try
        {
            _readerWriterLock.EnterWriteLock();
            if (_readerWriterLock.WaitingReadCount > 0)
            {
                isbreaked = true;
            }
            else
            {
                //Function to insert data in your database
            }
        }
        finally
        {
            _readerWriterLock.ExitWriteLock();
        }

        if (isbreaked)
        {
            Thread.Sleep(10);
            return RunInsertSQL(Sql);
        }
        return selectDataTable;
    }

Try this it will, improve your responsiveness and you have Select query to fire having higher priority over Insert SQL.

Please note, if some insertion is already running then Select will at least wait for that insertion to complete. This code will always give priority to SELECT over INSERT.

One more point, never perform the long ongoing operation on main thread like you have selecting from database, rather perform the operation in background and then reflect the latest results on UI using main thread. This will ensure that your UI will never freeze.

EDIT There can be a starvation case where all INSERT may be waiting, if there are continuous SELECT queries being fired without any gap.
But I believe in your case, this will not happen as the UI will not always be refreshing to get the latest changes so frequently without any time slice in between.

Answer 2

What mode are you running the databae in?

SQLite support three different threading modes:

1. Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.
2. Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.
3. Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.

The default mode is serialized.

http://www.sqlite.org/threadsafe.html

It would seem that Multi-Thread is the one you want. Serializing database access is slow.

Answer 3

A had exactly the same problem in my multithreaded caching subsystem It looks like it is something like only 'System.Data.SQLite' library issue

Adding this (found with reflector)

"...;Version=3;Pooling=True;Max Pool Size=100;"

to connection string solved the issue.