Manage the Isolation level in MySQL

Manage the Isolation level in MySQL

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 make every transaction in serialized form.

REPEATABLE READ:
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

Parameter:
transaction-isolation = REPEATABLE-READ

Check parameter:
show variable like 'tx-isolation';

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;

Change the isolation level at 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 with following commands also:

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

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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.