Solving database is locked in SQLite

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. Compact (small): I used vs 2005 to compile the 3.6.11,release version in Windows 368K, less than 20 seconds-while compiling MySQL, it took a few minutes. And when I insert 10,000 int data, the memory overhead is 660K and the disk cost is 92K.
3. Transactions (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 OS's file lock, it is very granular, but it can improve the concurrency of reads and writes as much as possible through some complex special processing (see Analysis Series).
5. Sql92:sqlite supports most of the standard SQL statements, you only need hundreds of k of space, you can exchange for hundreds of gigabytes of universal DBMS almost all operations.
6. Convenience (convenience): If your program wants to use 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 kind of 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.

We can write a test case, the code is very simple, concurrent execution of multiple update statements, using different machines, generally choose a good performance of the machine (I used to test the G4) and a performance of the Machine (G2)

test found that on the G2 machine, ran less than half a minute, probably executed 500 update statements, sqlite error, hint "database is locked", but it is difficult to find on the G4 machine, This also explains the difficulty of reproducing on the development machine and reporting errors on the client server.

Here to take a closer look at the above marked red content, because I just started not using any synchronization mechanism to operate, the use of the G4 phone test, the results of a run error, so the Internet query reason, the result is that a table to operate, need to lock, So I was in the same table crud operation, the use of the same lock, the operation is not wrong, but after a few days, the test over there to a bug table, altogether appeared above the problem, and is the G2 machine, so I used this machine to test, I found that the above problems will occur, At this time is very tangled, online various search, no fruit, the results saw an article to the point, said SQLite is a library-level lock, then I will be the above table-level lock changed to a library-level lock (very simple, different tables are all using the same lock), in the test found no such problem.