Thanks for the comments. I appreciate there are nuances of their differences that are likely obvious to a developer during development based on the current phrasing, but I'm putting this forward from a user's perspective.
Depending on the application, an end user likely won't see the error code, but instead just the error message (at least the application where I encountered this issue). They then google for "sqlite database locked" but get back information about SQLITE_LOCKED which isn't the same thing as SQLITE_BUSY. It doesn't particularly matter too much what the message is so long as it helps the user correctly find what it actually relates to in the documentation. Using my simplistic understanding of the differences, what about these two: SQLITE_BUSY - "Database is busy, locked by another connection" (some use of "busy" which should make googling for the actual problem easier to find). SQLITE_LOCKED - "Database table is locked" Cheers, Jonathan -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf Sent: Wednesday, December 03, 2014 3:21 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY >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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] 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 <slav...@bigfraud.org> >wrote: > >> >> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski <pontia...@gmail.com> >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 >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This message has been scanned for viruses by MailControl - www.mailcontrol.com Click https://www.mailcontrol.com/sr/WHaGemhH89nGX2PQPOmvUgny0!6zNmYYeEAyUWMWQN4rKeqIzynLqleDXcn!7sNfY3TQOiZ2K0I94h7CChK4GQ== to report this email as spam. ________________________________ HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them. If you have received this message in error please advise us immediately and destroy all copies of it. HR Wallingford Limited Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in England No. 02562099 ________________________________ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
