Databases
StackOverflow2013
Postsdbo
POsqlite returns SQLITE_BUSY in WAL mode
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsqlite returns SQLITE_BUSY in WAL mode
    primarykey
    data
    text
    <p>I have a web application working with sqlite database. My version of sqlite is the latest from official windows binary distribution - 3.7.13.</p> <p>The problem is that under heavy load on database, sqlite API functions (such as sqlite3_step) are returning SQLITE_BUSY.</p> <p>I pass the following pragmas when initializing a connection:</p> <pre><code>journal_mode = WAL page_size = 4096 synchronous = FULL foreign_keys = on </code></pre> <p>The databas is one-file database. And I'm using Mono 2.10.8 and Mono.Data.Sqlite assembly provided with it to access database.</p> <p>I'm testing it with 50 parallel threads which are sending 50 subsequent http-requests each to my application. On every request some reading and writing are done to the database. Every set of IO operations is executed inside the transaction.</p> <p>Everything goes well until near 400th - 700th request. In this (random) moment API functions are starting to return SQLITE_BUSY permanently (To be more exact - until the limit of retries is reached).</p> <p>As far as i know WAL mode transparently supports parallel reads and writes. I've guessed that it could be because of attempt to read database while checkpoint operation is executed. But even after turning autocheckpoint off the situation remains the same.</p> <p>What could be wrong in this situation? How to serve large amount of parallel database IO correctly?</p> <p>P.S.</p> <p>Only one connection per request is supposed. I use nhibernate configured with WebSessionContext.</p> <p>I initialize my NHibernate session like this:</p> <pre><code>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; </code></pre> <p>And on HttpApplication.EndRequest i release it like this:</p> <pre><code>//factory variable is session factory if (CurrentSessionContext.HasBind(factory)) { try { CurrentSessionContext.Unbind(factory) .Dispose(); } catch (Exception ee) { Logr.Error("Error uninitializing session", ee); } } </code></pre> <p>So as far as i know there should be only one connection per request life cycle. While proceessing the request, code is executed sequentially (ASP.NET MVC 3). So it doesn't look like any concurency is possible here. Can i conclude that no connections are shared in this case?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USILya
    1. USILya
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POsqlite returns SQLITE_BUSY in WAL mode
      1. USILya
      1. VTBountyStart
    2. VO
      singulars
      1. POsqlite returns SQLITE_BUSY in WAL mode
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POsqlite returns SQLITE_BUSY in WAL mode
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs a distinct connection opened for each request, or do all requests share the same connection? Also, could you add some relevant bits of code, especially the part where the threads interact with the database?
      singulars
      1. POsqlite returns SQLITE_BUSY in WAL mode
      1. This table or related slice is empty.
    2. COYour assumption about WAL mode is wrong. http://www.sqlite.org/wal.html says: "since there is only one WAL file, there can only be one writer at a time." For a database with heavy write load, use something like PostgreSQL or MySQL.
      singulars
      1. POsqlite returns SQLITE_BUSY in WAL mode
      1. USCL.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload