diff options
Diffstat (limited to 'storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result | 490 |
1 files changed, 490 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result new file mode 100644 index 00000000000..4b237dcb7aa --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/locking_issues.result @@ -0,0 +1,490 @@ + +----------------------------------------------------------------------- +- Locking issues case 1.1: +- Locking rows that do not exist when using all primary key columns in +- a WHERE clause +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +id1 id2 value +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +INSERT INTO t0 VALUES (1,5,0); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 1.1: +- Locking rows that do not exist when using all primary key columns in +- a WHERE clause +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +id1 id2 value +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +INSERT INTO t0 VALUES (1,5,0); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +SELECT * FROM t0 WHERE id1=1 AND id2=5 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 1.2: +- Locking rows that do not exist without using all primary key +- columns in a WHERE clause +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE id1=1 FOR UPDATE; +id1 id2 value +1 1 0 +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=4 FOR UPDATE; +id1 id2 value +INSERT INTO t0 VALUES (1,5,0); +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 1.2: +- Locking rows that do not exist without using all primary key +- columns in a WHERE clause +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id1 INT, id2 INT, value INT, PRIMARY KEY(id1, id2)); +INSERT INTO t0 VALUES (1,1,0), (3,3,0), (4,4,0), (6,6,0); +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE id1=1 FOR UPDATE; +id1 id2 value +1 1 0 +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE id1=1 AND id2=4 FOR UPDATE; +id1 id2 value +INSERT INTO t0 VALUES (1,5,0); +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using REPEATABLE READ transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +UPDATE t0 SET VALUE=10 WHERE id=1; +UPDATE t0 SET VALUE=10 WHERE id=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +UPDATE t0 SET value=100 WHERE id in (4,5) and value>0; +SELECT * FROM t0 WHERE id=4 FOR UPDATE; +id value +4 0 +COMMIT; +SELECT * FROM t0; +id value +1 10 +2 1 +3 0 +4 0 +5 1 +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using READ COMMITTED transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +UPDATE t0 SET VALUE=10 WHERE id=1; +UPDATE t0 SET VALUE=10 WHERE id=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +UPDATE t0 SET value=100 WHERE id in (4,5) and value>0; +SELECT * FROM t0 WHERE id=4 FOR UPDATE; +id value +4 0 +COMMIT; +SELECT * FROM t0; +id value +1 10 +2 1 +3 0 +4 0 +5 1 +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using REPEATABLE READ transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +UPDATE t0 SET VALUE=10 WHERE id=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +COMMIT; +DROP TABLE t0; +SET GLOBAL rocksdb_lock_scanned_rows=0; + +----------------------------------------------------------------------- +- Locking issues case 2: +- Rows that are scanned but do not match the WHERE are not locked +- using READ COMMITTED transaction isolation level unless +- rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t0(id INT PRIMARY KEY, value INT); +INSERT INTO t0 VALUES (1,0), (2,1), (3,0), (4,0), (5,1); +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +id value +2 1 +5 1 +UPDATE t0 SET VALUE=10 WHERE id=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t0.PRIMARY +COMMIT; +DROP TABLE t0; +SET GLOBAL rocksdb_lock_scanned_rows=0; + +----------------------------------------------------------------------- +- Locking issues case 3: +- After creating a snapshot, other clients updating rows +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +UPDATE t0 SET VALUE=VALUE+1 WHERE id=190000; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 3: +- After creating a snapshot, other clients updating rows +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +UPDATE t0 SET VALUE=VALUE+1 WHERE id=190000; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 4: +- Phantom rows +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +INSERT INTO t0 VALUES(200001,1), (-1,1); +id value +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 4: +- Phantom rows +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +Inserting 200,000 rows +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t0 WHERE value > 0 FOR UPDATE; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +INSERT INTO t0 VALUES(200001,1), (-1,1); +id value +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 5: +- Deleting primary key +- using REPEATABLE READ transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +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; +DELETE FROM t0 WHERE id=190000; +COMMIT; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- Locking issues case 5: +- Deleting primary key +- using READ COMMITTED transaction isolation level +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t0; +CREATE TABLE t0(id INT AUTO_INCREMENT PRIMARY KEY, value INT); +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; +DELETE FROM t0 WHERE id=190000; +COMMIT; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +COMMIT; +DROP TABLE t0; + +----------------------------------------------------------------------- +- 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); +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 40001: Deadlock found when trying to get lock; try restarting transaction +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); +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; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +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 rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +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 rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +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 rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +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: Timeout on index: test.t2.PRIMARY +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 rocksdb_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 rocksdb_lock_scanned_rows is on +----------------------------------------------------------------------- +DROP TABLE IF EXISTS t1, t2; +SELECT @@global.rocksdb_lock_scanned_rows; +@@global.rocksdb_lock_scanned_rows +0 +SET GLOBAL rocksdb_lock_scanned_rows=ON; +CREATE TABLE t1(id INT PRIMARY KEY, value INT); +CREATE TABLE t2(id INT PRIMARY KEY, value INT); +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: Timeout on index: test.t2.PRIMARY +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 rocksdb_lock_scanned_rows=0; |