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?

score 62 · Accepted Answer · edited May 06 '21 at 15:58

62

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

edited May 06 '21 at 15:58

But those new buttons though..

21,377
10
81
108

answered Oct 29 '11 at 07:08

Niet the Dark Absol

320,036
81
464
592

8

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: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html – alexpirine Feb 07 '13 at 23:23

score 5 · Answer 2 · answered Apr 24 '20 at 19:46

5

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.

answered Apr 24 '20 at 19:46

Sorter

9,704
6
64
74

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. – Andrey Doloka Mar 29 '21 at 12:02

Super Kai - Kazuya Ito · Answer 3 · 2022-09-22T15:49:38.920

You can set 4 transaction isolation levels with 4 scopes as shown below. *You can check more details on 13.3.7 SET TRANSACTION Statement.

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';

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';

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

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Or:

SET SESSION 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';

score -2 · Answer 4 · answered Sep 18 '18 at 12:07

-2

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)

answered Sep 18 '18 at 12:07

atrichkov

450
3
6

3

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. – GoYun.Info Feb 26 '21 at 16:04
@GoYun.Info, that was useful, I was looking for that, thanks. – akostadinov May 31 '23 at 10:21

How to set transaction isolation level (MySQL)

4 Answers4

Add session variable to connection string