No, you did not confuse me. We are talking about different things it
appears. My post refers to the shared-cache locking model (http://
sqlite.org/sharedcache.html). The document is clear by itself. What
makes it confusing, is that a shared cache instance exist as a single
normal connection which may coincide with other, "normal" database
connections. Quite a generous feature. But it means that the two
locking models do apply at the same time. The joint connections
within a shared cache are subject to the regular locking model in
relation to possible other connections to the same database.
Confusing or not?
Ken wrote:
Ed,
Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock
per sqlite documentation. I used the two interchangeably, pardon my
error.
A begin exclusive indicates the beginning of a transaction, It
escalates the database lock to an EXCLUSIVE lock. The begin
transaction does not immediately do this, rather it waits until the
buffer cache spills to disk. At this point it attempts to escalate
the Reserved lock to a Pending then an Exclusive lock.
There is only 1 type of EXCLUSIVE (write) lock, It is database
wide and is all or nothing. Once you have the lock, it prevents
other access to the DB.
Ken
Ed Pasma <[EMAIL PROTECTED]> wrote: The ticket has already been
resolved, I see. So it has been
considered a bug. In my earlier reply I tried to defend the current
behavour to be in line with the document, http://sqlite.org/
sharedcache.html. I'm happy to change my mind now. Only I miss
something in the model as described in the document. This may
either be:
- exclusive transactions as a new kind of transactions, apart form
read- and write-transactions
or
- database-level locking as a new level above transaction-level
locking.
May be this suggestion is too naive, anyway it helps me explain the
wonderful cache sharing.
Ken wrote:
Ed,
Dan opened a ticket. I agree the documentation isn't clear on the
Exlusive locking state.
Not really sure, if this is by design or a bug at this stage. I do
think its a great feature of the Shared cache mode to allow table
level locking. But I'm curious with this table level locking what
would happen if two threads performed writes to two seperate tables
concurrently using only a begin immediate.
Thread a writes to tab1,
Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked
kicked returned?)
If it is allowed then would there be two journal files concurrently
existing? And What happens during a crash with two journals ?
This gets complicated very quickly.
Ken
Ed Pasma wrote: Hello,`
Empirically I found that it is exactly true.
Must admit I'm confused but may it is in line with the Shared-Cache
locking model.
This does not mention the EXCLUSIVE locking state.
The most 'secure' locking state it mentions is a write-transaction
and this can coexist with read-transactions from others.
Thus "begin exclusive" starts a write-transaction and the on-going
read does not interfere.
The error message seems to clarify the situation further: database
table is locked. Thus the collision occurs at the table-level. And
yes, taking different tables for read and write, it does not occur.
Practically this may not help very much. But may be the following
does in case you have a busy_timeout setting.
When having Shared-Cache mode enabled, the timeout setting appears to
be ignored by SQLite. This makes locking situations surface rather
soon, also when there is no dead-lock.
The situation may be handled by a programmatic retry?
Regards, Ed
Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven:
Some additional info:
when the sqlite_lock is returned there is another thread that
appears to be reading the same table. Does the sqlite3 step return
sqlite_locked in this case?
Thanks,
Ken
Ken wrote:
While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a
strange lock situation.
SQLITE_LOCK is returned from an insert statement, even though
the thread/connection performed a successful "begin exclusive"
transaction.
begin exclusive
insert into table... ---> returns SQLITE_LOCKED
Is it possible for both connections to begin exclusive transactions
whilst having the shared cache anabled?
Thanks,
ken
---------------------------------------------------------------------
-
-------
To unsubscribe, send email to [EMAIL PROTECTED]
---------------------------------------------------------------------
-
-------
----------------------------------------------------------------------
-------
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------
-------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
