title: Preventing IIS from keeping a file lock on a SQLite database file
body: <p>We have a legacy web application written in Classic ASP that currently uses an MS Access database as its data store. Interaction with the database occurs via the Jet OleDB provider:</p>
  
  <pre class="lang-none prettyprint-override"><code>Provider=Microsoft.JET.OLEDB.4.0;Data Source=database.mdb
  </code></pre>
  
  <p>As is the case with most web applications, CRUD operations are performed on the database on a regular basis. </p>
  
  <p>The application connects to the database, carries out an <code>insert</code>, <code>update</code>, <code>delete</code> or <code>select</code> statement then closes the connection. Immediately after the connection is closed, we are able to <strong>rename</strong> or <strong>move</strong> the MS Access <code>.mdb</code> file with impunity. We have taken advantage of this behaviour for many years - it's necessary for our use case and it has served us well.</p>
  
  <p>For various reasons, we are in the process of replacing the MS Access database with SQLite 3. We connect to the SQLite version via ODBC. </p>
  
  <p>Interaction with the SQLite database happens just like it did previously, i.e connect to the database, issue the SQL command, close the connection.</p>
  
  <p>The problem is that the SQLite database file remains locked for 60 seconds after the connection is closed. This prevents us from renaming or moving the database file because it's highly likely that another connection occurs within that 60 seconds and so the file appears to be permanently locked. </p>
  
  <p>We created a minimal page in Classic ASP to illustrate the behaviour:</p>
  
  <pre class="lang-none prettyprint-override"><code>Dim connectionString: connectionString = "DRIVER=SQLite3 ODBC Driver;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;FKSupport=0;NoCreat=1;Database=database.sqlite"
  
  Dim con : Set con = CreateObject("ADODB.Connection")
  con.Open connectionString
  
  ' Issue some CRUD commands here
  
  con.Close
  Set con = Nothing
  Response.Write("We are here!!!!!")
  </code></pre>
  
  <p>In order to ascertain what is causing the lock, we built a <code>VBScript</code> file (using the same SQLite connection string shown above) then we ran that from the command line (therefore IIS is not involved at all.)  </p>
  
  <p>In that situation, after connecting, issuing any <code>CRUD</code> commands and then closing the database, the <code>SQLite</code> file was immediately accessible. So we concluded that somehow IIS 7.5 (or more precisely <code>W3WP.exe</code>) is holding some kind of lock on the SQLite file. In fact, if we stop the App Pool for the website, the lock on the file is immediately released.</p>
  
  <p>The code for the <code>VBScript</code> file is as follows:</p>
  
  <pre class="lang-none prettyprint-override"><code>Dim connectionString: connectionString = "DRIVER=SQLite3 ODBC Driver;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;FKSupport=0;NoCreat=1;Database=database.sqlite"
  
  Dim con : Set con = CreateObject("ADODB.Connection")
  con.Open connectionString
  
  ' Issue some CRUD commands here
  
  WScript.Echo "Press [ENTER] to continue..."
  
  ' Read dummy input. This call will not return until [ENTER] is pressed.
  WScript.StdIn.ReadLine
  
  WScript.Echo "Done."
  
  con.Close
  Set con = Nothing
  </code></pre>
  
  <p>Is there some kind of setting in IIS that we can utilise that will allow us to manipulate the SQLite database just as we currently do with MS Access?</p>