Sqlite3/PDO write lock issue

cesarcesar

ahhhhh.... Man my head hurts with all this table banging.

I'm having an issue with Sqlite not allowing me to write (UPDATE) to a table that i have just done a SELECT query on because it is "Locked" in a read only state. I have found a few articles on the web about this, but the suggested fix's seem not to work in my case. Any help is so much appreciated. Here's my code. I have commented as much as possible. I have also omitted some error checking code to trim this post.

You will see at the last db_query() is where its failing. My quetions is... How do i get the UPDATE query to work on table that was just SELECT queried? Thanks much for the help.

/* connect to the database (works) */
$dbHandle = new PDO('sqlite:'.$CFG->baseroot.'/db/mmt.db3');

function db_query($query) {
	global $dbHandle;

    /* load the query (works) */
$handle = $dbHandle->query($query);

/* check to see if Fetch is necessary (works) */ 
if (substr($query,0,6) <> 'UPDATE') {

	/* do the fetch (works) */	
	$qr = $handle->fetch();

}

    /* fix that releases the lock (doesn't work) */
$handle=null;

return $qr;
}

/* run the select query (works) */
$user = db_query("SELECT first_name FROM user WHERE id = '5'");

/* do the if (works) */
if ($user['name'] == 'John') {

/* run the update. (this does not work and throws an error) */
db_query("UPDATE user SET last_name = 'Doe' WHERE first_name = 'John'");
}

laserlight

Perhaps instead of:

$handle=null;

you could try:

$handle->closeCursor();

That said, I suggest that you use PDO directly or go for a full blown wrapper instead of a simplistic db_query() function that makes the connection handle global and does not accomodate prepared statements.

cesarcesar

$handle->closeCursor();

Tried this without success.

I suggest that you use PDO directly or go for a full blown wrapper instead of a simplistic db_query()

Do you have an example i can work off of? thanks.

katsurace

i think its not a good idea to make changes when db is in read only state, it directly breaks the law of serializability also called conflict serializability. as far as i know no data base allows such kind of transaction.. in ur case u may avoid this condition by this query :

db_query("UPDATE user SET last_name = 'Doe' WHERE first_name = 'John' AND id = '5'");

best of luck

laserlight

cesarcesar wrote:
Do you have an example i can work off of?

A rough equivalent would be something like this:

/* connect to the database (works) */
$dbHandle = new PDO('sqlite:' . $CFG->baseroot . '/db/mmt.db3');
$dbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbHandle->prepare('SELECT first_name FROM user WHERE id = :id');
$stmt->bindParam(':id', 5);
$stmt->execute();

$user = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
if ($user['name'] == 'John') {
    $stmt = $dbHandle->prepare('UPDATE user SET last_name = :lastname WHERE first_name = :firstname');
    $stmt->bindParam(':lastname', 'Doe');
    $stmt->bindParam(':firstname', 'John');
    $stmt->execute();
}

The prepared statements are not strictly necessary, but you probably won't want to fix those values anyway. Incidentally, note that I changed it such that an exception will be thrown on a database error; this may help you diagnose your current problem if it still occurs.

However, if you are only retrieving in order to update, then you might as well just update as in katsurace's example.

katsurace wrote:
i think its not a good idea to make changes when db is in read only state, it directly breaks the law of serializability also called conflict serializability.

hmm... but shouldn't the database be no longer in a read only state once the previous statement has finished execution?