Re: [sqlite] SQLITE transactions failing with multiple threads

[Simon Slavin]

On 30 Nov 2010, at 8:11pm, cricketfan wrote:

> Drake, I am using SQLITE in threadsafe mode. Transaction inside another
> transaction isnt that equivalent of nested transactions? Should that be
> allowed? I have no problem opening another handle but just trying to
> understand the intricacies, thanks.

The database handle is not just a pointer to the file on disk, it's used to 
store some details and state of the current operation.  The way you're using a 
single handle tries to cram the state of two different operations in it at 
once.  It's not going to work.  If you have two different threads that might 
access the database at the same time, you need two different handles.

You could get rid of the problem by implementing some sort of semaphore system 
between your threads to prevent them using the database at the same time.  But 
that would defeat the point of using two threads in the first place.  So it's 
probably best to let SQLite handle the semaphoring for you, by using two 
different handles.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE transactions failing with multiple threads

[cricketfan]

Drake,
 It is in serialized mode. Opening another handle did resolve the
issue. I am doing inserts in one thread and then if necessary update the
same using another thread. 


Drake Wilson-3 wrote:
> 
> Quoth cricketfan <srtedul...@yahoo.co.in>, on 2010-11-30 12:11:52 -0800:
>> Drake, I am using SQLITE in threadsafe mode. Transaction inside another
>> transaction isnt that equivalent of nested transactions? Should that be
>> allowed?
> 
> SQLite has named savepoints, but not nested BEGIN transactions.  It's
> hard to tell what exactly you're doing from the description, such as
> why you're doing these updates with two threads to start with, so it's
> hard to give good advice.  Perhaps you could show some example code?
> 
> Which threading mode do you mean?  Serialized or multithreaded?
> 
>---> Drake Wilson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/SQLITE-transactions-failing-with-multiple-threads-tp30340806p30343885.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE transactions failing with multiple threads

[Drake Wilson]

Quoth cricketfan , on 2010-11-30 12:11:52 -0800:
> Drake, I am using SQLITE in threadsafe mode. Transaction inside another
> transaction isnt that equivalent of nested transactions? Should that be
> allowed?

SQLite has named savepoints, but not nested BEGIN transactions.  It's
hard to tell what exactly you're doing from the description, such as
why you're doing these updates with two threads to start with, so it's
hard to give good advice.  Perhaps you could show some example code?

Which threading mode do you mean?  Serialized or multithreaded?

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE transactions failing with multiple threads

[cricketfan]

Drake, I am using SQLITE in threadsafe mode. Transaction inside another
transaction isnt that equivalent of nested transactions? Should that be
allowed? I have no problem opening another handle but just trying to
understand the intricacies, thanks.


Drake Wilson-3 wrote:
> 
> Quoth cricketfan <srtedul...@yahoo.co.in>, on 2010-11-30 07:49:36 -0800:
>> Also not that both threads are
>> using the same handle passed by main.
> 
> No, don't do that.  Using the same handle in two threads concurrently
> can break depending on the SQLite threading mode, and will gain you no
> parallelism in the modes where it works.  Aside from that, transaction
> state is bound to a handle; you're starting a transaction and then
> trying to start another one inside it.
> 
> Open two handles instead.
> 

-- 
View this message in context: 
http://old.nabble.com/SQLITE-transactions-failing-with-multiple-threads-tp30340806p30343167.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE transactions failing with multiple threads

[Simon Slavin]

On 30 Nov 2010, at 3:49pm, cricketfan wrote:

>I have 2 threads in my program, 1st thread is doing inserts into a 
> table and 2nd thread is trying to update the already inserted columns.
> 1. I have bundled the 1000 inserts per transaction in 1st thread.
> 2. When I try to start a transaction in the 2nd thread for my updates (when
> the 1st thread is running simultaneously) I am getting an error(error code 1
> - SQL error or missing database).
> If I start the begin transaction in 2nd thread after the 1st thread finishes
> it works normally. Is this normal behavior? Also not that both threads are
> using the same handle passed by main.

I think you identified the problem.  Try having separate handles for each 
thread.  Either have each thread open its own connection or have your main 
thread open two connections, one for the INSERTs, one for the UPDATEs.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE transactions failing with multiple threads

[Drake Wilson]

Quoth cricketfan , on 2010-11-30 07:49:36 -0800:
> Also not that both threads are
> using the same handle passed by main.

No, don't do that.  Using the same handle in two threads concurrently
can break depending on the SQLite threading mode, and will gain you no
parallelism in the modes where it works.  Aside from that, transaction
state is bound to a handle; you're starting a transaction and then
trying to start another one inside it.

Open two handles instead.

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE transactions failing with multiple threads

[cricketfan]

Hello,
I have 2 threads in my program, 1st thread is doing inserts into a 
table and 2nd thread is trying to update the already inserted columns.
1. I have bundled the 1000 inserts per transaction in 1st thread.
2. When I try to start a transaction in the 2nd thread for my updates (when
the 1st thread is running simultaneously) I am getting an error(error code 1
- SQL error or missing database).
If I start the begin transaction in 2nd thread after the 1st thread finishes
it works normally. Is this normal behavior? Also not that both threads are
using the same handle passed by main.
-- 
View this message in context: 
http://old.nabble.com/SQLITE-transactions-failing-with-multiple-threads-tp30340806p30340806.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users