pdo_sqlite; slow query; database locks

[gmac63]

All,

I have another issue which I'm stumped on. I have code that checks form input data against an sqlite3 database and inserts the data if the data is not there. Issue is that it locks the database for ~1 minute and does not insert the data.

$addYes = 1;
$app = "ddns";
$addApp = strtoupper($app);

if (isset($addYes)){
	$addApp = strtoupper($app);
	$dbc = new PDO('sqlite:/var/www/l56/l56.sq3');
	$dbc->beginTransaction();
	$sql = "select count(app) as app FROM app where app = \"$addApp\"";
	$result = $dbc->query($sql);
	$dbc->commit();
	$dbc = null;
	foreach ($result as $row){
		if (!$row['app']) {
			$dba = new PDO('sqlite:/var/www/l56/l56.sq3');
			$dba->beginTransaction();
			$sql = "insert into app values(NULL,\"$addApp\")";
			$dba->query($sql);
			$dba->commit();
			$error = "$sql";
			$dba = null;
		} else { $error = "App exists"; }
	}
}

The code works without issue if the query returns a match. Alone, the insert works, even with a delete immediately after:

$dba = new PDO('sqlite:/var/www/l56/l56.sq3');
$dba->beginTransaction();
$sql = "insert into app values(NULL,\"$addApp\")";
$dba->query($sql);
$dba->commit();
$error = $sql;

$dba = new PDO('sqlite:/var/www/l56/l56.sq3');
$dba->beginTransaction();
$sql = "delete from app where app = \"$addApp\"";
$dba->query($sql);
$dba->commit();

What am I missing? Any clues?

Thanks!

[jheitz]

Not sure if this will solve your issue but my comments are:

1) Get rid of both your transactions and commits. I don't believe the one around the select statement will not do anything useful, and the one around the insert seems a bit pointless too as you only have one INSERT (normally transactions are used to ensure several INSERT/UPDATE/DELETEs happen, not just one).

2) Maybe your "$dbc = null;" is causing problems? Try moving it to the end of your code.

[MarkR]

Do not use more than one connection object; I think your process is locking itself out resulting in deadlock.

Just use a single connection and you should not have problems. Although, SQLite does not have locking which works well for highly concurrent applications (It deadlocks too often)

Mark

[gmac63]

Actually, I solved the problem by using PDO::Statement Prepare and Execute

if (isset($_GET['add'])){
	$addApp = strtoupper($_GET['app']);
	try{
		$dbc = new PDO('sqlite:/var/www/l56/l56.sq3');
		$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$sth = $dbc->prepare("select app FROM app where app = \"$addApp\"");
		$sth->execute();
		$result = $sth->fetch(PDO::FETCH_ASSOC);
	} catch (Exception $e) {
		print "Failed: " . $e->getMessage();
	$dbc->rollback();
	}
	if ($result['app']) {
		$sql = "";
		$msg = "Application exists";
	} else {
		try{
			$dba = new PDO('sqlite:/var/www/l56/l56.sq3');
			$dba->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$sql = "insert into app values(NULL,\"$addApp\")";
			$sth = $dba->prepare($sql);
			$sth->execute();
			$msg = "Application $addApp Added";
			$dba = null;
		} catch (Expection $e) {
			print "Failed: " . $e->getMessage()."<p>";
			$dba->rollback();
		}
	}
}

Works fine now. Thanks anyhow!!