Hello,
First let me take this opportunity to thank you for the great job you are doing with this provider!
As for our problem, perhaps it is a result of not understanding correctly - the way SQLite behaves in a multithreading scenario, but we have an issue when multiple threads are attempting to perform inserts on the same table.
each insert should be very quick but when the two are run together we get the dreaded "The database file is locked" mesage.
I have added a sample application that replicates this scenario (using provider V1.0.49.0 as well as 1.0.48.0).
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Threading;
namespace SqliteTest
{
class Program
{
private static volatile bool ms_bContinueRunning = true;
static void Main(string[ args)
{
bool bTableCreated = false;
//first we create the test db and table
Console.WriteLine("Creating Test DB...");
try
{
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source = {0};", AppDomain.CurrentDomain.BaseDirectory + "TestDB.DB")))
{
connection.Open();
using (SQLiteCommand commandCreate = connection.CreateCommand())
{
commandCreate.CommandText = "create table TestTable(aTest INTEGER, bTest INTEGER, cTest INTEGER, dTest INTEGER, eTest INTEGER)";
commandCreate.CommandType = System.Data.CommandType.Text;
commandCreate.ExecuteNonQuery();
bTableCreated = true;
Console.WriteLine("Test DB created successfully");
}
}
}
catch (Exception ex)
{
bTableCreated = false;
if (ex != null)
{
if (ex.InnerException != null) ex = ex.InnerException;
Console.WriteLine("Error while creating table: " + ex.ToString());
}
}
if (bTableCreated)
{
//start 2 concurrent threads
Console.WriteLine("Starting Test insert threads...");
Console.WriteLine();
Thread[ threadsInsertTest = new Thread[2];
for (int iThreadCounter = 0; iThreadCounter < 2; iThreadCounter++)
{
threadsInsertTest[iThreadCounter] = new Thread(delegate() { InsertSqlite(); });
threadsInsertTest[iThreadCounter].IsBackground = true;
threadsInsertTest[iThreadCounter].Name = string.Format("Thread Insert Test #{0}", iThreadCounter);
threadsInsertTest[iThreadCounter].Start();
}
Console.ReadKey();
try
{
ms_bContinueRunning = false;
for (int iThreadCounter = 0; iThreadCounter < 2; iThreadCounter++)
{
if (!threadsInsertTest[iThreadCounter].Join(5000)) threadsInsertTest[iThreadCounter].Abort();
}
}
finally
{
Console.WriteLine("Done, Exitting....");
Thread.Sleep(1500);
}
}
else Console.ReadKey();
}
static public void InsertSqlite()
{
string sSQLCommand = "INSERT INTO TestTable(aTest, bTest, cTest, dTest, eTest) VALUES(1, 2, 3, 4, 5)";
try
{
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source = {0};", AppDomain.CurrentDomain.BaseDirectory + "TestDB.DB")))
{
connection.Open();
while (ms_bContinueRunning)
{
for (int iLoopCounter = 0; iLoopCounter < 10; iLoopCounter++)
{
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = sSQLCommand;
command.CommandType = System.Data.CommandType.Text;
int iResult = command.ExecuteNonQuery();
}
}
}
}
}
catch (SQLiteException sqlitex)
{
if (sqlitex != null)
{
Console.WriteLine("SQLite Exeption: " + sqlitex.ToString());
}
else Console.WriteLine("Unknown SQLite Error occurred");
}
catch (Exception ex)
{
if (ex != null)
{
if (ex.InnerException != null) ex = ex.InnerException;
Console.WriteLine("General Exception: " + ex.ToString());
}
else Console.WriteLine("Unknown General Error occurred");
}
}
}
}
and the error we get is :
SQLite Exeption: System.Data.SQLite.SQLiteException: The database file is locked
database is locked
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at SqliteTest.Program.InsertSqlite() in C:\Users\tomk\Documents\Visual Studio
2005\Projects\CSSQLiteLockTest\CSSQLiteLockTest\Program.cs:line 101
The error is not caused by a 30 seconds timeout (which is the default) - we measured that and it happens instantaneously with sending the command, and none of the inserts should take that long anyway... Is this a problem with the code or with the DB/Provider?
Thanks in advance for any assistance.