Re: Transaction modes, explicit rollbacks etc.

[Bojan Smojver <bo...@rexursive.com>]

On Thu, 2006-06-22 at 14:31 -0700, Rick Keiner wrote:

> After some problems I was having with "cannot start a transaction
> within a transaction" problem with SQLite3, I came across this and I
> think a check should go in the transaction handling for busy
> conditions in the end transaction.
> 
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg15985.html

Finally got some time to experiment with this. Under high concurrency of
transactions (20 threads, both read and update transactions from all
threads), it is much better to have BEGIN IMMEDIATE or BEGIN EXCLUSIVE
at the start of the transaction and then handle transaction failures by
the caller. Starting a transaction blindly and then waiting around in
query/select (basically in a deadlock) was almost 20% performance loss
in my tests.

Maybe we should introduce APR_DBD_TRANSACTION_IMMEDIATE/EXCLUSIVE modes
to tell SQLite3 what we really want? This would have no effect on other
drivers, of course.

-- 
Bojan

Re: Transaction modes, explicit rollbacks etc.

[Bojan Smojver <bo...@rexursive.com>]

Quoting Bojan Smojver <bo...@rexursive.com>:

> We can then handle failures by having a wait loop inside
> transaction_start(), just like we do with select/query.

Actually, it is dbd_sqlite3_query() that's going to do that for us, so  
all we need is just BEGIN IMMEDIATE.

-- 
Bojan

Re: Transaction modes, explicit rollbacks etc.

[Bojan Smojver <bo...@rexursive.com>]

Quoting Bojan Smojver <bo...@rexursive.com>:

> Maybe we should introduce APR_DBD_TRANSACTION_IMMEDIATE/EXCLUSIVE modes
> to tell SQLite3 what we really want? This would have no effect on other
> drivers, of course.

Actually, after reading SQLite3 documentation a bit more, probably the  
best thing to do is to just BEGIN IMMEDIATE (most people want to  
update the database when using transactions). This may lead to writer  
starvation in extreme concurrency situations, but at least it avoids  
the deadlocks.

We can then handle failures by having a wait loop inside  
transaction_start(), just like we do with select/query.

If nobody objects to this in the next few days, I'm going to commit  
changes to this effect.

-- 
Bojan