How to set transaction isolation level (MySQL)

Question

How do I set the isolation level of MySQL 5.1 InnoDB?

By entering:

mysql> show variables like '%isola%';

The default level set for InnoDB is repeatable read.
How do I change the isolation level?

Vulgarize

Answer 1

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SESSION is optional, just limits the setting to the current session.
READ UNCOMMITTED is to be replaced with your desired level.

https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

Monkeypot

Just to make this answer even more complete: you can set the isolation level globally, for the current session, or only for the next transaction, as documented here: dev.mysql.com/doc/refman/5.5/en/set-transaction.html – Chapiter

Answer 2

You can set 4 transaction isolation levels with 4 scopes as shown below. *The doc explains how to set transaction isolation level in more detail and my answer explains how to show transaction isolation level in MySQL:

With PERSIST scope, transaction isolation level is not reset even after restarting MySQL:

SET PERSIST TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Or:

SET PERSIST transaction_isolation = 'READ-UNCOMMITTED';

Or:

SET @@PERSIST.transaction_isolation = 'READ-UNCOMMITTED';

With GLOBAL scope, transaction isolation level is reset after restarting MySQL:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Or:

SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Or:

SET @@GLOBAL.transaction_isolation = 'READ-COMMITTED';

With SESSION scope, transaction isolation level is reset after logging out of MySQL:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Or:

SET SESSION transaction_isolation = 'REPEATABLE-READ';

Or:

SET @@SESSION.transaction_isolation = 'REPEATABLE-READ';

Or:

SET transaction_isolation = 'REPEATABLE-READ';

With no scope, transaction isolation level is reset after performing the next single transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Or:

SET @@transaction_isolation = SERIALIZABLE;

Chancellery

Answer 3

Add session variable to connection string

Simply add it to the connection string, when connecting to the mysql database.

?sessionVariables=transaction_isolation='READ-COMMITTED'

You can check for the values of other isolation levels.

Judicator

It might work, but inconvenient. It will require having a separate datasource intance if you need to have a stricter level on specific query. Problem with the built-in transactions or locking mechanisms inside ORM (i.e Hibernate or Batis) that they do not guarantee data consistency in the multi-instance scenario. And another server or application may overwire data which seem to be 'locked' for modification. – Lemkul

Answer 4

My default isolation level is also was REPEATABLE READ with the example above you can change it for current session but if you want to change it entirely you can try with this command:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Tested on Mysql server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)

Dolt

This is incorrect, you need to add 'GLOBAL'. Without any SESSION or GLOBAL keyword: The statement applies only to the next single transaction performed within the session. Subsequent transactions revert to using the session value of the named characteristics. – Intrigante

@GoYun.Info, that was useful, I was looking for that, thanks. – Sharma