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]
-----------------------------------------------------------------------------
