Lock in mysql database

Question

My schema 'test' has 3 tables: 'user', 'post' and 'revision'.

CREATE TABLE IF NOT EXISTS `test`.`user` (
  `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
   PRIMARY KEY (`iduser`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`post` (
  `idpost` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `iduser` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
  `revision_count` INT NOT NULL DEFAULT 0,
   PRIMARY KEY (`idpost`),
   INDEX `fk_post_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_post_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost` ASC, `idrevision` ASC),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `fk_revision_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_revision_post`
     FOREIGN KEY (`idpost`)
     REFERENCES `test`.`post` (`idpost`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION,
   CONSTRAINT `fk_revision_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

And there is the script creates data:

INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (1, 'Ann');
INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (2, 'Bob');

INSERT INTO `test`.`post` (`idpost`, `iduser`, `content`, `revision_count`) VALUES (1, 1, 'this is first post', 1);

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, `content`) VALUES (1, 1, 1, 'this is old content of first post');

I create 2 sessions work concurrently. Session 1 works with query:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES  
  ((SELECT max_idrevision + 1 
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1), 
   1, 2, 'abc xyz');

Now session 2 works:

BEGIN;
UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;
-- will hang by lock

-- but with this update
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;
-- not hang

I don't know what's lock mysql used with query insert in session 1? Is it row lock or table lock or anything else ? If it's row lock, why does second update query in session 2 not hang ?

Answer 1

Simplified scenario:

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost`, `idrevision`),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `user1_idx` (`iduser`));

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, content`) VALUES (1, 1, 1, 'this is old content of first post');

Session1:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES  
  ((SELECT max_idrevision + 1 
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1), 
   1, 2, 'abc xyz');

select max(idrevision) AS max_idrevision from revision where idpost=1;

• The above query will fetch the value from the secondary index 'index1'. Because idpost and idrevision is covered by index1 itself. So index1 is a covering index for this query.
• It will take S lock on the row(1,1) in the index 'index1' due to repeatable read isolation level. (Read about repeatable read isolation level).

Session2:

BEGIN;
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;

Above query will change the clustered index alone, it won't change any secondary index like 'index1' because 'content' is not part of any secondary index.

UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;

• But in this case, idrevision has been changed. So it will try to update the key part of 'index1' ( because idrevision is a part of key in index1).

Why it leads to hang:

• As I mentioned earlier in session 1, S row lock taken for the row (1,1) in the index1.
• Now session 2 tries to take X row lock to update the row(1,1) in the same index1.
• If you look lock type compatibility matrix, S and X locks are Conflicts.

Note:

During hanging, you can open one more session and give the following query to find out where it is hanging.

mysql> select * from information_schema.innodb_locks;
--------------
select * from information_schema.innodb_locks
--------------

+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id    | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 1826:8:4:2 | 1826        | X         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
| 1817:8:4:2 | 1817        | S         | RECORD    | `test`.`revision` | index1     |          8 |         4 |        2 | 1, 1      |
+------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+

The above analysis is based on mysql-5.6