1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
# Test of SKIP LOCKED anfd NOWAIT on table with PRIMARY KEY constraint.
--source include/count_sessions.inc
set @start_read_only= @@global.read_only;
set @start_autocommit= @@global.autocommit;
set @@global.autocommit= 0;
CREATE USER test@localhost;
grant CREATE, SELECT, UPDATE on *.* to test@localhost;
CREATE USER test2@localhost;
grant CREATE, SELECT, UPDATE on *.* to test2@localhost;
--enable_connect_log
connection default;
CREATE TABLE t1 ( a INT, b INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);
INSERT INTO t1 VALUES (3, 3);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TABLE t2 ( a INT) ENGINE=InnoDB;
INSERT INTO t2 VALUES (10);
INSERT INTO t2 VALUES (20);
INSERT INTO t2 VALUES (30);
--echo #######################################################################
--echo # SELECT...FOR UPDATE:sequential search lock all rows as no key exists
connect (con1,localhost,test,,test);
SET SESSION innodb_lock_wait_timeout=1;
BEGIN;
SELECT * FROM t1 WHERE a=2 FOR UPDATE ;
connect (con2,localhost,test2,,test);
BEGIN;
SET SESSION innodb_lock_wait_timeout=1;
--echo #
--echo # UPDATE ...
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1 FOR UPDATE;
--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR UPDATE NOWAIT;
SELECT * FROM t1 FOR UPDATE SKIP LOCKED;
--echo #
--echo # SHARE ...
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1 FOR SHARE;
--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR SHARE NOWAIT;
SELECT * FROM t1 FOR SHARE SKIP LOCKED;
--echo #
--echo # Dual locking clauses
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR SHARE OF t2;
--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR SHARE OF t1 NOWAIT;
SELECT * FROM t1 FOR SHARE OF t1 SKIP LOCKED;
--error ER_LOCK_NOWAIT
SELECT * FROM t1, t2 FOR SHARE OF t1 NOWAIT FOR SHARE OF t2 NOWAIT;
explain SELECT * FROM t1, t2 FOR SHARE OF t1 SKIP LOCKED
FOR UPDATE OF t2 NOWAIT;
COMMIT;
connection con1;
commit;
connection default;
ALTER TABLE t1 ADD PRIMARY KEY (a);
--echo ########################################################################
--echo # SELECT ... FOR UPDATE is only locking row with a=2
connection con1;
BEGIN;
SELECT * FROM t1 WHERE a=2 FOR UPDATE ;
connection con2;
BEGIN;
SET SESSION innodb_lock_wait_timeout=1;
--echo #
--echo # UPDATE ...
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1 FOR UPDATE;
--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR UPDATE NOWAIT;
SELECT * FROM t1 ORDER BY a FOR UPDATE SKIP LOCKED;
UPDATE t1 SET b=4 WHERE a=3;
UPDATE t1 SET b=5 WHERE a=1;
SELECT * FROM t1 ORDER BY a;
--echo #
--echo # SHARE ...
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1 FOR SHARE;
--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR SHARE NOWAIT;
SELECT * FROM t1 ORDER BY a FOR SHARE SKIP LOCKED;
--echo #
--echo # Dual locking clauses
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
--error ER_LOCK_WAIT_TIMEOUT
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR SHARE OF t2;
--error ER_LOCK_NOWAIT
SELECT * FROM t1 FOR SHARE OF t1 NOWAIT;
SELECT * FROM t1 FOR SHARE OF t1 SKIP LOCKED;
--error ER_LOCK_NOWAIT
SELECT * FROM t1, t2 FOR SHARE OF t1 NOWAIT FOR SHARE OF t2 NOWAIT;
COMMIT;
connection con1;
COMMIT;
connection default;
disconnect con1;
disconnect con2;
DROP VIEW v1;
DROP TABLE t1, t2;
--disable_connect_log
DROP USER test@localhost;
DROP USER test2@localhost;
set @@global.read_only= @start_read_only;
set @@global.autocommit= @start_autocommit;
--source include/wait_until_count_sessions.inc
|
