Re: [sqlite] shared cache mode locking
So did my post. We are talking about the same thing. Definately confusing, at least to me.. The problem exists wherein you have two shared connections and one connection performs a begin exclusive... The other connection was just ignoring the exclusivity lock and continuing on its merry way and acquiring a table level lock. Which causes the first connection to get a SQLITE_LOCKED upon an insert to a table that the second connection is reading. The documentation is quite clear that once a connection acquires an EXCLUSIVE lock that it has controll and should not be locked out from writing by any other connections. The dual locking model (prior to the resolution) is ambiguous and could possibly lead application to deadlocks. These are just my thoughts on the matter, and are probably not 100% correct. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: 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 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_cach
Re: [sqlite] shared cache mode locking
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] -
Re: [sqlite] shared cache mode locking
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] -
Re: [sqlite] shared cache mode locking
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 <[EMAIL PROTECTED]> 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] -
Re: [sqlite] shared cache mode locking
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 <[EMAIL PROTECTED]> 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] -
Re: [sqlite] shared cache mode locking
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 <[EMAIL PROTECTED]> 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] -
Re: [sqlite] shared cache mode locking
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 <[EMAIL PROTECTED]> 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
[sqlite] shared cache mode locking
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
