Sqlite returns SQLITE_BUSY in WAL mode

Question

I have a web application working with sqlite database. My version of sqlite is the latest from the official binary distribution of Windows - 3.7.13.

The problem is that when the database is heavily loaded, sqlite API functions (such as sqlite3_step) return SQLITE_BUSY.

When the connection is initialized, I transmit the following pragmas:

journal_mode = WAL page_size = 4096 synchronous = FULL foreign_keys = on 

Databases are a single-file database. And I use the Mono 2.10.8 assembly and Mono.Data.Sqlite provided with it to access the database.

I am testing it with 50 parallel threads that send 50 subsequent http requests to everyone in my application. For each request to the database, some reading and writing is performed. Each block of I / O operations is performed within a transaction.

Everything goes well until almost the 400th - 700th request. At this (random) moment, the API functions begin to return SQLITE_BUSY forever (or rather, until the repetition limit is reached).

As far as I know, WAL mode transparently supports concurrent reads and writes. I figured it might be due to trying to read the database while the checkpoint operation is in progress. But even after turning off the autosave point, the situation remains the same.

What could be wrong in this situation? How to service a large number of parallel databases?

PS

Only one connection per request is assumed. I am using nhibernate configured using WebSessionContext.

I initialize my NHibernate session as follows:

 ISession session = null; //factory variable is session factory if (CurrentSessionContext.HasBind(factory)) { session = factory.GetCurrentSession(); if (session == null) CurrentSessionContext.Unbind(factory); } if (session == null) { session = factory.OpenSession(); CurrentSessionContext.Bind(session); } return session; 

And on HttpApplication.EndRequest I will write it as follows:

 //factory variable is session factory if (CurrentSessionContext.HasBind(factory)) { try { CurrentSessionContext.Unbind(factory) .Dispose(); } catch (Exception ee) { Logr.Error("Error uninitializing session", ee); } } 

So, as far as I know, there should be only one connection per request life cycle. When executing the request, the code is executed sequentially (ASP.NET MVC 3). So, this is not like agreeing here. Can I conclude that in this case the connections are not used?

Answer 1

It is not clear to me if there is the same connection in the request stream. If they do not, you should not have these problems.

Assuming that you really use the connection object for multiple threads, you should use a locking mechanism, since SqliteConnection is not thread safe (old post, but the SQLite library supported as part of Mono came from System.Data.SQLite found at http: // sqlite.phxsoftware.com ).

So, assuming you are not blocking the use of the SqliteConnection object, can you try? An easy way to accomplish this might look like this:

 static readonly object _locker = new object(); public void ProcessRequest() { lock (_locker) { using (IDbCommand dbcmd = conn.CreateCommand()) { string sql = "INSERT INTO foo VALUES ('bar')"; dbcmd.CommandText = sql; dbcmd.ExecuteNonQuery(); } } } 

However, you can open a separate connection with each thread to make sure that there are no more thread issues in the SQLite library.

EDIT

Following the code that you posted, do you close the session after the transaction? If you do not use any ITransaction, do you close and close the session? I ask because I do not see it in your code, and I see that it is mentioned in fooobar.com/questions/35039 / ...

I also see that it is mentioned in http://nhibernate.info/doc/nh/en/index.html#session-configuration :

Also note that you can call NHibernateHelper.GetCurrentSession (); as many times as you like, you will always receive the current permission of this HTTP request. You must make sure that ISession is closed after your work unit is completed either in the Application_EndRequest event handler in your application class or in the HttpModule before the HTTP response is sent.