WITH (NOLOCK) table hint equivalent for MySQL

本文探讨了SQL Server中WITH(NOLOCK)提示的概念及其在MySQL中的等效设置。详细介绍了四种标准的事务隔离级别,并说明了如何在MySQL中通过改变会话或全局隔离级别来实现类似的效果。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

I don’t remember how many times I was asked about an equivalent term of the infamous “NOLOCK” hint for mysql database server, hence I thought it was worth to write about it here. “WITH (NOLOCK)” is a transaction isolation levels that defines how data is available during an update, or 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 other processes.

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 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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值