title: Database file is inexplicably locked during SQLite commit
body: <p>I'm performing a large number of INSERTS to a SQLite database.  I'm using just one thread.  I batch the writes to improve performance and have a bit of security in case of a crash.  Basically I cache up a bunch of data in memory and then when I deem appropriate, I loop over all of that data and perform the INSERTS.  The code for this is shown below:</p>
  
  <pre><code>    public void Commit()
      {
          using (SQLiteConnection conn = new SQLiteConnection(this.connString))
          {
              conn.Open();
              using (SQLiteTransaction trans = conn.BeginTransaction())
              {
                  using (SQLiteCommand command = conn.CreateCommand())
                  {
                      command.CommandText = "INSERT OR IGNORE INTO [MY_TABLE] (col1, col2) VALUES (?,?)";
  
                      command.Parameters.Add(this.col1Param);
                      command.Parameters.Add(this.col2Param);
  
                      foreach (Data o in this.dataTemp)
                      {
                          this.col1Param.Value = o.Col1Prop;
                          this. col2Param.Value = o.Col2Prop;
  
                          command.ExecuteNonQuery();
                      }
                  }
                  this.TryHandleCommit(trans);
              }
              conn.Close();
          }
      }
  </code></pre>
  
  <p>I now employ the following gimmick to get the thing to eventually work:</p>
  
  <pre><code>    private void TryHandleCommit(SQLiteTransaction trans)
      {
          try
          {
              trans.Commit();
          }
          catch (Exception e)
          {
              Console.WriteLine("Trying again...");
              this.TryHandleCommit(trans);
          }
      }
  </code></pre>
  
  <p>I create my DB like so:</p>
  
  <pre><code>    public DataBase(String path)
      {
          //build connection string
          SQLiteConnectionStringBuilder connString = new SQLiteConnectionStringBuilder();
          connString.DataSource = path;
          connString.Version = 3;
          connString.DefaultTimeout = 5;
          connString.JournalMode = SQLiteJournalModeEnum.Persist;
          connString.UseUTF16Encoding = true;
  
          using (connection = new SQLiteConnection(connString.ToString()))
          {
              //check for existence of db
              FileInfo f = new FileInfo(path);
  
              if (!f.Exists)  //build new blank db
              {
                  SQLiteConnection.CreateFile(path);
                  connection.Open();
  
                  using (SQLiteTransaction trans = connection.BeginTransaction())
                  {
                      using (SQLiteCommand command = connection.CreateCommand())
                      {
                          command.CommandText = DataBase.CREATE_MATCHES;
                          command.ExecuteNonQuery();
  
                          command.CommandText = DataBase.CREATE_STRING_DATA;
                          command.ExecuteNonQuery();
                          //TODO add logging
                      }
                      trans.Commit();
                  }
                  connection.Close();
              }
          }            
      }
  </code></pre>
  
  <p>I then export the connection string and use it to obtain new connections in different parts of the program.</p>
  
  <p>At seemingly random intervals, though at far too great a rate to ignore or otherwise workaround this problem, I get unhandled SQLiteException: Database file is locked.  This occurs when I attempt to commit the transaction.  No errors seem to occur prior to then.  This does not <strong><em>always</em></strong> happen.  Sometimes the whole thing runs without a hitch.  </p>
  
  <ul>
  <li>No reads are being performed on these files before the commits finish.  </li>
  <li>I have the very latest SQLite binary.  </li>
  <li>I'm compiling for .NET 2.0.  </li>
  <li>I'm using VS 2008.</li>
  <li>The db is a local file.</li>
  <li>All of this activity is encapsulated within one thread / process.</li>
  <li>Virus protection is off (though I think that was only relevant if you were connecting over a network?).</li>
  <li>As per Scotsman's post I have implemented the following changes:</li>
  <li>Journal Mode set to Persist</li>
  <li>DB files stored in C:\Docs + Settings\ApplicationData via <code>System.Windows.Forms.Application.AppData</code> windows call</li>
  <li>No inner exception</li>
  <li>Witnessed on two distinct machines (albeit very similar hardware and software)</li>
  <li>Have been running Process Monitor - no extraneous processes are attaching themselves to the DB files - the problem is definitely in my code...</li>
  </ul>
  
  <p><strong><em>Does anyone have any idea whats going on here?</em></strong>  </p>
  
  <p>I know I just dropped a whole mess of code, but I've been trying to figure this out for way too long.  My thanks to anyone who makes it to the end of this question!</p>
  
  <p>brian</p>
  
  <p>UPDATES:</p>
  
  <p>Thanks for the suggestions so far!  I've implemented many of the suggested changes.  I feel that we are getting closer to the answer...however...</p>
  
  <p>The code above technically works however it is non-deterministic!  It is not guaranteed to do anything aside from spin in neutral forever.  In practice it seems to work somewhere between the 1st and 10th iteration.  If i batch my commits at a reasonable interval damage will be mitigated but I really do not want to leave things in this state...</p>
  
  <p>More suggestions welcome!</p>