Detecting locked database

[frostfire]

Hiya,

We are converting to SQLite from MS SQL Server Express 2005. Our original data model allowed multiple threads to write to the database, but that is not allowed with SQLite.

So we implemented a query queue that is processed by a single thread, and even locking the connection object with Synclock to ensure only a single connection can be open at once.

We still get the occasional Database File is Locked error in our logs however. Its very rare now, but still happens... and our queuing system handles them by re-submitting them for processing, but I would like to go a step further and try to detect this error before the query is submitted and initiate a wait until the lock is removed.

I see that there is a option with SQLite for setting a timeout in the connection (Busy Timeout), but I see no option for that in the connection object. Is this something that is possible with DOTConnect?

If not, do you have any suggestions for handing BUSY or LOCKED states? EVen a way to detect these would be good..

Thanks!

David Borneman

[Shalex]

So we implemented a query queue that is processed by a single thread, and even locking the connection object with Synclock to ensure only a single connection can be open at once.

We still get the occasional Database File is Locked error in our logs however. Its very rare now, but still happens... and our queuing system handles them by re-submitting them for processing, but I would like to go a step further and try to detect this error before the query is submitted and initiate a wait until the lock is removed.

Please make sure that you have turned off the connection pooling (the "Pooling=false;" connection string parameter): http://www.devart.com/dotconnect/sqlite ... q.html#q52.

I see that there is a option with SQLite for setting a timeout in the connection (Busy Timeout), but I see no option for that in the connection object. Is this something that is possible with DOTConnect?

You can set the "Connection Timeout" parameter in your connection string.

If not, do you have any suggestions for handing BUSY or LOCKED states? EVen a way to detect these would be good..

It is possible to access to one database from several threads within one process. For this, you should call static SQLiteConnection.EnableSharedCache() before opening connections and set the "Read Uncommitted=true;" connection string parameter. For example:

Code: Select all

    SQLiteConnection.EnableSharedCache();
    using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=D:\sqlitetest.db;Read Uncommitted=true;")) {
        conn.Open();
        SQLiteCommand cmd = conn.CreateCommand();
        cmd.CommandText = "select * from dept";
        SQLiteDataReader reader = cmd.ExecuteReader();
        while (reader.Read()) {
            using (SQLiteConnection conn2 = new SQLiteConnection(@"Data Source=D:\sqlitetest.db;Read Uncommitted=true;")) {
                conn2.Open();
                SQLiteCommand cmd2 = conn2.CreateCommand();
                cmd2.CommandText = "insert into dept values (6,'a','a')";
                cmd2.ExecuteNonQuery();
            }
        }
    }

The SQLiteConnection.EnableSharedCache()/SQLiteConnection.DisableSharedCache() methods will be available in dotConnect for SQLite starting from the next build. I will post here when it is available for download.

[Shalex]

New build of dotConnect for SQLite 3.10.135 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=20710 .

[PatrickM]

How can we make multi-threaded accesses to the DB work without setting "Read Uncommitted=true;"? I ask, because setting that is a Bad Idea to globally apply to your entire DB, unless you are very careful, since you are reading data that is not in a safe state, and might be rolled back at any time.

[Pinturiccio]

How can we make multi-threaded accesses to the DB work without setting "Read Uncommitted=true;"?

We think that currently using Read Uncommitted=true; is necessary only when several connections write to and read from one table.

Tables support two types of locks, "read-locks" and "write-locks".
At any moment, a single table may have any number of active read-locks or a single active write lock. To read data from a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. If a required table lock cannot be obtained, the query fails and SQLITE_LOCKED is returned to the caller.

The behavior described above may be modified slightly using the read_uncommitted pragma to change the isolation level from serialized (the default), to read-uncommitted.
A database connection in read-uncommitted mode does not attempt to obtain read-locks or write-locks.

For more information, please refer to http://www.sqlite.org/sharedcache.html

[PatrickM]

True, and I wasn't asking how to allow reads at the same time as writes. I'm just asking how to make writes and reads block and wait, instead of instantly throwing an Exception, while "Read Uncommitted" is false.

[Pinturiccio]

dotConnect for SQLite does not allow you to wait while read or write block ends. We recommend you to synchronize your threads, so that only one thread could get access to a table in your database.