WITH (NOLOCK) table hint equivalent for MySQL

-- MySQL 2012. 7. 18. 17:04
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

아래의 글에 저자인 Peter처럼 나도 오랜 시간동안 MSSQL에서 쓰는 WITH(NOLOCK)과 같은 것이 MySQL에는 없을까 고민했었다.
오랜만에 유레카를 외칠만한 글을 발견하여 기쁘다.

Thank you, Peter~

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


출처 : http://itecsoftware.com/with-nolock-table-hint-equivalent-for-mysql

'-- MySQL' 카테고리의 다른 글

CentOS6에 MySQL5.5.25a 설치  (0) 2012.07.30
mysql 테이블과 인덱스 설계 시 주의사항 13가지  (0) 2012.07.24
EXPLAIN Output Format  (0) 2012.07.24
mysql버전 확인  (0) 2012.07.24
Show Processlist (like SP_WHO2)  (0) 2012.07.19
posted by 어린왕자악꿍