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