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
