875 lines
22 KiB
Plaintext
875 lines
22 KiB
Plaintext
#
|
|
# WL#6631: Detect transaction boundaries
|
|
#
|
|
########################################################################
|
|
#
|
|
# set up: save settings
|
|
#
|
|
SET autocommit=1;
|
|
# if we track CHARACTERISTICS, we must also track the tx_* sysvars!
|
|
SELECT @@session.session_track_system_variables INTO @old_track_list;
|
|
SET @track_list= CONCAT(@old_track_list, ",transaction_isolation,
|
|
transaction_read_only");
|
|
SET SESSION session_track_system_variables=@track_list;
|
|
SELECT @@session.session_track_state_change INTO @old_track_enable;
|
|
SET SESSION session_track_state_change=TRUE;
|
|
SELECT @@session.session_track_transaction_info INTO @old_track_tx;
|
|
FLUSH STATUS;
|
|
########################################################################
|
|
#
|
|
# test "STATE" tracking: transaction type transitions
|
|
#
|
|
SET SESSION session_track_transaction_info="STATE";
|
|
# 3.1.1.1.1 "explicit transaction active"
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
# 3.1.1.1.1 ending explicit transaction explicitly
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# 3.1.1.1.1 ending explicit transaction implicitly
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
DROP TABLE t1;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# 3.1.1.2 "no work attached"
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
SET @dummy=0;
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# 3.1.1.1.2 "implicit transaction active"
|
|
#
|
|
SET autocommit=0;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- autocommit
|
|
-- OFF
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
INSERT INTO t1 VALUES (1);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I___W___
|
|
|
|
SELECT f1 FROM t1 LIMIT 1 INTO @dummy;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I_R_Ws__
|
|
|
|
SELECT f1 FROM t1;
|
|
f1
|
|
1
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I_R_WsS_
|
|
|
|
BEGIN WORK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
DROP TABLE t1;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
SELECT RAND(22) INTO @dummy;
|
|
COMMIT;
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
SET TRANSACTION READ ONLY;
|
|
SET TRANSACTION READ WRITE;
|
|
SELECT RAND(22) INTO @dummy;
|
|
SET TRANSACTION READ WRITE;
|
|
INSERT INTO t1 VALUES (1);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I___W___
|
|
|
|
SET TRANSACTION READ WRITE;
|
|
ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
|
|
DROP TABLE t1;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
SET autocommit=1;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- autocommit
|
|
-- ON
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
# read with and without result set:
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
CREATE TABLE t2 (f1 INT) ENGINE="InnoDB";
|
|
INSERT INTO t1 VALUES (123);
|
|
BEGIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
SELECT f1 FROM t1;
|
|
f1
|
|
123
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R___S_
|
|
|
|
COMMIT AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
INSERT INTO t2 SELECT f1 FROM t1;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_W___
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
########################################################################
|
|
#
|
|
# test "CHARACTERISTICS" tracking
|
|
#
|
|
SET SESSION session_track_transaction_info="CHARACTERISTICS";
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_____
|
|
|
|
START TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ WRITE;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
# state is again "we have an empty transaction", so make no state item
|
|
START TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ ONLY;
|
|
|
|
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_____
|
|
|
|
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
|
|
|
|
# chain read chistics, but not snapshot:
|
|
COMMIT AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ ONLY;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
SET TRANSACTION READ ONLY;
|
|
ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
|
|
# will create an empty characteristics item by convention, plus 0 state
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# chistics: READ ONLY
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
# chistics: READ ONLY + ISOL RR
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION READ ONLY;
|
|
|
|
# chistics: READ ONLY + ISOL RR
|
|
SET TRANSACTION READ ONLY;
|
|
# chistics: READ WRITE + ISOL RR
|
|
SET TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION READ WRITE;
|
|
|
|
# chistics: READ WRITE + ISOL RR
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
# chistics: READ ONLY + ISOL SER
|
|
SET TRANSACTION READ ONLY, ISOLATION LEVEL SERIALIZABLE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ ONLY;
|
|
|
|
# chistics: READ ONLY + ISOL SER
|
|
BEGIN WORK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION READ ONLY;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
SET SESSION transaction_read_only=0;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_read_only
|
|
-- OFF
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
# one-shot (different from session default)
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ ONLY;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# one-shot (repeats session default)
|
|
SET TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ WRITE;
|
|
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ WRITE;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# session
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_isolation
|
|
-- READ-UNCOMMITTED
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
# "isolation" one-shot is set, and added to chistics tracker (=> 1 item)
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
|
|
# "read-only" one-shot is set, and added to chistics tracker (=> 2 items)
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION READ ONLY;
|
|
|
|
# setting the session default:
|
|
# - we receive "changed variable" for @@session.transaction_read_only
|
|
# - "read-only" one-shot is cleared, disappears from chistics tracker
|
|
# - "isolation" one-shot remains set, and remains in chistics tracker
|
|
SET SESSION TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_read_only
|
|
-- ON
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
|
|
# chistics: isolation level is READ COMMITTED (from one-shot), READ ONLY
|
|
# or READ WRITE not given, as we're using session default again
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# chistics: READ ONLY
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
# chistics: READ ONLY, READ COMM
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION READ ONLY;
|
|
|
|
# chistics: SESSION resets ISOL one-shot, READ ONLY remains
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_isolation
|
|
-- READ-UNCOMMITTED
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
# chistics: SESSION resets READ one-shot, nothing remains
|
|
SET SESSION TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_read_only
|
|
-- ON
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_isolation
|
|
-- REPEATABLE-READ
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
SET SESSION TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- transaction_read_only
|
|
-- OFF
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
|
|
# show that START TRANSACTION READ ... overrides SET TRANSACTION READ ..
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
START TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ WRITE;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# chistics AND CHAIN
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ ONLY;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ ONLY;
|
|
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
|
|
START TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION READ ONLY;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
ROLLBACK AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION READ ONLY;
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# show that session_track_transaction_info="STATE" will hide some edges
|
|
SET session_track_transaction_info="STATE";
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
# normal syntax: TR->T->0
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_____
|
|
|
|
COMMIT AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# normal syntax: T->T->0
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
# state does not change, and therefore isn't reported
|
|
COMMIT AND CHAIN;
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# pathological syntax: TR->TR->0
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_____
|
|
|
|
# state does not change, and therefore isn't reported
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# show that session_track_transaction_info="CHARACTERISTICS" shows more edges
|
|
SET session_track_transaction_info="CHARACTERISTICS";
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
# normal syntax: TR->T->0
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_____
|
|
|
|
COMMIT AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# normal syntax: T->T->0
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
COMMIT AND CHAIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION;
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# pathological syntax: TR->TR->0
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R_____
|
|
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
# chistics and interaction of implicit trx and explicit trx
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
SET autocommit=0;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- autocommit
|
|
-- OFF
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
INSERT INTO t1 VALUES(1);
|
|
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
SET TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ WRITE;
|
|
|
|
SET TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ ONLY;
|
|
|
|
SET TRANSACTION READ WRITE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION READ WRITE;
|
|
|
|
INSERT INTO t1 VALUES(1);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I___W___
|
|
|
|
SET TRANSACTION READ WRITE;
|
|
ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
|
|
START TRANSACTION READ ONLY;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION READ ONLY;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
DROP TABLE t1;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
|
|
|
INSERT INTO t1 VALUES(1);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I___W___
|
|
|
|
# implicit commit (chistics item here, clearing isolation level):
|
|
ALTER TABLE t1 ADD COLUMN f2 INT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
|
|
INSERT INTO t1 VALUES(2,2);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- I___W___
|
|
|
|
# implicit commit (no chistics item here):
|
|
ALTER TABLE t1 ADD COLUMN f3 INT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
START TRANSACTION;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- START TRANSACTION;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
DROP TABLE t1;
|
|
SET autocommit=1;
|
|
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
|
|
-- autocommit
|
|
-- ON
|
|
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
SET session_track_transaction_info="STATE";
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
|
|
########################################################################
|
|
#
|
|
# show that table access reporting works in Stored Functions (SF)
|
|
#
|
|
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
|
|
INSERT INTO t1 VALUES (1);
|
|
# create func1() in system table:
|
|
CREATE FUNCTION func1()
|
|
RETURNS INTEGER
|
|
BEGIN
|
|
SET @dummy = 0;
|
|
IF (SELECT * FROM t1) THEN
|
|
SET @dummy = 1;
|
|
END IF;
|
|
RETURN @dummy;
|
|
END|
|
|
# func1() reads a trx table (and is read from a system table!):
|
|
BEGIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
SELECT func1();
|
|
func1()
|
|
1
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_R___S_
|
|
|
|
COMMIT;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
DROP TABLE t1;
|
|
# drop func1() from system table:
|
|
DROP FUNCTION func1;
|
|
#
|
|
# log tables
|
|
#
|
|
SET @old_log_output= @@global.log_output;
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
SET @old_general_log= @@global.general_log;
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
SET @old_general_log_file= @@global.general_log_file;
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
TRUNCATE TABLE mysql.general_log;
|
|
SET GLOBAL log_output = 'TABLE';
|
|
SET GLOBAL general_log= 'ON';
|
|
BEGIN;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
SELECT 1 FROM DUAL;
|
|
1
|
|
1
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_____S_
|
|
|
|
SELECT " -> ", argument FROM mysql.general_log WHERE argument LIKE '% DUAL' AND (command_type!='Prepare');
|
|
-> argument
|
|
-> SELECT 1 FROM DUAL
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- Tr____S_
|
|
|
|
ROLLBACK;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
TRUNCATE TABLE mysql.general_log;
|
|
SET GLOBAL general_log_file= @old_general_log_file;
|
|
SET GLOBAL general_log= @old_general_log;
|
|
SET GLOBAL log_output= @old_log_output;
|
|
########################################################################
|
|
#
|
|
# XA
|
|
#
|
|
CREATE TABLE t1 (f1 int) ENGINE="InnoDB";
|
|
SET SESSION session_track_transaction_info="CHARACTERISTICS";
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
# XA ROLLBACK
|
|
XA START 'test1';
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- XA START 'test1';
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
INSERT t1 VALUES (1);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T___W___
|
|
|
|
XA END 'test1';
|
|
XA PREPARE 'test1';
|
|
XA ROLLBACK 'test1';
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# XA COMMIT
|
|
XA START 'test2', 'yy';
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- XA START 'test2','yy';
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
INSERT t1 VALUES (2);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T___W___
|
|
|
|
XA END 'test2', 'yy';
|
|
XA PREPARE 'test2', 'yy';
|
|
XA COMMIT 'test2', 'yy';
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
# XA COMMIT ONE PHASE
|
|
XA START 'test3','xx',5;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
-- XA START 'test3','xx',5;
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T_______
|
|
|
|
INSERT t1 VALUES (3);
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- T___W___
|
|
|
|
XA END 'test3','xx',5;
|
|
XA COMMIT 'test3','xx',5 ONE PHASE;
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
|
|
--
|
|
|
|
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
|
|
-- ________
|
|
|
|
SET SESSION session_track_transaction_info="OFF";
|
|
-- Tracker : SESSION_TRACK_STATE_CHANGE
|
|
-- 1
|
|
|
|
DROP TABLE t1;
|
|
########################################################################
|
|
#
|
|
# cleanup
|
|
#
|
|
SET SESSION session_track_system_variables= @old_track_list;
|
|
SET SESSION session_track_state_change=@old_track_enable;
|
|
SET SESSION session_track_transaction_info=@old_track_tx;
|