innodb_row_lock_4.result 3.02 KB
Newer Older
unknown's avatar
unknown committed
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 135 136 137 138 139 140 141 142
SELECT @@global.innodb_table_locks into @table_locks;
SET @@global.innodb_table_locks= OFF;
DROP TABLE IF EXISTS t1, t2;
SET autocommit=0;
SET autocommit=0;
connection default;
CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,123,1,123);
INSERT INTO t1 VALUES (2,124,2,124);
INSERT INTO t1 VALUES (3,125,3,125);
INSERT INTO t1 VALUES (4,126,4,126);
CREATE INDEX ixi ON t1 (i);
CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,123,1,123);
INSERT INTO t2 VALUES (2,124,2,124);
INSERT INTO t2 VALUES (3,125,3,125);
INSERT INTO t2 VALUES (4,126,4,126);
CREATE INDEX ixi ON t2 (i);
COMMIT;
SELECT @@global.tx_isolation;
@@global.tx_isolation
REPEATABLE-READ
EXPLAIN SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i<125 AND t2.i=t1.i LOCK IN SHARE MODE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	ixi	ixi	5	NULL	4	Using where; Using index
1	SIMPLE	t2	ref	ixi	ixi	5	test.t1.i	2	Using where; Using index
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i<125 AND t2.i=t1.i LOCK IN SHARE MODE;
i	i
123	123
124	124
connection root1;
UPDATE t1,t2 SET t1.i=225,t2.i=225 WHERE t1.i=125 AND t2.i=t1.i;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	123	1	123
2	124	2	124
3	225	3	125
4	126	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	123	1	123
2	124	2	124
3	225	3	125
4	126	4	126
connection default;
UPDATE t1,t2 SET t1.i=223,t2.i=223 WHERE t1.i=123 AND t2.i=t1.i;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	223	1	123
2	124	2	124
3	125	3	125
4	126	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	223	1	123
2	124	2	124
3	125	3	125
4	126	4	126
connection root1;
UPDATE t1,t2 SET t1.i=226,t2.i=226 WHERE t1.i=126 AND t2.i=t1.i;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	123	1	123
2	124	2	124
3	225	3	125
4	226	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	123	1	123
2	124	2	124
3	225	3	125
4	226	4	126
connection default;
UPDATE t1,t2 SET t1.i=224,t2.i=224 WHERE t1.i=124 AND t2.i=t1.i;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	223	1	123
2	224	2	124
3	125	3	125
4	126	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	223	1	123
2	224	2	124
3	125	3	125
4	126	4	126
connection root1;
DELETE FROM t1 WHERE t1.i=226;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	123	1	123
2	124	2	124
3	225	3	125
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	123	1	123
2	124	2	124
3	225	3	125
4	226	4	126
connection default;
DELETE FROM t1 WHERE t1.i=224;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	223	1	123
3	125	3	125
4	126	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	223	1	123
2	224	2	124
3	125	3	125
4	126	4	126
COMMIT;
connection root1;
ROLLBACK;
connection default;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	223	1	123
3	125	3	125
4	126	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	223	1	123
2	224	2	124
3	125	3	125
4	126	4	126
connection root1;
SELECT * FROM t1 ORDER BY t1.k;
k	i	j	l
1	223	1	123
3	125	3	125
4	126	4	126
SELECT * FROM t2 ORDER BY t2.k;
k	i	j	l
1	223	1	123
2	224	2	124
3	125	3	125
4	126	4	126
connection default;
DROP TABLE t1, t2;
SET @@global.innodb_table_locks= @table_locks;