Reading the BEGIN docs here: http://www.sqlite.org/lang_transaction.html
I saw "After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE". This is because "If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed". To me, this means that if all threads do BEGIN IMMEDIATE, it will prevent other threads from doing BEGIN IMMEDIATE, thus serializing all transactions. It also says that "The first read operation against a database creates a SHARED lock". I took this to mean that a BEGIN (meaning deferred) transaction would only escalate to SHARED on the first select, where a BEGIN IMMEDIATE would escalate to RESERVED. So, for transactions where a write occurs (usually including a read), I do a BEGIN IMMEDIATE. For transactions where I know that I'm only reading (select), I simply do BEGIN and let the database escalate to SHARED. It's entirely possible I'm reading these docs incorrectly, but this strategy has worked quite well for me. Pat -----Original Message----- From: A. Pagaltzis [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 1:27 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problems with multiple threads? * Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 20:50]: > Beginning everything with BEGIN IMMEDIATE should eliminate the > possibility of deadlock, but you will serialize read-only > operations. Why? BEGIN IMMEDIATE acquires a for-read lock. Multiple for-read locks can be acquired concurrently. It is only for-write locks that can only be acquired in the absence of any other locks, which leads to serialisation. Putting all your read operations in BEGIN IMMEDIATE means that all your write operations will be serialised in relation to all other operations taking place, but read operations can proceed apace. Of course, if your writes are short and frequent, they will likely take much longer than necessary if all your operations acquire read locks before they *really* need them. Regards, -- Aristotle Pagaltzis // <http://plasmasturm.org/>
