innodb_row_lock_1.result 3.01 KB
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 FOR UPDATE;
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 FOR UPDATE;
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;