2

I locked the table using FOR UPDATE with an IF validation. However, it seems that they aren't able to handle the parallel request?

I have two samples of stored procedures. I tested these using cURL to make a parallel requests. 

DROP PROCEDURE IF EXISTS SAMPLE;
DELIMITER //
CREATE PROCEDURE SAMPLE(_quantity INT)
BEGIN
   DECLARE _total INT DEFAULT 0;
   SELECT COALESCE(SUM(price),0) + _quantity INTO _total FROM account FOR UPDATE;
   IF(_total <= 50) THEN
       CALL SAMPLE2(_quantity);
   ELSE
       SELECT 'EXCEEDED';
   END IF;
END; //
DELIMITER ;

DROP PROCEDURE SAMPLE2;
DELIMITER //
CREATE PROCEDURE SAMPLE2(_quantity INT)
BEGIN
   INSERT INTO account(price) VALUES(_quantity);
END; //
DELIMITER ;

I expect the output to not exceed 50 based on my validation.

LOG OUTPUT:

CALL SAMPLE(?) [ [ RowDataPacket { _total: 20, _quantity: 20, 'now()': 2019-07-22T08:33:27.000Z } ],

CALL SAMPLE(?) [ [ RowDataPacket { _total: 20, _quantity: 20, 'now()': 2019-07-22T08:33:27.000Z } ],

CALL SAMPLE(?) [ [ RowDataPacket { _total: 40, _quantity: 20, 'now()': 2019-07-22T08:33:27.000Z } ],

CALL SAMPLE(?) [ [ RowDataPacket { _total: 80, _quantity: 20, 'now()': 2019-07-22T08:33:28.000Z } ],
  [ RowDataPacket { EXCEEDED: 'EXCEEDED' } ],

CALL SAMPLE(?) [ [ RowDataPacket { _total: 80, _quantity: 20, 'now()': 2019-07-22T08:33:28.000Z } ],
  [ RowDataPacket { EXCEEDED: 'EXCEEDED' } ],

CALL SAMPLE(?) [ [ RowDataPacket { _total: 80, _quantity: 20, 'now()': 2019-07-22T08:33:28.000Z } ],
  [ RowDataPacket { EXCEEDED: 'EXCEEDED' } ],

On the second call, it seems that they didn't read the inserted value.

Data Inserted.

+----+-------+---------------------+
| id | price | created             |
+----+-------+---------------------+
|  1 |    20 | 2019-07-25 13:44:51 |
|  2 |    20 | 2019-07-25 13:44:51 |
|  3 |    20 | 2019-07-25 13:44:51 |
+----+-------+---------------------+

What I need is the summation of price <= 50. But the above output showed 60.

Ramil
  • 21
  • 3
  • What you are testing by adding `for update`? – James Jul 24 '19 at 12:03
  • I am trying to lock the table to prevent the parallel request from exceeding my validation – Ramil Jul 24 '19 at 14:23
  • Can you explain in detail like what you need as a result – James Jul 25 '19 at 04:21
  • I edited my question above. Please check. – Ramil Jul 25 '19 at 05:56
  • I couldn't understand the LOG OUTPUT but i guess data inserted should stop with `id`=2? – James Jul 25 '19 at 07:28
  • Yes that is right. The LOG OUTPUT is the **Parameter passed (which is quantity) + summation of price**, **Parameter passed**, **Date function** – Ramil Jul 25 '19 at 07:38
  • So is it like application output? like php if so add the tag – James Jul 25 '19 at 09:22
  • I think the issue raises due to nested call try the insert directly in the same sp sample – James Jul 25 '19 at 09:23
  • If my theory is wrong, just add the expected LOG OUTPUT – James Jul 25 '19 at 09:26
  • I am having a hard time replicating this issue. Sometimes it exceeds, sometimes it does not. I tried inserting it directly in the same SP. But still, summation of 60 occurs. – Ramil Jul 26 '19 at 02:55
  • Or try adding explicit `start transaction` and `commit` and inserting in single SP. – James Jul 26 '19 at 04:06
  • check your Isolation level Refer: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html – James Jul 26 '19 at 04:12
  • I cannot replicate it by adding `start transaction ` and `commit`. But I am not sure if this explicit `start transaction` should fix it. I have a similar behavior in production with explicit `transaction` and they bypassed my validation. – Ramil Jul 26 '19 at 10:17
  • Can you recommend other libraries/tools for making parallel request? I believe looping is concurrent, not parallel. – Ramil Jul 26 '19 at 10:18
  • Since the docs said that to hold a lock you should use between the transaction explicitly. Use can read the note: on the docs https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html – James Jul 27 '19 at 04:04

0 Answers0