many users...check and set simultaneously

[sneakyimp]

hi:

i have the classic limited resource, many user problem.

i have some number of tickets available. i don't want to sell more tickets than i have space. and there might be a stampede for tickets.

so doing two queries
1) check to see if tickets available
2) if available, buy a ticket

might not work so well because between query 1 and 2, somebody else might have snatched up the last ticket.

how do i handle this situation?

[tomhath]

There are two ways. In the PHP code you can use a semaphore, or in the database you can use a transaction. Both are used to ensure a series of instructions are executed without another process or thread prempting the CPU and causing the problem you are wisely trying to avoid.

I suggest using a database transaction if you can, semaphores are a bit more error prone.

http://us2.php.net/manual/en/function.sem-acquire.php

http://dev.mysql.com/doc/mysql/en/COMMIT.html

[sneakyimp]

THANKS

i thought mysql transactions might be the ticket, but i am TOTALLY new to these. i am using mysql v. 3.23.58 and these are available (i think) but i'm not sure which to use.

i'm also uncertain about a couple of things...do i need to call LOCK TABLE? or is BEGIN WORK going to exclude other users?

how does this PHP look?

mysql_query("set autocommit=0;")
  or die ("couldn't set auto commit");
mysql_query("BEGIN WORK;")
  or die ("couldn't begin work");
$result = mysql_query("SELECT * FROM tbl_tickets WHERE event=1;")
  or die ("couldn't get ticket record");
$ticket_info = mysql_fetch_assoc($result);
if ($ticket_info['tickets_sold'] >= ticket_info['total_tickets']) {
  echo "sorry, that show is sold out!";
} else {
  mysql_query("UPDATE tbl_tickets SET tickets_sold=tickets_sold+1 WHERE event=1;")
    or die("couldn't update tickets sold");
  $you_have_a_ticket = true;
} // if tickets available
mysql_query("COMMIT;")
    or die("couldn't commit changes!");

is that bad that i'm calling die() if things fail? seems like my thread might die while still holding the keys to the kingdom.

[tomhath]

Just so you're warned, I don't have a lot of experience with MySQL locking...

From my reading of the manual you should use BEGIN WORK...COMMIT, not LOCK TABLE. The transaction should be rolled back automatically if the connection is ended without a COMMIT.

Make sure your database is set up to use transaction safe tables (INNODB or BD😎.

I tried to test transactions using two command tools and running the SQL statements one at a time but it didn't seem to be locking the tables/rows the way I expected, but the database I used might not have transaction safe tables.

[sneakyimp]

THANKS for your response. and i appreciate your honesty about your experience. for what it's worth, i have no experience whatsoever.

ok...will check into rollback...i only have one statement (the last one) that makes any changes. it increments tickets_sold. if that fails, no other changes have been made.

Make sure your database is set up to use transaction safe tables (INNODB or BD😎.

any idea how to be sure of this?

also, what did your test entail?

[tomhath]

Here's a link that explains it pretty well:

http://webmonkey.wired.com/webmonkey/02/11/index4a_page4.html?tw=backend

A couple of things to note in the article:

show variables ... to ensure INNODB is available

alter table ... to enable INNODB on a table

SELECT ... FOR UPDATE to avoid the problem you are trying to avoid.

I tested this by opening two MS-DOS command windows and running the mysql interactive tool in each. Try a few SQL commands alternating windows to ensure the locking works as expected (when the first window locks a table the second window should wait until the first does a COMMIT before it returns from a select:

WINDOW 1: START TRANSACTION;
WINDOW 1: select * from mytable FOR UPDATE;
    <returns rows>
WINDOW 2: START TRANSACTION;
WINDOW 2: select * from mytable FOR UPDATE;
  <doesn't return, it's blocked by WINDOW 1...>
WINDOW 1: update mytable set mycol=2 where id = 1;
WINDOW 1: COMMIT;
WINDOW 2: <displays rows with mycol=2>

I use MSSQL more than MySQL; it assumes you want the row locked unless you say otherwise. MySQL assumes no lock unless you SELECT ... FOR UPDATE.

[sneakyimp]

THANK YOU.

i will check that out.