How to manage concurrency in mysql?

Question

I'm stuck with this. To make it simpler, let's say I have this table

CREATE TABLE IF NOT EXISTS `tbl_bags_oranges` (
  `idbag` int(11) NOT NULL AUTO_INCREMENT,
  `n_oranges` int(11) NOT NULL,
  PRIMARY KEY (`idbag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci AUTO_INCREMENT=1 ;

And I'm developing a php script that represents the situation in which users grab oranges. This is what I'm doing:

I use a SELECT to check if there are oranges in the given idgab (n_oranges > 0)
If so, I UPDATE the table subtracting 1 to n_oranges

But it may happen the following when there is just one orange in a bag:

User A wants to grab an orange. The ´SELECT´ says there is one orange.
User B wants to grab an orange. The ´SELECT´ says there is one orange.
User A grab the orange. I UPDATE the table and now n_oranges = 0.
User B is able to grab the orange because his SELECT said that there was one orange.

How can I solve this question? Transactions? LOCK TABLES? Since I can't check this situation there is no way to know if I'm doing well or wrong.

Do an [intention lock](http://stackoverflow.com/a/38079598). Yours will be somewhat similar, but with a decrement. Split second, safe. — Drew, Jul 19 '16 at 04:18
Did you try add a check again n_oranges before you procceed to update in one step? — Konstantinos Tsourdinis, Jul 19 '16 at 05:30

Herris · Answer 1 · 2016-07-19T05:32:43.877

0

You can do a checking when you update the table something like this:

UPDATE `tbl_bags_oranges`
SET n_oranges=n_oranges-1 
WHERE n_oranges>=1;

You may modify the code based on your situation.

edited Jul 19 '16 at 05:32

answered Jul 19 '16 at 05:27

Herris

61
5

Sorry, my fault. I wanted the question to be as simpler as possible to made it clear and I didn’t realize that my example was too much simple. Actually I have to check values from 3 different tables and then update one of them. – DandyCC Jul 19 '16 at 13:29

Oleg Kuralenko · Accepted Answer · 2016-07-19T12:58:02.357

0

You can use locking reads. In detail:

make sure autocommit is disabled
START TRANSACTION
SELECT ... FROM tbl_bags_oranges WHERE ... FOR UPDATE
do whatever else you need to do in your code
save the new valueUPDATE tbl_bags_oranges SET ...
COMMIT

In this case in your scenario the second query would block until the first transaction commits hence no race condition would occur

edited Jul 19 '16 at 12:58

answered Jul 19 '16 at 12:42

Oleg Kuralenko

11,003
1
30
40

That was exactly what I was looking for. Thanks a lot! – DandyCC Jul 19 '16 at 13:30

How to manage concurrency in mysql?

2 Answers2