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?

Improve this question
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. Commented 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. Commented Jun 6, 2017 at 17:41
  • @RickJames what if the select is the only query that is run? Commented 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? Commented Jun 7, 2017 at 9:59

1 Answer 1

Reset to default
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
Improve this answer
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! Commented Jun 9, 2017 at 12:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.