Sqlite - SQLITE_BUSY 처리 방식

여러 프로세스가 트랜젝션을 동시에 수행하려는 경우 busy 오류가 발생할 수 있다.

busy 해결 방식 중 하나는 busy_timeout 시간을 설정하여 해당 시간 내에서는 계속 시도하도록 하는 것이다.

PRAGMA 설정으로 busy_timeout 을 설정할 수 있다.

#PRAGMA busy_timeout=500

https://www.sqlite.org/pragma.html#pragma_busy_timeout

Pragma statements supported by SQLite

If the result is a general expression, not a just the name of a source table column, then the name of the result is a copy of the expression text.

www.sqlite.org

PRAGMA busy_timeout;
PRAGMA busy_timeout = milliseconds;

Query or change the setting of the busy timeout. This pragma is an alternative to the sqlite3_busy_timeout() C-language interface which is made available as a pragma for use with language bindings that do not provide direct access to sqlite3_busy_timeout().

Each database connection can only have a single busy handler. This PRAGMA sets the busy handler for the process, possibly overwriting any previously set busy handler.

https://www.sqlite.org/rescode.html#busy

Result and Error Codes

Overview Many of the routines in the SQLite C-language Interface return numeric result codes indicating either success or failure, and in the event of a failure, providing some idea of the cause of the failure. This document strives to explain what each of

www.sqlite.org

(5) SQLITE_BUSY

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at the same time process B attempts to start a new write transaction, process B will get back an SQLITE_BUSY result because SQLite only supports one writer at a time. Process B will need to wait for process A to finish its transaction before starting a new transaction. The sqlite3_busy_timeout() and sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to process B to help it deal with SQLITE_BUSY errors.

An SQLITE_BUSY error can occur at any point in a transaction: when the transaction is first started, during any write or update operations, or when the transaction commits. To avoid encountering SQLITE_BUSY errors in the middle of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN to start a transaction. The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY.

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY indicates a conflict with a separate database connection, probably in a separate process, whereas SQLITE_LOCKED indicates a conflict within the same database connection (or sometimes a database connection with a shared cache).

note