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 <paiva...@gmail.com>
Till: Pontus Bergsten <pontus_bergs...@yahoo.se>; General Discussion of SQLite
Database <sqlite-users@sqlite.org>
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
<pontus_bergs...@yahoo.se> 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
> <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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
