CODEWITHSUNDEEP
sqldatabaseisolation-level
velikolay
velikolay

Reputation: 33

What's the point of using high isolation-level when in autocommit mode?

I don't see a reason why anything higher than READ COMMITTED is useful in autocommit mode. Autocommit ends the transaction after each query, which in turn releases the aquired locks over the selected data. If the lock doesn't survive multiple queries you can't do consistent reads. So having higher isolation level in autocommit mode only causes locking more data => BAD

Is that correct?

Upvotes: 1

Views: 1042

Answers (2)

usr
usr

Reputation: 171246

You seem to assume that a single statement is always safe from concurrency issues just by the fact that it is one statement. This is not true. Let's make an example to intuitively see this. Compare the following two transactions:

--A
select * from T where ID = 1
select * from T where ID = 2

--B
select * from T where ID IN (1, 2)

Cramming the two reads into a single statement does not avoid any concurrency problems (at least not in all RDBMS'es and storage engines). The two transactions have identical locking and consistency properties in SQL Server for example. Some other RDBMS'es use MVCC for each statement. MVCC does not provide serializability. Only under serializability you are always safe of concurrency problems.

Whether you use one or two statements does not make any difference. The same for autocommit or not.

Note, that both versions not serializable under READ COMMITTED. So you see: There is a reason to not use READ COMMITTED and auto-commit at the same time.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 563021

I can answer regarding MySQL implementation, which may have different implementation details compared to other brands like Oracle or PostgreSQL, etc.

You're right, you should just use READ COMMITTED if you can. In MySQL, it creates fewer locks than REPEATABLE READ, and if you use only autocommit, then you don't need the consistent transaction read view of REPEATABLE READ.

In MySQL, REPEATABLE READ is the default transaction isolation level. That's kind of a pity, since it creates a bit more overhead.

You are not at risk for the single statement viewing different results if reading the same row twice, as @MK supposes. Even in READ COMMITTED, each statement is made "atomic" by creating a short-lived transaction read view. Yet it doesn't block concurrent updates. That's the magic of MVCC!

See also http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

Upvotes: 0

Related Questions