Multithreading Question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Multithreading Question

Michael J. Sviridov
Hi All

 

Using sqlite 3.2.7 in a multi-threaded C++ application:

 

I've got two thread's (with unique db handles), each thread does the
following:

 

BEGIN EXCLUSIVE TRANSACTION;

 

(60,000 INSERT OR REPLACE statements into the same table)

 

COMMIT TRANSACTION;

 

This works fine, as expected, one thread acquires the lock and the other
thread wait's for it to be released in a SQLITE_BUSY loop.

 

My question: Is the same thing possible/safe with a DEFERRED or IMMEDIATE
transaction?

 

When I use an EXCLUSIVE transaction all is well, but if I try to use a
DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from
sqlite3_step() on one or two of the INSERT OR REPLACE statements. My indexes
are also sometimes corrupted after this. Am I missing something?

 

 

I would appreciate any help/advice/enlightenment,

 

Mike.

 

 

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Multithreading Question

Igor Tandetnik
Michael J. Sviridov wrote:

> I've got two thread's (with unique db handles), each thread does the
> following:
>
> BEGIN EXCLUSIVE TRANSACTION;
> (60,000 INSERT OR REPLACE statements into the same table)
> COMMIT TRANSACTION;
>
> This works fine, as expected, one thread acquires the lock and the
> other thread wait's for it to be released in a SQLITE_BUSY loop.
>
> My question: Is the same thing possible/safe with a DEFERRED or
> IMMEDIATE transaction?
>
> When I use an EXCLUSIVE transaction all is well, but if I try to use a
> DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from
> sqlite3_step() on one or two of the INSERT OR REPLACE statements.

This is normal for DEFERRED transaction. Your transaction starts as
read-only, and is converted to read/write when the first modifying
statement is executed. But it is possible that another transaction has
already expressed an intent to write, and is waiting for all readers to
clear. The only way out of this situation is to roll back the
transaction and restart it from the beginning. Just retrying the last
statement is pointless, and will result in the same error.

SQLITE_ERROR should not happen for IMMEDIATE transaction, but it is
possible for it to get SQLITE_BUSY on the first modifying statement (as
well as on BEGIN statement).

> My
> indexes are also sometimes corrupted after this.

This should not happen under any circumstances. If this is indeed the
case, it is probably a bug and you should report it.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Multithreading Question

Michael J. Sviridov
Thanks for the response Igor.

I've added a few more introspection routines to my code to see what is going
on while these two threads are working and it seems the following is
happening:

Again, with two threads (A and B), each thread does the following:

BEGIN IMMEDIATE TRANSACTION;
(60,000 INSERT OR REPLACE statements into the same table)
COMMIT TRANSACTION;

thread A acquires the lock and starts to write. Meanwhile, thread B waits
for the lock to be released in a SQLITE_BUSY loop. The strange thing is,
however, that thread B's wait loop seems to somehow affect thread A's
writing. After many thousand SQLITE_BUSY's from thread B, thread A's
sqlite3_step() returns SQLITE_ERROR "SQLite logic error or missing database"
and sqlite3_finalize() returns SQLITE_IOERR "disk I/O error". Each thread
runs perfectly when run in turn (i.e. one after the other) but when I try to
run them concurrently, the first thread seems always to experience this
problem. The second thread always behaves normally, i.e. it eventually
breaks out of it's SQLITE_BUSY loop when thread A has finished (but failed)
and happily commits it's changes to disk. I'm really battling with this, how
can thread B's SQLITE_BUSY loop be affecting thread A's writing?

I'm using Visual C++ 7.1 on Windows XP and using the sqlite3.dll from
www.sqlite.org.

Again, any help would be appreciated.
Mike.

-----Original Message-----
From: Igor Tandetnik [mailto:[hidden email]]
Sent: 21 October 2005 02:38 PM
To: SQLite
Subject: [sqlite] Re: Multithreading Question

Michael J. Sviridov wrote:

> I've got two thread's (with unique db handles), each thread does the
> following:
>
> BEGIN EXCLUSIVE TRANSACTION;
> (60,000 INSERT OR REPLACE statements into the same table)
> COMMIT TRANSACTION;
>
> This works fine, as expected, one thread acquires the lock and the
> other thread wait's for it to be released in a SQLITE_BUSY loop.
>
> My question: Is the same thing possible/safe with a DEFERRED or
> IMMEDIATE transaction?
>
> When I use an EXCLUSIVE transaction all is well, but if I try to use a
> DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from
> sqlite3_step() on one or two of the INSERT OR REPLACE statements.

This is normal for DEFERRED transaction. Your transaction starts as
read-only, and is converted to read/write when the first modifying
statement is executed. But it is possible that another transaction has
already expressed an intent to write, and is waiting for all readers to
clear. The only way out of this situation is to roll back the
transaction and restart it from the beginning. Just retrying the last
statement is pointless, and will result in the same error.

SQLITE_ERROR should not happen for IMMEDIATE transaction, but it is
possible for it to get SQLITE_BUSY on the first modifying statement (as
well as on BEGIN statement).

> My
> indexes are also sometimes corrupted after this.

This should not happen under any circumstances. If this is indeed the
case, it is probably a bug and you should report it.

Igor Tandetnik


Reply | Threaded
Open this post in threaded view
|

Re: Re: Multithreading Question

John Stanton-3
In reply to this post by Michael J. Sviridov
Have you thought of performing your own thread locking using the
equivalent of a semaphore?  It is a much better method than some form of
busy wait and is not only less likely to unearth deep synchronisation
problems but will result in a faster running application.

Threads A and B wait on the semaphore before the BEGIN TRANSACTION and
signal it after the COMMIT or a possible ROLLBACK.
JS

Michael J. Sviridov wrote:

> Thanks for the response Igor.
>
> I've added a few more introspection routines to my code to see what is going
> on while these two threads are working and it seems the following is
> happening:
>
> Again, with two threads (A and B), each thread does the following:
>
> BEGIN IMMEDIATE TRANSACTION;
> (60,000 INSERT OR REPLACE statements into the same table)
> COMMIT TRANSACTION;
>
> thread A acquires the lock and starts to write. Meanwhile, thread B waits
> for the lock to be released in a SQLITE_BUSY loop. The strange thing is,
> however, that thread B's wait loop seems to somehow affect thread A's
> writing. After many thousand SQLITE_BUSY's from thread B, thread A's
> sqlite3_step() returns SQLITE_ERROR "SQLite logic error or missing database"
> and sqlite3_finalize() returns SQLITE_IOERR "disk I/O error". Each thread
> runs perfectly when run in turn (i.e. one after the other) but when I try to
> run them concurrently, the first thread seems always to experience this
> problem. The second thread always behaves normally, i.e. it eventually
> breaks out of it's SQLITE_BUSY loop when thread A has finished (but failed)
> and happily commits it's changes to disk. I'm really battling with this, how
> can thread B's SQLITE_BUSY loop be affecting thread A's writing?
>
> I'm using Visual C++ 7.1 on Windows XP and using the sqlite3.dll from
> www.sqlite.org.
>
> Again, any help would be appreciated.
> Mike.
>
> -----Original Message-----
> From: Igor Tandetnik [mailto:[hidden email]]
> Sent: 21 October 2005 02:38 PM
> To: SQLite
> Subject: [sqlite] Re: Multithreading Question
>
> Michael J. Sviridov wrote:
>
>>I've got two thread's (with unique db handles), each thread does the
>>following:
>>
>>BEGIN EXCLUSIVE TRANSACTION;
>>(60,000 INSERT OR REPLACE statements into the same table)
>>COMMIT TRANSACTION;
>>
>>This works fine, as expected, one thread acquires the lock and the
>>other thread wait's for it to be released in a SQLITE_BUSY loop.
>>
>>My question: Is the same thing possible/safe with a DEFERRED or
>>IMMEDIATE transaction?
>>
>>When I use an EXCLUSIVE transaction all is well, but if I try to use a
>>DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from
>>sqlite3_step() on one or two of the INSERT OR REPLACE statements.
>
>
> This is normal for DEFERRED transaction. Your transaction starts as
> read-only, and is converted to read/write when the first modifying
> statement is executed. But it is possible that another transaction has
> already expressed an intent to write, and is waiting for all readers to
> clear. The only way out of this situation is to roll back the
> transaction and restart it from the beginning. Just retrying the last
> statement is pointless, and will result in the same error.
>
> SQLITE_ERROR should not happen for IMMEDIATE transaction, but it is
> possible for it to get SQLITE_BUSY on the first modifying statement (as
> well as on BEGIN statement).
>
>
>>My
>>indexes are also sometimes corrupted after this.
>
>
> This should not happen under any circumstances. If this is indeed the
> case, it is probably a bug and you should report it.
>
> Igor Tandetnik
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Re: Multithreading Question

Lloyd Dupont
In reply to this post by Michael J. Sviridov
Well, I guess I was not clear in my explanations.
I'm already doing my own locking, that's the problem!
But I'm not sure how to improve it.


My problem here is the following: I am doing a search through all record,
could be long. Do some string matching, cutting, etc with all text of all
records.

For a good user experience my application doesn't block but do that in a
thread.
Therefore the user still has the hand and could do other things such as
update 1 value.
At which stage the data will be saved it and.....

that will froze the application because the search/select already get the
database lock.

Now I wonder how to work around the problem?

could I have 2 connection and the connection which runs the select could
intermix with the update command of the other thread?

is it thread unsafe only when moving the database cursor?
is i.e. I jyst have to lock everytim I move the cursor so I could mix both
the select and the update?
any tips?
Reply | Threaded
Open this post in threaded view
|

Re: Re: Multithreading Question

Chris Schirlinger
> Well, I guess I was not clear in my explanations.
> I'm already doing my own locking, that's the problem!
> But I'm not sure how to improve it.
...
> At which stage the data will be saved it and.....
>
> that will froze the application because the search/select already get the
> database lock.

How about putting the update into in it's own thread and just wait
till any locks are released?



Reply | Threaded
Open this post in threaded view
|

Re: Re: Multithreading Question

Lloyd Dupont
> How about putting the update into in it's own thread and just wait
> till any locks are released?
>
I though of that but think it's annoying, what if the user want to Quit the
application?
Should he wait for all the thread to perform...

But someone give me a good idea, I could make a copy of the dabase and
perform the search on the copy!

Reply | Threaded
Open this post in threaded view
|

Re: Multithreading Question

Jens Miltner
In reply to this post by Michael J. Sviridov

Am 21.10.2005 um 11:58 schrieb Michael J. Sviridov:

> Using sqlite 3.2.7 in a multi-threaded C++ application:
>
> I've got two thread's (with unique db handles), each thread does the
> following:
>
> BEGIN EXCLUSIVE TRANSACTION;
>
> (60,000 INSERT OR REPLACE statements into the same table)
>
> COMMIT TRANSACTION;
>
> This works fine, as expected, one thread acquires the lock and the  
> other
> thread wait's for it to be released in a SQLITE_BUSY loop.
>
> My question: Is the same thing possible/safe with a DEFERRED or  
> IMMEDIATE
> transaction?
>
> When I use an EXCLUSIVE transaction all is well, but if I try to use a
> DEFERRED or IMMEDIATE transaction I randomly get SQLITE_ERROR from
> sqlite3_step() on one or two of the INSERT OR REPLACE statements.  
> My indexes
> are also sometimes corrupted after this. Am I missing something?

We're using sqlite in a heavily threaded app as well and we haven't  
yet seen any such problem.
Which version of sqlite are you using (there was one version that had  
a thread-safety problem)?
Are you sure you built with THREADSAFE=1 ?

</jum>


Reply | Threaded
Open this post in threaded view
|

Re: Re: Multithreading Question

John Stanton-3
In reply to this post by Chris Schirlinger
>Well, I guess I was not clear in my explanations.
>I'm already doing my own locking, that's the problem!
>But I'm not sure how to improve it.

  ...

>At which stage the data will be saved it and.....
>
>that will froze the application because the search/select already get the
>database lock.

If you were already doing your own locking then you could never get an
SQLITE_BUSY state.  One way or another application threads will have to
block to permit access to a single SQLITE database.  You can design it
into your program using some form of synchronisation or leave it up to
Sqlite to figure out.

If you are concerned about one thread being a hog, maybe you can make
your DB accesses more granular with shorter transactions, releasing
control between them.