NOLOCK equivalent in MySQL

SQL Server WITH (NOLOCK) looks like this:

SELECT * FROM TABLE WITH (nolock)

To achieve the same with MySQL, we change the session isolation mode using the SET SESSION command.

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

This statement will work similar to WITH (NOLOCK) i.e READ UNCOMMITTED data. We can also set the isolation level for all connections globally:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

In addition, two system variables related to isolation also level exist in MySQL:

--global isolation level
SELECT @@global.tx_isolation; 
--session isolation level
SELECT @@tx_isolation; 

Or set the isolation level inside a transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

MySQL Isolation levels documentation is here

Advertisements

3 Responses to NOLOCK equivalent in MySQL

  1. Raul says:

    didn’t work for me, just won’t return any Data

  2. Dushyant says:

    Mohsin,
    I’m totally new to MySQL workbench and i have quick question
    How can I transfer multiple tables with the data from same database from SQL Server 2005 to MySQL workbench 5.2ce?
    It’s really appreciated if you can help me..Thanks

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: