Nolock mechanism in MySQL

    Before going to Nolock mechanism,first we learn some basic terms in common SQL,listed here,

Lock: it allows different type of resource to be lock by the transaction.

When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.

A transaction is unit of work submitted as hole to database for processing.


Dirty Read is a process of reading database record without locking the record being read.

Stand the risk of reading an uncommitted row that is subsequently rolled back. Resulting in reading of local copy of record that is not consistent with what is stored in database.

Non repeatable read is a situation where a session finds itself in when it perform multiple read.

It is possible to perform the query in the same transaction more than one and show different result.

Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.

This occur when user insert a record while transaction is underway.

Shared Lock(s) is read lock it is occur when SQL server perform select statement or any other statement to read the data tell to read but do not modify the data.

When one user is reading the data he/she issue read lock so that other user do not modify data.

Exclusive Lock (x) are generally use during modification activity use to lock data being modified by one transaction.it prevent modification by another concurrent transaction.

Update Lock (u) update lock a mix of shared and exclusive lock.

Update Lock is kind of Exclusive Lock except it can be placed on the row which already has Shared Lock on it. Update Lock reads the data of row which has Shared Lock, as soon as Update Lock is ready to change the data it converts itself to Exclusive Lock. 

WITH (NOLOCK) hit

Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. 

MSSQL Syntax:

SELECT * FROM TABLE_NAME WITH (nolock)

For MySQL syntax:

 the above Nolock, MYSQL equivalent is

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT * FROM TABLE_NAME ;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

                                                OR

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT * FROM TABLE_NAME ;

COMMIT ;