sql – Scope for transaction isolation level

Question:

Good day. There is a sql set transaction isolation level statement. What is the scope of this instruction? So I didn’t apply it, and when does it end? Indeed, in sql there is no curly brace operator ( {} ) with which you can determine the scope of a variable in languages ​​such as java, C # or C ++. Thanks in advance

Answer:

The SQL standard describes this expression in part E152. Publicly applicable standards are not available, but draft versions of the standards can be read freely.

Set the characteristics of the next SQL-transaction for the SQL-agent.

NOTE 522 – This statement has no effect on any SQL-transactions subsequent to the next SQL-transaction.

According to the standard, it acts on a subsequent transaction and terminates immediately after it.

Well, a standard is a standard, and specific implementations may have their own opinion. (checked only for PostgreSQL, for the rest of the interpretation of the manual at the given link)

PostgreSQL

Affects only the current transaction, can be called only after the start of the transaction and before any request manipulating data. Outside an open transaction, it has no effect and issues a warning about this when used.

begin;
set transaction isolation level REPEATABLE READ ;
-- any queries

MySQL

The description conforms to the standard, without specifying GLOBAL or SESSION, the expression acts on the next transaction and only on it.

Oracle

The expression sets the parameters of the transaction and directly starts the transaction. Affects only this transaction.

MS SQL

Continues for the current connection until explicitly changed.

SQLite

Doesn't support the team at all.

sqlite> set transaction isolation level REPEATABLE READ ;
Error: near "set": syntax error
Scroll to Top