Competing transactions in Mysql

Question

I am working on a system that needs to draw multiple data(coupons) from one table and return to the user, but I cannot risk delivering the same coupon to the other user.

I thought I’d use the GET_LOCK() Mysql to lock the table temporarily while the draw is done, but it does not secure the HTTP connection and the connection gets lost by making the call again.

Is there any right way or just better to do it?

Answer 1

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

For situations like this, you need to know a little 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 quilt", this answer will be valid just if you are using the engine innoDB.

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 while accessing a data.
For this case I would recommend the : set session transaction isolation level read committed;
For more information on a read in the Mysql here.
This Isolation level will ensure that you will only read data that are comitados/confirmed at the bank.

According to, define a time-out that your transaction will wait for a lock.
This serves to update , if it competes with another, have a time limit to wait for the release of the record.
In Mysql this is done with the command set session innodb_lock_wait_timeout=10
Here I set for 10 seconds, which I consider more than enough.
If it is not enough, you can increase this time and/or prepare your application to treat the exception ERROR HY000: Lock wait timeout exceeded; try restarting transaction For more information on a read in the Mysql here

Third, disable the autocommit so that you can have more control over the transaction, when it starts and when it should end. For that use the SET autocommit = 0

Quarter, initiate a transaction with start transaction or begin work.
It doesn’t matter which of the two commands, because they are synonymous.
But the important thing here is to keep in mind that everything you change in the database will remain with an active lock until the transaction is completed!

Quinto, 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 of table, structure, SQL you used I will post something of my imagination even.

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

Explaining:

• The column drawn is the flag that says if the coupon has already been drawn. your content is the draw number (so you can relate the day/time draw X to the coupon Y)
• The cupom_id is the key field of the coupon, in case the 4444 would be the one of the drawn coupon. Of course you must have your logic for this...
• The where sorteado is null one of the legs in the "cat jump".
  It will minimize the chance of picking up a coupon that is being drawn at exactly the same time, but still this may occur, if it does occur, SQL LOCK handling will go into action and avoid updating this coupon because your session will "crash" and wait for the other transaction to be completed (due to the timeout set), when the other transaction is completed, this transaction is turned on and must identify that the column drawn has been completed, that is, "is not null" and will not update anything, zero records.

Then the "cat jump" is in the "is null" in the sorted column + the transaction control.

Sixth , check how many lines were updated in the update above, if it is 1, that means that everything worked out... otherwise, draw another coupon...
To do this use the row_count function().
For more information on a read in the Mysql here.

Seventh, finalize the transaction with a commit or rollback, according to 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)

Answer 2

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

Here’s another answer to ensure full access to the table with transactions.