We're running into a lot of very slow queries and db locks when running with
multiple processes accessing the same database. As a test we created a
small application that has only two threads and a small single table
database.
RunRead reads all data from the one table (100 records).
RunWrite updates all the records in the table (no add/delete, just update).
When run by itself with just one process, each read/write concurrent
operation runs in 500ms. It's synchronized to start both read/write at the
same time and then wait for each to finish before starting the next test,
and then loops. It's pretty consistent around 500ms.
When I run the same app multiple times (multiple concurrent processes), the
operations degrade very quickly. It starts off taking 1-5 seconds for each
read/write concurrent operation but after a few minutes it often takes 20-30
seconds for a a single operation and sometimes 45 seconds.
The transactions are all BEGIN IMMEDIATE and the noticable time taken is
during COMMIT. The reads are not run within a transaction.
I'm using sqlite 3.5.9 in SQLite.NET. Full C# test code follows.
Is there something I'm doing wrong that is causing this lock contention? Is
there anything I can do to improve performance in a multi-process
application?
Thanks,
Sam
using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Threading;
namespace test
{
public class DbLockTest
{
private static readonly Random _random = new Random();
private readonly ManualResetEvent _start = new ManualResetEvent(false);
private readonly ManualResetEvent _readDone = new
ManualResetEvent(false);
private readonly ManualResetEvent _writeDone = new
ManualResetEvent(false);
private Stopwatch _timer;
public static void Run()
{
if (!File.Exists("DbLockTest.dat"))
{
using (SQLiteConnection cnn = CreateConnection())
{
using (SQLiteTransaction trans = cnn.BeginTransaction())
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Data (id INTEGER PRIMARY KEY
AUTOINCREMENT, text TEXT);";
cmd.ExecuteNonQuery();
}
for (int i = 0; i < 100; i++)
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Data (text) VALUES (@text);";
cmd.Parameters.AddWithValue("@text", new string((char)(65 +
i), i * 100));
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
}
}
for (int i = 0; i < 50; i++)
{
new DbLockTest().RunImpl();
Thread.Sleep(1);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine();
}
Console.WriteLine("Done. Hit any key.");
Console.ReadKey();
}
public void RunImpl()
{
_timer = Stopwatch.StartNew();
Console.WriteLine("{0:0,000} - MAIN - Queuing threads",
_timer.ElapsedMilliseconds);
ThreadPool.QueueUserWorkItem(RunRead, _random.Next(15));
ThreadPool.QueueUserWorkItem(RunWrite, _random.Next(15));
Thread.Sleep(100);
Console.WriteLine("{0:0,000} - MAIN - Signaling threads",
_timer.ElapsedMilliseconds);
_start.Set();
_readDone.WaitOne();
Console.WriteLine("{0:0,000} - MAIN - Read done received",
_timer.ElapsedMilliseconds);
_writeDone.WaitOne();
Console.WriteLine("{0:0,000} - MAIN - Write done received",
_timer.ElapsedMilliseconds);
}
private void RunRead(object state)
{
try
{
Console.WriteLine("{0:0,000} - READ - Waiting for signal",
_timer.ElapsedMilliseconds);
_start.WaitOne();
/*
int wait = (int) state;
Console.WriteLine("{0:0,000} - READ - Sleeping {1} ms",
_timer.ElapsedMilliseconds, wait);
Thread.Sleep(wait);
*/
IDataReader reader;
Console.WriteLine("{0:0,000} - READ - Opening connection",
_timer.ElapsedMilliseconds);
SQLiteConnection cnn = CreateConnection();
using(SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Data";
Console.WriteLine("{0:0,000} - READ - Getting reader",
_timer.ElapsedMilliseconds);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
Console.WriteLine("{0:0,000} - READ - Looping through data",
_timer.ElapsedMilliseconds);
int i = 0;
while (reader.Read())
{
if (i++ % 10 == 0)
{
Thread.Sleep(1);
}
}
Console.WriteLine("{0:0,000} - READ - Closing reader (and thus
connection)", _timer.ElapsedMilliseconds);
reader.Close();
Console.WriteLine("{0:0,000} - READ - Signaling done",
_timer.ElapsedMilliseconds);
}
catch (Exception ex)
{
Console.WriteLine("{0:0,000} - READ - ERROR\r\n\r\n" + ex,
_timer.ElapsedMilliseconds);
}
_readDone.Set();
}
private void RunWrite(object state)
{
try
{
Console.WriteLine("{0:0,000} - WRITE - Waiting for signal",
_timer.ElapsedMilliseconds);
_start.WaitOne();
/*
int wait = (int)state;
Console.WriteLine("{0:0,000} - WRITE - Sleeping {1} ms",
_timer.ElapsedMilliseconds, wait);
Thread.Sleep(wait);
*/
Console.WriteLine("{0:0,000} - WRITE - Opening connection",
_timer.ElapsedMilliseconds);
using (SQLiteConnection cnn = CreateConnection())
{
Console.WriteLine("{0:0,000} - WRITE - Starting transaction",
_timer.ElapsedMilliseconds);
using (SQLiteTransaction trans = cnn.BeginTransaction())
{
Console.WriteLine("{0:0,000} - WRITE - Updating data",
_timer.ElapsedMilliseconds);
for (int i = 0; i < 10; i++)
{
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "UPDATE Data SET text = " + i;
cmd.ExecuteNonQuery();
}
}
Console.WriteLine("{0:0,000} - WRITE - Committing transaction",
_timer.ElapsedMilliseconds);
trans.Commit();
}
Console.WriteLine("{0:0,000} - WRITE - Closing connection",
_timer.ElapsedMilliseconds);
}
Console.WriteLine("{0:0,000} - WRITE - Signaling done",
_timer.ElapsedMilliseconds);
}
catch (Exception ex)
{
Console.WriteLine("{0:0,000} - WRITE - ERROR\r\n\r\n" + ex,
_timer.ElapsedMilliseconds);
}
_writeDone.Set();
}
private static SQLiteConnection CreateConnection()
{
SQLiteConnection cnn = new SQLiteConnection("Data
Source=DbLockTest.dat");
cnn.Open();
return cnn;
}
}
}
--
-----------------------------------------------------------------
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
