diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/monitor.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/monitor.result | 111 |
1 files changed, 111 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result index 16aa8630012..4cfe85090b6 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -685,6 +685,28 @@ LIKE 'buffer_page_written_index_leaf'; NAME COUNT > 0 buffer_page_written_index_leaf 1 DROP TABLE t1; +CREATE TABLE fl0 ( +id INT NOT NULL PRIMARY KEY +) ENGINE = InnoDB; +CREATE TABLE fl1 ( +id INT NOT NULL PRIMARY KEY, +fl0_id INT, +CONSTRAINT `fkl0` + FOREIGN KEY (fl0_id) REFERENCES fl0 (id) +ON DELETE CASCADE +ON UPDATE RESTRICT +) ENGINE = InnoDB; +CREATE TABLE fl2 ( +id INT NOT NULL PRIMARY KEY, +fl1_id INT, +CONSTRAINT `fkl1` + FOREIGN KEY (fl1_id) REFERENCES fl1 (id) +ON DELETE CASCADE +ON UPDATE SET NULL +) ENGINE = InnoDB; +INSERT INTO fl0 VALUES (1000); +INSERT INTO fl1 VALUES (500, 1000), (1500, 1000); +INSERT INTO fl2 VALUES (200, 500), (800, 500), (1200, 1500), (1800, 1500); CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b)) ENGINE=InnoDB; SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME @@ -698,7 +720,96 @@ SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME SELECT @end - @start; @end - @start 0 +# Records must not be S/X-locked if a table is X-locked. +SET @start = @end; +SET autocommit = 0; +BEGIN; +LOCK TABLE t1 WRITE; +SELECT * FROM t1; +id a b +1 1 a +4 4 d +3 10000 c +SELECT * FROM t1 WHERE a>=10000; +id a b +3 10000 c +SELECT * FROM t1 FOR UPDATE; +id a b +1 1 a +4 4 d +3 10000 c +SELECT * FROM t1 WHERE a>=10000 FOR UPDATE; +id a b +3 10000 c +UPDATE t1 SET b = 'b' WHERE id = 4; +UPDATE t1 SET b = 'b' WHERE a = 10000; +REPLACE INTO t1 VALUES (4,3,'a'); +INSERT INTO t1 VALUES (3,1,'e') ON DUPLICATE KEY UPDATE b = 'b'; +INSERT INTO t1 VALUES (5,5,'e'); +DELETE FROM t1 WHERE a = 1 AND b='a'; +DELETE FROM t1; +COMMIT; +UNLOCK TABLES; +SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); +SELECT @end - @start; +@end - @start +0 +# Records must not be S-locked if a table is S-locked. +SET @start = @end; +BEGIN; +LOCK TABLE t1 WRITE; +INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d'); +DELETE FROM t1 WHERE a = 9999 AND b='b'; +COMMIT; +UNLOCK TABLES; +BEGIN; +LOCK TABLE t1 READ; +SELECT * FROM t1 LOCK IN SHARE MODE; +id a b +1 1 a +4 4 d +3 10000 c +SELECT * FROM t1 WHERE a>=10000 LOCK IN SHARE MODE; +id a b +3 10000 c +COMMIT; +UNLOCK TABLES; +SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); +SELECT @end - @start; +@end - @start +0 +# Records must not be S-locked for foreign keys enforcement +SET @start = @end; +BEGIN; +LOCK TABLE fl0 READ, fl1 READ, fl2 WRITE; +INSERT INTO fl2 VALUES (300, 500), (700, 500), (1300, 1500), (1700, 1500); +SELECT * FROM fl1 LOCK IN SHARE MODE; +id fl0_id +500 1000 +1500 1000 +COMMIT; +UNLOCK TABLES; +SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); +SELECT @end - @start; +@end - @start +0 +# Records must not be X-locked for foreign keys cascade +SET @start = @end; +BEGIN; +LOCK TABLE fl0 READ, fl1 WRITE, fl2 WRITE; +DELETE FROM fl1 WHERE id = 1500; +UPDATE fl1 SET id = 2500 WHERE id = 500; +COMMIT; +UNLOCK TABLES; +SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); +SELECT @end - @start; +@end - @start +0 +SET autocommit = default; DROP TABLE t1; +DROP TABLE fl2; +DROP TABLE fl1; +DROP TABLE fl0; SET GLOBAL innodb_monitor_enable=default; SET GLOBAL innodb_monitor_disable=default; SET GLOBAL innodb_monitor_reset_all=default; |