Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Jay A. Kreibich]

On Thu, Jun 21, 2012 at 09:53:57AM +0100, Pontus Bergsten scratched on the wall:

> It seems that it is very important that every statement evaluation is
> concluded with a sqlite3_reset() (or sqlite3_finalize() if the statement
> is not going to be used anymore). 

  That's true, and as others have pointed out, it is fairly well
  documented.

> "You should be in the habit of calling sqlite3_reset() on each query as
> soon as that query finishes. Otherwise the query *might* leave a
> read-lock on the database file and thus prevent subsequent write
> operations for working correctly."
> 
> I suppose that the "*might* locking part" should be explicitly stated
> in the SQLite API documentation for step/reset.

  I believe the documentation is purposely vague about locking.

  The documentation is clear that you should call _reset() or
  _finalize() reasonably soon after you're done with a statement
  execution.

  As for the locking issue, I had a private conversation with R. Hipp
  about this when working on "Using SQLite"... are locks released
  when _step() returns SQLITE_DONE, or are they released when
  _reset()/_finalize() is called?  He said it varied, both statement
  to statement, as well as version to version.  The documentation made
  it clear which APIs a well-behaved program had to use; the rest was
  just internals (as most of locking is).  How things were handled
  changed from time to time to get better performance, reduce
  deadlocks, etc.

  Mind you, this was several years ago, back in the late 2.6 days.  It
  was before WAL.  I have no idea if locking is now more consistent (or
  less).  I'm sure WAL mixed things up quite a bit.
  
  Regardless, I think the general principal still stands... Locking is
  generally considered an internal function of SQLite that the vast
  majority of programmers never directly deals with.  We have to deal
  with some of the ramifications, but not the process itself.  Allowing
  the SQLite devs to alter the locking scheme depending on journal modes,
  platforms, code-refactoring, or whatever, allows the SQLite dev team
  the freedom to change internal details, and things will work just
  fine-- as long as an application follows the well documented API
  sequence.  
  
  In short, you should never be deciding to call or not call _reset()
  or _finalize() because of locking semantics.  If your program can call
  one of those APIs and maintain correct flow, it should be calling them.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pontus Bergsten]

Aaah...

I only read the documentation in sqlite3.h.

Thanks for the tip.


Pontus




 Från: Simon Slavin 
Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
Skickat: torsdag, 21 juni 2012 13:46
Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
 

On 21 Jun 2012, at 9:53am, Pontus Bergsten  wrote:

> were it was stated that
> 
> "You should be in the habit of calling sqlite3_reset() on each query as soon 
> as that query finishes. Otherwise the query *might* leave a read-lock on the 
> database file and thus prevent subsequent write operations for working 
> correctly."
> 
> I suppose that the "*might* locking part" should be explicitly stated in the 
> SQLite API documentation for step/reset.

The documentation for the use of statements has it:

<http://www.sqlite.org/c3ref/stmt.html>

It doesn't tell you what will go wrong if you don't use _finalize() but it does 
tell you when to use _reset() and that you must use _finalize().

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

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Simon Slavin]

On 21 Jun 2012, at 9:53am, Pontus Bergsten  wrote:

> were it was stated that
> 
> "You should be in the habit of calling sqlite3_reset() on each query as soon 
> as that query finishes. Otherwise the query *might* leave a read-lock on the 
> database file and thus prevent subsequent write operations for working 
> correctly."
> 
> I suppose that the "*might* locking part" should be explicitly stated in the 
> SQLite API documentation for step/reset.

The documentation for the use of statements has it:



It doesn't tell you what will go wrong if you don't use _finalize() but it does 
tell you when to use _reset() and that you must use _finalize().

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

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pontus Bergsten]

Found it!!!

The problem were some SQL statements in Thread1 that called sqlite3_reset() 
before evaluation using sqlite3_step().

It worked alright in the single threaded unit test, but not in the 
multi-threaded application.

It seems that it is very important that every statement evaluation is concluded 
with a sqlite3_reset() (or sqlite3_finalize() if the statement is not going to 
be used anymore). I found an old discussion

http://sqlite.1065341.n5.nabble.com/Sqlite3-reset-needed-td7927.html

were it was stated that

"You should be in the habit of calling sqlite3_reset() on each query as soon as 
that query finishes. Otherwise the query *might* leave a read-lock on the 
database file and thus prevent subsequent write operations for working 
correctly."

I suppose that the "*might* locking part" should be explicitly stated in the 
SQLite API documentation for step/reset.


However, now it works like a charm :)


Pavel, thanks for your input.

Pontus




 Från: Pavel Ivanov 
Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
Skickat: onsdag, 20 juni 2012 17:45
Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
 
It looks to me that you have corrupted memory or already closed
database at this point. AFAIK, ROLLBACK cannot return SQLITE_ERROR in
any "normal" situation (I exclude such situations as faulty disk or
some similar I/O error), normally it can return only SQLITE_BUSY.
Check your application in valgrind.

Pavel

On Wed, Jun 20, 2012 at 11:27 AM, Pontus Bergsten
 wrote:
> Let's see, the code in Thread 2 then look something like this:
>
>
> {
>    ...
>
>    copy data
>
>    request mutex
>
>    sqlite3_get_autocommit     returns 1 ( Autocommit mode enabled )
>
>    exec("ROLLBACK")   returns 1 ( SQLITE_ERROR )
>
>    exec("DETACH DATABASE Dest")   returns 1( SQLITE_ERROR )
>    release mutex
>    ...
>
> }
>
> as before copying data from "main" to "Dest" before entering the synchronized 
> section is successful, but detach fails.
>
>
> Pontus
>
>
>
> ________________
>  Från: Pavel Ivanov 
> Till: Pontus Bergsten ; General Discussion of 
> SQLite Database 
> Skickat: onsdag, 20 juni 2012 16:24
> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>
> Then something else should happen in your application besides the
> given pseudo code. Could you try to check what
> sqlite3_get_autocommit() returns and execute rollback under the mutex
> but before doing detach?
>
> Pavel
>
>
> On Wed, Jun 20, 2012 at 10:11 AM, Pontus Bergsten
>  wrote:
>>
>>
>>> I guess you meant Thread 1 in the last sentence. And how do you know
>>> that? Do you check the return code?
>>
>> You are absolutely right, I meant Thread 1. We are checking the return value 
>> of the commit statement which indicate success ( 0 ).
>>
>> Pontus
>>
>>
>>>Pavel
>>>
>>>
>>>On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
>>  wrote:
>>> Thanks for the response,
>>>
>>>
>>> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
>>> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
>>> once in the beginning of the transfer loop. In each iteration the 
>>> statements are bounded with new time interval parameters and reset, before 
>>> the query is executed.
>>>
>>>
>>> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
>>> commit transactions. As we know the commit statement in Thread 2 does not 
>>> fail.
>>>
>>> Any ideas?
>>>
>>> Pontus
>>>
>>>
>>>
>>> 
>>>  Från: Pavel Ivanov 
>>> Till: Pontus Bergsten ; General Discussion of 
>>> SQLite Database 
>>> Skickat: onsdag, 20 juni 2012 14:27
>>> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>>>
>>> Probably the following scenario is possible in your situation:
>>> - thread 1 locks transaction mutex
>>> - thread 1 inserts buffered data
>>> - thread 2 starts transferring data to Dest database
>>> - thread 1 tries to commit, commit fails (do you check return code
>>> from it?), transaction is left open
>>> - thread 1 unlocks mutex
>>> - thread 2 locks mutex
>>> - thread 2 tries to detach and fails
>>>
>>> So do you check

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pavel Ivanov]

It looks to me that you have corrupted memory or already closed
database at this point. AFAIK, ROLLBACK cannot return SQLITE_ERROR in
any "normal" situation (I exclude such situations as faulty disk or
some similar I/O error), normally it can return only SQLITE_BUSY.
Check your application in valgrind.

Pavel

On Wed, Jun 20, 2012 at 11:27 AM, Pontus Bergsten
 wrote:
> Let's see, the code in Thread 2 then look something like this:
>
>
> {
>    ...
>
>    copy data
>
>    request mutex
>
>    sqlite3_get_autocommit     returns 1 ( Autocommit mode enabled )
>
>    exec("ROLLBACK")   returns 1 ( SQLITE_ERROR )
>
>    exec("DETACH DATABASE Dest")   returns 1( SQLITE_ERROR )
>    release mutex
>    ...
>
> }
>
> as before copying data from "main" to "Dest" before entering the synchronized 
> section is successful, but detach fails.
>
>
> Pontus
>
>
>
> ____
>  Från: Pavel Ivanov 
> Till: Pontus Bergsten ; General Discussion of 
> SQLite Database 
> Skickat: onsdag, 20 juni 2012 16:24
> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>
> Then something else should happen in your application besides the
> given pseudo code. Could you try to check what
> sqlite3_get_autocommit() returns and execute rollback under the mutex
> but before doing detach?
>
> Pavel
>
>
> On Wed, Jun 20, 2012 at 10:11 AM, Pontus Bergsten
>  wrote:
>>
>>
>>> I guess you meant Thread 1 in the last sentence. And how do you know
>>> that? Do you check the return code?
>>
>> You are absolutely right, I meant Thread 1. We are checking the return value 
>> of the commit statement which indicate success ( 0 ).
>>
>> Pontus
>>
>>
>>>Pavel
>>>
>>>
>>>On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
>>  wrote:
>>> Thanks for the response,
>>>
>>>
>>> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
>>> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
>>> once in the beginning of the transfer loop. In each iteration the 
>>> statements are bounded with new time interval parameters and reset, before 
>>> the query is executed.
>>>
>>>
>>> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
>>> commit transactions. As we know the commit statement in Thread 2 does not 
>>> fail.
>>>
>>> Any ideas?
>>>
>>> Pontus
>>>
>>>
>>>
>>> 
>>>  Från: Pavel Ivanov 
>>> Till: Pontus Bergsten ; General Discussion of 
>>> SQLite Database 
>>> Skickat: onsdag, 20 juni 2012 14:27
>>> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>>>
>>> Probably the following scenario is possible in your situation:
>>> - thread 1 locks transaction mutex
>>> - thread 1 inserts buffered data
>>> - thread 2 starts transferring data to Dest database
>>> - thread 1 tries to commit, commit fails (do you check return code
>>> from it?), transaction is left open
>>> - thread 1 unlocks mutex
>>> - thread 2 locks mutex
>>> - thread 2 tries to detach and fails
>>>
>>> So do you check return code from commit? And do you really use only
>>> INSERT INTO ... SELECT in thread 2 and no other SELECT queries?
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
>>>  wrote:
>>>> In our application we have two threads implementing a signal logger 
>>>> functionality, see pseudo code below.
>>>> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
>>>> after a pre-determined number of reads.
>>>>
>>>>  1   while( true )
>>>>  2  {
>>>>  3 // Buffer data, blocks until new data is available
>>>>  4 buffer.append( readData() )
>>>>  5 ++numberOfReads;
>>>>  6
>>>>
>>>>  7 if ( numberOfReads == maxReads )
>>>>  8 {
>>>>  9    globalTransactionMutex.request()
>>>> 10
>>>> 11    begin transaction
>>>> 12    insert bufferered data to "main" in GlobalDbConnection
>>>>
>>>> 13    commit transaction
>>>> 14
>>

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pontus Bergsten]

Let's see, the code in Thread 2 then look something like this:


{
   ...

   copy data

   request mutex

   sqlite3_get_autocommit     returns 1 ( Autocommit mode enabled )

   exec("ROLLBACK")   returns 1 ( SQLITE_ERROR ) 

   exec("DETACH DATABASE Dest")   returns 1( SQLITE_ERROR )
   release mutex
   ...

}

as before copying data from "main" to "Dest" before entering the synchronized 
section is successful, but detach fails.


Pontus




 Från: Pavel Ivanov 
Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
Skickat: onsdag, 20 juni 2012 16:24
Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
 
Then something else should happen in your application besides the
given pseudo code. Could you try to check what
sqlite3_get_autocommit() returns and execute rollback under the mutex
but before doing detach?

Pavel


On Wed, Jun 20, 2012 at 10:11 AM, Pontus Bergsten
 wrote:
>
>
>> I guess you meant Thread 1 in the last sentence. And how do you know
>> that? Do you check the return code?
>
> You are absolutely right, I meant Thread 1. We are checking the return value 
> of the commit statement which indicate success ( 0 ).
>
> Pontus
>
>
>>Pavel
>>
>>
>>On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
>  wrote:
>> Thanks for the response,
>>
>>
>> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
>> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
>> once in the beginning of the transfer loop. In each iteration the statements 
>> are bounded with new time interval parameters and reset, before the query is 
>> executed.
>>
>>
>> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
>> commit transactions. As we know the commit statement in Thread 2 does not 
>> fail.
>>
>> Any ideas?
>>
>> Pontus
>>
>>
>>
>> 
>>  Från: Pavel Ivanov 
>> Till: Pontus Bergsten ; General Discussion of 
>> SQLite Database 
>> Skickat: onsdag, 20 juni 2012 14:27
>> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>>
>> Probably the following scenario is possible in your situation:
>> - thread 1 locks transaction mutex
>> - thread 1 inserts buffered data
>> - thread 2 starts transferring data to Dest database
>> - thread 1 tries to commit, commit fails (do you check return code
>> from it?), transaction is left open
>> - thread 1 unlocks mutex
>> - thread 2 locks mutex
>> - thread 2 tries to detach and fails
>>
>> So do you check return code from commit? And do you really use only
>> INSERT INTO ... SELECT in thread 2 and no other SELECT queries?
>>
>>
>> Pavel
>>
>>
>> On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
>>  wrote:
>>> In our application we have two threads implementing a signal logger 
>>> functionality, see pseudo code below.
>>> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
>>> after a pre-determined number of reads.
>>>
>>>  1   while( true )
>>>  2  {
>>>  3 // Buffer data, blocks until new data is available
>>>  4 buffer.append( readData() )
>>>  5 ++numberOfReads;
>>>  6
>>>
>>>  7 if ( numberOfReads == maxReads )
>>>  8 {
>>>  9    globalTransactionMutex.request()
>>> 10
>>> 11    begin transaction
>>> 12    insert bufferered data to "main" in GlobalDbConnection
>>>
>>> 13    commit transaction
>>> 14
>>>
>>> 15    globalTransactionMutex.release()
>>> 16    numberOfReads = 0;
>>> 17 }
>>>
>>> 18   }
>>>
>>>
>>> Thread 2: Upon user action, transfer data within a specified time window 
>>> from the in-memory database to a database file
>>>
>>> 19  while( true )
>>>
>>> 20  {
>>> 21 waitForUserAction()
>>> 22
>>>
>>> 23 DestDbConnection = Create destination database file Dest.db
>>>
>>> 24 close DestDbConnection
>>> 25
>>>
>>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>>
>>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>>> INSERT INTO Dest SELECT FROM main
>>>
>>> 28
>>

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pavel Ivanov]

Then something else should happen in your application besides the
given pseudo code. Could you try to check what
sqlite3_get_autocommit() returns and execute rollback under the mutex
but before doing detach?

Pavel


On Wed, Jun 20, 2012 at 10:11 AM, Pontus Bergsten
 wrote:
>
>
>> I guess you meant Thread 1 in the last sentence. And how do you know
>> that? Do you check the return code?
>
> You are absolutely right, I meant Thread 1. We are checking the return value 
> of the commit statement which indicate success ( 0 ).
>
> Pontus
>
>
>>Pavel
>>
>>
>>On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
>  wrote:
>> Thanks for the response,
>>
>>
>> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
>> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
>> once in the beginning of the transfer loop. In each iteration the statements 
>> are bounded with new time interval parameters and reset, before the query is 
>> executed.
>>
>>
>> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
>> commit transactions. As we know the commit statement in Thread 2 does not 
>> fail.
>>
>> Any ideas?
>>
>> Pontus
>>
>>
>>
>> ____
>>  Från: Pavel Ivanov 
>> Till: Pontus Bergsten ; General Discussion of 
>> SQLite Database 
>> Skickat: onsdag, 20 juni 2012 14:27
>> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>>
>> Probably the following scenario is possible in your situation:
>> - thread 1 locks transaction mutex
>> - thread 1 inserts buffered data
>> - thread 2 starts transferring data to Dest database
>> - thread 1 tries to commit, commit fails (do you check return code
>> from it?), transaction is left open
>> - thread 1 unlocks mutex
>> - thread 2 locks mutex
>> - thread 2 tries to detach and fails
>>
>> So do you check return code from commit? And do you really use only
>> INSERT INTO ... SELECT in thread 2 and no other SELECT queries?
>>
>>
>> Pavel
>>
>>
>> On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
>>  wrote:
>>> In our application we have two threads implementing a signal logger 
>>> functionality, see pseudo code below.
>>> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
>>> after a pre-determined number of reads.
>>>
>>>  1   while( true )
>>>  2  {
>>>  3 // Buffer data, blocks until new data is available
>>>  4 buffer.append( readData() )
>>>  5 ++numberOfReads;
>>>  6
>>>
>>>  7 if ( numberOfReads == maxReads )
>>>  8 {
>>>  9    globalTransactionMutex.request()
>>> 10
>>> 11    begin transaction
>>> 12    insert bufferered data to "main" in GlobalDbConnection
>>>
>>> 13    commit transaction
>>> 14
>>>
>>> 15    globalTransactionMutex.release()
>>> 16    numberOfReads = 0;
>>> 17 }
>>>
>>> 18   }
>>>
>>>
>>> Thread 2: Upon user action, transfer data within a specified time window 
>>> from the in-memory database to a database file
>>>
>>> 19  while( true )
>>>
>>> 20  {
>>> 21 waitForUserAction()
>>> 22
>>>
>>> 23 DestDbConnection = Create destination database file Dest.db
>>>
>>> 24 close DestDbConnection
>>> 25
>>>
>>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>>
>>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>>> INSERT INTO Dest SELECT FROM main
>>>
>>> 28
>>>
>>> 29     globalTransactionMutex.request()
>>> 30     detach "Dest" from GlobalDbConnection
>>>
>>> 31     globalTransactionMutex.release()
>>> 32  }
>>>
>>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>>> work without any hassle.
>>>
>>> The problem we're facing is that the detach statement (30) fails with error 
>>> message "SQL Logic error or missing database".
>>>
>>> The transaction is protected with a mutex, so it should not be possible to 
>>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>>> both t

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pontus Bergsten]

> I guess you meant Thread 1 in the last sentence. And how do you know
> that? Do you check the return code?

You are absolutely right, I meant Thread 1. We are checking the return value of 
the commit statement which indicate success ( 0 ).

Pontus


>Pavel
>
>
>On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
 wrote:
> Thanks for the response,
>
>
> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
> once in the beginning of the transfer loop. In each iteration the statements 
> are bounded with new time interval parameters and reset, before the query is 
> executed.
>
>
> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
> commit transactions. As we know the commit statement in Thread 2 does not 
> fail.
>
> Any ideas?
>
> Pontus
>
>
>
> 
>  Från: Pavel Ivanov 
> Till: Pontus Bergsten ; General Discussion of 
> SQLite Database 
> Skickat: onsdag, 20 juni 2012 14:27
> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>
> Probably the following scenario is possible in your situation:
> - thread 1 locks transaction mutex
> - thread 1 inserts buffered data
> - thread 2 starts transferring data to Dest database
> - thread 1 tries to commit, commit fails (do you check return code
> from it?), transaction is left open
> - thread 1 unlocks mutex
> - thread 2 locks mutex
> - thread 2 tries to detach and fails
>
> So do you check return code from commit? And do you really use only
> INSERT INTO ... SELECT in thread 2 and no other SELECT queries?
>
>
> Pavel
>
>
> On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
>  wrote:
>> In our application we have two threads implementing a signal logger 
>> functionality, see pseudo code below.
>> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
>> after a pre-determined number of reads.
>>
>>  1   while( true )
>>  2  {
>>  3 // Buffer data, blocks until new data is available
>>  4 buffer.append( readData() )
>>  5 ++numberOfReads;
>>  6
>>
>>  7 if ( numberOfReads == maxReads )
>>  8 {
>>  9    globalTransactionMutex.request()
>> 10
>> 11    begin transaction
>> 12    insert bufferered data to "main" in GlobalDbConnection
>>
>> 13    commit transaction
>> 14
>>
>> 15    globalTransactionMutex.release()
>> 16    numberOfReads = 0;
>> 17 }
>>
>> 18   }
>>
>>
>> Thread 2: Upon user action, transfer data within a specified time window 
>> from the in-memory database to a database file
>>
>> 19  while( true )
>>
>> 20  {
>> 21 waitForUserAction()
>> 22
>>
>> 23 DestDbConnection = Create destination database file Dest.db
>>
>> 24 close DestDbConnection
>> 25
>>
>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>
>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>> INSERT INTO Dest SELECT FROM main
>>
>> 28
>>
>> 29     globalTransactionMutex.request()
>> 30     detach "Dest" from GlobalDbConnection
>>
>> 31     globalTransactionMutex.release()
>> 32  }
>>
>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>> work without any hassle.
>>
>> The problem we're facing is that the detach statement (30) fails with error 
>> message "SQL Logic error or missing database".
>>
>> The transaction is protected with a mutex, so it should not be possible to 
>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>> both threads have the same priority, so we don't have any dead-lock issues.
>>
>> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
>> test. We're using SQLite v. 3.6.21 on Windows XP.
>>
>>
>> Does anyone have any idea about the source of this problem? Have we missed 
>> something regarding SQLite and multi-threading?
>>
>>
>> Regards,
>>
>> Pontus Bergsten
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pavel Ivanov]

> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
> commit transactions. As we know the commit statement in Thread 2 does not 
> fail.

I guess you meant Thread 1 in the last sentence. And how do you know
that? Do you check the return code?


Pavel


On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
 wrote:
> Thanks for the response,
>
>
> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
> once in the beginning of the transfer loop. In each iteration the statements 
> are bounded with new time interval parameters and reset, before the query is 
> executed.
>
>
> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
> commit transactions. As we know the commit statement in Thread 2 does not 
> fail.
>
> Any ideas?
>
> Pontus
>
>
>
> 
>  Från: Pavel Ivanov 
> Till: Pontus Bergsten ; General Discussion of 
> SQLite Database 
> Skickat: onsdag, 20 juni 2012 14:27
> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>
> Probably the following scenario is possible in your situation:
> - thread 1 locks transaction mutex
> - thread 1 inserts buffered data
> - thread 2 starts transferring data to Dest database
> - thread 1 tries to commit, commit fails (do you check return code
> from it?), transaction is left open
> - thread 1 unlocks mutex
> - thread 2 locks mutex
> - thread 2 tries to detach and fails
>
> So do you check return code from commit? And do you really use only
> INSERT INTO ... SELECT in thread 2 and no other SELECT queries?
>
>
> Pavel
>
>
> On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
>  wrote:
>> In our application we have two threads implementing a signal logger 
>> functionality, see pseudo code below.
>> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
>> after a pre-determined number of reads.
>>
>>  1   while( true )
>>  2  {
>>  3 // Buffer data, blocks until new data is available
>>  4 buffer.append( readData() )
>>  5 ++numberOfReads;
>>  6
>>
>>  7 if ( numberOfReads == maxReads )
>>  8 {
>>  9    globalTransactionMutex.request()
>> 10
>> 11    begin transaction
>> 12    insert bufferered data to "main" in GlobalDbConnection
>>
>> 13    commit transaction
>> 14
>>
>> 15    globalTransactionMutex.release()
>> 16    numberOfReads = 0;
>> 17 }
>>
>> 18   }
>>
>>
>> Thread 2: Upon user action, transfer data within a specified time window 
>> from the in-memory database to a database file
>>
>> 19  while( true )
>>
>> 20  {
>> 21 waitForUserAction()
>> 22
>>
>> 23 DestDbConnection = Create destination database file Dest.db
>>
>> 24 close DestDbConnection
>> 25
>>
>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>
>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>> INSERT INTO Dest SELECT FROM main
>>
>> 28
>>
>> 29     globalTransactionMutex.request()
>> 30     detach "Dest" from GlobalDbConnection
>>
>> 31     globalTransactionMutex.release()
>> 32  }
>>
>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>> work without any hassle.
>>
>> The problem we're facing is that the detach statement (30) fails with error 
>> message "SQL Logic error or missing database".
>>
>> The transaction is protected with a mutex, so it should not be possible to 
>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>> both threads have the same priority, so we don't have any dead-lock issues.
>>
>> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
>> test. We're using SQLite v. 3.6.21 on Windows XP.
>>
>>
>> Does anyone have any idea about the source of this problem? Have we missed 
>> something regarding SQLite and multi-threading?
>>
>>
>> Regards,
>>
>> Pontus Bergsten
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pontus Bergsten]

Thanks for the response,


In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
data in chunks in a loop, from "main" to "Dest". The queries are prepared once 
in the beginning of the transfer loop. In each iteration the statements are 
bounded with new time interval parameters and reset, before the query is 
executed.


Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
commit transactions. As we know the commit statement in Thread 2 does not fail.

Any ideas?

Pontus




 Från: Pavel Ivanov 
Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
Skickat: onsdag, 20 juni 2012 14:27
Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
 
Probably the following scenario is possible in your situation:
- thread 1 locks transaction mutex
- thread 1 inserts buffered data
- thread 2 starts transferring data to Dest database
- thread 1 tries to commit, commit fails (do you check return code
from it?), transaction is left open
- thread 1 unlocks mutex
- thread 2 locks mutex
- thread 2 tries to detach and fails

So do you check return code from commit? And do you really use only
INSERT INTO ... SELECT in thread 2 and no other SELECT queries?


Pavel


On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
 wrote:
> In our application we have two threads implementing a signal logger 
> functionality, see pseudo code below.
> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
> after a pre-determined number of reads.
>
>  1   while( true )
>  2  {
>  3 // Buffer data, blocks until new data is available
>  4 buffer.append( readData() )
>  5 ++numberOfReads;
>  6
>
>  7 if ( numberOfReads == maxReads )
>  8 {
>  9    globalTransactionMutex.request()
> 10
> 11    begin transaction
> 12    insert bufferered data to "main" in GlobalDbConnection
>
> 13    commit transaction
> 14
>
> 15    globalTransactionMutex.release()
> 16    numberOfReads = 0;
> 17 }
>
> 18   }
>
>
> Thread 2: Upon user action, transfer data within a specified time window from 
> the in-memory database to a database file
>
> 19  while( true )
>
> 20  {
> 21 waitForUserAction()
> 22
>
> 23 DestDbConnection = Create destination database file Dest.db
>
> 24 close DestDbConnection
> 25
>
> 26     attach Dest.db with GlobalDbConnection as "Dest"
>
> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
> INSERT INTO Dest SELECT FROM main
>
> 28
>
> 29     globalTransactionMutex.request()
> 30     detach "Dest" from GlobalDbConnection
>
> 31     globalTransactionMutex.release()
> 32  }
>
> Attaching the destination database Dest.db and transferring data (23)-(27) 
> work without any hassle.
>
> The problem we're facing is that the detach statement (30) fails with error 
> message "SQL Logic error or missing database".
>
> The transaction is protected with a mutex, so it should not be possible to 
> execute the detach statement (30) during an ongoing transaction. Moreover, 
> both threads have the same priority, so we don't have any dead-lock issues.
>
> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
> test. We're using SQLite v. 3.6.21 on Windows XP.
>
>
> Does anyone have any idea about the source of this problem? Have we missed 
> something regarding SQLite and multi-threading?
>
>
> Regards,
>
> Pontus Bergsten
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

[Pavel Ivanov]

Probably the following scenario is possible in your situation:
- thread 1 locks transaction mutex
- thread 1 inserts buffered data
- thread 2 starts transferring data to Dest database
- thread 1 tries to commit, commit fails (do you check return code
from it?), transaction is left open
- thread 1 unlocks mutex
- thread 2 locks mutex
- thread 2 tries to detach and fails

So do you check return code from commit? And do you really use only
INSERT INTO ... SELECT in thread 2 and no other SELECT queries?


Pavel


On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
 wrote:
> In our application we have two threads implementing a signal logger 
> functionality, see pseudo code below.
> Thread 1: Reads signal data, and log to a global in-memory SQLite database 
> after a pre-determined number of reads.
>
>  1   while( true )
>  2  {
>  3 // Buffer data, blocks until new data is available
>  4 buffer.append( readData() )
>  5 ++numberOfReads;
>  6
>
>  7 if ( numberOfReads == maxReads )
>  8 {
>  9    globalTransactionMutex.request()
> 10
> 11    begin transaction
> 12    insert bufferered data to "main" in GlobalDbConnection
>
> 13    commit transaction
> 14
>
> 15    globalTransactionMutex.release()
> 16    numberOfReads = 0;
> 17 }
>
> 18   }
>
>
> Thread 2: Upon user action, transfer data within a specified time window from 
> the in-memory database to a database file
>
> 19  while( true )
>
> 20  {
> 21 waitForUserAction()
> 22
>
> 23 DestDbConnection = Create destination database file Dest.db
>
> 24 close DestDbConnection
> 25
>
> 26     attach Dest.db with GlobalDbConnection as "Dest"
>
> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
> INSERT INTO Dest SELECT FROM main
>
> 28
>
> 29     globalTransactionMutex.request()
> 30     detach "Dest" from GlobalDbConnection
>
> 31     globalTransactionMutex.release()
> 32  }
>
> Attaching the destination database Dest.db and transferring data (23)-(27) 
> work without any hassle.
>
> The problem we're facing is that the detach statement (30) fails with error 
> message "SQL Logic error or missing database".
>
> The transaction is protected with a mutex, so it should not be possible to 
> execute the detach statement (30) during an ongoing transaction. Moreover, 
> both threads have the same priority, so we don't have any dead-lock issues.
>
> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
> test. We're using SQLite v. 3.6.21 on Windows XP.
>
>
> Does anyone have any idea about the source of this problem? Have we missed 
> something regarding SQLite and multi-threading?
>
>
> Regards,
>
> Pontus Bergsten
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users