Description of the database file is locked error in the system

Recently, I am working on a demo version of a server project. The process is as follows:

Tcpserver-> WCF server-> Silverlight client/winfom Client

The database uses SQLite and the persistence layer uses ibatis. TCP adopts the asynchronous transmission method and can be run at the beginning. However, after a period of operation, the database file is locked error occurs, I do not know the specific reason, also find some information on the Internet, as follows: http://liuleijsjx.javaeye.com/blog/425061

 

During the running of the system, there are occasional strange error prompts. According to the background log prompt, when the system concurrency is large, the error of the database file is locked is easy to appear, the specific error information is recorded in the background log file as follows:
06:54:59, 609 [20] Error-unhandled system error:
System. Data. SQLite. sqliteexception: the database file is locked
Database is locked
In system. Data. SQLite. sqlite3.step (sqlitestatement stmt)
In system. Data. SQLite. sqlitedatareader. nextresult ()
In system. Data. SQLite. sqlitedatareader.. ctor (sqlitecommand cmd, commandbehavior behave)
In system. Data. SQLite. sqlitecommand. executenonquery ()
In levenblog. Data. SQLite. sqlitehelper. executenonquery (idbprovider provider, string sqlstring, idataparameter [] sqlparas)
In levenblog. Data. SQLite. sqlitestate. Update (idbprovider provider, lb_state state)
In levenblog. Service. MVC. blogbasecontroller. onactionexecuting (actionexecutingcontext filtercontext)
In system. Web. MVC. Controller. system. Web. MVC. iactionfilter. onactionexecuting (actionexecutingcontext filtercontext)
In system. Web. MVC. controlleractioninvoker. invokeactionmethodfilter (iactionfilterfilter, actionexecutingcontext precontext, func '1 continuation)
In system. Web. MVC. controlleractioninvoker. <> C _ displayclassc. <> C _ displayclasse. <invokeactionmethodwithfilters> B _ B ()
In system. Web. MVC. controlleractioninvoker. invokeactionmethodwithfilters (methodinfomethodinfo, idictionary '2 parameters, ilist '1 filters)
In system. Web. MVC. controlleractioninvoker. invokeaction (controllercontext, string actionname)

Check the SQLite information and find that SQLite has some problems in concurrency. The FAQ information is as follows:
(7) can multiple examples of multiple applications or the same application simultaneously access the same database file?

Multi-process can open the same database at the same time, or select at the same time. However, only one process can change the database immediately.

SQLite uses read/write locks to control database access. (The Win95/98/me operating system does not support read/write locking. In versions earlier than 2.7.0, this means that only one process can read the database at a time in windows. In version 2.7.0, this problem is solved by executing a user interval probability read/write lock policy in the Windows interface code .) However, if the database file is in an NFS file system, the locking mechanism for controlling concurrent reading may fail. This is because the fcntl () file of NFS is sometimes locked. If multiple processes may read the database concurrently, avoid placing the database file in the NFS file system. According to Microsoft's documentation, if you do not run the cmd.exe background program, the lock in the FAT file system may not work. People who are very experienced in Windows tell me that the locking of network files has many problems and is not reliable. If so, sharing an SQLite database file in two or more Windows systems will cause unpredictable problems.

We know that no other embedded SQL database engine supports more concurrency than SQLite. SQLite allows multiple processes to open and read databases at the same time. When any process needs to be written, the entire database will be locked in this process. However, this generally takes only a few milliseconds. Other processes only need to wait and continue other transactions. Other embedded SQL database engines often only allow single-process access to the database.

However, Client/Server database engines (such as PostgreSQL, MySQL, and Oracle) usually support higher concurrency and multi-process writing to the same database at the same time. Since there is always a well-controlled server that coordinates database access, this ensures the implementation of the above features. If your application requires high concurrency, you should consider using the Client/Server database. In fact, experience tells us that most applications require less concurrency than their designers think.

When SQLite attempts to operate a file locked by another process, the default action is to return sqlite_busy. You can use the C code to change this line. Use the sqlite3_busy_handler () or sqlite3_busy_timeout () API function.

If two or more processes open the same database at the same time, and one of the processes creates a new table or index, other processes may not immediately see the new table. Other processes may need to be shut down and re-linked to the database.

--------------------------------------------------------------------------------

(8) is SQLite thread-safe?

Sometimes yes. To ensure thread security, SQLite must set the threadsafe preprocessing macro to 1 during compilation. In the default released compiled version, Windows is thread-safe, but Linux is not. If thread security is required, the Linux version must be re-compiled.

"Thread security" refers to the "sqlite3" structure returned by two or three threads that can call different sqlite3_open () at the same time. Instead of using the same sqlite3 structure pointer in multiple threads.

A sqlite3 structure can only be used in the process that calls sqlite3_open to create it. You cannot open a database in one thread and pass the pointer to another thread for use. This is because of the limitations of most multithreading systems (or bugs ?) For example, on redhat9. In these problematic systems, the fcntl () Lock created by one thread cannot be deleted or modified by another thread. Because SQLite relies on the fcntl () lock for concurrency control, serious problems may occur when database connections are transferred between threads.

Maybe there is a way to solve the fcntl () lock problem in Linux, but it is very complicated and it will be extremely difficult to test the correctness. Therefore, SQLite currently does not allow sharing handles between threads.

In UNIX, you cannot use a fork () system call to put an open SQLite database into a sub-process. Otherwise, an error occurs.

Therefore, it is certain that the system has a lock error because at the same time two or more threads perform write operations on the database. The database write operations should be completed in a short time, other processes can also write data in sequence. However, in some cases, the old f server may fail to write data to a write process because of insufficient resources, leading to timeout errors of other write processes, the lock error occurs.

Solution: SQLite is a small database for embedded systems after all, and we cannot require it to have high concurrency capabilities. Therefore, we can consider correcting this problem in the program, for example, if you lock the database write operations so that the system can avoid such errors, of course, a better choice is to use the sqlserver version system, such as 51mvc.com. there was no time recently, and the problem was not serious. The chance of occurrence was still very small, so I decided to fix the problem later.