> 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
<pontus_bergs...@yahoo.se> 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 <paiva...@gmail.com>
> Till: Pontus Bergsten <pontus_bergs...@yahoo.se>; General Discussion of
> SQLite Database <sqlite-users@sqlite.org>
> 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
> <pontus_bergs...@yahoo.se> 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
