MySQL NOLOCK syntax
MySQL NOLOCK syntax is equivalent to WITH (NOLOCK) in SQL Sever, and it is a transaction isolation level that defines how data is available during an update. With other words, it is a property that defines at what point changes made by an update operation will become available in a row, table or database to requesting processes.
This is equally important to a DBA as it is to a developer, as both need to be aware of the potentially outdated data returned by the database server. There is an equivalent mysql nolock syntax of the infamous SQL Server NOLOCK hint for mysql database server.
The official SQL standard defines four isolation levels:
READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
Oracle, SQL Server and MySQL support isolation levels. During an operation, the database engine places certain locks to maintain data integrity. Different types of locking apply to different databases (Oracle vs. MySQL), or table types (eg. MyISAM vs. InnoDB).
When WITH (NOLOCK) is used with SQL Server, the statement does not place a lock nor honor exclusive locks on table. The MySQL nolock hint equivalent is READ UNCOMMITTED, also known as “dirty read” because it is the lowest level of isolation. If we specify a table hint then it will override the current default 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.
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 server:
SELECT @@global.tx_isolation; (global isolation level) SELECT @@tx_isolation; (session isolation level)
Or set the isolation level inside a transaction:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO
what is the disadvantages of nolock. Please explain for Mysql. I think if its very usefull why its not set as default.
thanks.
Relational databases such as mysql employ a consistency model of Fully Consistent, which means that during an update, no reads can be made. MyISAM locks the entire table, while InnoDB locks the row. If you as a developer like the application to allow reads that are dirty (you may get the old or new value during an update), you can use NO LOCK to your advantage.
I use this MySQL commands to perform a SELECT with no lock, on a Web Service programmed on Java and i got an error related with isolation level. Why?
WHAT is difference between Following Scope
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
AND
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
READ UNCOMMITTED ;
somehow increases query execution time from 100 ms to 1 s