Greetings,
I'm developing a tech support log using php and MySQL. Users will
add, delete and modify records. In fact, it's fully implemented
except for concurrency control. I'm trying to use get_lock and
release_lock to implement record-level locking, but when I go to
release the lock, the connection id doesn't match the id used to get
the lock, and so the release fails and the record stays, in effect,
locked.
When a user loads the edit page for a record, get_lock is called with
a unique id before the record is read. When the user saves changes,
the edit page is reloaded, and the record is modified. The lock is
released only after the user loads some other page.
It often works, but sometimes I see this in the log:
[25/Jul/2007:11:33:39] 192.168.2.102 InitSQL: new connection: pdo
client id = 279 // load the edit page, and ...
[25/Jul/2007:11:33:39] 192.168.2.102 Lock iss_23 gotten using
connection id 279 // lock the record.
[25/Jul/2007:11:33:42] 192.168.2.102 InitSQL: new connection: pdo
client id = 288 // load some other page, but get a different
connection id, and so ...
[25/Jul/2007:11:33:42] 192.168.2.102 Lock iss_23 was not gotten using
current connection id (288), not released // fail to
unlock the record.
Since I'm requesting persistent connections, why don't I get the same
connection each time? Or, to ask it another way: I notice that the
number of threads slowly increases as I continue to access records --
why would this happen if I'm using persistent connections? I assumed
that the first time I did 'new PDO' I'd create a connection, and each
time after that I'd get the same connection. At least that's how I
read the documentation.
Can anyone point me in the right direction? Thanks.
The gory details:
At the beginning of every page is a call to a function with this code:
$gSQL = new
PDO('mysql:unix_socket=/tmp/mysql.sock;dbname=operations',
$_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'],
array(PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE =>
PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 86400));
// get connection id
$stmt = $sql->query('show processlist');
$therow = $stmt->fetch();
$pdoclientid = $therow['Id'];
elog('InitSQL: new connection: pdo client id = ' . $pdoclientid);
In the edit page I call to a function that does this:
// get the current connection id
$stmt = $gSQL->query('select connection_id()');
$therow = $stmt->fetch();
$connectionid = $therow[0];
// get the lock
$thelock = 'iss_' . $issueid;
foreach ($gSQL->query("select get_lock('" . $thelock . "', " . 1 .
")") as $row) {
// check the result
$result = $row[0];
switch ($result) {
case 1:
eLog("Lock " . $thelock . ' gotten using connection
id ' . $connectionid);
return true;
break;
case 0:
eLog("Getting lock " . $thelock . ' timed out');
return false;
break;
case NULL:
eLog("Error getting lock " . $thelock);
return false;
break;
default:
return false;
break;
}
}
On all other pages, I try to release the lock with this code:
// get the current connection id
$stmt = $gSQL->query('select connection_id()');
$therow = $stmt->fetch();
$connectionid = $therow[0];
// try to release the lock
foreach ($gSQL->query("select release_lock('" . $thelock . "')") as $row) {
$result = $row[0];
switch ($result) {
case 1:
eLog("Lock " . $thelock . ' released');
break;
case 0:
eLog("Lock " . $thelock . ' was not gotten
using current connection id (' . $connectionid . '), not released');
break;
case NULL:
eLog("Lock " . $thelock . ' does not exist,
not released');
break;
default:
break;
}
}
--
Charles Whitaker
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
