mysql with nolock

In last two weeks more than once i was asked about WITH (nolock) in mysql database server, so thought to write here that there is no one to one statement in mysql. To understand what is WITH (nolock) we have to understand transaction isolation levels, its a property which defines when changes made by one operation will become available to another operation in database. There are four isolation levels defined by SQL standards.

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Oracle, SQL Server and MySQL supports isolation levels. When an operation starts database engine places different locks to maintain the integrity of data.

Note : Oracle does not support all four isolation levels as pointed out by David Aldridge (see comments), also oracle MVCC feature mentioned by Mark Harrison are important to read if you are using Oracle database system and concerned about read locks.

When WITH (nolock) is used in SQL Server to perform operation, it does not place shared lock (S) and also does not honor exclusive locks on table. The WITH (nolock) table hint is equivalent to READ UNCOMMITTED also known as "dirty read", it is the lowest level of isolation. If you specify table hint then it will override the current isolation level.

MySQL default isolation level is REPEATABLE READ which means locks will be placed for each operation but multiple connections can read data concurrently with out making table unavailable during read. MySQL database server does not support changing the default isolation mode as a hint like SQL Server.

SQL Server WITH (nolock) example :

SELECT * FROM TABLE_NAME WITH (nolock)

To accomplish the same in MySQL server you can change the session isolation mode using SET SESSION command.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

The above statement will work like WITH (nolock) i.e READ UNCOMMITTED data. You can also set the isolation level globally for all connections.

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

There are two system variables related to isolation level in MySQL server.

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

The first statement will return the global isolation level, the second will return only for current session.

In SQL Server you can also set the isolation level at transaction level like this.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

Add Comments

Name: *

Email: *

URL:

Comments: *

SQL DBA

Database administration and more....

SQL DBA Nav.

Interesting Links

mysql with nolock

Add Comments

SQL Server DBA