Solve SQLite database is locked

Some time ago, colleagues used SQLite to store some temporary data on the site server, but SQLite throws an exception when multiple people are concurrent: The database file is locked, and the database is locked, And this is in the customer production environment prompted, the development environment is difficult to reproduce, colleagues really do not want to start all research and development colleagues through the operation of software to reproduce the problem, I can only hehe. Since it is the reason for SQLite, directly write a small program under the test SQLite does not, and even if reproduced, it is difficult to change the source of SQLite ...

Features of SQLite:

1. Simple: SQLite is a very lightweight, self-contained (lightweight and self-contained) DBMS: a header file, a dynamic library file, and you have all the functionality of a relational database. Simple, is the most obvious philosophy of SQLite. It provides a small and simple API. Just a DLL file, and your program has a powerful database engine right away, which is a wonderful thing to do.
2. Small (small): I compiled 3.6.11,release version 368K with VS 2005 in Windows, less than 20 seconds--and it took a few minutes to compile MySQL. And when I insert 10,000 int data, the memory overhead is 660K and the disk cost is 92K.
3. Transaction (transaction): Transactions are the most basic requirement of modern business data processing systems, and access, Whether it is in the executable file size (see Access2003 executable file size of 6.32M, both are not a magnitude), or transactional characteristics, are not compared with SQLite.
4. concurrency (Concurrency): Because SQLite implements a library-level lock through the file lock of the OS, it is very granular, but it improves the concurrency of reads and writes as much as possible through some complex special processing (see Analysis Series). If you are still worried, you can read this article: http://www.dbanotes.net/database/sqlite_cms.html. The
5. Sql92:sqlite supports the vast majority of standard SQL statements, and you only need hundreds of k of space, so you can switch to almost any operation that requires hundreds of megabytes of universal DBMS.
6. Convenient (convenience): If your program is using SQLite, just copy your program directory.
7. Open Source (opensource): This is its most powerful place. Open source means that you can read its source code, you can modify it at any time, add your own features, and it's all completely free. Open source, is a spirit

SQLite only supports library-level locks, what does a library-level lock mean? --meaning that only one write operation is allowed at the same time, that is, the transaction T1 inserts a data in the a table, the transaction T2 inserts a data in the B table, the two operations cannot simultaneously, even if your machine has 100 CPUs, it cannot be done simultaneously, but only in order. The table level cannot be parallel, let alone the tuple level-this is the library-level lock. However, SQLite tries to delay the application of the X lock until the block is actually written to apply for the X lock, which is very clever and effective.

The above introduction can be seen that SQLite is actually a client embedded database, in the high concurrency of the server is not applicable, colleague Baidu, found the connection string added "Journal Mode=wal;" Can alleviate the concurrency pressure, but the customer production environment still appears "database is locked" error.

The test procedure is as follows:

   static void Main(string[] args)
        {
             for (int i = 0; i < 140; i++)
             {
                 ParameterizedThreadStart pStart = new      ParameterizedThreadStart(ClientTest.Excute);
                 Thread td = new Thread(pStart);
                 td.Start(1012 + i);
             }
             Console.Read();
        }


    class ClientTest
    {

        public static void Excute(Object id)
        {
            bool flag = true;
            while (true)
            {
                if (flag)
                {
                    string sql = "update asr_info set asr_check = 1 where id = ‘" + id.ToString() + "‘";
                    Sqlite.ExecuteSql(sql);
                    flag = false;
                }
                else
                {
                    string sql = "update asr_info set asr_check = 0 where id = ‘" + id.ToString() + "‘";
                    Sqlite.ExecuteSql(sql);
                    flag = true;
                }
            }
        }
    }

Test found that in the i5 2.5Ghz four-core machine, ran less than half a minute, probably executed 500 update statements, sqlite error, hint "database is locked", but it is difficult to reproduce on the very close of the machine, This also explains the difficulty of reproducing on the development machine and reporting errors on the client server.

Workaround:

  private static readonly object obj = new object();
        private static int ExecuteNonQuery(string StrSQL, CommandType CmdType, SQLiteParameter[] SQLiteParams)
        {
            SQLiteConnection SQLiteConn = new SQLiteConnection(strConn);
            SQLiteCommand SQLiteCmd = SQLiteCommandConstructor(SQLiteConn, StrSQL, CmdType, SQLiteParams);
            if (SQLiteConn.State != ConnectionState.Open)
            {
                SQLiteConn.Open();
            }
            Monitor.Enter(obj);
            int result = SQLiteCmd.ExecuteNonQuery();
            Monitor.Exit(obj);
            //aaa++;
            // Console.WriteLine(aaa);
            SQLiteCmd.Dispose();
            SQLiteConn.Close();

            return result;
        }

It turns out that SQLite does not support concurrent write operations, even for different tables, only library-level locks are supported, and this sqlite itself is not implemented and must implement this library-level lock on its own.

Solve SQLite database is locked