Hi,
Recently when I upgrade from SQLite.ado.net 1.0.60 to 1.0.64, I met issues in my concurrent test of the driver.
In my stress tes, 2 threads try to insert rows into a table 't', 2 threads try to read and delete the inserted rows.And these 4 threads all share a connection.
Before each read/write block, I add "lock" to synchronize the operation. And I've added the "Default Timeout" to 10 minutes to add the command timeout.
Finally, it can easilly pass in the version of 1.0.60.
For 1.0.61, when 1802 rows have been inserted, database is locked.
For 1.0.62, 1.0.64, then about 300 rows have been inserted, database is locked.
I'm not sure whether it's a SQLite compact fADO.net bug or a sqlite bug.
Any help is appreciated.
Here is the source code I'm using to reproduce. (only for reproduce)
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Data;
using System.Data.SQLite;
namespace sqlitemt_dotnet
{
class Program
{
static string _dbPath = "Data Source=\\ttt.db;Default Timeout=600";
const int BATCH = 2000;
static void output(string s)
{
#if PLATFORM_COMPACTFRAMEWORK
System.Diagnostics.Debug.WriteLine(s);
#else
Console.WriteLine(s);
#endif
}
static SQLiteConnection _conn = null;
static Program()
{
_conn = new SQLiteConnection();
_conn.ConnectionString = _dbPath;
_conn.Open();
}
static void t1_write_func()
{
//lock (_conn)
{
for (int i = 1; i <= BATCH; i++)
{
lock (_conn)
{
System.Data.Common.DbCommand cmd;
try
{
SQLiteTransaction tx = _conn.BeginTransaction();
cmd = _conn.CreateCommand();
cmd.CommandText = "insert into t values(" + i + ", 'abc')";
cmd.ExecuteNonQuery();
output(Thread.CurrentThread.Name + " insert : " + i);
cmd.Dispose();
tx.Commit();
tx.Dispose();
// Thread.Sleep(50);
}
catch (Exception ex)
{
output(ex.ToString());
output(ex.StackTrace);
throw ex;
}
}
}
}
}
static void t2_write_func()
{
//lock (_conn)
{
for (int i = BATCH; i <= BATCH + BATCH; i++)
{
lock (_conn)
{
System.Data.Common.DbCommand cmd;
try
{
SQLiteTransaction tx = _conn.BeginTransaction();
cmd = _conn.CreateCommand();
cmd.CommandText = "insert into t values(" + i + ", 'abc')";
cmd.ExecuteNonQuery();
output(Thread.CurrentThread.Name + " insert : " + i);
cmd.Dispose();
tx.Commit();
tx.Dispose();
// Thread.Sleep(50);
}
catch (Exception ex)
{
output(ex.ToString());
output(ex.StackTrace);
throw ex;
}
}
}
}
}
static void t_read_write_func()
{
while (true)
{
Thread.Sleep(100);
run_read_write();
}
}
static void run_read_write()
{
lock (_conn)
{
try
{
SQLiteTransaction tx = _conn.BeginTransaction();
System.Data.IDbCommand selCmd = _conn.CreateCommand();
selCmd.CommandText = "SELECT id, col2 from t ORDER BY id";
System.Data.IDataReader rs = selCmd.ExecuteReader();
int count = 0;
List<int> ids = new List<int>();
while (rs.Read())
{
count++;
ids.Add(rs.GetInt32(0));
if (count >= 500) break;
}
output(Thread.CurrentThread.Name + ": to delete rows = " + count);
rs.Close();
selCmd.Dispose();
for (int i = 0; i < ids.Count; i++)
{
SQLiteCommand delCmd = _conn.CreateCommand();
delCmd.CommandText = "DELETE FROM t WHERE id = @id";
delCmd.Parameters.AddWithValue("@id", ids[i]);
output(Thread.CurrentThread.Name + ": del id = " + ids[i]);
delCmd.ExecuteNonQuery();
delCmd.Dispose();
}
tx.Commit();
tx.Dispose();
}
catch (Exception ex)
{
output(ex.ToString());
output(ex.StackTrace);
throw ex;
}
}
}
static void Main(string[ args)
{
// create a demo table first
lock (_conn)
{
using (System.Data.Common.DbCommand cmd = _conn.CreateCommand())
{
try
{
cmd.CommandText = "drop table t";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
try
{
cmd.CommandText = "create table t(id int, col2 varchar(32))";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
}
}
Thread t1 = new Thread(new ThreadStart(t1_write_func));
t1.Name = "t1";
t1.Start();
Thread t2 = new Thread(new ThreadStart(t2_write_func));
t2.Name = "t2";
t2.Start();
Thread t3 = new Thread(new ThreadStart(t_read_write_func));
t3.Name = "t3";
t3.Start();
Thread t4 = new Thread(new ThreadStart(t_read_write_func));
t4.Name = "t4";
t4.Start();
t1.Join();
t2.Join();
t3.Join();
t4.Join();
}
}
}