>From https://www.sqlite.org/rescode.html#busy
In both cases there are specific extended codes that may further pinpoint the
source just in case you do not know what you are doing at the time the result
code was returned. Interpretation is only difficult if you do not know what
you are doing when the result code was returned.
(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.
See also: SQLITE_BUSY_RECOVERY and SQLITE_BUSY_SNAPSHOT.
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).
(6) SQLITE_LOCKED
The SQLITE_LOCKED result code indicates that a write operation could not
continue because of a conflict within the same database connection or a
conflict with a different database connection that uses a shared cache.
For example, a DROP TABLE statement cannot be run while another thread is
reading from that table on the same database connection because dropping the
table would delete the table out from under the concurrent reader.
The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED
indicates a conflict on the same database connection (or on a connection with a
shared cache) whereas SQLITE_BUSY indicates a conflict with a different
database connection, probably in a different process.
(261) SQLITE_BUSY_RECOVERY
The SQLITE_BUSY_RECOVERY error code is an extended error code for SQLITE_BUSY
that indicates that an operation could not continue because another process is
busy recovering a WAL mode database file following a crash. The
SQLITE_BUSY_RECOVERY error code only occurs on WAL mode databases.
(517) SQLITE_BUSY_SNAPSHOT
The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY
that occurs on WAL mode databases when a database connection tries to promote a
read transaction into a write transaction but finds that another database
connection has already written to the database and thus invalidated prior reads.
The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might
arise:
Process A starts a read transaction on the database and does one or more
SELECT statement. Process A keeps the transaction open.
Process B updates the database, changing values previous read by process A.
Process A now tries to write to the database. But process A's view of the
database content is now obsolete because process B has modified the database
file after process A read from it. Hence process B gets an SQLITE_BUSY_SNAPSHOT
error.
(262) SQLITE_LOCKED_SHAREDCACHE
The SQLITE_LOCKED_SHAREDCACHE error code is an extended error code for
SQLITE_LOCKED indicating that the locking conflict has occurred due to
contention with a different database connection that happens to hold a shared
cache with the database connection to which the error was returned. For
example, if the other database connection is holding an exclusive lock on the
database, then the database connection that receives this error will be unable
to read or write any part of the database file unless it has the
read_uncommitted pragma enabled.
The SQLITE_LOCKED_SHARECACHE error code works very much like the SQLITE_BUSY
error code except that SQLITE_LOCKED_SHARECACHE is for separate database
connections that share a cache whereas SQLITE_BUSY is for the much more common
case of separate database connections that do not share the same cache. Also,
the sqlite3_busy_handler() and sqlite3_busy_timeout() interfaces do not help in
resolving SQLITE_LOCKED_SHAREDCACHE conflicts.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Stephen Chrzanowski
>Sent: Wednesday, 3 December, 2014 07:47
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY
>
>To me, a BUSY state would mean that everything up to actually reading or
>writing the data out is valid, but, the response time coming back was
>just
>too long, so a timeout hit which might mean that a retry later might be
>appropriate. To me, a timeout = busy, but, locked != busy. When
>something
>is locked, you're basically denied being able to perform the function for
>one reason or another. If the file or connection is R/O, that'd be a
>valid
>locked error result for write functions. If the connection was alive, a
>write to the database was asked, but it took too long to complete, then,
>BUSY would make sense to me. If the connection was able to send out one
>successful write to the database/WAL, but later down the road the
>transaction took too long to complete, then maybe a BUSY error back would
>be appropriate, but, if the first write failed, then LOCKED might be
>appropriate.
>
>If anything was written to the the DB itself, or the journal file, then
>during the life of that transaction, if write attempt takes too long, the
>result would be BUSY. If no write was done but took too long, then a
>LOCKED error is the error result.
>
>Disclaimer: I've not had my pot of coffee yet, so I might be missing a
>few
>tidbits of information both in what I've written above, as well as the
>mental think-through when I wrote it. :]
>
>On Wed, Dec 3, 2014 at 9:23 AM, Simon Slavin <[email protected]>
>wrote:
>
>>
>> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski <[email protected]>
>wrote:
>>
>> > Although I think there is already an error result, one situation
>might be
>> > when the DB is in a read only state.
>>
>> I just thought of the database /file/ being marked 'read-only'. But it
>> turns out that there's a different SQLite result code for that
>situation.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
