2

The documentation states that without the GLOBAL or SESSION keyword, the isolation level will be valid for the next transaction and will be reverted to the session default after the transaction was commited.

With autocommit enabled, what is the scope of an transaction isolation level for a SELECT statement?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM mytable WHERE id = 1;

Is a SELECT statement a complete transaction, meaning the isolation level will be reverted to session default after the SELECT was executed, or does a SELECT somehow not represent its own transaction, meaning that the isolation level will not be restored after a SELECT statement?

4
  • My understanding, in MS SQL at least, is that every statement (including SELECT) is a transaction. Whether you specify a TRANSACTION block or not just tells SQL whether to encapsulate multiple statements into one and when to commit. To which your ISOLATION LEVEL would apply to that block. Maybe someone has additional insight.
    – Jacob H
    Jun 6, 2017 at 13:21
  • Let's see the entire transaction; it is hard to discuss a single SELECT by itself, without knowing how the resultset will be used.
    – Rick James
    Jun 6, 2017 at 17:41
  • @RickJames what if the select is the only query that is run?
    – Jacob H
    Jun 6, 2017 at 18:02
  • The question for me is, is a SELECT a selfcontained transaction if autocommit is enabled without a BEGIN statement, or do subsequent queries, be it SELECT or UPDATE, count as part of the same transaction?
    – rewb0rn
    Jun 7, 2017 at 9:59

1 Answer 1

2

(To answer question in comment)

There are three ways to build a transaction:

(1)

BEGIN;
any number of statements  -- all in one transaction
COMMIT;

(2)

SET AUTOCOMMIT = ON;
...
statement  -- This is a transaction
...
statement  -- This is a different transaction

(3)

SET AUTOCOMMIT = OFF;
...
statement
statement
COMMIT;  -- 2 statements in a transaction

statement
statement
COMMIT;  -- another 2 statements in a different transaction
1
  • Okay so based on this answer it would mean that with autocommit enabled, the TRANSACTION ISOLATION LEVEL would be reverted to the session default after the SELECT statement has been executed, hence I am marking this as the correct reply. Thanks!
    – rewb0rn
    Jun 9, 2017 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.