Re: [sqlite] Understanding TRANSACTION

[Sam Carleton]

I figured it out, I was NOT calling sqlite3_finalize(stmt) in the
initial call to setDefaultIsInSlideShow() which was read only, so the
table still had a read lock on it, so the read/write always failed.

On Sun, Mar 7, 2010 at 3:36 PM, Sam Carleton  wrote:
> 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, ,
> 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, , );
>        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, ,
> 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),
> , );
>        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 

[sqlite] Understanding TRANSACTION

[Sam Carleton]

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, ,
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, , );
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, ,
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),
, );
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