[EMAIL PROTECTED] wrote: >> It appears that if 2 threads start transactions at the same time, >> both inserting into the same table, neither thread can finish until >> one has rolled back. > The behavior is deliberate because it gives you, the programmer, > more control and better concurrency in some situations. But it > can also result in the deadlock behavior that you observe. > > The plain BEGIN will succeed even if another process is > already expressed and interest in writing to the database. > This allows you to read from the database concurrently > with the writer, if that is what you want to do. But > because another process is already writing, you will not be > able to write. And if you try to write, you will get into a > deadlock. > > The BEGIN IMMEDIATE, on the other hand, will fail with SQLITE_BUSY > if another process has already started writing. It will not > succeed until the other process commits. This is the perferred > thing to do if you intend to write within your transaction.
Hmm, I just modified my test to make each thread write to a separate table, and the same symptom occurs. I guess I assumed that this wouldn't occur with multiple tables, but apparently the lock happens on a database-wide level. Now I'm just confused at the reason why transactions have any other mode besides IMMEDIATE (or EXCLUSIVE) in SQLite. I don't think any implementations would use transactions for read-only work, as I don't believe there is any benefit to using transactions in that scenario. I don't understand how _not_ using IMMEDIATE would give you better concurrency in any situation... If you were performing read-only queries, and not using a transaction at all, you should get the same behavior [as a standard BEGIN transaction, then performing the read-only query], if there was an outstanding RESERVED or EXCLUSIVE lock. Please enlighten me if I am wrong here, I'm still pretty new to SQLite. Thanks! -Brad ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
