diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/partition_locking.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/partition_locking.result | 461 |
1 files changed, 461 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/partition_locking.result b/mysql-test/suite/innodb/r/partition_locking.result new file mode 100644 index 00000000..f25b8a15 --- /dev/null +++ b/mysql-test/suite/innodb/r/partition_locking.result @@ -0,0 +1,461 @@ +set @start_read_only= @@global.read_only; +set @start_autocommit= @@global.autocommit; +set default_storage_engine= innodb; +set @@global.autocommit= 0; +CREATE USER test@localhost; +grant CREATE, SELECT, UPDATE, INSERT on *.* to test@localhost; +CREATE USER test2@localhost; +grant CREATE, SELECT, UPDATE on *.* to test2@localhost; +CREATE TABLE t1 ( +a char(2) NOT NULL, +b char(2) NOT NULL, +c int(10) unsigned NOT NULL, +d varchar(255) DEFAULT NULL, +e varchar(1000) DEFAULT NULL, +PRIMARY KEY (a, b, c), +KEY (a), +KEY (a, b) +) charset latin1 PARTITION BY KEY (a) PARTITIONS 20; +INSERT INTO t1 (a, b, c, d, e) VALUES +('07', '03', 343, '1', '07_03_343'), +('01', '04', 343, '2', '01_04_343'), +('01', '06', 343, '3', '01_06_343'), +('01', '07', 343, '4', '01_07_343'), +('01', '08', 343, '5', '01_08_343'), +('01', '09', 343, '6', '01_09_343'), +('03', '03', 343, '7', '03_03_343'), +('03', '06', 343, '8', '03_06_343'), +('03', '07', 343, '9', '03_07_343'), +('04', '03', 343, '10', '04_03_343'), +('04', '06', 343, '11', '04_06_343'), +('05', '03', 343, '12', '05_03_343'), +('11', '03', 343, '13', '11_03_343'), +('11', '04', 343, '14', '11_04_343') +; +CREATE TABLE t2 (a int, name VARCHAR(50), purchased DATE) +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (3), +PARTITION p1 VALUES LESS THAN (7), +PARTITION p2 VALUES LESS THAN (9), +PARTITION p3 VALUES LESS THAN (11)); +INSERT INTO t2 VALUES +(1, 'desk organiser', '2003-10-15'), +(2, 'CD player', '1993-11-05'), +(3, 'TV set', '1996-03-10'), +(4, 'bookcase', '1982-01-10'), +(5, 'exercise bike', '2004-05-09'), +(6, 'sofa', '1987-06-05'), +(7, 'popcorn maker', '2001-11-22'), +(8, 'acquarium', '1992-08-04'), +(9, 'study desk', '1984-09-16'), +(10, 'lava lamp', '1998-12-25'); +CREATE TABLE t3 SELECT * FROM t1; +ALTER TABLE t3 ADD PRIMARY KEY (d); +ALTER TABLE t3 ADD KEY (a); +ALTER TABLE t3 ADD KEY (a, b); +ANALYZE TABLE t3; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +######################################################################## +connect con1,localhost,test,,test; +BEGIN; +SELECT d,a,b,c FROM t1 partition (p0); +d a b c +SELECT d,a,b,c FROM t1 partition (p1); +d a b c +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t1 partition (p2); +d a b c +SELECT d,a,b,c FROM t1 partition (p3); +d a b c +SELECT d,a,b,c FROM t1 partition (p4); +d a b c +SELECT d,a,b,c FROM t1 partition (p5); +d a b c +SELECT d,a,b,c FROM t1 partition (p6); +d a b c +SELECT d,a,b,c FROM t1 partition (p7); +d a b c +SELECT d,a,b,c FROM t1 partition (p8); +d a b c +SELECT d,a,b,c FROM t1 partition (p9); +d a b c +2 01 04 343 +3 01 06 343 +4 01 07 343 +5 01 08 343 +6 01 09 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +SELECT d,a,b,c FROM t1 partition (p10); +d a b c +SELECT d,a,b,c FROM t1 partition (p11); +d a b c +13 11 03 343 +14 11 04 343 +SELECT d,a,b,c FROM t1 partition (p12); +d a b c +SELECT d,a,b,c FROM t1 partition (p13); +d a b c +SELECT d,a,b,c FROM t1 partition (p14); +d a b c +SELECT d,a,b,c FROM t1 partition (p15); +d a b c +SELECT d,a,b,c FROM t1 partition (p16); +d a b c +SELECT d,a,b,c FROM t1 partition (p17); +d a b c +1 07 03 343 +SELECT d,a,b,c FROM t1 partition (p18); +d a b c +SELECT d,a,b,c FROM t1 partition (p19); +d a b c +SELECT * FROM t1 WHERE a='01' FOR UPDATE ; +a b c d e +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' FOR UPDATE ; +a b c d e +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +connect con2,localhost,test,,test; +BEGIN; +SET SESSION innodb_lock_wait_timeout=1; +# +# SHARE ... +SELECT * FROM t1 LOCK IN SHARE MODE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED; +a b c d e +07 03 343 1 07_03_343 +04 03 343 10 04_03_343 +04 06 343 11 04_06_343 +05 03 343 12 05_03_343 +11 03 343 13 11_03_343 +11 04 343 14 11_04_343 +03 03 343 7 03_03_343 +03 06 343 8 03_06_343 +03 07 343 9 03_07_343 +SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED; +a count(b) +01 5 +03 3 +04 2 +05 1 +07 1 +11 2 +SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d +LOCK IN SHARE MODE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +ANALYZE TABLE t3; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +SELECT d,a,b,c FROM t3 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +explain SELECT d,a,b,c FROM t3 ORDER BY d +LOCK IN SHARE MODE SKIP LOCKED; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index NULL PRIMARY 257 NULL 14 +# +# UPDATE ... +SELECT * FROM t1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 FOR UPDATE NOWAIT; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED; +a b c d e +03 03 343 7 03_03_343 +03 06 343 8 03_06_343 +03 07 343 9 03_07_343 +04 03 343 10 04_03_343 +04 06 343 11 04_06_343 +05 03 343 12 05_03_343 +07 03 343 1 07_03_343 +11 03 343 13 11_03_343 +11 04 343 14 11_04_343 +SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a FOR UPDATE SKIP LOCKED; +a count(b) +03 3 +04 2 +05 1 +07 1 +11 2 +SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d +FOR UPDATE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t1 ORDER BY d FOR UPDATE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t3 ORDER BY d FOR UPDATE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +connection con1; +COMMIT; +connection con1; +BEGIN; +SELECT * FROM t1 WHERE a='01' LOCK IN SHARE MODE ; +a b c d e +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' LOCK IN SHARE MODE ; +a b c d e +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +connection con2; +BEGIN; +SET SESSION innodb_lock_wait_timeout=1; +# +# SHARE ... +SELECT * FROM t1 LOCK IN SHARE MODE; +a b c d e +03 03 343 7 03_03_343 +03 06 343 8 03_06_343 +03 07 343 9 03_07_343 +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +04 03 343 10 04_03_343 +04 06 343 11 04_06_343 +05 03 343 12 05_03_343 +11 03 343 13 11_03_343 +11 04 343 14 11_04_343 +07 03 343 1 07_03_343 +SELECT * FROM t1 LOCK IN SHARE MODE NOWAIT; +a b c d e +03 03 343 7 03_03_343 +03 06 343 8 03_06_343 +03 07 343 9 03_07_343 +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +04 03 343 10 04_03_343 +04 06 343 11 04_06_343 +05 03 343 12 05_03_343 +11 03 343 13 11_03_343 +11 04 343 14 11_04_343 +07 03 343 1 07_03_343 +SELECT * FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED; +a b c d e +07 03 343 1 07_03_343 +04 03 343 10 04_03_343 +04 06 343 11 04_06_343 +05 03 343 12 05_03_343 +11 03 343 13 11_03_343 +11 04 343 14 11_04_343 +01 04 343 2 01_04_343 +01 06 343 3 01_06_343 +01 07 343 4 01_07_343 +01 08 343 5 01_08_343 +01 09 343 6 01_09_343 +03 03 343 7 03_03_343 +03 06 343 8 03_06_343 +03 07 343 9 03_07_343 +SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED; +a count(b) +01 5 +03 3 +04 2 +05 1 +07 1 +11 2 +SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d +LOCK IN SHARE MODE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +2 01 04 343 +3 01 06 343 +4 01 07 343 +5 01 08 343 +6 01 09 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t1 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +2 01 04 343 +3 01 06 343 +4 01 07 343 +5 01 08 343 +6 01 09 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +ANALYZE TABLE t3; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +SELECT d,a,b,c FROM t3 ORDER BY d LOCK IN SHARE MODE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +2 01 04 343 +3 01 06 343 +4 01 07 343 +5 01 08 343 +6 01 09 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +explain SELECT d,a,b,c FROM t3 ORDER BY d +LOCK IN SHARE MODE SKIP LOCKED; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index NULL PRIMARY 257 NULL 14 +# +# UPDATE ... +SELECT * FROM t1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 FOR UPDATE NOWAIT; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED; +a b c d e +03 03 343 7 03_03_343 +03 06 343 8 03_06_343 +03 07 343 9 03_07_343 +04 03 343 10 04_03_343 +04 06 343 11 04_06_343 +05 03 343 12 05_03_343 +07 03 343 1 07_03_343 +11 03 343 13 11_03_343 +11 04 343 14 11_04_343 +SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a FOR UPDATE SKIP LOCKED; +a count(b) +03 3 +04 2 +05 1 +07 1 +11 2 +SELECT d,a,b,c FROM t1 partition (p1,p9,p11,p17) ORDER BY d +FOR UPDATE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t1 ORDER BY d FOR UPDATE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +SELECT d,a,b,c FROM t3 ORDER BY d FOR UPDATE SKIP LOCKED; +d a b c +1 07 03 343 +10 04 03 343 +11 04 06 343 +12 05 03 343 +13 11 03 343 +14 11 04 343 +7 03 03 343 +8 03 06 343 +9 03 07 343 +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1, t2, t3; +DROP USER test@localhost; +DROP USER test2@localhost; +set @@global.read_only= @start_read_only; +set @@global.autocommit= @start_autocommit; +set default_storage_engine= default; |