concurrency in innodb

i have a code like this

reserve.php

$r=mysql_query("select count(*) from ticket");
$rec=mysql_fetch_array($r);
if ($rec[0]==0)
{
insert into ticket values .....
}

i have 1 ticket only. two users request reserve.php.

"a" user request 开发者_Go百科reserve.php and available ticket is 0 . but before insert, for "b" user available ticket is 0 yet. so two users reserve ticket.

table is Innodb. how to prevent this? transaction , lock table or what?

In these situations I usually just use an UPDATE statement and check how many records were affected (mysql_affected_rows) by the update.

 UPDATE tickets SET ticket_count=ticket_count-1 WHERE ticket_id=123 AND ticket_count>0

If someone else decremented the counter first, then no update occurs and you don't give the user a ticket. Autocommit should be enabled so the update is a self-contained "transaction".

Alternatively, you can change your SELECT to be SELECT ... FOR UPDATE http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

I would do it in a single transaction, without a roundtrip back to the application level - both SELECT count() .... and INSERT INTO being sent in a single command from PHP and embedded into a TRANSACTION with EXCLUSIVE LOCK