Concurrent transactions in MySQL

I agree with @mmos, you should never work with LOCK TABLES in applications, except for very specific situations.

For situations like this, you need to know a bit about the bank's transaction scheme. MySQL is not my "strong", but the concept for OLTP database is universal.

In the case of MySQL, which with all due respect, but is a "patchwork," this response will be valid only if you are using the innoDB engine.

Here the lock control will be through SQL, under the record in the table you will use to control that "This coupon has already been drawn".

First , you have to worry about SET ISOLATION, it will define how your session should behave during access to a given. For this case I would recommend the: set session transaction isolation level read committed;
For more information, read the MySQL here manual. This isolation level will ensure that you will only read data that is committed / confirmed in the database.

Second , set a time-out that your transaction will wait for a lock. This is so that the update, if it competes with another, has a time limit to wait for the registry to be released. In MySQL this is done with the command set session innodb_lock_wait_timeout=10
Here I set it to 10 seconds, which I consider more than enough. If this is not enough, you can increase this time and / or you should prepare your application to handle the exception ERROR HY000: Lock wait timeout exceeded; try restarting transaction For more information, read the MySQL here manual. / p>

Third , disable autocommit so that you can have greater control over the transaction, from when it begins, and when it should end. To do this use SET autocommit = 0

Room , start a transaction with start transaction or begin work . Which of the two commands is synonymous? But the important thing here is to keep in mind that everything you change in the database will have an active lock until the transaction is finalized!

Fifth , perform the update that will save the information that the X coupon was drawn. This is the crucial part of logic and as you have not posted any example table, structure, SQL that I used will post something from my same imagination.

update cupons set sorteado=134 where sorteado is null and cupom_id = 4444 ; 

Explaining:

• The drawn column is the flag that says if the coupon has already been drawn. its content is the number of the draw (so you can relate the draw of day / time X with the coupon Y)
• The coupon_id is the key field of the coupon, in case the 4444 would be the one of the drawn coupon. Of course you should have your logic for this ...
• The% with one of the legs in the "leap of the cat". It will minimize the chance of picking up a coupon that is being raffled exactly at the same time, however still this may occur, if it occurs, the SQL LOCK handling will take effect and avoid updating this coupon because your session will " "and wait for the other transaction to finish (due to the configured timeout), when the other transaction finishes, it returns to active and should identify that the drawn column has been filled, ie" is not null "and will not update anything, zero records.

Then the "cat leap" is in the "is null" in the drawn column + transaction control.

Sixth , check how many rows have been updated in the above update, if it is 1, it means that everything went right ... otherwise, draw another coupon ... To do this, use the row_count () function. For more information, read the MySQL manual here.

Setimo , end the transaction with a where sorteado is null or commit , depending on your logic.

To illustrate a sequence of commands where I did the same thing in a parallel session ..

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table cupons (cupom_id integer, sorteado integer);
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into cupons (cupom_id) values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_lock_wait_timeout=60;
Query OK, 0 rows affected (0.00 sec)

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> begin work;
Query OK, 0 rows affected (0.00 sec)

-- aqui em outra sessão executei o mesmo update com os mesmos 
-- parametros de sessao exemplificado aqui..
-- depois de executar o update aqui, na outra sessão comitei a transação.
-- observe o tempo do update abaixo e a qtde de linhas atualizadas.
mysql> update cupons set sorteado=134 where sorteado is null and cupom_id = 4;
Query OK, 0 rows affected (11.14 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

You should not lock tables with LOCK TABLES in normal applications. You should instead use transactions with the correct isolation level to prevent concurrent accesses from changing the same records.

Here's another answer to guarantee the access integro the table with transactions .