By using BEGIN IMMEDIATE you lose any chance of concurrency.
Samuel Neff wrote:
> 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;
> }
> }
> }
>
>
>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
