Preventing IIS from keeping a file lock on a SQLite database file
I've revisited this approximately a year after posting the original question because a colleague actually discovered the solution that worked for us and maybe it will be of help to others.
The solution that worked is to make some changes to the registry. It's nothing to do with the CH Werner SQLIte ODBC Driver. In fact we had an email conversation with CH Werner himself and he confirmed that the driver does not affect the locking of the database as we described it.
The solution is to make the following changes to the registry:
Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}]@="MSDASQL""OLEDB_SERVICES"=dword:fffffffe[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}]@="MSDASQL""OLEDB_SERVICES"=dword:fffffffeIf you place the registry changes described above into a file with .reg file extension you will be able to double-click it in windows explorer to make the changes.
Once the registry has these settings, IIS will not hold a lock on the SQLite database file for 60 seconds after the connection is closed.
Hopefully this will help others that come across this problem.
With the CH Werner SQLite ODBC Driver you can't turn off connection pooling. There is code in the driver that handles the SQL_ATTR_CONNECTION_POOLING attribute, but that attribute is never used. This means that it will be using connection pooling by default.
Stopping IIS keeping a lock on the database would involve modifying the DLL to include a ConnectionPooling=True/False or CPTimeout=n so that we could send the appropriate values to the SQLSetEnvAttr function.