RE: [sqlite] Problems with multiple threads?

[Pat Wibbeler]

No problem.  In fact, I had to consult the docs again to be sure!  I've
sorted through these several times myself.

They are quite good, though I have to admit that I'm constantly having
to consult them to remember how the locking works.  I do agree that a
BEGIN SHARED would be nice for cases where you'd like consistent reads
across multiple statements without using a BEGIN IMMEDIATE and locking
other threads doing the same out.

Pat

-Original Message-
From: A. Pagaltzis [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 7:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems with multiple threads?

* Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 22:55]:
> It's entirely possible I'm reading these docs incorrectly, but
> this strategy has worked quite well for me.

No, I don't see any error in your reading. My apologies; I should
have consulted the docs instead of going by mailing list posts.

It's interesting that there's no way to force a SHARED lock to be
obtained immediately. The available mechanisms allow serialising
write operations with respect to each other, but not forcing a
well-defined sequence of read operations relative to write
operations.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>

Re: [sqlite] Problems with multiple threads?

[drh]

"Kervin L. Pierre" <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I was under the impress that we could never
> get an SQLITE_BUSY, not even on COMMIT if
> we use BEGIN EXCLUSIVE.  But this seems to
> say that COMMITs on exclusive transactions
> can through SQLITE_BUSY?...
> 

You can get an SQLITE_BUSY on the BEGIN EXCLUSIVE
itself.  Assuming you make it past the BEGIN EXCLUSIVE
you should never get another SQLITE_BUSY in that
transaction.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Problems with multiple threads?

[Kervin L. Pierre]

Hello,

I was under the impress that we could never
get an SQLITE_BUSY, not even on COMMIT if
we use BEGIN EXCLUSIVE.  But this seems to
say that COMMITs on exclusive transactions
can through SQLITE_BUSY?...

--- [EMAIL PROTECTED] wrote:

> then start the transaction initially with BEGIN
> EXCLUSIVE.  This
> will acquire the reserved lock immediately (instead
> of waiting to
> the first write occurs) and so you will either get
> an SQLITE_BUSY
> right away (when it is a simple matter to just rerun
> the BEGIN EXCLUSIVE
> statement until it works) or you can be assured of
> never getting
> another SQLITE_BUSY again until you try to COMMIT
> (and there too,
> you can simply rerun COMMIT repeatedly until it
> works.)

How is that?  Since the process at that
point has the exclusive access to the
database file.

Best regards,
Kervin

Re: [sqlite] Problems with multiple threads?

[Joe Wilson]

--- Nathaniel Smith <[EMAIL PROTECTED]> wrote:
> On Wed, Jun 07, 2006 at 01:24:38PM -0400, [EMAIL PROTECTED] wrote:
> > If it is inconvenient to rollback and retry the entire transaction,
> > then start the transaction initially with BEGIN EXCLUSIVE.  This
> > will acquire the reserved lock immediately (instead of waiting to
> > the first write occurs) and so you will either get an SQLITE_BUSY
> > right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE
> > statement until it works) or you can be assured of never getting
> > another SQLITE_BUSY again until you try to COMMIT (and there too,
> > you can simply rerun COMMIT repeatedly until it works.)
> 
> It would be convenient to have another form of "BEGIN", in between
> DEFERRED and IMMEDIATE, whose effect was to immediately acquire the
> shared lock.  That would allow read-only transactions to get this same
> level of programming convenience you describe, where one only has to
> be able to handle SQLITE_BUSY in one place.  (Of course, one could
> simulate this now by immediately running a meaningless SELECT after
> each call to BEGIN, solely for the side-effect of acquiring the lock,
> but it seems less elegant and perhaps not guaranteed to continue
> working in the future.)

Bill King's idea about using reader/writer locks is a much better
idea and far less error prone and should be built into the SQLite
library itself.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Problems with multiple threads?

[A. Pagaltzis]

* Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 22:55]:
> It's entirely possible I'm reading these docs incorrectly, but
> this strategy has worked quite well for me.

No, I don’t see any error in your reading. My apologies; I should
have consulted the docs instead of going by mailing list posts.

It’s interesting that there’s no way to force a SHARED lock to be
obtained immediately. The available mechanisms allow serialising
write operations with respect to each other, but not forcing a
well-defined sequence of read operations relative to write
operations.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] Problems with multiple threads?

[Bill KING]

Jay Sprenkle wrote:
> On 6/7/06, Bill KING <[EMAIL PROTECTED]> wrote:
>> I understand why I'm getting the deadlock now, lazy locking, (it's
>> against the logical grain of transaction/locking, but that's a whole
>> other argument) . Maybe this should be highlighted with big arrows in
>> the information around multi-threading, as starvation/deadlock happens
>> and often, especially if you get the scenario:
>>
>> begin   begin
>> write (fail because of read lock)   write ( busy deadlock)
>> commit (fail, busy, read lock).  commit (fail, busy, deadlock).
>
> Why are you putting transactions around single sql statements?
> There seems to be no benefit to it.
>
Because it's not a single statement, it's several levels of tables, and
the whole operation needs to be atomic for system consistency. Which is,
essentially what transactions are for.

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Problems with multiple threads?

[Bill KING]

[EMAIL PROTECTED] wrote:
> Bill King <[EMAIL PROTECTED]> wrote:
>   
>> Christian Smith wrote:
>> 
>>> If one transaction already has a read lock, and another transaction 
>>> has a reserved lock (trying to get a write lock), neither thread can 
>>> get a write lock. One of the transactions must abort.
>>>
>>> Such a sequence might be (in order):
>>> Transaction 1: BEGIN; SELECT ...
>>> Transaction 2: BEGIN; DELETE ...   (SQLITE_BUSY)
>>> T1   : UPDATE ...  (SQLITE_BUSY)
>>>
>>> Both transactions are now deadlocked.
>>>
>>> It would be nice if SQLite told us this. However, SQLite detects the 
>>> reserved lock and returns SQLITE_BUSY, telling niether transaction 
>>> much other than to try again. If a reserved lock is detected when 
>>> trying to promote an existing read lock, this is a deadlock situation 
>>> and should perhaps return an error code of SQLITE_DEADLOCK instead?
>>>   
>> According to DRH this scenario shouldn't happen. Begin should set a 
>> flag, and the second begin will bug out because the flag is set. This is 
>> what looks like happening in my scenario, and is definately wrong 
>> behaviour.
>> 
>
> The flag that is set is private to each sqlite3 database
> connection.  So setting the flag in one connection should not
> have any effect on any other connection.
>
>
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
>
>
>   
I understand why I'm getting the deadlock now, lazy locking, (it's
against the logical grain of transaction/locking, but that's a whole
other argument) . Maybe this should be highlighted with big arrows in
the information around multi-threading, as starvation/deadlock happens
and often, especially if you get the scenario:

begin   begin
write (fail because of read lock)   write ( busy deadlock)
commit (fail, busy, read lock).  commit (fail, busy, deadlock).

Which is quite common in a multi-threaded environment. (Our situation is
a multi-threaded directory scanner, inserting file entries into the
database, so collision occurs often)

The system descends into a sleep/wait scenario then, and with the
increasing sleep periods, just... stops. It eventually recovers, but, if
I'd known that had I used begin exclusive transaction in the first place
to stop this deadlock scenario, I wouldn't have had the levels of grief
that I have had.

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Problems with multiple threads?

[Craig Morrison]

Bill King wrote:


It would be nice if SQLite told us this. However, SQLite detects the 
reserved lock and returns SQLITE_BUSY, telling niether transaction 
much other than to try again. If a reserved lock is detected when 
trying to promote an existing read lock, this is a deadlock situation 
and should perhaps return an error code of SQLITE_DEADLOCK instead?



Christian

According to DRH this scenario shouldn't happen. Begin should set a 
flag, and the second begin will bug out because the flag is set. This is 
what looks like happening in my scenario, and is definately wrong 
behaviour. begin should be just that begin, mutually exclusive, unless 
Dr Hipp want's to implement versioning based transaction schemes. Not, 
begin "maybe i'm read, maybe i'm write, i'll decide later and woe betide 
any one else who tries to write".




It may be wrong behavior, but that's how SQLite works. Sometimes you 
just have to learn the quirks of the system and then deal with them.


File a bug report or submit a patch with the "correct" behavior and I am 
sure DRH will be more than happy to review it.


--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

Re: [sqlite] Problems with multiple threads?

[A. Pagaltzis]

* 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 // 

RE: [sqlite] Problems with multiple threads?

[Pat Wibbeler]

Beginning everything with BEGIN IMMEDIATE should eliminate the
possibility of deadlock, but you will serialize read-only operations.
If your transactions are short or contention is low, using BEGIN
IMMEDIATE makes things easy.

However, if you find that you have a set of read-only operations that
run frequently, or take a long time, you may want to consider simply
using BEGIN on these (or for single statement reads, you could leave the
transaction wrapping out altogether).

Pat  

-Original Message-
From: Jiri Hajek [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 11:33 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems with multiple threads?

> If it is inconvenient to rollback and retry the entire transaction,
then
start the transaction initially with BEGIN EXCLUSIVE.  
> This will acquire the reserved lock immediately (instead of waiting to
the
first write occurs) and so you will either get an 
> SQLITE_BUSY right away (when it is a simple matter to just rerun the
BEGIN
EXCLUSIVE statement until it works) or you can be
> assured of never getting another SQLITE_BUSY again until you try to
COMMIT
(and there too, you can simply rerun COMMIT 
> repeatedly until it works.)

Thanks, I overlooked that by default transactions are DEFERRED in
SQLite. It
really fixes the problem.

As I think about it, if I make _all_ transactions in my application
IMMEDIATE, there shouldn't be any risk of a deadlock, right?

Thanks,
Jiri

RE: [sqlite] Problems with multiple threads?

[Jiri Hajek]

> If it is inconvenient to rollback and retry the entire transaction, then
start the transaction initially with BEGIN EXCLUSIVE.  
> This will acquire the reserved lock immediately (instead of waiting to the
first write occurs) and so you will either get an 
> SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN
EXCLUSIVE statement until it works) or you can be
> assured of never getting another SQLITE_BUSY again until you try to COMMIT
(and there too, you can simply rerun COMMIT 
> repeatedly until it works.)

Thanks, I overlooked that by default transactions are DEFERRED in SQLite. It
really fixes the problem.

As I think about it, if I make _all_ transactions in my application
IMMEDIATE, there shouldn't be any risk of a deadlock, right?

Thanks,
Jiri

Re: [sqlite] Problems with multiple threads?

[drh]

Christian Smith <[EMAIL PROTECTED]> wrote:
> On Wed, 7 Jun 2006, Jiri Hajek wrote:
> 
> > However, right after fixing this, I found another problem. It certainly can
> > be my fault, but I don't see how could it be: If I don't use transactions,
> > multiple threads seem to proceed well, but then right after I add BEGIN and
> > COMMIT to some place, all threads lock eventually. I debugged it and found
> > that _all_ threads accessing SQLite are in a loop waiting for an action to
> > proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be
> > somehow my fault, or is it some kind of a dead-lock in SQLite?
> 
> 
> If one transaction already has a read lock, and another transaction has a 
> reserved lock (trying to get a write lock), neither thread can get a write 
> lock. One of the transactions must abort.
> 
> Such a sequence might be (in order):
> Transaction 1: BEGIN; SELECT ...
> Transaction 2: BEGIN; DELETE ...   (SQLITE_BUSY)
> T1   : UPDATE ...  (SQLITE_BUSY)
> 
> Both transactions are now deadlocked.
> 
> It would be nice if SQLite told us this. However, SQLite detects the 
> reserved lock and returns SQLITE_BUSY, telling niether transaction much 
> other than to try again. If a reserved lock is detected when trying to 
> promote an existing read lock, this is a deadlock situation and should 
> perhaps return an error code of SQLITE_DEADLOCK instead?
> 

If an application already has a shared lock and it gets an
SQLITE_BUSY while trying to do its first write, it can safely 
assume that it is in a deadlock situation.

Anytime you get an SQLITE_BUSY return on the first write attempt
of a transaction, the best way to deal with it is to rollback
and retry the entire transaction.

If it is inconvenient to rollback and retry the entire transaction,
then start the transaction initially with BEGIN EXCLUSIVE.  This
will acquire the reserved lock immediately (instead of waiting to
the first write occurs) and so you will either get an SQLITE_BUSY
right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE
statement until it works) or you can be assured of never getting
another SQLITE_BUSY again until you try to COMMIT (and there too,
you can simply rerun COMMIT repeatedly until it works.)

I will look into translating selected SQLITE_BUSY returns into
SQLITE_DEADLOCK.  This will be a slight change in the way things
work, so I'll have to move to a new minor version number: 3.4.0.
But perhaps that is worth doing.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] BEGIN and Backup [was [sqlite] Problems with multiple threads?]

[drh]

"Pat Wibbeler" <[EMAIL PROTECTED]> wrote:
> You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of
> lock you'd like.
> 

If you are just trying to make sure the database does
not change while you back it up, then Jay's suggestion
of BEGIN IMMEDIATE is the better approach (better than
my suggestion of BEGIN EXCLUSIVE) since BEGIN IMMEDIATE
only gets a read lock and thus allows other processes
to continue reading the database while the backup is
taking place.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] Problems with multiple threads?

[Pat Wibbeler]

Do you have any transactions that look like:

BEGIN
SELECT
INSERT/DELETE/UPDATE
COMMIT

If you do, you may have multiple threads trying to escalate from a
SHARED to a RESERVED lock as described here:
http://sqlite.org/capi3ref.html#sqlite3_busy_handler

It's important that if you have multithreaded access and a strategy that
involves either retrying or waiting on SQLITE_BUSY that you specify at
the beginning of write transactions that the transaction requires a
higher level lock.  I do this using "BEGIN IMMEDIATE".  

The thread currently on this list with subject
"[sqlite] BEGIN and Backup [was [sqlite] Problems with multiple
threads?]"
Discusses a similar issue.

Pat

-Original Message-
From: Jiri Hajek [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 9:26 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems with multiple threads?

Thanks for an additional explanation, I used sqlite3_get_autocommit()
for
debugging and it helped me to find out that it really was my fault.
There
was an incorrect processing after COMMIT returned SQLITE_BUSY. So sorry
for
this.

However, right after fixing this, I found another problem. It certainly
can
be my fault, but I don't see how could it be: If I don't use
transactions,
multiple threads seem to proceed well, but then right after I add BEGIN
and
COMMIT to some place, all threads lock eventually. I debugged it and
found
that _all_ threads accessing SQLite are in a loop waiting for an action
to
proceed and all of them keep getting SQLITE_BUSY result. I wonder, can
it be
somehow my fault, or is it some kind of a dead-lock in SQLite?

In the simpliest form it takes only two threads to reproduce and the
problem
looks like:

Thread 1:

BEGIN TRANSACTION<-- proceeded
INSERT INTO ...  <-- Processing stops here, waiting
in a
loop, Sqlite3_step() infinitely returns SQLITE_BUSY
COMMIT


Thread 2:

  // no explicit transaction start here
DELETE FROM ...   <-- Processing stops here, waiting in
a
loop, Sqlite3_step() infinitely returns SQLITE_BUSY


Thread 2 can also look like this in order to reproduce the problem
(situation in Thread 1 remains the same):

BEGIN TRANSACTION<-- proceeded
DELETE FROM ...  <-- proceeded 
COMMIT <-- Processing stops here,
waiting in
a loop, Sqlite3_step() infinitely returns SQLITE_BUSY

No other thread calls any SQLite function.

Do you have any idea what could be wrong?

Thanks,
Jiri

RE: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?]

[Pat Wibbeler]

You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of
lock you'd like.

SQLite BEGIN syntax:
http://sqlite.org/lang_transaction.html

SQLite locks:
http://sqlite.org/lockingv3.html

SQLite Busy Handler:
http://sqlite.org/capi3ref.html#sqlite3_busy_handler

Pat

-Original Message-
From: Russell Leighton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 8:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with
multiple threads?]

So, this was very enlightening...I have a simple backup function that I 
now question is correct.

It does:
- execute "begin" // lock from writes
-copy db file to new file byte by byte
- execute "commit" // unlock

...I was thinking that "begin" would lock the file.

If I use an flock()  [or should it be lockf() ?? ] to bracket the file 
copy will I be safe?

I was hoping to have something more efficient than a table by table copy

for backup.

Thx

[EMAIL PROTECTED] wrote:

>As various people search for application and/or SQLite bugs
>related to multiple threads and BEGIN, let me try to aid the
>effort by better describing exactly what BEGIN does and 
>suggesting some debugging tricks.
>
>Realize that BEGIN does not actually create any file locks
>or check to see if any file locks already exist, nor
>interact in any other way with the filesystem.  File locks
>are only created by SELECT, UPDATE, INSERT, and DELETE
>statements.  (OK, also CREATE and DROP statements, but 
>let's ignore those for now for simplicity.  Presumably 
>the schema is fixed at the point where the problems are
>occuring.)  All BEGIN does is to set a flag that says
>"do not automatically perform a COMMIT after each write
>to the database".  This is the autoCommit flag that I
>mentioned in a prior email.  autoCommit is a boolean
>member of the sqlite3 structure.  A lock is acquired
>at the beginning of each UPDATE, INSERT, or DELETE
>if it does not already exists.  After each UPDATE,
>INSERT, or DELETE, sqlite checks the value of the
>autoCommit flag, and if it is true it automatically
>does a COMMIT.  A read-lock is acquired before each
>SELECT if it does not already exists, and after the
>SELECT is done, the read-lock is dropped if autoCommit
>is true.
>
>So the BEGIN instruction does not do anything with the
>filesystem.  It does not interact in any way with the
>operating system or with other database connections.
>All BEGIN does is clear the autoCommit flag.  So it is
>hard to imagine how having other threads could possibly
>effect its behavior.
>
>At any time, you can determine the value of the autoCommit
>flag using the sqlite3_get_autocommit() API.  See
>
>  http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit
>
>I suggest that people who are getting back unexpected
>"cannot start a transaction within a transaction" errors
>should use the sqlite3_get_autocommit() API in some printf()s
>to trace the status of the autocommit flag within their
>application.  Prior to running BEGIN, it should always
>be the case that sqlite3_get_autocommit() returns TRUE.
>If sqlite3_get_autocommit() returns FALSE, then the BEGIN
>that follows will give the "cannot start..." error.
>
>I suspect what is happening is that some prior COMMIT
>or ROLLBACK is not setting the autoCommit flag back to
>TRUE.  This might be because the COMMIT or ROLLBACK
>failed.  Or, there could be some kind of bug in SQLite
>that is causing the autoCommit flag to not be set
>correctly.  I suspect the former, but am open to evidence
>pointing to the latter.  It might be useful to use the
>sqlite3_get_autocommit() function to print out the value
>of the autoCommit flag after each COMMIT and ROLLBACK is
>executed.  This might help to isolate the problem.
>
>--
>D. Richard Hipp   <[EMAIL PROTECTED]>
>
>  
>

Re: [sqlite] BEGIN and Backup [was [sqlite] Problems with multiple threads?]

[Russell Leighton]

Thx!

[EMAIL PROTECTED] wrote:


Russell Leighton <[EMAIL PROTECTED]> wrote:
 

So, this was very enlightening...I have a simple backup function that I 
now question is correct.


It does:
   - execute "begin" // lock from writes
   -copy db file to new file byte by byte
   - execute "commit" // unlock

I was thinking that "begin" would lock the file.

   



Use BEGIN EXCLUSIVE instead of just BEGIN.  The extra
"EXCLUSIVE" keyword causes it to acquire a lock right
away.
--
D. Richard Hipp   <[EMAIL PROTECTED]>



 

RE: [sqlite] Problems with multiple threads?

[Christian Smith]

On Wed, 7 Jun 2006, Jiri Hajek wrote:


However, right after fixing this, I found another problem. It certainly can
be my fault, but I don't see how could it be: If I don't use transactions,
multiple threads seem to proceed well, but then right after I add BEGIN and
COMMIT to some place, all threads lock eventually. I debugged it and found
that _all_ threads accessing SQLite are in a loop waiting for an action to
proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be
somehow my fault, or is it some kind of a dead-lock in SQLite?



If one transaction already has a read lock, and another transaction has a 
reserved lock (trying to get a write lock), neither thread can get a write 
lock. One of the transactions must abort.


Such a sequence might be (in order):
Transaction 1: BEGIN; SELECT ...
Transaction 2: BEGIN; DELETE ...   (SQLITE_BUSY)
T1   : UPDATE ...  (SQLITE_BUSY)

Both transactions are now deadlocked.

It would be nice if SQLite told us this. However, SQLite detects the 
reserved lock and returns SQLITE_BUSY, telling niether transaction much 
other than to try again. If a reserved lock is detected when trying to 
promote an existing read lock, this is a deadlock situation and should 
perhaps return an error code of SQLITE_DEADLOCK instead?



Christian

Re: [sqlite] Problems with multiple threads?

[Jay Sprenkle]

On 6/7/06, Jiri Hajek <[EMAIL PROTECTED]> wrote:


However, right after fixing this, I found another problem. It certainly can
be my fault, but I don't see how could it be: If I don't use transactions,
multiple threads seem to proceed well, but then right after I add BEGIN and
COMMIT to some place, all threads lock eventually. I debugged it and found
that _all_ threads accessing SQLite are in a loop waiting for an action to
proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be
somehow my fault, or is it some kind of a dead-lock in SQLite?


That looks like a deadlock. Can you take the transactions out?

From your example they're not doing anything for you anyway

with only a single statement within them

RE: [sqlite] Problems with multiple threads?

[Jiri Hajek]

Thanks for an additional explanation, I used sqlite3_get_autocommit() for
debugging and it helped me to find out that it really was my fault. There
was an incorrect processing after COMMIT returned SQLITE_BUSY. So sorry for
this.

However, right after fixing this, I found another problem. It certainly can
be my fault, but I don't see how could it be: If I don't use transactions,
multiple threads seem to proceed well, but then right after I add BEGIN and
COMMIT to some place, all threads lock eventually. I debugged it and found
that _all_ threads accessing SQLite are in a loop waiting for an action to
proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be
somehow my fault, or is it some kind of a dead-lock in SQLite?

In the simpliest form it takes only two threads to reproduce and the problem
looks like:

Thread 1:

BEGIN TRANSACTION<-- proceeded
INSERT INTO ...  <-- Processing stops here, waiting in a
loop, Sqlite3_step() infinitely returns SQLITE_BUSY
COMMIT


Thread 2:

  // no explicit transaction start here
DELETE FROM ...   <-- Processing stops here, waiting in a
loop, Sqlite3_step() infinitely returns SQLITE_BUSY


Thread 2 can also look like this in order to reproduce the problem
(situation in Thread 1 remains the same):

BEGIN TRANSACTION<-- proceeded
DELETE FROM ...  <-- proceeded 
COMMIT <-- Processing stops here, waiting in
a loop, Sqlite3_step() infinitely returns SQLITE_BUSY

No other thread calls any SQLite function.

Do you have any idea what could be wrong?

Thanks,
Jiri

Re: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?]

[Jay Sprenkle]

On 6/7/06, Russell Leighton <[EMAIL PROTECTED]> wrote:

So, this was very enlightening...I have a simple backup function that I
now question is correct.

It does:
- execute "begin" // lock from writes
-copy db file to new file byte by byte
- execute "commit" // unlock

...I was thinking that "begin" would lock the file.



I believe you want a "BEGIN IMMEDIATE" instead of a "BEGIN".
This is how I implemented my own version of it. If it doesn't work
would you let us know?


--
SqliteImporter, SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

RE: [sqlite] Problems with multiple threads?

[Brannon King]

For me, I have a bunch of threads writing to the database. That is the only
part I do multithreaded. (All my read queries are handled after all the data
is written.) I just use the scoped_lock operator from the Boost library at
the top of my function that does the bind and step calls. I pass a pointer
to the class containing that function and the mutex object to all my writer
threads. It seems to work great; I don't even have SQLite compiled with
threading enabled. My write function starts a new transaction every few
thousand writes.

> Hi Bill,
> 
> When you say "handle read/write locking [your]self" do you 
> mean outside of SQLite in your code or by altering SQLite's 
> source code?
> 
> What algorithm do you employ?

Re: [sqlite] Problems with multiple threads?

[drh]

As various people search for application and/or SQLite bugs
related to multiple threads and BEGIN, let me try to aid the
effort by better describing exactly what BEGIN does and 
suggesting some debugging tricks.

Realize that BEGIN does not actually create any file locks
or check to see if any file locks already exist, nor
interact in any other way with the filesystem.  File locks
are only created by SELECT, UPDATE, INSERT, and DELETE
statements.  (OK, also CREATE and DROP statements, but 
let's ignore those for now for simplicity.  Presumably 
the schema is fixed at the point where the problems are
occuring.)  All BEGIN does is to set a flag that says
"do not automatically perform a COMMIT after each write
to the database".  This is the autoCommit flag that I
mentioned in a prior email.  autoCommit is a boolean
member of the sqlite3 structure.  A lock is acquired
at the beginning of each UPDATE, INSERT, or DELETE
if it does not already exists.  After each UPDATE,
INSERT, or DELETE, sqlite checks the value of the
autoCommit flag, and if it is true it automatically
does a COMMIT.  A read-lock is acquired before each
SELECT if it does not already exists, and after the
SELECT is done, the read-lock is dropped if autoCommit
is true.

So the BEGIN instruction does not do anything with the
filesystem.  It does not interact in any way with the
operating system or with other database connections.
All BEGIN does is clear the autoCommit flag.  So it is
hard to imagine how having other threads could possibly
effect its behavior.

At any time, you can determine the value of the autoCommit
flag using the sqlite3_get_autocommit() API.  See

  http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit

I suggest that people who are getting back unexpected
"cannot start a transaction within a transaction" errors
should use the sqlite3_get_autocommit() API in some printf()s
to trace the status of the autocommit flag within their
application.  Prior to running BEGIN, it should always
be the case that sqlite3_get_autocommit() returns TRUE.
If sqlite3_get_autocommit() returns FALSE, then the BEGIN
that follows will give the "cannot start..." error.

I suspect what is happening is that some prior COMMIT
or ROLLBACK is not setting the autoCommit flag back to
TRUE.  This might be because the COMMIT or ROLLBACK
failed.  Or, there could be some kind of bug in SQLite
that is causing the autoCommit flag to not be set
correctly.  I suspect the former, but am open to evidence
pointing to the latter.  It might be useful to use the
sqlite3_get_autocommit() function to print out the value
of the autoCommit flag after each COMMIT and ROLLBACK is
executed.  This might help to isolate the problem.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] Problems with multiple threads?

[Pat Wibbeler]

One means of troubleshooting this is to emit a log statement that
includes the thread id with every BEGIN/COMMIT (e.g. printf("%d - %s",
thread, sql)).  It may be useful to log other sql statements this way as
well.  

This sort of troubleshooting has always shown the mistake to be mine,
not SQLite when I see this issue.

Pat

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 06, 2006 6:36 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems with multiple threads?

Bill KING <[EMAIL PROTECTED]> wrote:
> Could this be cause by one thread opening a transaction, then a second
> on a second connection trying to open a transaction on it, and failing
> to open the transaction file (as it already exists?). 
> 

No.

Each database connection (each sqlite3* pointer) has a boolean
field called "autoCommit".  If autoCommit is false, that means
a transaction is active.  If autoCommit is true, that means you
are not inside a transaction.  autoCommit is true by default,
of course.

Executing BEGIN does not do anything with the disk.  All it does
is change the autoCommit flag from true to false.  If the flag
was already false at the time BEGIN is executed, it generates
the "cannot start a transaction within a transaction" error.

The important thing to note here is that BEGIN never does
any system calls - it never interacts with the database file
in any way.  All it does is set what should be a private boolean
variable to false.

Now, if you do BEGIN EXCLUSIVE or some other variation on BEGIN,
that is a different matter.  But you said you just did a simple
BEGIN.  And in that case, the nothing visible to other database
connections ever gets touched.

This leads me to conjecture that you have multiple threads
messing with the same database connection

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Problems with multiple threads?

[Bill KING]

Joe Wilson wrote:
> --- Bill KING <[EMAIL PROTECTED]> wrote:
>   
>> Outside of, and I use Qt's QReadWriteLock. ->lockForRead()/lockForWrite()
>> http://doc.trolltech.com/4.1/qreadwritelock.html
>> 
>
> Thanks.
>
> Many follow-up questions... :-)
>
> Is this for an application or for the Qt SQLite database driver class?
> If it is the latter, how could your database wrapper code know when 
> to employ a read lock vs. write lock? Only transactions use write locks?
> What about a standalone update, insert or delete outside a transaction?
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
>
>   
This is for essentially a standalone ;) I'm in the Mobile and Embedded
Systems office, working on QTopia (mobile phone/pda/portable media
player operating environment). Essentially, this is the backend engine
for a document management system, so I control all access to this
particular database via an engine interface class, which in turn means
that I can read or write lock around each QSqlQuery as fits.

http://doc.trolltech.com/qtopia4.1/docsys-overview.html
The documentation at the link above is a little out of date, but gives a
good idea/overview.

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Problems with multiple threads?

[Bill KING]

Joe Wilson wrote:
> Hi Bill,
>
> When you say "handle read/write locking [your]self" do
> you mean outside of SQLite in your code or by altering 
> SQLite's source code?
>
> What algorithm do you employ?
>
> --- Bill KING <[EMAIL PROTECTED]> wrote:
>   
>> I personally did do all this, this doesn't solve the issue. As I
>> mentioned earlier, I followed all the rules, the only solution to avoid
>> the issues he's getting was to handle read/write locking myself.
>> 
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
>
>   
Outside of, and I use Qt's QReadWriteLock. ->lockForRead()/lockForWrite()
http://doc.trolltech.com/4.1/qreadwritelock.html

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Problems with multiple threads?

[John Stanton]

[EMAIL PROTECTED] wrote:

Bill KING <[EMAIL PROTECTED]> wrote:


Could this be cause by one thread opening a transaction, then a second
on a second connection trying to open a transaction on it, and failing
to open the transaction file (as it already exists?). 




No.

Each database connection (each sqlite3* pointer) has a boolean
field called "autoCommit".  If autoCommit is false, that means
a transaction is active.  If autoCommit is true, that means you
are not inside a transaction.  autoCommit is true by default,
of course.

Executing BEGIN does not do anything with the disk.  All it does
is change the autoCommit flag from true to false.  If the flag
was already false at the time BEGIN is executed, it generates
the "cannot start a transaction within a transaction" error.

The important thing to note here is that BEGIN never does
any system calls - it never interacts with the database file
in any way.  All it does is set what should be a private boolean
variable to false.

Now, if you do BEGIN EXCLUSIVE or some other variation on BEGIN,
that is a different matter.  But you said you just did a simple
BEGIN.  And in that case, the nothing visible to other database
connections ever gets touched.

This leads me to conjecture that you have multiple threads
messing with the same database connection

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Is it conceivable that the autocommit flag could actually be a semaphore 
or similar so that BEGIN would actually queue the transaction and 
resolve the clash?  The problem of thread interaction appears to puzzle 
many users so moving the solution into Sqlite rather than expecting the 
application to handle it might be better partitioning.


I realize that every extra feature like that detracts from "Lite" and 
burdens simple embedded applications, so it could perhaps be an option.

Re: [sqlite] Problems with multiple threads?

[Joe Wilson]

Hi Bill,

When you say "handle read/write locking [your]self" do
you mean outside of SQLite in your code or by altering 
SQLite's source code?

What algorithm do you employ?

--- Bill KING <[EMAIL PROTECTED]> wrote:
> I personally did do all this, this doesn't solve the issue. As I
> mentioned earlier, I followed all the rules, the only solution to avoid
> the issues he's getting was to handle read/write locking myself.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Problems with multiple threads?

[drh]

Bill KING <[EMAIL PROTECTED]> wrote:
> Could this be cause by one thread opening a transaction, then a second
> on a second connection trying to open a transaction on it, and failing
> to open the transaction file (as it already exists?). 
> 

No.

Each database connection (each sqlite3* pointer) has a boolean
field called "autoCommit".  If autoCommit is false, that means
a transaction is active.  If autoCommit is true, that means you
are not inside a transaction.  autoCommit is true by default,
of course.

Executing BEGIN does not do anything with the disk.  All it does
is change the autoCommit flag from true to false.  If the flag
was already false at the time BEGIN is executed, it generates
the "cannot start a transaction within a transaction" error.

The important thing to note here is that BEGIN never does
any system calls - it never interacts with the database file
in any way.  All it does is set what should be a private boolean
variable to false.

Now, if you do BEGIN EXCLUSIVE or some other variation on BEGIN,
that is a different matter.  But you said you just did a simple
BEGIN.  And in that case, the nothing visible to other database
connections ever gets touched.

This leads me to conjecture that you have multiple threads
messing with the same database connection

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Problems with multiple threads?

[Bill KING]

[EMAIL PROTECTED] wrote:
> "Jiri Hajek" <[EMAIL PROTECTED]> wrote:
>   
>> 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
>> to open the database file') error. I found out that it can be fixed by
>> running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
>> a big issue, but still I wonder why this error message is returned?
>> Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?
>> 
>
> This happens when CreateFileW() fails.  In some cases, it retries
> a few times before giving up.  I think SQLITE_CANTOPEN is more
> specific (and thus a better error) than SQLITE_BUSY or SQLITE_LOCKED
> which can happen for a variety of reasons.
>
>   
>> 2. More serious issue is that after I enable transaction usage (not used in
>> 1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
>> an error SQLITE_ERROR ('cannot start a transaction within a transaction').
>> Problem is that I definitely am not already in a transaction. The only
>> reason for this seems to be that there's >1 thread running, with only 1
>> thread running there's no problem.
>>
>> 
>
> The code that generates the 'cannot start a transaction within a
> transactoin' message is very simple.  There is not much to go wrong.
> I'm thinking that the either (1) some other thread is doing a "BEGIN" 
> on the same database connection before the current thread gets around 
> to it, or (2) the previous "COMMIT" or "ROLLBACK" ended the prior 
> transaction did not run to completion.  In either case, you are in
> an active transaction and the error message is telling you the truth.
>
> If you have additional evidence of problems in SQLite, I will be
> glad to listen to it.  But based on what I see above, I think this
> is mostly likely a bug in your application, not in SQLite.
>
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
>
>
>   
Could this be cause by one thread opening a transaction, then a second
on a second connection trying to open a transaction on it, and failing
to open the transaction file (as it already exists?). Could this be
solved by per connection transaction journals, or maybe by blocking? One
problem I did see with all this, is that once this error occurs, there
are cascading failures (possibly because transaction left open due to
failure to commit or some other reason).

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Problems with multiple threads?

[drh]

"Jiri Hajek" <[EMAIL PROTECTED]> wrote:
> 
> 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
> to open the database file') error. I found out that it can be fixed by
> running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
> a big issue, but still I wonder why this error message is returned?
> Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?

This happens when CreateFileW() fails.  In some cases, it retries
a few times before giving up.  I think SQLITE_CANTOPEN is more
specific (and thus a better error) than SQLITE_BUSY or SQLITE_LOCKED
which can happen for a variety of reasons.

> 
> 2. More serious issue is that after I enable transaction usage (not used in
> 1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
> an error SQLITE_ERROR ('cannot start a transaction within a transaction').
> Problem is that I definitely am not already in a transaction. The only
> reason for this seems to be that there's >1 thread running, with only 1
> thread running there's no problem.
> 

The code that generates the 'cannot start a transaction within a
transactoin' message is very simple.  There is not much to go wrong.
I'm thinking that the either (1) some other thread is doing a "BEGIN" 
on the same database connection before the current thread gets around 
to it, or (2) the previous "COMMIT" or "ROLLBACK" ended the prior 
transaction did not run to completion.  In either case, you are in
an active transaction and the error message is telling you the truth.

If you have additional evidence of problems in SQLite, I will be
glad to listen to it.  But based on what I see above, I think this
is mostly likely a bug in your application, not in SQLite.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Problems with multiple threads?

[Bill KING]

[EMAIL PROTECTED] wrote:
> "Jiri Hajek" <[EMAIL PROTECTED]> writes:
>
>   
>> 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
>> to open the database file') error. I found out that it can be fixed by
>> running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
>> a big issue, but still I wonder why this error message is returned?
>> Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?
>>
>> 2. More serious issue is that after I enable transaction usage (not used in
>> 1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
>> an error SQLITE_ERROR ('cannot start a transaction within a transaction').
>> Problem is that I definitely am not already in a transaction. The only
>> reason for this seems to be that there's >1 thread running, with only 1
>> thread running there's no problem.
>> 
>
> Do you have a separate database handle (provided by a unique call to
> sqlite3_open()) for each and every thread?  If not, the above sort of error
> can occur.  If you ensure that every thread opens the database itself and no
> thread ever modifies the schema, you should not see either of those problems.
>
> Cheers,
>
> Derrell
>
>   
I personally did do all this, this doesn't solve the issue. As I
mentioned earlier, I followed all the rules, the only solution to avoid
the issues he's getting was to handle read/write locking myself.

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] Problems with multiple threads?

[Derrell . Lipman]

"Jiri Hajek" <[EMAIL PROTECTED]> writes:

> 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
> to open the database file') error. I found out that it can be fixed by
> running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
> a big issue, but still I wonder why this error message is returned?
> Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?
>
> 2. More serious issue is that after I enable transaction usage (not used in
> 1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
> an error SQLITE_ERROR ('cannot start a transaction within a transaction').
> Problem is that I definitely am not already in a transaction. The only
> reason for this seems to be that there's >1 thread running, with only 1
> thread running there's no problem.

Do you have a separate database handle (provided by a unique call to
sqlite3_open()) for each and every thread?  If not, the above sort of error
can occur.  If you ensure that every thread opens the database itself and no
thread ever modifies the schema, you should not see either of those problems.

Cheers,

Derrell

Re: [sqlite] Problems with multiple threads?

[John Stanton]

Synchronize you database access so that only one transaction is current 
and is finalized on cpmpletion.  In other words serialize it.  You can 
use a mutex or similar to achieve synchronization.


Look back at the recent discussion on this forum.

Jiri Hajek wrote:

Hello,

I'm trying to use SQLite in an application where it's needed to work with
one database in mutliple threads. Based on all the info I read in SQLite
documentation I create a new database connection for every new thread
created. Each thread does some SELECTs, INSERTs or UPDATEs, but there isn't
any schema modification. If multiple threads are running, I encounter some
strange problems:

1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
to open the database file') error. I found out that it can be fixed by
running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
a big issue, but still I wonder why this error message is returned?
Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?

2. More serious issue is that after I enable transaction usage (not used in
1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
an error SQLITE_ERROR ('cannot start a transaction within a transaction').
Problem is that I definitely am not already in a transaction. The only
reason for this seems to be that there's >1 thread running, with only 1
thread running there's no problem.

I tried this also with the latest version of SQLite (3.3.6 on Windows).

Any idea what can I do about it?

Thanks,
Jiri

Re: [sqlite] Problems with multiple threads?

[Bill KING]

Jiri Hajek wrote:
> Hello,
>
> I'm trying to use SQLite in an application where it's needed to work with
> one database in mutliple threads. Based on all the info I read in SQLite
> documentation I create a new database connection for every new thread
> created. Each thread does some SELECTs, INSERTs or UPDATEs, but there isn't
> any schema modification. If multiple threads are running, I encounter some
> strange problems:
>
> 1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
> to open the database file') error. I found out that it can be fixed by
> running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
> a big issue, but still I wonder why this error message is returned?
> Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?
>
> 2. More serious issue is that after I enable transaction usage (not used in
> 1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
> an error SQLITE_ERROR ('cannot start a transaction within a transaction').
> Problem is that I definitely am not already in a transaction. The only
> reason for this seems to be that there's >1 thread running, with only 1
> thread running there's no problem.
>
> I tried this also with the latest version of SQLite (3.3.6 on Windows).
>
> Any idea what can I do about it?
>
> Thanks,
> Jiri
>
>
>   
I've seen these same issues myself too on linux/3.3.5, my solution was
using our own internal classes to marshal read/write locking ourselves.
Cleared everything right up.

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

[sqlite] Problems with multiple threads?

[Jiri Hajek]

Hello,

I'm trying to use SQLite in an application where it's needed to work with
one database in mutliple threads. Based on all the info I read in SQLite
documentation I create a new database connection for every new thread
created. Each thread does some SELECTs, INSERTs or UPDATEs, but there isn't
any schema modification. If multiple threads are running, I encounter some
strange problems:

1. Occasionally after running Sqlite3_step() I get SQLITE_CANTOPEN ('Unable
to open the database file') error. I found out that it can be fixed by
running the query again, i.e. again calling Sqlite3_Prepare(). So this isn't
a big issue, but still I wonder why this error message is returned?
Shouldn't SQLITE_BUSY or SQLITE_LOCKED be returned instead?

2. More serious issue is that after I enable transaction usage (not used in
1.) sometimes (again, it's random) after calling 'BEGIN TRANSACTION' I get
an error SQLITE_ERROR ('cannot start a transaction within a transaction').
Problem is that I definitely am not already in a transaction. The only
reason for this seems to be that there's >1 thread running, with only 1
thread running there's no problem.

I tried this also with the latest version of SQLite (3.3.6 on Windows).

Any idea what can I do about it?

Thanks,
Jiri