You are correct about the reserved lock. I looked back at my notes
instead of using memory. It is only set when the database is about to
have something written to it, and it stops further reserved locks from
being set. A reserved lock is promoted to a pending lock which stops
further shared locks being set. A pending lock can be promoted to
exclusive when all shared locks are reset. When an exclusive lock is
held it is safe to modify the database.
The pending lock phase limits write starvation by forcing a gap in reads.
BEGIN doesn't do any locking. BEGIN IMMEDIATE does.
More correctly:
BEGIN Thread one
BEGIN Thread two
INSERT Thread one sets reserved lock
INSERT Thread two, fails to set reserved lock
SELECT Thread two, set shared lock
COMMIT on thread one promotes reserved lock to pending
SELECT Thread two fails to set shared lock
COMMIT on thread one continues, promoting pending lock to
exclusive and commits journal, then releases lock
INSERT Thread two, retries and gets reserved lock
...
I am not clear on the mechanism of promoting a pending lock to
exclusive. Does it block until all shared locks are cleared or does it
return? Does an attampt to set a shared lock when a pending lock is set
return a BUSY or block?
Alternatively if maximum concurrency is not required
BEGIN IMMEDIATE Sets write lock on thread one
BEGIN IMMEDIATE Fails to set write lock on thread two
SQL on thread one runs with a chance of a BUSY
COMMIT Thread one promotes commits journal and releases lock
BEGIN IMMEDIATE On thread two now retries and gets write lock
In a threaded environment what we do is equivalent to a BEGIN IMMEDIATE
except that it offers greater concurrency by permitting multiple
concurrent read-only transactions:
a. Read-Only Transaction -
set pthread_rwlock to read
BEGIN
SQL....
COMMIT
reset pthread-rwlock
b. Transaction which modifies DB -
set pthread_rwlock to write
BEGIN
SQL...
COMMIT
reset pthread_rwlock
A dummy fcntl avoids Sqlite from needlessly mirroring the locking. In
an intense traffic environment some extra logic to provide mandatory
write cycles might be necessary, but we do not at this stage use Sqlite
in such applications.
My apologies for being careless and not checking the facts initially.
Igor Tandetnik wrote:
John Stanton <[EMAIL PROTECTED]> wrote:
How about the case of:
BEGIN sets reserved lock on thread one
You mean BEGIN IMMEDIATE, right?
SELECT promotes lock to shared on thread one
I'm not sure what you mean by "promotes" here. If anything, RESERVED
lock is a superset of SHARED, not the other way round. SELECT statement
most definitely does not cause a transaction that started with BEGIN
IMMEDIATE to release its RESERVED lock.
BEGIN sets reserved lock from thread two
It can't. Thread one already holds a RESERVED lock. Only one thread can
acquire such.
SELECT promotes reserved lock in thread two to shared
Wrong. See above.
INSERT tries to promote shared lock to exclusive on thread one
but fails because second thread holds a shared lock
This statement doesn't make any sense to me, sorry. It bears no
relationship to reality, so I don't even know where to begin to disprove
it.
My understanding is that once a reserved lock has been promoted to
shared
A reserved lock is never "promoted" to shared. Whatever gave you this idea?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
