2
DROP TABLE IF EXISTS t1, t2;
3
DROP VIEW IF EXISTS v1;
6
# Create additional connections used through test
7
CONNECT (root1, localhost, root,,);
9
--echo connection default;
11
eval CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=$engine;
13
eval CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=$engine;
16
CREATE PROCEDURE fill_t1 (IN upb int)
18
DECLARE cnt int DEFAULT 0;
20
INSERT INTO t1 VALUES (cnt, cnt+100, cnt, cnt+100);
25
CREATE FUNCTION half_t1() RETURNS int
27
DECLARE res int DEFAULT 0;
28
SELECT MOD(k,2) INTO res FROM t1;
32
CREATE PROCEDURE fill_t2 (IN upb int)
34
DECLARE cnt int DEFAULT 0;
36
INSERT INTO t2 VALUES (cnt, cnt+100, cnt, cnt+100);
41
CREATE FUNCTION half_t2() RETURNS int
43
DECLARE res int DEFAULT 0;
44
SELECT MOD(k,2) INTO res FROM t2;
49
eval CALL fill_t1 ($nbrows);
50
eval CALL fill_t2 ($nbrows);
52
SELECT @@global.tx_isolation;
53
# With the two separate selects (without join) the differs from
54
# that select with join.
56
# Both transaction are able to update the tables
60
--echo connection root1;
62
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.k % 2= 1 AND t1.k = t2.k LOCK IN SHARE MODE;
63
UPDATE t1,t2 SET t1.i=1111,t2.i=2222 WHERE t1.k % 2 = 1 AND t1.k = t2.k;
64
SELECT * FROM t1 WHERE k < 20 ORDER BY t1.k;
65
SELECT * FROM t2 WHERE k < 20 ORDER BY t2.k;
67
--echo connection default;
69
UPDATE t1,t2 SET t1.i=3333,t2.i=4444 WHERE t1.k % 2 = 0 AND t1.k = t2.k;
70
SELECT * FROM t1 WHERE k < 20 ORDER BY t1.k;
71
SELECT * FROM t2 WHERE k < 20 ORDER BY t2.k;
75
--echo connection root1;
79
--echo connection default;
81
SELECT * FROM t1 WHERE k < 40 ORDER BY t1.k;
82
SELECT * FROM t2 WHERE k < 40 ORDER BY t2.k;
84
--echo connection root1;
86
SELECT * FROM t1 WHERE k < 40 ORDER BY t1.k;
87
SELECT * FROM t2 WHERE k < 40 ORDER BY t2.k;
89
--echo connection default;