2

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 ?

  • 3
    If it's row lock, why does second update query in session 2 not hang ? – W.W Apr 16 '15 at 15:30
2

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

  • can you explain detail about why query "UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1"; isn't hung though it acquires X lock and session 1 acquired S lock before ? – W.W Apr 17 '15 at 8:50
  • Session 1 acquires S lock on the row in the index 'index1'. But "UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1" command will not update anything on the index "index1". Because content is not a part of index1 key column(idpost, idrevision). – Muniyadi Apr 17 '15 at 10:42
  • If both of 2 sessions update revision table like that "UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1", one of them will hang, though not update anything on the index "index1". – W.W Apr 17 '15 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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