1

MySQL Version 5.7.16

Process 1:

START TRANSACTION;
SELECT * from statistic_activity WHERE activity_id = 1 FOR UPDATE;

Process 2:

START TRANSACTION;
INSERT INTO `statistic_activity`  (`activity_id`)  values (2678597);

If Process 1 SELECT statement returns results, Process 2 is not blocked (as you will expect)

But If Process 1 returns empty set (no rows exists with activity_id = 1) then whole table is locked and all INSERTS are blocked until Process 1 transaction ends.

Is this expected behavior ?

Mohit Agrawal
  • 33
  • 1
  • 4
  • 2
    Does this answer your question? [Does "select for update" prevent other connections inserting when the row is not present](https://stackoverflow.com/questions/3601895/does-select-for-update-prevent-other-connections-inserting-when-the-row-is-not) – EJoshuaS - Stand with Ukraine Jun 13 '20 at 14:58
  • Is there an index on the `activity_id` column? If not, then there's no way for the locks to apply to only the rows with the single value. InnoDB defaults to locking every row, to ensure it locks the row(s) where activity_id = 1 – Bill Karwin Jun 14 '20 at 14:29
  • There is index on activity_id. and row level locks are working well when select statement returns non empty results – Mohit Agrawal Jun 15 '20 at 04:12
  • 1
    Then I would guess it's creating a [gap lock](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks) that blocks the inserts. – Bill Karwin Jun 15 '20 at 16:15

0 Answers0