How to Find Current Transaction Level

How to find current transaction level?

Run this:

SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

docs.microsoft.com reference for the constant values.

How to find current transaction level?

Run this:

SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

docs.microsoft.com reference for the constant values.

How to show transaction isolation level (MySQL)

I did a bit of more searching on the google and found out that if have MySQL 5.1+ then you can find out the isolation level by firing below query

SELECT * FROM information_schema.session_variables
WHERE variable_name = 'tx_isolation';

How to find out the transaction isolation level used for the particular query in hibernate?

I would say that question is raised incorrectly: a query itself doesn't have any isolation level. An Isolation level is a feature of a transaction.

So you, most probably, want to know what is the isolation level of current transaction? Hence you, most probably, do not manage this level by your code. In this case it is managed by DBMS default or current settings.

One of possible ways to explore it is described here:

try {
    session = getSessionFactory().openSession();
    txn = session.beginTransaction();
    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
        }
    });
    txn.commit();
} catch (RuntimeException e) {
    if ( txn != null && txn.isActive() ) txn.rollback();
    throw e;
} finally {
    if (session != null) {
        session.close();
    }
}

How to get isolation level in current session?

Unless your driver provides a method, I don't think there's a way other than querying appropriate tables in the sysmaster database. The key information is in a table sysopendb (and also in another — syssqlstat which is not readable by the general public: SQL -272: No SELECT permission for syssqlstat).

File: find.isolation.sql

SELECT s.sid,
       s.username,
       s.feprogram,
       o.odb_dbname,
       o.odb_isolation,
  FROM syssessions AS s
  JOIN sysopendb AS o ON s.sid = o.odb_sessionid
 WHERE s.sid = DBINFO('sessionid')

The syssessions and sysopendb tables are generally readable. You could simply query sysopendb and not use syssessions at all — and you could simply fetch the odb_isolation column. The other information assures me I got the right information.

The statements that alter isolation level are:

• SET ISOLATION
• SET TRANSACTION

SET ISOLATION can be used outside a transaction (it is an Informix extension, but also the one most commonly used by Informix users). SET TRANSACTION can only be used while a transaction is running.

The other key information is decoding the isolation level. I used this script running as user informix:

for level in "read committed" "repeatable read" "serializable" "read uncommitted"
do
    sqlcmd -d sysmaster \
        -e begin \
        -x \
        -e "set transaction isolation level $level" \
        -e 'trace off' \
        -f find.isolation.sql
done

for level in "dirty read" "committed read" "committed read last committed" \
             "repeatable read" "cursor stability"
do
    a6 sqlcmd -d sysmaster -x \
        -e "set isolation to $level" \
        -e 'trace off' \
        -f find.isolation.sql
done

The output was:

+ set transaction isolation level read committed
479|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|2
+ set transaction isolation level repeatable read
480|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|5
+ set transaction isolation level serializable
481|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|5
+ set transaction isolation level read uncommitted
482|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|1
+ set isolation to dirty read
483|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|1
+ set isolation to committed read
484|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|2
+ set isolation to committed read last committed
485|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|11
+ set isolation to repeatable read
486|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|5
+ set isolation to cursor stability
487|informix|/Users/jonathanleffler/bin/sqlcmd|sysmaster|3

I used this query and the results to find syssqlstat and other tables with a column name containing iso:

$ sqlcmd -d sysmaster -e "select t.tabname, t.tabid, t.tabtype, c.colname,
>                                c.colno, c.coltype, c.collength
>                           from systables as t
>                           join syscolumns as c on t.tabid = c.tabid
>                           where c.colname matches '*iso*'"
systxptab|116|T|isolevel|25|1|2
sysxatab|117|T|isolevel|5|1|2
sysopendb|181|T|odb_isolation|7|1|2
syssqlstat|183|T|sqs_iso|3|1|2
syssqltrace|218|T|sql_isollevel|37|2|4
systrans|277|V|tx_isolevel|15|1|2
syssqlcurall|286|V|sqc_isolationlevel|3|13|200
syssqlcurses|287|V|scs_isolationlevel|3|13|200
$

The program in use is SQLCMD (available from the IIUG Software
Archive), which
I wrote to behave consistently in shell scripting contexts whereas
DB-Access doesn't.
It dates back to 1986 (before there was dbaccess; in those days, you
used isql instead — DB-Access was carved out of isql in an
evening).
It bears no relation to Microsoft's johnny-come-lately program of the
same name — except for the name and having the same general purpose
(manipulate SQL databases).
You could more or less achieve the same result using DB-Access.

How can I query the transaction-isolation level of an existing postgres session?

I don't think there's any way to look into one session from within another session. (I could be wrong.)

The only alternative I can think of is to expand your logging. In postgresql.conf, set

logging_collector = on
log_statement = 'all'

Restart the server.

This will log all SQL statements, including those that set the transaction isolation level. There are a lot of settings; log_connections might be a useful one.

So you'd assume that the isolation level is "read committed" unless you log a different isolation level.

How to check the isolation level of another connection SQL Server 2008

SELECT CASE transaction_isolation_level 
                        WHEN 0 THEN 'Unspecified' 
                        WHEN 1 THEN 'ReadUncomitted' 
                        WHEN 2 THEN 'Readcomitted' 
                        WHEN 3 THEN 'Repeatable' 
                        WHEN 4 THEN 'Serializable' 
                        WHEN 5 THEN 'Snapshot' 
                  END 
FROM sys.dm_exec_sessions 
WHERE session_id = <spid_of_other_session>

How to find started transaction?

I got the reason.

My tests use this trait \Illuminate\Foundation\Testing\DatabaseTransactions

It covers test in transaction. In the end does rollback to keep database clean.

---

Related Topics