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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users