Trevor Talbot wrote:
On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote:
But am I correct in assuming that one way that SQLite provides
serializable transactions is by automatically rolling back
transactions when necessary (and returning SQLITE_IOERR)?
No. That will happen in some specific unavoidable cases, but you
cannot assume it will happen all of the time.
I assume you are referring to the case discussed in the article:
http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError
i.e. the case where a non-COMMIT command within an explicit trans-
action fails when the executing process is unable to spill its cache
to disk due to a SQLITE_BUSY error, resulting in an inconsistent cache.
What will most likely
happen instead is that the first INSERT or UPDATE in a transaction
will return SQLITE_BUSY, and you will have to roll back the
transaction yourself.
It's also possible to simply keep
retrying a failing statement until it succeeds, but in the case of
INSERT or UPDATE that may lead to deadlock, as two connections that
want to write cannot proceed until one of them yields.
Perhaps the best solution is to follow these rules:
IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db):
----------------------------------------------------------------------
(1) Begin the transaction with 'BEGIN IMMEDIATE'. If you get SQLITE_
BUSY, it means that another writer is already accessing the db. Just
sleep awhile and retry, as many times as necessary. Once you get
SQLITE_OK, you're the only writer accessing the db.
(2) If you get SQLITE_BUSY later in the transaction when you want to
write your cache to disk, it can only be due to one or more readers
holding SHARED locks (because you're the only writer). They will
eventually exit without trying to acquire any stronger locks (again,
because you're the only writer). Therefore, no deadlock is possible.
Just sleep awhile and retry, as many times as necessary.
IF THE TRANSACTION IS A READER (i.e. will only read from the db):
-----------------------------------------------------------------
(1) Begin the transaction with a simple 'BEGIN'. This starts the
transaction in the UNLOCKED state, so you *cannot* get a SQLITE_
BUSY at this point.
(2) If you get SQLITE_BUSY later in the transaction, it can only be
due to your trying to acquire a SHARED lock while some other process
holds a PENDING or EXCLUSIVE lock. That process will eventually
finish its writing and exit. Therefore, no deadlock is possible.
Just sleep awhile and retry, as many times as necessary.
If all transactions follow the above rules, then explicit rollbacks
should never be necessary.
- Richard Klein
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
