Re: [PHP-DB] connection id mystery

[Stut]

Charles Whitaker wrote:
Using persistant connections simply means that you will be given a 
connection from a pool of open connections rather than creating a new 
one each time. There is no guarantee you'll get the same connection 
from request to request.


Right. I had assumed that I would get a connection previously initiated 
by me, and that there would therefore be only one, so I would always get 
the same connection. So much for assumptions.


I would suggest you add another field to the table you are trying to 
lock. Put an ID in there that you can pass from request to request. 
Use the locking feature to lock the table, read that value for a 
record, if it's not set to something write your ID to it then unlock 
it. If it does already contain an ID you treat it as locked. you just 
need to make sure you unlock the row when you're done (probably by 
setting that value to NULL.


If you're already using sessions I would strongly recommend using the 
session ID as the lock ID. If not you can easily generate one but 
you'll need to pass it manually from request to request.


I did as you suggest, and it seems to work fine. I surrounded the lock 
code with get_lock() and release_lock(), to make it quasi-atomic.


Thanks for the suggestion, and thanks to Charles Morris as well for his 
response.


No problem, but please include the list when replying in future.

-Stut

--
http://stut.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] connection id mystery

[Stut]

Charles Whitaker wrote:
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.


Using persistant connections simply means that you will be given a 
connection from a pool of open connections rather than creating a new 
one each time. There is no guarantee you'll get the same connection from 
request to request.



Can anyone point me in the right direction? Thanks.


I would suggest you add another field to the table you are trying to 
lock. Put an ID in there that you can pass from request to request. Use 
the locking feature to lock the table, read that value for a record, if 
it's not set to something write your ID to it then unlock it. If it does 
already contain an ID you treat it as locked. you just need to make sure 
you unlock the row when you're done (probably by setting that value to NULL.


If you're already using sessions I would strongly recommend using the 
session ID as the lock ID. If not you can easily generate one but you'll 
need to pass it manually from request to request.


That may not be as clean as a pure SQL solution, but it will work. You 
may also want to add a timestamp row to allow for a timeout on the lock.


-Stut

--
http://stut.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] connection id mystery

[Charles Whitaker]

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