I appologise in advance if this is not the correct place to post this
query ...
I was just wondering whether there might be any particular reason why
there doesn't appear to be support for creating transactions that
acquire a shared lock on execution of the BEGIN statement?
The reason why i ask this is that i try and perform all my database
access within transactions, to avoid conditions which might create
potential deadlocks (and the busy handler not being called). For this i
prefer to start a transaction using a BEGIN statement with the
transaction type specified, such as IMMEDIATE or EXCLUSIVE. This ensures
that any subsequent calls should succeed assuming readers are short lived.
The case i am trying to avoid is a reader starts a transaction with a
deferred type of transaction (the default) a writer goes from a shared
lock to a pending lock to an exclusive and takes some time to write,
during this time the reader bails due to SQLITE_BUSY as it cannot
acquire the lock that it needs to and the busy handler has expired,
albeit a "BEGIN" has been executed.
I have inlined a diff below of what i think is a patch that will create
the necessary op codes on the begin statement to start a transaction
that acquires the shared lock on execution. I have done this as an extra
type of transaction, being a "SHARED" transaction.
Perhaps i am just trying to do this a different way to what was
intended, i however feel more comfortable that if i am going to read
from a sqlite database that i have the necessary lock before i do
anything, that way the sqlite busy stuff is handled around the "BEGIN"
and i don't need to worry about SQLITE_BUSY being returned from further
api calls.
CU,
Andrew
diff -u -r ./sqlite-3.3.10/src/build.c ./sqlite-3.3.10.patched/src/build.c
--- ./sqlite-3.3.10/src/build.c Wed Jan 10 02:53:04 2007
+++ ./sqlite-3.3.10.patched/src/build.c Mon Jan 22 01:57:42 2007
@@ -3043,7 +3043,7 @@
if( !v ) return;
if( type!=TK_DEFERRED ){
for(i=0; i<db->nDb; i++){
- sqlite3VdbeAddOp(v, OP_Transaction, i, (type==TK_EXCLUSIVE)+1);
+ sqlite3VdbeAddOp(v, OP_Transaction, i, (type==TK_SHARED) ? 0 :
(type==TK_EXCLUSIVE)+1);
}
}
sqlite3VdbeAddOp(v, OP_AutoCommit, 0, 0);
diff -u -r ./sqlite-3.3.10/src/parse.y ./sqlite-3.3.10.patched/src/parse.y
--- ./sqlite-3.3.10/src/parse.y Sat Jan 6 02:59:52 2007
+++ ./sqlite-3.3.10.patched/src/parse.y Mon Jan 22 01:56:25 2007
@@ -118,6 +118,7 @@
transtype(A) ::= DEFERRED(X). {A = @X;}
transtype(A) ::= IMMEDIATE(X). {A = @X;}
transtype(A) ::= EXCLUSIVE(X). {A = @X;}
+transtype(A) ::= SHARED(X). {A = @X;}
cmd ::= COMMIT trans_opt. {sqlite3CommitTransaction(pParse);}
cmd ::= END trans_opt. {sqlite3CommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt. {sqlite3RollbackTransaction(pParse);}
@@ -175,7 +176,7 @@
ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN CASCADE CAST CONFLICT
DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN QUERY KEY
- OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
+ OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW SHARED STATEMENT
TEMP TRIGGER VACUUM VIEW VIRTUAL
%ifdef SQLITE_OMIT_COMPOUND_SELECT
EXCEPT INTERSECT UNION
diff -u -r ./sqlite-3.3.10/tool/mkkeywordhash.c
./sqlite-3.3.10.patched/tool/mkkeywordhash.c
--- ./sqlite-3.3.10/tool/mkkeywordhash.c Sat Dec 16 10:33:42 2006
+++ ./sqlite-3.3.10.patched/tool/mkkeywordhash.c Mon Jan 22 01:57:52 2007
@@ -212,6 +212,7 @@
{ "RIGHT", "TK_JOIN_KW", ALWAYS },
{ "ROLLBACK", "TK_ROLLBACK", ALWAYS },
{ "ROW", "TK_ROW", TRIGGER },
+ { "SHARED", "TK_SHARED", ALWAYS },
{ "SELECT", "TK_SELECT", ALWAYS },
{ "SET", "TK_SET", ALWAYS },
{ "STATEMENT", "TK_STATEMENT", TRIGGER },
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
