Re: [sqlite] Shared Lock Transactions
Jay Sprenkle uttered: On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote: > > > What is a 'shared' lock? You can't share a lock, only one process > > can own it at a time. > > > > As per this document > > http://www.sqlite.org/lockingv3.html > > SHARED The database may be read but not written. Any number of > processes can hold SHARED locks at the same time, hence there can be > many simultaneous readers. But no other thread or process is allowed > to write to the database file while one or more SHARED locks are > active. > A file lock is used to serialize write access. When reading the file is not locked. This makes little sense. There are no 'shared' locks. Sorry, Accidently hit send before I was finished. This documentation needs to be updated so it makes sense. Making up this conceptual 'shared lock' thing instead of explaining how the locking mechanism really works is not helpful. A 'shared lock' in reality is just opening the disk file that makes up the database without locking. Any number of other processes can read the file. You can't acquire a shared lock, you just don't acquire a write lock. No, you do acquire a lock. You must lock out any writer, but you do not require locking out other readers, hence you can share the lock with other readers, but it is a lock. It is a conceptual lock, and an OS level lock. Without the shared lock, the writer would have no way of knowing whether any other process is reading the file, thus making it unsafe to change the file. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared Lock Transactions
POSIX defines read and write locks. A write lock is exclusive and a read lock inhibits a write lock from occurring. Any number of processes/threads can have a read lock but as long as one read lock is in place a write lock cannot be established. When a write lock is set no read or write locks can complete. Andrew Teirney wrote: Jay Sprenkle wrote Sorry, Accidently hit send before I was finished. This documentation needs to be updated so it makes sense. Making up this conceptual 'shared lock' thing instead of explaining how the locking mechanism really works is not helpful. A 'shared lock' in reality is just opening the disk file that makes up the database without locking. Any number of other processes can read the file. You can't acquire a shared lock, you just don't acquire a write lock. Perhaps i have misread something along the way, but when it comes to a SHARED lock its actually obtaining a advisory read lock on the file? Several documents that i have seen regarding posix advisory locks indicate that there is such a thing, and that they can prevent a write lock from be taken. CU, Andrew - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared Lock Transactions
Jay Sprenkle wrote Sorry, Accidently hit send before I was finished. This documentation needs to be updated so it makes sense. Making up this conceptual 'shared lock' thing instead of explaining how the locking mechanism really works is not helpful. A 'shared lock' in reality is just opening the disk file that makes up the database without locking. Any number of other processes can read the file. You can't acquire a shared lock, you just don't acquire a write lock. Perhaps i have misread something along the way, but when it comes to a SHARED lock its actually obtaining a advisory read lock on the file? Several documents that i have seen regarding posix advisory locks indicate that there is such a thing, and that they can prevent a write lock from be taken. CU, Andrew - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared Lock Transactions
On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote: > > > What is a 'shared' lock? You can't share a lock, only one process can > > own it > > at a time. > > > > As per this document > > http://www.sqlite.org/lockingv3.html > > SHARED The database may be read but not written. Any number of > processes > can hold SHARED locks at the same time, hence there can be many > simultaneous readers. But no other thread or process is allowed to write > > to the database file while one or more SHARED locks are active. > A file lock is used to serialize write access. W hen reading the file is not locked. This makes little sense. There are no 'shared' locks. Sorry, Accidently hit send before I was finished. This documentation needs to be updated so it makes sense. Making up this conceptual 'shared lock' thing instead of explaining how the locking mechanism really works is not helpful. A 'shared lock' in reality is just opening the disk file that makes up the database without locking. Any number of other processes can read the file. You can't acquire a shared lock, you just don't acquire a write lock. -- -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
Re: [sqlite] Shared Lock Transactions
On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote: > What is a 'shared' lock? You can't share a lock, only one process can > own it > at a time. > As per this document http://www.sqlite.org/lockingv3.html SHARED The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active. A file lock is used to serialize write access. W hen reading the file is not locked. This makes little sense. There are no 'shared' locks.
Re: [sqlite] Shared Lock Transactions
Jay Sprenkle wrote: On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote: 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? What is a 'shared' lock? You can't share a lock, only one process can own it at a time. As per this document http://www.sqlite.org/lockingv3.html SHARED The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active. /* Opcode: Transaction P1 P2 * ** ** Begin a transaction. The transaction ends when a Commit or Rollback ** opcode is encountered. Depending on the ON CONFLICT setting, the ** transaction might also be rolled back if an error is encountered. ** ** P1 is the index of the database file on which the transaction is ** started. Index 0 is the main database file and index 1 is the ** file used for temporary tables. ** ** If P2 is non-zero, then a write-transaction is started. A RESERVED lock is ** obtained on the database file when a write-transaction is started. No ** other process can start another write transaction while this transaction is ** underway. Starting a write transaction also creates a rollback journal. A ** write transaction must be started before any changes can be made to the ** database. If P2 is 2 or greater then an EXCLUSIVE lock is also obtained ** on the file. ** ** If P2 is zero, then a read-lock is obtained on the database file. */ BEGIN IMMEDIATE creates the following opcode(s) Transaction 1 BEGIN EXCLUSIVE creates the following opcode(s) Transaction 2 BEGIN SHARED attempts to create the following opcode(s), i found no other way of creating a transaction where a read-lock is obtained on the database, by read-lock i suggest that means a SHARED lock based on my experiments. OP_Transaction 0 So as you can see i feel there is a gap in that one cannot create a transaction which acquires the SHARED (read-only) lock at the execution of a BEGIN statement. This is what the addition of the SHARED transaction type attempts to address. CU, Andrew - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared Lock Transactions
On 1/21/07, Andrew Teirney <[EMAIL PROTECTED]> wrote: 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? What is a 'shared' lock? You can't share a lock, only one process can own it at a time.
[sqlite] Shared Lock Transactions
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; inDb; 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.cSat Dec 16 10:33:42 2006
+++ ./sqlite-3.3.10.patched/tool/mkkeywordhash.cMon 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]
-
