Re: [sqlite] "database is locked" for SQLITE_BUSY
On 4 Dec 2014, at 5:36pm, Jonathan Moules wrote: > Depending on the application, an end user likely won't see the error code, > but instead just the error message SQlite is not a program. It's an API, intended for use by a programmer. Those error codes should not be reported to the end user. They are intended for the user of the API: the programmer. An end user is not expected to know what something like SQLITE_BUSY_SNAPSHOT means. What the programmer has their program do about them is the heart of the matter. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" for SQLITE_BUSY
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
Re: [sqlite] "database is locked" for SQLITE_BUSY
On 3 Dec 2014, at 3:20pm, Richard Hipp wrote: > https://www.sqlite.org/rescode.html#busy Thanks, Richard. I have somehow never seen that. I had no idea that the difference between _BUSY and _LOCKED was purely about whether the conflicting access was from the same connection. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" for SQLITE_BUSY
heory 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 >wrote: > >> >> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski >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
Re: [sqlite] "database is locked" for SQLITE_BUSY
On Wed, Dec 3, 2014 at 10:18 AM, Simon Slavin wrote: > > On 3 Dec 2014, at 3:10pm, Hick Gunter wrote: > > > SQLITE_BUSY means that some connection is BUSY with a write transaction > and has locked the database file; presumably, it will be possible to write > to the database when the current writer has finished, just not now or > within the specified busy timeout. > > > > SQLITE_LOCKED otoh means that the calling application is in error and > has specified two or more transactions whose table access modes are > incompatible and whose table access orders differ. This situation is > resolvable only if at least one involved transaction is rolled back. > > This is very illuminating and far better information than I managed to > find in the official SQLite documentation. It would be really helpful if > something like this could be incorporated in an appropriate place. > > https://www.sqlite.org/rescode.html#busy -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" for SQLITE_BUSY
On 3 Dec 2014, at 3:10pm, Hick Gunter wrote: > SQLITE_BUSY means that some connection is BUSY with a write transaction and > has locked the database file; presumably, it will be possible to write to the > database when the current writer has finished, just not now or within the > specified busy timeout. > > SQLITE_LOCKED otoh means that the calling application is in error and has > specified two or more transactions whose table access modes are incompatible > and whose table access orders differ. This situation is resolvable only if at > least one involved transaction is rolled back. This is very illuminating and far better information than I managed to find in the official SQLite documentation. It would be really helpful if something like this could be incorporated in an appropriate place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "database is locked" for SQLITE_BUSY
I think the error messages are distinct enough as is.
SQLITE_BUSY means that some connection is BUSY with a write transaction and has
locked the database file; presumably, it will be possible to write to the
database when the current writer has finished, just not now or within the
specified busy timeout.
SQLITE_LOCKED otoh means that the calling application is in error and has
specified two or more transactions whose table access modes are incompatible
and whose table access orders differ. This situation is resolvable only if at
least one involved transaction is rolled back.
const char *sqlite3ErrStr(int rc){
static const char* const aMsg[] = {
...
/* SQLITE_BUSY*/ "database is locked",
/* SQLITE_LOCKED */ "database table is locked",
...
};
-Ursprüngliche Nachricht-
Von: Jonathan Moules [mailto:j.mou...@hrwallingford.com]
Gesendet: Mittwoch, 03. Dezember 2014 12:01
An: 'General Discussion of SQLite Database'
Betreff: [sqlite] "database is locked" for SQLITE_BUSY
Hi,
Just a quick request/suggestion.
Currently SQLITE_BUSY events return an error of "Database is locked". Is it
possible to change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked",
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I
can tell is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.
Thanks,
Jonathan
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
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 wrote: > > On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski 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
Re: [sqlite] "database is locked" for SQLITE_BUSY
On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski 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
Re: [sqlite] "database is locked" for SQLITE_BUSY
Although I think there is already an error result, one situation might be when the DB is in a read only state. On Wed, Dec 3, 2014 at 6:15 AM, RSmith wrote: > > On 2014/12/03 13:00, Jonathan Moules wrote: > >> Hi, >> Just a quick request/suggestion. >> >> Currently SQLITE_BUSY events return an error of "Database is locked". Is >> it possible to change this to "Database is busy" or something similar? >> I ask because when someone then goes googling for "SQLite database >> locked", they'll end up thinking they're hitting the SQLITE_LOCKED event >> which as best I can tell is very different to the SQLITE_BUSY event. >> I'm sure I can't be the only person who has been tripped up by that one. >> > > I see your point, but is it not in principle the exact same thing? How is > it "very different"? i.e. - Why else would a DB be "Locked" other than > being explicitly busy? Or, why else would it be "Busy" other than being > specifically locked? > > Is there an event for which the DB can be "Locked" but not necessarily > because it is busy? If so I would like to second this request, but I am > unaware currently that the case exists. > > (Would the case where a DB was opened exclusively by another connection > return a different error? I have not checked this yet). > > > ___ > 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
Re: [sqlite] "database is locked" for SQLITE_BUSY
On 2014/12/03 13:00, Jonathan Moules wrote: Hi, Just a quick request/suggestion. Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to change this to "Database is busy" or something similar? I ask because when someone then goes googling for "SQLite database locked", they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I can tell is very different to the SQLITE_BUSY event. I'm sure I can't be the only person who has been tripped up by that one. I see your point, but is it not in principle the exact same thing? How is it "very different"? i.e. - Why else would a DB be "Locked" other than being explicitly busy? Or, why else would it be "Busy" other than being specifically locked? Is there an event for which the DB can be "Locked" but not necessarily because it is busy? If so I would like to second this request, but I am unaware currently that the case exists. (Would the case where a DB was opened exclusively by another connection return a different error? I have not checked this yet). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "database is locked" for SQLITE_BUSY
Hi, Just a quick request/suggestion. Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to change this to "Database is busy" or something similar? I ask because when someone then goes googling for "SQLite database locked", they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I can tell is very different to the SQLITE_BUSY event. I'm sure I can't be the only person who has been tripped up by that one. Thanks, Jonathan 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
