455 lines
11 KiB
Plaintext
455 lines
11 KiB
Plaintext
--echo # Scenario:1
|
|
--echo # T1= ({W(B)}, HIGH_PRIORITY)
|
|
--echo # T2= ( Try to take table level lock )
|
|
--echo # Outcome: T2 waits for t1 to acquire table lock
|
|
|
|
--source include/have_debug.inc
|
|
--source include/count_sessions.inc
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connect(con2,localhost,root,,test)
|
|
|
|
--echo
|
|
--echo # On connection 1
|
|
--connection con1
|
|
let $con1_id=`SELECT CONNECTION_ID()`;
|
|
--source include/start_transaction_high_prio.inc
|
|
UPDATE t1 SET c1=1 WHERE c1=0;
|
|
|
|
--echo
|
|
--echo # On connection 2
|
|
--connection con2
|
|
let $con2_id=`SELECT CONNECTION_ID()`;
|
|
--send LOCK TABLE t1 WRITE/* From connection 2 */
|
|
|
|
--echo
|
|
--echo # On connection 1
|
|
--connection con1
|
|
--disable_query_log
|
|
eval SET @con2_id = $con2_id;
|
|
--enable_query_log
|
|
let $wait_timeout = 10;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE id = @con2_id AND INFO = 'LOCK TABLE t1 WRITE/* From connection 2 */';
|
|
--source include/wait_condition.inc
|
|
--sleep 1
|
|
COMMIT;
|
|
|
|
--echo
|
|
--echo # On connection 2.
|
|
--connection con2
|
|
--reap
|
|
SELECT * FROM t1;
|
|
UNLOCK TABLES;
|
|
|
|
--connection default
|
|
--let $assert_text= 'There is a 1 in t1'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 1, count, 1] = 1
|
|
--source include/assert.inc
|
|
|
|
|
|
DROP TABLE t1;
|
|
|
|
--disconnect con1
|
|
--disconnect con2
|
|
|
|
|
|
--echo # Scenario:2
|
|
--echo # T1= (LOCK TABLE)
|
|
--echo # T2= ({ W(B)},HIGH_PRIORITY)
|
|
--echo # Outcome : high prio transaction T2 waits for T1.
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connect(con2,localhost,root,,test)
|
|
|
|
--echo
|
|
--echo # On connection 2
|
|
--connection con2
|
|
let $con2_id=`SELECT CONNECTION_ID()`;
|
|
LOCK TABLE t1 WRITE/* From connection 2 */;
|
|
UPDATE t1 SET c1=2 WHERE c1=0;
|
|
|
|
|
|
--echo
|
|
--echo # On connection 1
|
|
--connection con1
|
|
let $con1_id=`SELECT CONNECTION_ID()`;
|
|
--source include/start_transaction_high_prio.inc
|
|
--send UPDATE t1 SET c1=1 WHERE c1=0
|
|
|
|
--echo # On connection 2
|
|
--connection con2
|
|
--disable_query_log
|
|
eval SET @con1_id = $con1_id;
|
|
--enable_query_log
|
|
let $wait_timeout = 10;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE id = @con1_id AND INFO = 'UPDATE t1 SET c1=1 WHERE c1=0';
|
|
--source include/wait_condition.inc
|
|
--sleep 1
|
|
COMMIT;
|
|
UNLOCK TABLES;
|
|
|
|
--connection con1
|
|
--reap
|
|
COMMIT;
|
|
|
|
--connection default
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--disconnect con1
|
|
--disconnect con2
|
|
|
|
|
|
--echo # Scenario:3
|
|
--echo # T1= ({R(B)}, HIGH_PRIORITY)
|
|
--echo # T2= ({ R(B)},HIGH_PRIORITY)
|
|
--echo # Outcome: Both T1 and T2 continue as they are read only.
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connect(con2,localhost,root,,test)
|
|
|
|
--echo
|
|
--echo # On connection 1
|
|
--connection con1
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
|
|
|
|
--echo
|
|
--echo # On connection 2
|
|
--connection con2
|
|
SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
START TRANSACTION READ ONLY /* HIGH PRIORITY */ ;
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
SELECT c1 FROM t1 WHERE c1=0 LOCK IN SHARE MODE;
|
|
--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
|
|
UPDATE t1 SET c1=1 WHERE c1=0;
|
|
|
|
--connection con1
|
|
COMMIT;
|
|
--connection con2
|
|
COMMIT;
|
|
|
|
--connection default
|
|
--let $assert_text= 'There is a 1 in t1'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 0, count, 1] = 1
|
|
--source include/assert.inc
|
|
|
|
|
|
DROP TABLE t1;
|
|
|
|
--disconnect con1
|
|
--disconnect con2
|
|
|
|
|
|
--echo # Scenario:4
|
|
--echo # Start of transaction causes table locks acquired with LOCK TABLES to be released
|
|
--echo # LOCK TABLES Implicitly commit any active transaction
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0);
|
|
INSERT INTO t2 VALUES (0);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connection con1
|
|
LOCK TABLE t2 WRITE;
|
|
--echo # In_use is 1
|
|
SHOW OPEN TABLES LIKE 't2';
|
|
--source include/start_transaction_high_prio.inc
|
|
--echo # In_use is 0 , its released due to start transaction statement
|
|
SHOW OPEN TABLES LIKE 't2';
|
|
UPDATE t1 SET c1=1;
|
|
--echo # Implicitly commits active transaction
|
|
LOCK TABLE t1 READ;
|
|
UNLOCK TABLES;
|
|
|
|
|
|
--connection default
|
|
--let $assert_text= 'There is a 1 in t1'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 1, count, 1] = 1
|
|
--source include/assert.inc
|
|
|
|
|
|
DROP TABLES t1,t2;
|
|
|
|
--disconnect con1
|
|
|
|
|
|
--echo # Scenario:5
|
|
--echo # Multiple concurrent high prio transactions with the same priority.
|
|
--echo # Outcome : Transaction which is marked high prio first completes first and other high prio txns wait
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connect(con2,localhost,root,,test)
|
|
--connect(con3,localhost,root,,test)
|
|
--connect(con4,localhost,root,,test)
|
|
--connect(con5,localhost,root,,test)
|
|
|
|
--connection con1
|
|
--source include/start_transaction_high_prio.inc
|
|
--echo # connection 1:
|
|
# statement rollback won't make txn as non high prio
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t1 VALUES (0);
|
|
UPDATE t1 SET c1=1 WHERE c1=0;
|
|
|
|
--connection con2
|
|
--source include/start_transaction_high_prio.inc
|
|
--echo # connection 2: wait - update
|
|
let $con2_id=`SELECT CONNECTION_ID()`;
|
|
--send UPDATE t1 SET c1=2 WHERE c1=1 /* From connection 2 */
|
|
|
|
--connection con3
|
|
--source include/start_transaction_high_prio.inc
|
|
--echo # connection 3: wait - delete
|
|
--disable_query_log
|
|
eval SET @con2_id = $con2_id;
|
|
--enable_query_log
|
|
let $wait_timeout = 10;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE id = @con2_id AND INFO = 'UPDATE t1 SET c1=2 WHERE c1=1 /* From connection 2 */'
|
|
AND STATE = 'updating';
|
|
--source include/wait_condition.inc
|
|
let $con3_id=`SELECT CONNECTION_ID()`;
|
|
--sleep 1
|
|
--send DELETE FROM t1 WHERE c1=1 /* From connection 3 */
|
|
|
|
--connection con4
|
|
--source include/start_transaction_high_prio.inc
|
|
--echo # connection 4: wait - insert
|
|
--disable_query_log
|
|
eval SET @con3_id = $con3_id;
|
|
--enable_query_log
|
|
let $wait_timeout = 10;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE id = @con3_id AND INFO = 'DELETE FROM t1 WHERE c1=1 /* From connection 3 */'
|
|
AND STATE = 'updating';
|
|
--source include/wait_condition.inc
|
|
--echo # wait over
|
|
let $con4_id=`SELECT CONNECTION_ID()`;
|
|
--sleep 1
|
|
--send INSERT INTO t1 VALUES (1) /* From connection 4 */
|
|
|
|
--connection con5
|
|
--source include/start_transaction_high_prio.inc
|
|
--echo # connection 5: Insert goes but as it new row
|
|
INSERT INTO t1 VALUES (50);
|
|
|
|
--connection con1
|
|
--echo # connection 1:
|
|
--disable_query_log
|
|
eval SET @con4_id = $con4_id;
|
|
--enable_query_log
|
|
let $wait_timeout = 10;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE id = @con4_id AND INFO = 'INSERT INTO t1 VALUES (1) /* From connection 4 */';
|
|
--source include/wait_condition.inc
|
|
--sleep 1
|
|
COMMIT;
|
|
|
|
--connection con2
|
|
--echo # connection 2: Reap UPDATE t1 SET c1=2 WHERE c1=1
|
|
--reap
|
|
COMMIT;
|
|
|
|
--connection con3
|
|
--echo # connection 3: Reap DELETE FROM t1 WHERE c1=1
|
|
--reap
|
|
COMMIT;
|
|
|
|
--connection con4
|
|
--echo # connection 4: Reap INSERT INTO t1 VALUES (1)
|
|
--reap
|
|
COMMIT;
|
|
|
|
--connection con1
|
|
INSERT INTO t1 VALUES (3);
|
|
UPDATE t1 SET c1=4 WHERE c1=2;
|
|
let $con1_id=`SELECT CONNECTION_ID()`;
|
|
|
|
# con1 has already done commit hence its no longer high prio txn
|
|
# Insert waits for con5 to complete.
|
|
--send INSERT INTO t1 VALUES (50)
|
|
|
|
--connection con5
|
|
# Check con1 insert is waiting
|
|
--disable_query_log
|
|
eval SET @con1_id = $con1_id;
|
|
--enable_query_log
|
|
let $wait_timeout = 10;
|
|
let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE id = @con1_id AND INFO = 'INSERT INTO t1 VALUES (50)';
|
|
--source include/wait_condition.inc
|
|
--sleep 1
|
|
COMMIT;
|
|
|
|
--connection con1
|
|
--error ER_DUP_ENTRY
|
|
--reap
|
|
|
|
--connection default
|
|
--let $assert_text= 'There is a 3 in t1'
|
|
--let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE t1.c1 = 3, count, 1] = 1
|
|
--source include/assert.inc
|
|
--echo # Expected values : 1,3,4,50
|
|
SELECT * FROM t1 order by c1;
|
|
|
|
DROP TABLES t1;
|
|
|
|
--disconnect con1
|
|
--disconnect con2
|
|
--disconnect con3
|
|
--disconnect con4
|
|
--disconnect con5
|
|
|
|
|
|
--echo # Scenario:6
|
|
--echo # Mark XA transaction as high prio.
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0),(1);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connect(con2,localhost,root,,test)
|
|
|
|
--connection con2
|
|
START TRANSACTION;
|
|
UPDATE t1 SET c1=11 WHERE c1=0;
|
|
|
|
--connection con1
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
UPDATE t1 SET c1=10 WHERE c1=0;
|
|
XA END 'xa_high_prio';
|
|
XA PREPARE 'xa_high_prio';
|
|
XA COMMIT 'xa_high_prio';
|
|
|
|
--connection con2
|
|
--error ER_ERROR_DURING_COMMIT
|
|
COMMIT;
|
|
|
|
--connection default
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--disconnect con1
|
|
--disconnect con2
|
|
|
|
--echo # Scenario:7
|
|
--echo # Multiple XA transaction as high prio.
|
|
--echo # Outcome : detect deadlock .
|
|
|
|
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (0),(1);
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
--connect(con2,localhost,root,,test)
|
|
--connect(con3,localhost,root,,test)
|
|
|
|
--connection con2
|
|
START TRANSACTION;
|
|
UPDATE t1 SET c1=11 WHERE c1=0;
|
|
|
|
--connection con1
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
XA START 'xa_high_prio' /* HIGH PRIORITY */ ;
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
UPDATE t1 SET c1=10 WHERE c1=0;
|
|
|
|
--connection con3
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='+d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
XA START 'second_xa_high_prio' /* HIGH PRIORITY */ ;
|
|
--disable_query_log
|
|
SET GLOBAL DEBUG='-d,dbug_set_high_prio_trx';
|
|
--enable_query_log
|
|
--send UPDATE t1 SET c1=13 WHERE c1=0
|
|
|
|
--connection con1
|
|
XA END 'xa_high_prio';
|
|
XA PREPARE 'xa_high_prio';
|
|
XA COMMIT 'xa_high_prio';
|
|
--disconnect con1
|
|
|
|
--connection con2
|
|
--error ER_ERROR_DURING_COMMIT
|
|
COMMIT;
|
|
--disconnect con2
|
|
|
|
--connection con3
|
|
--echo Reap UPDATE t1 SET c1=13 WHERE c1=0;
|
|
--reap
|
|
--disconnect con3
|
|
|
|
--connection default
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#21075892 SHUTDOWN HANG, WAITING FOR ASYNC ROLLBACK TO FINISH
|
|
--echo #
|
|
|
|
--connect(con1,localhost,root,,test)
|
|
|
|
CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 values(10, 10), (20, 20), (30, 30);
|
|
|
|
--echo #connection 0
|
|
--connection default
|
|
START TRANSACTION;
|
|
--echo #Lock record (col1 = 20)
|
|
UPDATE t1 set col2 = 25 where col1 = 20;
|
|
|
|
--echo #connection 1
|
|
--connection con1
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
--source include/start_transaction_high_prio.inc
|
|
|
|
--echo #Semi-consistent read: Attempt lock (col1 = 20) and skip in SQL
|
|
UPDATE t1 SET col2 = 20 where col1 > 10 and col1 <30 and col2 = 30;
|
|
COMMIT WORK;
|
|
|
|
--echo #connection 0
|
|
--connection default
|
|
ROLLBACK WORK;
|
|
DROP TABLE t1;
|
|
|
|
--disconnect con1
|
|
|
|
--source include/wait_until_count_sessions.inc
|