Hi Tristan,
Do you honestly have a use-case where you do not know whether a transaction is
going to be writing to the DB or not?
I would imagine the only way this is possible is that you are doing some form of select query, and then based on the outcome, decide
whether or not to start writing values or not.
If this is the case, the solution is simple - Do the select query before starting the transaction. Only when you KNOW you are going
to need to write to the DB, THEN start a transaction - and start it explicitely Immediate - if this fails, feel free to retry as
many times as ppossible.
If you insist on starting a transaction without immediate, and later maybe upgrade it to a writing transaction (i.e. obtain a
lock)... then it may fail (if the db is Busy) and if it does you can end and retry the entire transaction because no writes would
have been effected yet, although it will also rerun the selects which is not efficient, but might be needed if the other thread
writing to the DB (which made your current thread fail with SQLITE_BUSY) changed some of the data you depend on for the write
decisions. This is easy in DB terms, but might complicate your App code - I would go with the first suggestion.
As an aside - you mention "Nested Transaction"... there is no such thing, unless you are using Savepoints. You can only ever begin 1
single transaction, the next "BEGIN TRANSACTION" in the same DB connection will fail hard.
http://www.sqlite.org/lang_transaction.html
http://www.sqlite.org/lang_savepoint.html
To get back to Simon's advice about failing loud and you suggesting you wish to
suppress this....
if you set a sufficient time-out, you should never get Busy replies - unless you have something that updates the database for, say,
an hour or so - some long-running update preventing your thread from doing its thing - In which case you have to ask yourself, do I
really want to keep my user hanging-on for an hour waiting while I retry? - just so I gracefully suppress the message? If you think
that is a good idea, you have not had many users. Let them KNOW what is going on, it's always safer. A user that doesn't know what
is going on will use the OS managers to simply stop your task/app from running and then blame you for the broken database
(rightfully so in IMHO) and various other nefarious things.
http://www.sqlite.org/pragma.html#pragma_busy_timeout
Cheers,
Ryan
On 2013/11/20 07:03, Tristan Van Berkom wrote:
Thanks for the slightly more definitive answer than I could get on IRC. It doesn't exactly answer my question though. More
precisely then, in the case that a read transaction is upgraded to a write transaction, either by issuing an INSERT/DELETE command
or by issuing a nested BEGIN IMMEDIATE command, is it safe to retry sqlite3_exec() until SQLITE_BUSY is not returned anymore ? Or,
must the entire transaction be retried from the beginning, in the case that SQLITE_BUSY is returned at the point of this upgrade
from read to write transaction ?
What the documentation is saying however is a word to the wise: Always check
your return codes, and always deal with every situation that you can foresee.
If a situation arises for which you are not prepared then die -- as
expeditiously and as noisily as possible.
Yes, good general advice of course.
My particular interest here is to handle SQLITE_BUSY gracefully
so that callers of my library don't have to deal with it.
Similar to how one normally would check (errno == EINTR) in
a read()/write() loop and keep writing until finished, or until
an error from which one cannot recover, occurs (instead
of propagating the cumbersome error up the call chain).
You seem to suggest that I should fail loudly in the case
of SQLITE_BUSY, this is exactly what I want to avoid.
Cheers,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
