4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/innodb/r/lock_impl_to_expl.result

678 lines
14 KiB
Plaintext
Raw Permalink Blame History

# 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;
Reference in New Issue View Git Blame Copy Permalink