MySQL table lock, or other possible solutions?

Question

I have a security problem. I think to lock a table would be a solution, only I don't know how to properly do it in MySQL, PHP, and don't have enough time to search Google and documentations anymore.

So, the task:
Make a database and write a php code to randomly get lottery-tickets.
I have a table, that contains only a few row. (id, name, quantity)

So for example:

1 - no win - 20000
2 - Hello Kitty bag - 200
3 - a very nice pen - 50 etc.

I wrote the php, so the logic's the following:
1) Get the amount of evey tickets from the database, and create a PHP array that contains the intervals, like:
no win: 20000,
Hello Kitty bag: 20200,
a very nice pen: 20250.
2)Generate a random number from 1 to max, so I know what ticket the user got.
3)Update the database: subtract 1 from the proper row.

Now, this works great, however the problem:
What if I have a very large amount of users, and two or three of them clicks at the same time, random the same number, (lets assume it's hello kitty) but only have 1 of that item?
All the three of them subtract from the database, not stopping at 0. (In this example, we would have -2 hello kitty bag)

Huge issue, at least for me.
So in summary, my question is:
1)How can I lock the table from the selection, until I am ready to subtract? Is it a good solution?
2)Can I make it one query, or MySQL can't handle that?
3)Other solutions?

I appreciate every single answer, really!
Thanks in advance, also sorry for the long post, but wanted to keep things straight.
[ SOLVED ]
I used a stored procedure, here are the details. I had some luck to have my server version above 5.x, since as I read the MyISAM system supports stored procedures only above this version. I will write some code here hoping someone'll find it useful in the future:

delimiter //
CREATE PROCEDURE `name`(IN a_parameter INT(20))
  DECLARE some_text VARCHAR(50) DEFAULT '';
  #now using all this stuff
  SELECT `name` INTO some_text FROM `users_table` WHERE `table_id` = a_parameter;
  SELECT some_text
END//
delimiter ;

Radu M. · Accepted Answer

You need to use a transaction (documentation here), and a table lock (read about the interaction between table lock and transactions here).

To do it in one query you can build a stored procedure (documentation here). I will go with a procedure.

If you are using PHP PDO you can read all about in in the manual (here, most important PDO:: beginTransaction and PDO:: commit). If you are still using mysql extendion you need to do it explicit like this:

mysql_query('START TRANSACTION');
// Because you cannot nest lock's and transactions, this is a workaround to lock the table
mysql_query('SELECT * FROM foo FOR UPDATE'); // Lock issued, the important part is "FOR UPDATE"
//[ your query's here ]

// if something goes wront, revert the changes
mysql_query('ROLLBACK');
// at the end commit the changes
mysql_query('COMMIT');

MySQL table lock, or other possible solutions?

Answers (2)

Related Questions