Table Lock / Unlock Syntax

[chestertb]

Hi All,

I just want to make sure I have the correct syntax here.

The following snippet checks the accounts and makes sure that ledgers exists for the current month. If not, it will create them.

The idea is that the first transaction in any month will trigger the creation of the ledgers, so to do that, I need to read lock the ledger table so that any attempt to read the ledger table will be blocked until the function is complete.

Like this...
$accmth = $dx[0]."-".$dx[1];
$qrl = mysql_query("LOCK TABLE ledger READ");
$qrm = "SELECT * FROM ledger WHERE PLmonth = '$accmth'";
$rsm = mysql_query($qrm, $link);
$nrm = mysql_num_rows($rsm);
if($nrm==0)
{
//make the new ledgers here
}
$qrl = mysql_query("UNLOCK TABLES");

I know this is simple, but because this is a critical function, and it might not return errors even though it might not do exactly what I want, I thought I'd ask people who know more than me.

Thanks
CTB

[chazzy]

my only comment would be why lock the table before making the new ledger? it seems more like you'd want to lock the table once you've determined that you need to.

also, you can use a count(*) query to determine this, rather than selecting everything and using php's count function like that.

[chestertb]

thanks chazzy.
why? because you dont want the possibility of two ledgers being created, no matter how small the probability. what can not be allowed to happen is that the first client checks whether the month's ledgers exist and in between finding they don't and creating them, a second client comes along and does the same.

and i've never used count for this... ah well... back to the documentation i go.
thanks
CTB

[chazzy]

no what i mean is something like this, for each client.


$sql = "SELECT COUNT(*) AS cnt FROM ledger WHERE PLmonth = '$accmth'";
$result = mysql_query($sql) or die(mysql_error());
$counter = mysql_fetch_array($result);
if($counter['cnt'] == 0){
$qrl = mysql_query("LOCK TABLE ledger READ");
....
}

this way, unless i'm mistaken, you don't want to put the read lock on when the month isn't different.

[mattyblah]

This is what constraints are for. Make PLmonth a unique key so you can't have 2 of them. You should probably never explicitly lock the tables. what if the script times out, or your php script goes crazy and stops executing, or someone else needs access to the ledger table?