Mysql – How does the transactions interact if you set a different isolation level per connection

concurrencyMySQLtransaction

I read that I can have different isolation levels per connection and in the server.
The default isolation level is REPEATABLE-READ.
So if I have a transaction that issues a SERIALIZED to the client connection, how does it interact with other transactions from other client connections? Do they all end up being serialized? From set transaction seems not:

With the SESSION keyword, the statement sets the default transaction
level for all subsequent transactions performed within the current
session.

But this seems useless to me. I mean if I set serialized to a client connection then I would want all currently running transactions to be sequential, right? Otherwise what's the point?

Best Answer

Every connection will work with its own transaction isolation level. Setting SERIALIZABLE level for one connection doesn't change isolation level of other connections (new or existing).

However interactions between transactions performed on different isolation level are quite complicated. Everything depends on many factors:

If different transactions operate on the same table/tables?
How your database engine implements transaction isolation levels (pessimistics versus optimistics locking)?
What is characteristics of access from your app to particular tables?

If you have to work with SERIALIZABLE level for some connections in your app you should know answers for above questions.

For example. If you have database with pessimistics locking (e.g. IBM DB2) and you have long running transactions with SERIALIZABLE level it is very probable that your system would hang (or slow down).

Related Solutions

Sql-server – “When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained”

A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.

The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the start of any batch, to ensure your code isn't impacted by somebody else being sloppy, and return it to the default at the end.

This behaviour, while baffling, is apparently by-design.

MySQL 5.7: Scope of SET TRANSACTION ISOLATION LEVEL for SELECT

(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

Best Answer

Related Solutions

Sql-server – “When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained”

MySQL 5.7: Scope of SET TRANSACTION ISOLATION LEVEL for SELECT

Related Question