トランザクションID

• MySQL

(5.6)
https://dev.mysql.com/doc/refman/5.6/ja/innodb-trx-table.html

SELECT CONNECTION_ID();

--実行中トランザクション確認
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G

SQL文は一般クエリログやスロークエリログから確認

(8.0.18)
-- コネクションIDとスレッドIDの取得
select connection_id(),ps_thread_id(connection_id()),ps_current_thread_id()\G

select * from performance_schema.threads
where thread_id = ps_current_thread_id()
\G

• Oracle

(12cR1)

SELECT current_scn FROM v$database;

--実行中トランザクション確認
SELECT
a.xid
,a.xidusn
,a.start_scn
,b.sid
,b.serial#
,b.sql_exec_id
,b.prev_sql_id
FROM v$transaction a
INNER JOIN v$session b
ON a.ses_addr = b.saddr
;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

--Oracle Flashback Version Query

SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
col1,col2,col3
FROM tab1
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE col1 = 100
;

-- Oracle Flashback Transaction Query

SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('09001C00362D0300');

• PostgreSQL

(9.4)
http://tyawan080.hatenablog.com/entry/2018/01/28/075031

--現在のトランザクションIDの取得

SELECT txid_current();

--タプルごとのトランザクションID確認
SELECT tableoid,ctid,xmin,xmax,col1 FROM tab1;

--データベースで凍結済みの最大トランザクションID
SELECT datname,datfrozenxid from pg_database
where datname = 'test'
;

--テーブルで凍結済みの最大トランザクションID
SELECT relname,relfrozenxid from pg_class
where relname = 'tab1'
;

--タプルの凍結
vacuum freeze tab1;

• SQL Server

(2016)

SELECT CURRENT_TRANSACTION_ID();

SELECT * FROM sys.dm_tran_session_transactions ;

--実行中トランザクション確認
SELECT * FROM sys.dm_tran_active_transactions ;

SELECT
c.session_id, c.net_transport, c.encrypt_option,
c.auth_scheme, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.nt_domain,
s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time ,
c.most_recent_sql_handle
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.session_id = 142;

SELECT * FROM sys.dm_exec_sql_text(0x02000000226E0109ADF31A46C57B02777D9B8CB2C012FB990000000000000000000000000000000000000000);