On 10/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Scott Hess" <[EMAIL PROTECTED]> wrote:
> > We've just had a bit of discussion on the Google Gears team about some
> > cases where failure of an UPDATE/DELETE/INSERT while within a
> > transaction is unexpected. Well, that and that when you're
> > multi-threaded you can hit some hard-to-understand cases.
> >
> > One suggestion was to use BEGIN IMMEDIATE for explicit transactions,
> > rather than BEGIN. And it seemed to us like that might be a
> > reasonable default, given that Gears encourages multiple threads
> > hitting the same database.
> >
> > It looks pretty easy to make this happen (one-line mod to parse.y),
> > and BEGIN DEFERRED is supported syntax for those who really do mean
> > that. Does anyone have a strong argument for why we're descending
> > into a pit of despair by considering this?
>
> Many (most?) of the other teams using SQLite in situations
> similar to Gears have their own separate methods for starting,
> committing, and rolling back transactions. They don't run
> BEGIN, COMMIT, or ROLLBACK statements - they call their own
> built-in methods which in turn runs BEGIN, COMMIT, and
> ROLLBACK on the user's behalf. If you used this approach,
> then you could easily revise your method to call BEGIN IMMEDIATE
> instead of just BEGIN. You could also do the BUSY retry
> handling that Ken suggests.
Indeed, there has been past discussion about whether we should do some
sort of transaction API which integrated with the language (for
instance, automating ROLLBACK on uncaught exceptions). It may be that
this is the time for that to come back to the fore.
> If you really want to use SQL instead of a separate method,
> I would suggest a compile-time switch to make IMMEDIATE the
> default in place of DEFERRED - not a pragma. We already have
> way too many pragmas. I will be happy to add a compile-time
> option to make IMMEDATE the default behavior. I will require
> rather more convincing to add another pragma.
That's a reasonable position to take. For Gears, it would be super
easy to just make the change directly to parse.y, and I _think_ that I
understand things well enough to implement
SQLITE_TRANSACTION_DEFAULT_IMMEDIATE or something along those lines.
Thinking out loud, it looks to me like the change would be something like:
cmd ::= BEGIN transtype(Y) trans_opt. {sqlite3BeginTransaction(pParse, Y);}
trans_opt ::= .
trans_opt ::= TRANSACTION.
trans_opt ::= TRANSACTION nm.
%type transtype {int}
+ %ifdef SQLITE_TRANSACTION_DEFAULT_IMMEDIATE
+ transtype(A) ::= . {A = TK_IMMEDIATE;}
+ %endif
+ %ifndef SQLITE_TRANSACTION_DEFAULT_IMMEDIATE
transtype(A) ::= . {A = 0;}
+ %endif
transtype(A) ::= DEFERRED(X). {A = @X;}
transtype(A) ::= IMMEDIATE(X). {A = @X;}
transtype(A) ::= EXCLUSIVE(X). {A = @X;}
cmd ::= COMMIT trans_opt. {sqlite3CommitTransaction(pParse);}
cmd ::= END trans_opt. {sqlite3CommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt. {sqlite3RollbackTransaction(pParse);}
I can wrap that up better tomorrow. And maybe even think of a better
define than SQLITE_TRANSACTION_DEFAULT_IMMEDIATE :-).
-scott
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
