[Solved]Sqlite error: SQLITE_BUSY and SQLITE_LOCKED
Background
My program has been writing to the SQLite database, which is fine. But when another program (another process) reads, after a period of time, a few minutes, or even a dozen minutes, my program will crash and cause a write database error.
The error message is: SQLITE_BUSY.
The official explanation for SQLITE_BUSY is as follows:
The SQLITE_BUSY result code indicates that the database file cannot be written (or read) because it is being used by another database connection, usually another process’s database connection.
Solution
For example, if process A is in the middle of A write transaction, and process B tries to start A write transaction, then process B gets an SQLITE_BUSY return code.
The solution is to use the sqlite3_busy_timeout() interface. Prototype as below:
int sqlite3_busy_timeout(sqlite3*, int ms);
Set a timeout time. I set this ms variable to 2000 at the beginning(becuase the other process read the data from sqlite database per 2 seconds in my code). It did run for several hours without any problems. However, a few hours later, there was an error occurs. I think it was caused by the ms setting is not precisely. The time should be smaller, such as 1000ms, or 1500ms. But I haven’t tested this, you can test it if it is correct. Or it was a limition.
In addition to this interface, there is the sqlite3_busy_handler() interface, which I didn’t use in my program, so I won’t cover it here.
Basic Concpet of SQLite
SQLite has a number of return codes. SQLITE_OK is a normal return, indicating that the operation against the database was correct and successful. There are two error return codes, one is SQLITE_BUSY described above and the other is SQLITE_LOCKED.
The SQLITE_LOCKED result code indicates that a write operation cannot continue because of a conflict between the same database connection operation or a conflict between different database connections in the shared cache.
For example, a DROP_TABLE operation that is read by another thread using the same database connection will return SQLITE_LOCKED.
To distinguish between the two error result codes, SQLITE_BUSY is a different database connection, usually for a different process. SQLITE_LOCKED is the same database connection, usually a different thread of the same process and using the same database connection.
Finally, here’s how SQLite behaves under multithreading. SQLite supports three different threading modes.
- Single-threaded mode. In this mode, all mutex semaphores are disabled and SQLite is not safe if it uses multiple threads.
- Multithreading mode. In this mode, SQLite can be safe, but only if different threads use different database connections.
- Serialization mode.In this mode, SQLite can be secure and there are no restrictions. The default is serialization mode, which is what I use in my own programs.
We can use sqlite3_threadsafe() to see which threading model our program uses.
int sqlite3_threadsafe(void);
The return value of this function is the variable DSQLITE_THREADSAFE.
- DSQLITE_THREADSAFE = 0 indicates single-threaded mode.
- DSQLITE_THREADSAFE = 2 indicates multithreading mode.
- DSQLITE_THREADSAFE = 1 indicates serialization mode.
Reference
SQLite official address:
- About return codes: https://www.sqlite.org/rescode.html
- About Multithread safety:https://www.sqlite.org/threadsafe.html
Any feedbacks are welcome.
