[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\Pulpit>sqlite3.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\Pulpit>sqlite3.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]
-----------------------------------------------------------------------------
