4

I've come across something strage using Amazon RDS (MySQL 5.6.23).

I had issues when concurrent transactions were locking and then calculating a value from multiple tables. The following is a quick demo of what was happening when using transactions with isolation level REPEATABLE READ.

+------+-----------------------------------------------+-----------------------------------------------+
| Time | first transaction                             | second transaction                            |
+------+-----------------------------------------------+-----------------------------------------------+
| 1    | START TRANSACTION;                            | START TRANSACTION;                            |
|      |                                               |                                               |
| 2    | SELECT * FROM stock WHERE sku=SKU0 FOR UPDATE | SELECT * FROM stock WHERE sku=SKU0 FOR UPDATE |
|      |                                               |                                               |
| 3    | [GOT THE LOCK]                                | [WAIT]                                        |
|      |                                               |                                               |
| 4    | SELECT s.qty - COUNT(r.fk_stock) AS qty       |                                               |
|      |   FROM stock s                                |                                               |
|      |   LEFT JOIN reservation r                     | [WAIT]                                        |
|      |   WHERE s.sku = SKU0                          |                                               |
|      |   GROUP BY s.simple_sku;                      |                                               |
|      |                                               |                                               |
| 5    | qty === 10                                    |                                               |
|      |                                               |                                               |
| 6    | INSERT INTO reservation ....                  | [WAIT]                                        |
|      |                                               |                                               |
| 7    | SELECT s.qty - COUNT(r.fk_stock) AS qty       |                                               |
|      |   FROM stock s                                |                                               |
|      |   LEFT JOIN reservation r                     | [WAIT]                                        |
|      |   WHERE s.sku = SKU0                          |                                               |
|      |   GROUP BY s.simple_sku;                      |                                               |
|      |                                               |                                               |
| 8    | qty === 9                                     |                                               |
|      |                                               |                                               |
| 9    | UPDATE stock SET qty = 9                      |                                               |
|      |                                               |                                               |
| 10   | COMMIT;                                       |                                               |
|      |                                               |                                               |
| 11   |                                               | [GOT THE LOCK]                                |
|      |                                               |                                               |
| 12   |                                               | SELECT s.qty - COUNT(r.fk_stock) AS qty       |
|      |                                               |   FROM stock s                                |
|      |                                               |   LEFT JOIN reservation r                     |
|      |                                               |   WHERE s.sku = SKU0                          |
|      |                                               |   GROUP BY s.simple_sku;                      |
|      |                                               |                                               |
| 13   |                                               | qty === 10 <=== it should be 9                |
|      |                                               |                                               |
| 14   |                                               | INSERT INTO reservation ....                  |
|      |                                               |                                               |
| 15   |                                               | SELECT s.qty - COUNT(r.fk_stock) AS qty       |
|      |                                               |   FROM stock s                                |
|      |                                               |   LEFT JOIN reservation r                     |
|      |                                               |   WHERE s.sku = SKU0                          |
|      |                                               |   GROUP BY s.simple_sku;                      |
|      |                                               |                                               |
| 16   |                                               | qty === 9 <=== it should be 8                 |
|      |                                               |                                               |
| 17   |                                               | UPDATE stock SET qty = 9                      |
|      |                                               |                                               |
|      |                                               | COMMIT;                                       |
|      |                                               |                                               |
|      |                                               |                                               |
+------+-----------------------------------------------+-----------------------------------------------+

As soon as I set isolation level to READ COMMITTED the problem is gone.

On my local mysql (MySQL 5.6.19) installation, I can do the same operation and I see the right value for qty using REPEATABLE READ isolation level. Not true when using the same dataset that I have on RDS. See the answer below

For what I found the REPEATABLE READ isolation level creates a read view at the beginning of first read and keeps reading values from that "view". The READ COMMITTED instead will create a read view for every read in a transaction, reading always the most fresher values.

This may explain ..., but I would expect to have the same behaviour also on my local mysql.

This explain what'a happening on RDS.

Has someone experienced something like this before?

closed as off-topic by RolandoMySQLDBA, Max Vernon, Shawn Melton, Marian, dezso Aug 6 '15 at 10:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Max Vernon, Shawn Melton, Marian, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I doubt if RDS is relevant. Also, I suspect that all versions of InnoDB act the same. – Rick James Aug 3 '15 at 19:22
  • @RickJames I would say the same, but I'm having two different results running the same transactions with isolation level REPEATABLE READ – Filippo De Santis Aug 4 '15 at 5:43
  • @RickJames I've downloaded the whole data set I have on the RDS db and now even in local I have the same behaviour. I'll edit the question and write the an answer to my own question. – Filippo De Santis Aug 5 '15 at 13:43
0

The problem seems to be related to the amount of data present in the two tables.

In local I had a small part of the dataset I had on RDS. Once I've dumped the RDS data and load that in local I got the same bahaviour.

So, the flow seems to be:

  1. after the first transaction COMMIT, mysql takes some time to update the table based on the number of its rows
  2. This update comes after the second transaction starts its SELECT s.qty - COUNT(r.fk_stock) AS qty ...
  3. So the second transaction reads from a read view created with the old data

To avoid this behaviour you need to use the READ COMMITTED isolation level. It seems that with this isolation level, the second transaction will wait the update of the table and then create a read view.

Not the answer you're looking for? Browse other questions tagged or ask your own question.