Yes, for write transactions, begin immediate will cause the second write
transaction to wait on the first. However, the reads are done without an
explicity transaction so the reads should still proceed while the writes
have a reserved lock in place, right?
And even if there is no concurrency, at worst the 4 process test should take
just slightly more than 4 times longer than one process, or 2 seconds. Not
exponentially longer, 10, 20, 45 seconds, which is what I'm seeing. right?
Thanks,
Sam
On Fri, May 16, 2008 at 9:59 AM, John Stanton <[EMAIL PROTECTED]> wrote:
> 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
>
--
-----------------------------------------------------------------
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
