Re: [sqlite] Does Attach improve Concurrency ?
[EMAIL PROTECTED] uttered: When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? 1st process: C:\Documents and Settings\Administrator\Pulpitsqlite3.exe a.db SQLite version 3.3.17 Enter .help for instructions sqlite attach 'b.db' as b; sqlite begin exclusive; 2nd: C:\Documents and Settings\Administrator\Pulpitsqlite3.exe b.db SQLite version 3.3.17 Enter .help for instructions sqlite create table tab(col); SQL error: database is locked So it locks all attached databases. Not by default. BEGIN EXCLUSIVE is not the default transaction locking mode. By default, BEGIN will not lock anything until needed, in which case you can have different sessions locking different attached databases in different ways. SQLite will not block readers until it gets an EXCLUSIVE lock, which is usually upon committal of a transaction, or the spillage of pages from a full page cache. But that will only be on the database that is being updated. BEGIN EXCLUSIVE is explicit and applied to all attached databases. But upgrading an attached database to an EXCLUSIVE lock does not upgrade locks on other attached databases. So, by default, using multiple attached databases may increase concurrency, but watch for deadlock conditions. Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? Yes it does. You can open a connection to a memory database, store each table in a separate database and attach them if needed. I'v already changed sqlite to do it transparently. It still has a lot of bugs, but it has already proven to work. But if you don't mind attaching databases manually originall sqlite will work perfectly. As stated above, not needed if you avoid using BEGIN EXCLUSIVE. Christian -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does Attach improve Concurrency ?
Eduardo Morras uttered: At 19:32 01/06/2007, you wrote: When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? It locks all attached databases. No, it does not improve concurrency but i can improve speed if database files are on more than one phisical disk. The sqlite bottleneck is i/o access on most cases. Tip for performance on Linux and ext3. Mount your database filesystem with data=journal option to write data to the journal before being written in place. This reduces latency drastically, as the journal is contiguous and written at the full IO rate of the device without seeks. An example of the effect it has on a test I did can be found here: http://www.osnews.com/permalink.php?news_id=16522comment_id=184137 Christian -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does Attach improve Concurrency ?
Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? Yes it does. You can open a connection to a memory database, store each table in a separate database and attach them if needed. I'v already changed sqlite to do it transparently. It still has a lot of bugs, but it has already proven to work. But if you don't mind attaching databases manually originall sqlite will work perfectly. As stated above, not needed if you avoid using BEGIN EXCLUSIVE. It is impossible to write to 2 tables even with default transaction. It is impossible to insert inside a select callback. Seeing numerous Table is locked topics I think there are lots of other impossible things to do. And commit does appear at some time, so reading should be synchronized outside of SQLite (or perhaps repeated when error occures). Attach allows to forget about those problems and makes possible table level locks instead of database level locks. -- Nowy darmowy serwis og³oszeniowy Populada. Kup, sprzedaj, zamieñ http://link.interia.pl/f1a8a - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Does Attach improve Concurrency ?
When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? Thanks, Sam - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does Attach improve Concurrency ?
At 19:32 01/06/2007, you wrote: When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? It locks all attached databases. No, it does not improve concurrency but i can improve speed if database files are on more than one phisical disk. The sqlite bottleneck is i/o access on most cases. Usuario de FreeBSD+Xfce, OpenOffice y muchos mas OSS. Microsoft declara que el OSS viola 235 patentes. Por favor, DENUNCIAME. - To unsubscribe, send email to [EMAIL PROTECTED] -
