Check and Change the Isolation level in MySQL or MariaDB

Manage the Isolation level in MySQL or MariaDB

InnoDB offers all four transaction isolation levels:

Serializable:
Most conservative reading level, all transaction is treated complete separated.
Always get accurate data, but performance is slow because it makes every transaction in serialized form.

REPEATABLE READ:
The default for INNODB.

READ COMMITTED:
Uses a locking strategy that is ideal for performance.

READ UNCOMMITTED:
Least amount of protection between transactions.

File used to change transactional level in MySQL:

C:\MYSQL\my sql server 5.6\my.ini

Entry:
transaction-isolation = REPEATABLE-READ

Note: Need to reboot the MySQL before my.ini file parameter change affects

Check the Current Isolation level:

SELECT @@global.tx_isolation as Global_isolation_level,@@tx_isolation as session_isolation_level;

Global_isolation_level|session_isolation_level|
----------------------+-----------------------+
REPEATABLE-READ       |REPEATABLE-READ        |


Change the isolation level at the session level:

set session transaction isolation level read committed;

Change the global transaction isolation level:

set global transaction isolation level read committed;
set global transaction isolation level serializable;

You can change it with the following commands also:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.