Such a thing might look something like the attached patch. I'm not enthusiastic about using an extra byte in the sqlite3 structure. If it could be reduced to a flag (default deferred, default immediate), that might be worthwhile.
-scott On 10/10/07, Ken <[EMAIL PROTECTED]> wrote: > Scott, > > I found that using begin imediate was very helpful. But it didn't quite fix > everything. I ended up wrapping the begin immediate with some retry logic > when a sqlite busy is encounted. Once you get the transaction you shouldn't > have any isuses with DML. > > A pragma that could configure the default begin > "deffered/immediate/exclusive" would be nice :) > > Ken > > > Scott Hess <[EMAIL PROTECTED]> wrote: To clarify, this is for Google Gears, a > JavaScript library which > includes a Database component which is implemented using SQLite. If > we were simply building an app on top of SQLite, then the distinction > between BEGIN and BEGIN IMMEDIATE would be no problem - we'd just use > the right thing in the appropriate places. This is a little bit of a > departure from using SQLite in an embedded environment. > > -scott > > > On 10/10/07, John Stanton wrote: > > If you are going to use BEGIN IMMEDIATE why not just enclose the > > transaction in some form of lock like a mutex? > > > > Scott Hess 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? > > > > > > Thanks, > > > scott > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > >
Index: src/build.c =================================================================== RCS file: /sqlite/sqlite/src/build.c,v retrieving revision 1.445 diff -u -r1.445 build.c --- src/build.c 4 Oct 2007 18:11:16 -0000 1.445 +++ src/build.c 11 Oct 2007 00:02:21 -0000 @@ -3092,6 +3092,7 @@ v = sqlite3GetVdbe(pParse); if( !v ) return; + if( !type ) type = db->dfltTransMode; if( type!=TK_DEFERRED ){ for(i=0; i<db->nDb; i++){ sqlite3VdbeAddOp(v, OP_Transaction, i, (type==TK_EXCLUSIVE)+1); Index: src/parse.y =================================================================== RCS file: /sqlite/sqlite/src/parse.y,v retrieving revision 1.234 diff -u -r1.234 parse.y --- src/parse.y 21 Aug 2007 10:44:16 -0000 1.234 +++ src/parse.y 11 Oct 2007 00:02:21 -0000 @@ -113,7 +113,7 @@ trans_opt ::= TRANSACTION. trans_opt ::= TRANSACTION nm. %type transtype {int} -transtype(A) ::= . {A = TK_DEFERRED;} +transtype(A) ::= . {A = 0;} transtype(A) ::= DEFERRED(X). {A = @X;} transtype(A) ::= IMMEDIATE(X). {A = @X;} transtype(A) ::= EXCLUSIVE(X). {A = @X;} Index: src/pragma.c =================================================================== RCS file: /sqlite/sqlite/src/pragma.c,v retrieving revision 1.149 diff -u -r1.149 pragma.c --- src/pragma.c 31 Aug 2007 18:34:59 -0000 1.149 +++ src/pragma.c 11 Oct 2007 00:02:21 -0000 @@ -84,6 +84,17 @@ } #endif /* ifndef SQLITE_OMIT_AUTOVACUUM */ +static int getTransactionMode(const char *z){ + int i; + if( 0==sqlite3StrICmp(z, "deferred") ) return TK_DEFERRED; + if( 0==sqlite3StrICmp(z, "immediate") ) return TK_IMMEDIATE; + if( 0==sqlite3StrICmp(z, "exclusive") ) return TK_EXCLUSIVE; + i = atoi(z); + if( i==1 ) return TK_IMMEDIATE; + if( i==2 ) return TK_EXCLUSIVE; + return TK_DEFERRED; +} + #ifndef SQLITE_OMIT_PAGER_PRAGMAS /* ** Interpret the given string as a temp db location. Return 1 for file @@ -492,6 +503,24 @@ }else #endif + /* + ** PRAGMA [database.]transaction_mode + ** PRAGMA [database.]transaction_mode = 0 | deferred | 1 | immediate | 2 | exclusive + ** + */ + if( sqlite3StrICmp(zLeft,"transaction_mode")==0 ){ + if( sqlite3ReadSchema(pParse) ) goto pragma_out; + if( !zRight ){ + int mode = 0; + if( db->dfltTransMode==TK_IMMEDIATE ) mode = 1; + if( db->dfltTransMode==TK_EXCLUSIVE ) mode = 2; + returnSingleInt(pParse, "transaction_mode", mode ); + }else{ + db->dfltTransMode = getTransactionMode(zRight); + } + }else + + #ifndef SQLITE_OMIT_PAGER_PRAGMAS /* ** PRAGMA [database.]cache_size Index: src/sqliteInt.h =================================================================== RCS file: /sqlite/sqlite/src/sqliteInt.h,v retrieving revision 1.614 diff -u -r1.614 sqliteInt.h --- src/sqliteInt.h 4 Oct 2007 18:11:16 -0000 1.614 +++ src/sqliteInt.h 11 Oct 2007 00:02:21 -0000 @@ -519,6 +519,7 @@ sqlite3_stmt *pFetch; /* Used by SSE to fetch stored statements */ #endif u8 dfltLockMode; /* Default locking-mode for attached dbs */ + u8 dfltTransMode; /* Default transaction mode for */ }; /*
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------