C Programming Interface of SQLite database (7) database locking -- Reading Notes of using SQLite

C Programming Interface for SQlite Database (7) Database Locking)
By drizzle QQ: 253786989

The content in Using SQLite is not very clear. Have time to take a closer look at SQLite documentation: http://www.sqlite.org/lockingv3.html (File Locking And Concurrency In SQLite Version 3)

SQLite uses different locks to protect the database, so that multiple databases can be connected to the same database file at the same time without any database corruption. These locks work well in both the autocommit transaction mode and the explicit transaction mode.

The SQLite locking system involves several locks at different levels to reduce competition and avoid deadlocks. So that SQlite allows multiple database connections to read the same database file in parallel. However, any write operation must be complete and the entire database file is exclusively accessed.

The locking system works well most of the time, allowing different applications to easily and securely share the same database file. If properly encoded, most write operations only take a few minutes. However, if multiple database connections attempt to access the same database file at the same time, these operations will sooner or later meet. Normally, if a database operation requires a lock that is temporarily unavailable, SQLite will return SQLITE_BUSY or, in more extreme cases, return SQLITE_IOERR (or the expansion code SQLITE_IOERR_BLOCKED ). Sqlite3_prepare_xxx, sqlite3_step, sqlite3_reset, and sqlite3_finalize functions return SQLITE_BUSY. The sqlite3_backup_step and sqlite3_blob_open functions also return SQLITE_BUSY, because both functions call the sqlite3_prepare_xxx and sqlite3_step functions internally to complete the work. If the SQL ite3_close function is called and the connected database has a statement that does not destroy (unfinalize), the function returns SQLITE_BUSY.

If you want to access a lock, you need to wait for its owner to complete and release its use. Generally, it will not wait too long. The waiting status can be processed by the application. For example, if the SQLITE_BUSY response is received, the application tries to process the statement again. Alternatively, you can use a busy handler to process it.

Busy Processing Program (Busy handlers)

Busy handler is a callback function called when SQLite library cannot obtain a lock. In busy handler, you can continue to try to obtain the lock, or give up and return the SQLITE_BUSY error code.

SQLite has a built-in timer-based busy handler. You can set a timeout time in milliseconds for this busy handler. Within the time-out period, busy handler will continue to try again to obtain the lock.

int sqlite3_busy_timeout(sqlite3*, int ms);

It is used to set the timeout time of the built-in busy handler, in milliseconds. If 0 or a negative value is passed to the ms parameter, the built-in busy handler is cleared.

Programmers can also write busy handler by themselves, and then set it through the aqlite3_busy_handler function.

int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*);

Set a custom busy handler for the specified database connection and pass the self-written busy handler function to the 2nd parameters of the function. If NULL is passed to the 2nd parameters, the custom busy handler is removed. The 3rd parameters are user data pointers passed to the busy handler callback function.

The following is a prototype of the custom busy handler callback function:

int user_defined_busy_handler_callback( void *udp, int incr )

The 1st parameters are user data pointers passed through the sqlite3_busy_handler function. The first parameter is a counter. The Count of this counter increases each time the busy handler is called. If the callback function returns 0, SQLite will discard the lock acquisition operation and return SQLITE_BUSY. If this function returns a non-0 value, SQLite will continue to try to obtain the lock.

Note that a database connection can only have one busy handler. You cannot set a custom busy handler at the same time and configure a built-in timer-based busy handler. Every time you set one of the busy handler types, the other busy handler will be deleted.

Deadlock (Deadlocks)

Setting busy handler for a database connection cannot solve all problems. Sometimes, deadlocks may occur between multiple database connections. For example, the two databases each hold some locks, but they are now waiting for the lock held by the other side to be released, which will cause a deadlock. The only solution is to discard the lock acquisition operation for one database connection and release the lock held by the database.

If SQLite detects a potential deadlock, it skips busy handler and returns SQLITE_BUSY immediately after a database connection is established. This is done to encourage applications to release their own locks to break the deadlock phenomenon.

Avoid SQLITE_BUSY (Avoiding SQLITE_BUSY)

If we need to fully consider the concurrent performance of the database when developing a project, the simplest way is to use sqlite3_busy_timeout to set the built-in busy handler and adjust the timeout within milliseconds, this reduces the generation of SQLITE_BUSY.

To completely avoid SQLITE_BUSY, the only way is to ensure that only one database connection exists at a certain time point in the same database. In this case, set PRAGMA locking-mode to EXCLUSIVE (EXCLUSIVE ).

In addition, applications can use exclusive transactions, which makes it easier to process sqlite_busy return values. You can use the begin exclusive transaction command to start an exclusive transaction. if the transaction is successful, sqlite_busy is not returned during the transaction execution. However, the begin command may fail to be executed and sqlite_busy is returned. However, in this case, the application can reset the begin statement through the sqlite3_reset function and then try again. The disadvantage of begin exclusive is that it can be successfully executed only when no other database connection (including read-only transactions) is currently accessing the database. Once an exclusive transaction starts to execute, it also locks the database so that other database connections (including read-only transactions) cannot access the database.

To allow more concurrent access, another type of transaction is immediate transaction ). You can run the begin immediate transaction command to start an instant transaction. if the transaction is successful, sqlite_busy is usually returned only when the commit statement is executed. No matter which commands in the transaction (including commit), once sqlite_busy is encountered, the application can simply reset the statement and wait and try again. The begin immediate statement may also encounter sqlite_busy. In this case, the application can simply reset the begin statement and try again. Unlike exclusive transaction, if other database connections are reading (not writing) The database, the immediate transaction can be started at this time. Once the immediate transaction is successfully started, other database connections are not allowed to write data, but read-only database connections can still access the database, unless the immediate transaction is forcibly modifying the database file (usually the transaction is performing the commit operation ). Immediate transactions do not experience deadlocks, and all sqlite_busy can be processed through retry operations.

Avoid deadlocks (Avoiding deadlocks)

The principle of avoiding deadlocks is relatively simple, but following these principles will complicate the application.

First, sqlite3_prepare_xxx, sqlite3_backup_step, and sqlite3_blob_open functions do not produce deadlocks. If these functions return SQLITE_BUSY at any time, simply wait and try again.

If the sqlite3_step, sqlite3_reset, and sqlite3_reset functions return SQLITE_BUSY in a (deferred) transaction, the application must roll back and try again. If these statements are not in an explicit transaction, the prepared statement can be reset and re-executed. If these statements are in an explicit transaction, the entire transaction must be rolled back and then executed from the beginning. The reason for the rollback is that some other database connections need to modify the database. In addition, if the application completes some read operations and is ready to perform write operations, it is best to re-read the information in the new transaction to confirm that the data is still valid.

No matter what you do, do not ignore SQLITE_BUSY. It may rarely happen, but if it is not handled properly, it may become the source of great trouble.

When "BUSY" is converted to "BLOCKED" (When BUSY becomes BLOCKED)

When a connection needs to modify the data in the database, the database will be locked to make it read-only for other connections. In fact, a transaction does not immediately write a database file to a database modification, but is saved in the page cache of the database. If the modification is directly written to the database, the modification is visible to other read connections, which breaks the principle of transaction isolation.

When all the necessary modification operations are completed, the transaction starts to prepare for submission. At this time, the database file will be further locked and new read-only transactions cannot be started. Allow existing read Operations (reader) to complete and release the database locks they hold. When all the read operations are completed, the writer needs to access the database exclusively, and finally refresh the changes in the page cache to the database.

This process allows the write transaction to be executed while the read-only transaction can continue to run. When the write transaction is indeed committed, the read transaction is locked. However, a key assumption is that during this process, all modifications are put into the page cache and written to the database until the transaction is committed. If the cache is full (including the modified data page in the suspended state), there is no other choice for writing transactions. You can only add an exclusive lock to the database, and refresh the cache before the commit (commit) phase. The transaction can still be rolled back at any time, but the write operation must obtain immediate access with an exclusive lock to refresh the cache.

If the lock is not immediately available, the write operation will be forced to terminate the entire transaction. The write transaction will be rolled back and the SQLITE_IOERR_BLOCKED extension code will be returned. Because the transaction is automatically rolled back, the application has no more options and can only restart the transaction.

To avoid this situation, it is best to use the displayed begin exclusive statement to start a large transaction that can modify multiple rows of data. Begin exclusive may fail and SQLITE_BUSY is returned, but the application can simply retry until it is successful. Once an exclusive transaction is successfully started, it can completely access the database and eliminate the emergence of SQLITE_IOERR_BLOCKED, even transactions cause buffer overflow before committing (increasing the database cache will be helpful ).

C Programming Interface for SQlite Database (7) Database Locking)
By drizzle QQ: 253786989