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;