# Bug #27491839  INNODB: ASSERTION FAILURE:
#                LOCK0LOCK.CC:NNN:!LOCK_REC_OTHER_TRX_HOLDS_EXPL( LOCK
SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
SET @@global.innodb_lock_wait_timeout = 100000;
# Scenario 1
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
1
1
BEGIN;
UPDATE t1 SET c2=13 WHERE id=1;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
ROLLBACK;
1
1
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 2
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
UPDATE t1 SET c1=55 WHERE id=1;
BEGIN;
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
1
BEGIN;
UPDATE t1 SET c2=13 WHERE id=1;
SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
1
ROLLBACK;
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 3
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
DELETE FROM t1 WHERE id=1;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4
# Scenario 4a
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4b
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c2=13 WHERE id=2;
UPDATE t1 SET c2=42 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
# Scenario 4c
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
UPDATE t1 SET c1=10 WHERE id=2;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4d
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c1=10 WHERE id=2;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4e
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
# Scenario 1
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
1
1
BEGIN;
UPDATE t1 SET c2=13 WHERE id=1;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE c1=1 FOR UPDATE;;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
ROLLBACK;
1
1
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 2
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
UPDATE t1 SET c1=55 WHERE id=1;
BEGIN;
SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
1
BEGIN;
UPDATE t1 SET c2=13 WHERE id=1;
SELECT 1 FROM t1 WHERE c1=1 FOR UPDATE;
1
ROLLBACK;
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 3
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
DELETE FROM t1 WHERE id=1;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE c1=1 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4
# Scenario 4a
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4b
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c2=13 WHERE id=2;
UPDATE t1 SET c2=42 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
# Scenario 4c
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
UPDATE t1 SET c1=10 WHERE id=2;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE c1=10 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4d
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c1=10 WHERE id=2;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE c1=10 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4e
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (id DESC),
UNIQUE KEY(c1)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE c1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
# Scenario 1
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
1
1
BEGIN;
UPDATE t1 SET c2=13 WHERE id=1;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
ROLLBACK;
1
1
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 2
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
UPDATE t1 SET c1=55 WHERE id=1;
BEGIN;
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
1
BEGIN;
UPDATE t1 SET c2=13 WHERE id=1;
SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
1
ROLLBACK;
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 2b
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
UPDATE t1 SET c1=55 WHERE id=1;
BEGIN;
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
1
BEGIN;
UPDATE t1 SET c3=13 WHERE id=1;
UPDATE t1 SET c2=13 WHERE id=1;
SELECT 1 FROM t1 WHERE v1=1 FOR UPDATE;
1
ROLLBACK;
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
# Scenario 3
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
DELETE FROM t1 WHERE id=1;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=1 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4
# Scenario 4a
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4b
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c2=13 WHERE id=2;
UPDATE t1 SET c2=42 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
# Scenario 4b-prime
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c3=13 WHERE id=2;
UPDATE t1 SET c2=42 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
# Scenario 4c
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
BEGIN;
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
UPDATE t1 SET c1=10 WHERE id=2;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4d
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c1=10 WHERE id=2;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SET DEBUG_SYNC='lock_wait_will_wait SIGNAL c1_will_wait';
SELECT 1 FROM t1 WHERE v1=10 FOR SHARE;
SET DEBUG_SYNC='now WAIT_FOR c1_will_wait';
COMMIT;
1
1
ROLLBACK;
DROP TABLE t1;
# Scenario 4e
CREATE TABLE t1(
id INT NOT NULL,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL DEFAULT 1337,
v1 INT AS (c1) VIRTUAL,
PRIMARY KEY (id DESC),
UNIQUE KEY(v1),
KEY (c2)
) Engine=InnoDB;
INSERT INTO t1 (id,c1,c2) VALUES (0,0,0),(1,1,1),(3,3,3);
BEGIN;
UPDATE t1 SET c2=13 WHERE id = 3;
INSERT INTO t1 (id,c1,c2) VALUES (4,4,4);
INSERT INTO t1 (id,c1,c2) VALUES (2,2,2);
BEGIN;
UPDATE t1 SET c2=13 WHERE id=2;
BEGIN;
SELECT 1 FROM t1 WHERE v1=2 FOR SHARE;
1
1
COMMIT;
COMMIT;
ROLLBACK;
DROP TABLE t1;
SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;