174 lines
5.6 KiB
Plaintext
174 lines
5.6 KiB
Plaintext
|
|
-----------------------------------------------------------------------
|
|
- Locking issues case 6:
|
|
- Changing primary key
|
|
- using REPEATABLE READ transaction isolation level
|
|
-----------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS t0;
|
|
CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
Inserting 200,000 rows
|
|
UPDATE t0 SET value=100 WHERE id=190000;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
BEGIN;
|
|
SELECT * FROM t0 WHERE value > 0 FOR UPDATE;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
BEGIN;
|
|
UPDATE t0 SET id=200001 WHERE id=190000;
|
|
COMMIT;
|
|
ERROR: 1213
|
|
COMMIT;
|
|
DROP TABLE t0;
|
|
|
|
-----------------------------------------------------------------------
|
|
- Locking issues case 6:
|
|
- Changing primary key
|
|
- using READ COMMITTED transaction isolation level
|
|
-----------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS t0;
|
|
CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
Inserting 200,000 rows
|
|
UPDATE t0 SET value=100 WHERE id=190000;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
SELECT * FROM t0 WHERE value > 0 FOR UPDATE;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
UPDATE t0 SET id=200001 WHERE id=190000;
|
|
COMMIT;
|
|
id value
|
|
ERROR: 0
|
|
COMMIT;
|
|
DROP TABLE t0;
|
|
|
|
-----------------------------------------------------------------------
|
|
- Locking issues case 7:
|
|
- Rows that are scanned as part of a query but not in the table being
|
|
- updated should not be locked unless xengine_lock_scanned_rows is on
|
|
-----------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
SELECT @@global.xengine_lock_scanned_rows;
|
|
@@global.xengine_lock_scanned_rows
|
|
0
|
|
CREATE TABLE t1(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
CREATE TABLE t2(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
BEGIN;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
BEGIN;
|
|
lock_scanned_rows is 0
|
|
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3;
|
|
UPDATE t2 SET value=value+100;
|
|
SELECT * FROM t2;
|
|
id value
|
|
1 101
|
|
2 102
|
|
3 103
|
|
4 104
|
|
5 105
|
|
COMMIT;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
-----------------------------------------------------------------------
|
|
- Locking issues case 7:
|
|
- Rows that are scanned as part of a query but not in the table being
|
|
- updated should not be locked unless xengine_lock_scanned_rows is on
|
|
-----------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
SELECT @@global.xengine_lock_scanned_rows;
|
|
@@global.xengine_lock_scanned_rows
|
|
0
|
|
CREATE TABLE t1(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
CREATE TABLE t2(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
lock_scanned_rows is 0
|
|
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3;
|
|
UPDATE t2 SET value=value+100;
|
|
SELECT * FROM t2;
|
|
id value
|
|
1 101
|
|
2 102
|
|
3 103
|
|
4 104
|
|
5 105
|
|
COMMIT;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
-----------------------------------------------------------------------
|
|
- Locking issues case 7:
|
|
- Rows that are scanned as part of a query but not in the table being
|
|
- updated should not be locked unless xengine_lock_scanned_rows is on
|
|
-----------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
SELECT @@global.xengine_lock_scanned_rows;
|
|
@@global.xengine_lock_scanned_rows
|
|
0
|
|
SET GLOBAL xengine_lock_scanned_rows=ON;
|
|
CREATE TABLE t1(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
CREATE TABLE t2(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
BEGIN;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
BEGIN;
|
|
lock_scanned_rows is 1
|
|
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3;
|
|
UPDATE t2 SET value=value+100 WHERE id=3;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5);
|
|
SELECT * FROM t2;
|
|
id value
|
|
1 101
|
|
2 102
|
|
3 3
|
|
4 104
|
|
5 105
|
|
COMMIT;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
SET GLOBAL xengine_lock_scanned_rows=0;
|
|
|
|
-----------------------------------------------------------------------
|
|
- Locking issues case 7:
|
|
- Rows that are scanned as part of a query but not in the table being
|
|
- updated should not be locked unless xengine_lock_scanned_rows is on
|
|
-----------------------------------------------------------------------
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
SELECT @@global.xengine_lock_scanned_rows;
|
|
@@global.xengine_lock_scanned_rows
|
|
0
|
|
SET GLOBAL xengine_lock_scanned_rows=ON;
|
|
CREATE TABLE t1(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
CREATE TABLE t2(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
lock_scanned_rows is 1
|
|
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3;
|
|
UPDATE t2 SET value=value+100 WHERE id=3;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5);
|
|
SELECT * FROM t2;
|
|
id value
|
|
1 101
|
|
2 102
|
|
3 3
|
|
4 104
|
|
5 105
|
|
COMMIT;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
SET GLOBAL xengine_lock_scanned_rows=0;
|