On Wed, 2013-11-20 at 13:23 +0200, RSmith wrote: > 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 > >
Thanks for both of your detailed replies. In this case I have a one writer / many reader setup, and the writing is performed in a dedicated process, so it really doesnt make sense to report to a calling process that it was busy and it should try again. The routines are asynchronous already with user/IPC system defined timeouts, so it should certainly retry if say, the DB lock could not be immediately obtained, possibly because of some VFS subsystem. I was unaware of the automatic timeout system that I can use with SQLite, that sounds much better than handling it manually. And finally yes, it's possible that we might run a transaction that initially starts with selects and only might insert/delete depending on, for instance, what happens during a schema upgrade after running "PRAGMA table_info (folder_id)". While there will never be more than one writer, it has been known to happen in my concurrency tests that SQLITE_BUSY is returned, certainly not because someone else actually has a writer's lock. (in this case we retry, and so far that doesn't cause problems, I raised this issue because of the documentation in http://www.sqlite.org/c3ref/step.html ... which I found to be alarming, but now I understand a bit better, thanks to your replies). There are cases where readers will run multiple selects inside a transaction, to ensure that the overall DB revision string was stable before accessing data (in some cases it helps to avoid races to just wait for the writer process to send us a change notification with the new revision string before allowing the direct reader to read again). I had been considering optionally using IMMEDIATE though just to make sure that I can retry at the right place when SQLITE_BUSY is returned, and I might do that as well for the cases where I know there will be a write coming along. Of course, this propagates some responsibility up the call chain, I am implementing a sort of "convenient" API to interact with a vCard database, so I should be as flexible as possible and allow the caller to lock / fetch settings / add contacts / unlock the addressbook at will, without exposing gruesome implementation details of whatever backing DB I am using. So, I will look into this timeout setting as it seems interesting, and also consider using IMMEDIATE for cases where I know there will be a write. Regarding nested transactions, that was my mistake, some of my C code allows nesting of transactions by keeping a counter of the nesting level, but only issuing SQLite commands at the outer most frame (I forgot this detail, hence my confusion). Thanks again. Cheers, -Tristan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
