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
