A transaction state is dependent upon the lock state, and the lock is at
database level because the database is the resource being shared by user
initiated connections. Locks are held by individual connections and
those connections can be at process or thread level.
The Sqlite locks are process level file locks and are less efficient
when invoked by threads because of the mutexes and extra logic required.
In essence a transaction is a journal file and the transaction lock is a
lock on that file. There can be only one journal current for each
database file. Sqlite does have some sophistications which increase
concurrency. For example it is possible to read a database while a
journal file is being built, but not when it is being committed or if
you specify an exclusive transaction.
Finally, Sqlite is "lite" and designed towards embedded use rather than
enterprise implementations. If you need the features of Oracle, then
use it but if you don't Sqlite's small footprint and minimalist nature
can be highly beneficial.
John Firebaugh wrote:
You're confusing threads with the context of the connection. When you
call sqlite3_open() you get a single connection to a db. That
connection can then be used across threads, but it is for all intents
and purposes a single line of communication with a database (using it
twice at the same time doesn't somehow multiply that relationship).
Thank you for attempting to clarify this for me. You are saying that the
transaction state is part of the connection context, whereas I was
expecting it to be a per-thread-per-connection state.
I had thought, based on the documentation, that there was little or no
functional difference between:
A) Two threads, each of which has its own database connection
B) Two threads, sharing a single database connection
Note that your example of two threads attempting to update the same
record at the same time is consistent with this theory -- in either case
SQLITE_BUSY (or similar) is likely to occur on one of the threads. In my
case, I was also expecting to get SQLITE_BUSY (or similar) with the
second transaction, rather than an error. (I'm well aquainted with the
SQLITE_BUSY behavior and application-level synchronization techniques.)
What else, besides transaction state, is part of the connection context
and shared with shared connections? I know of the page cache, of which
sharing is desired. Anything else to be aware of?
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
