My use of SQLite is within a Apache module and Axis2/C web services. Every request invokes a unique call to sqlite3_open_v2, most of the time there are multiple calls to sqlite3_open_v2 in one request. For example the module part, which sends configuration to the web servers calls sqlite3_open_v2 as read only to initialize the web service, it closes the connection prior to the web service being called. Next the web service will open up the database in either read/write or read, depending on what it is doing.
I am using my first transaction, which I think it working fine, except I am getting some strange behavior with a call that is made immediately after the transaction. (This client is a test harness designed exclusively to test web services, no other clients are running and only in instance of the Apache process is running. I am able to change the order quickly and easily, and this order is not indicative to the order things will normally be called in.) 1: Client calls the web server method setDefaultIsInSlideShow () as to have it return the current state, aka read only. 2: The client calls the method enableSlideShow() which creates and commits the transaction all in one call, code to follow. 3: The client calls setDefaultIsInSlideShow() again, this time to set the value. It is this time the sqlite3_step() returns SQLITE_BUSY, EVERY time. When I move #3 into the #1 spot, it works fine. What is really strange is even when I removed the BEGIN TRANSACTION/COMMIT TRANSACTION, I continued to get the error when #3 is #3. So at this point I really don't know what the issue might be. Oh, another strange fact, the two databases are DIFFERENT files. The transaction happens in the event database, the other call acts on the system database. Since I am totally stumped, I am posting the code as complete as I can make it: It is broken up into a number of different functions in my code, but I will try to consolidate it as much as possible while not leaving out details: /********** #2: enableSlideShow *********/ #define SQL_SET_ISINSLIDESHOW_CUSTOMER \ "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId = ?1" #define SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER "UPDATE Customer SET IsInSlideShow = ?2 WHERE CustomerId <> ?1" enableSlideShow() { // Make note this is a different database file then the call below int rc = sqlite3_open_v2(pCfg->pszEventDB, &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, zVfs); rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); rc = UpdateSlideShowTable(env, db, SQL_CMD(SQL_SET_ISINSLIDESHOW_NOT_CUSTOMER), nCustomerId, AXIS2_FALSE); if( rc == SQLITE_OK) rc = UpdateSlideShowTable(env, db, SQL_CMD(SQL_SET_ISINSLIDESHOW_CUSTOMER), nCustomerId, AXIS2_TRUE); if( rc == SQLITE_OK) rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL, NULL); else rc = sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL, NULL); sqlite3_close(db); } static int UpdateSlideShowTable(const axutil_env_t *env, sqlite3 *db, const char *zSql, int nByte, int nCustomerId, axis2_bool_t bEnable) { adb_imageDTO_t* pImageDTO = NULL; sqlite3_stmt *stmt = NULL; const char* pzTail = NULL; int rc = sqlite3_prepare_v2(db, zSql, nByte, &stmt, &pzTail); if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, nCustomerId); if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 2, bEnable == AXIS2_FALSE ? 0 : 1); if( rc == SQLITE_OK) rc = sqlite3_step(stmt); sqlite3_finalize(stmt); if(rc == SQLITE_DONE) return SQLITE_OK; return rc; } /********** #3: setDefaultIsInSlideShow() that is a reader/writer *********/ #define SQL_INSERT_DEFAULTISINSLIDESHOW \ "UPDATE DBLookup SET ItemValue = ?1 WHERE Category = 'SystemSettings' AND ItemName = 'DefaultIsInSlideShow'" setDefaultIsInSlideShow() { // Make know that this is a different database file then the call above int rc = sqlite3_open_v2(pCfg->pszSystemDBFile, &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, NULL); sqlite3_stmt *stmt = NULL; const char* pzTail = NULL; rc = sqlite3_prepare_v2(db, SQL_CMD(SQL_INSERT_DEFAULTISINSLIDESHOW), &stmt, &pzTail); if( rc == SQLITE_OK) rc = sqlite3_bind_int( stmt, 1, bValue == AXIS2_FALSE ? 0 : 1); if( rc == SQLITE_OK) rc = sqlite3_step(stmt); // *** This returns SQLITE_BUSY every time it is called AFTER the transaction above, whether or not the transaction statements are present. sqlite3_finalize(stmt); sqlite3_close(db); } _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users