SELECT에 LOCK을 거는 방법

DB (MSSQL)를 사용하다 보면, 가끔 SELECT에서도 LOCK을 걸어야 할 때가 있다.

예를 들면...

1. IP대장에서 아무도 사용하고 있지 않은 IP를 확인 (SELECT)
2. 확인된 IP의 사용자를 등록하여 IP 배정 (UPDATE)

위의 두가지 ACTION은 한 트랜잭션으로 처리되어야 한다.
1에서 SELECT를 수행 한 다음에 2번 UPDATE까지 완료되는 사이에 다른 세션에서
동일한 작업을 시도한다면... 한 IP가 두명에게 배정되어버리는 문제가 생길 수 있다.

일반적으로는 트랜잭션 처리를 했다 하더라도... IP가 중복배정되는 현상을 피할 수 없다. SELECT에는 LOCK이 걸리지 않기 때문이다.


이런 경우에 두 가지 해결방법이 있다.

1. LOCK HINT를 사용하여 인위적으로 SELECT에 업데이트락을 거는 방법

업데이트락은 SELECT나 INSERT 등에서도 걸 수 있고, UPDATE LOCK간에는 배타적이기 때문에 SELECT문도 동시 실행이 되지 않도록 할 수 있다.

DECLARE @IP VARCHAR(20)
BEGIN TRAN

-- 아래의 SELECT문은 동시에 실행되지 않는다.
SELECT @IP = IP_ADDR
FROM IP_TABLE
WITH (UPDLOCK)
WHERE USER = '' AND ID = (SELECT MAX(ID) FROM IP_TABLE WHERE USER = '')

WAITFOR DELAY '0:0:5'  -- SLEEP!!

UPDATE IP_TABLE SET USER = '홍길동' WHERE IP = @IP

COMMIT

SELECT @IP + ' 가 배정되었습니다.'

위의 SQL을 서로 다른 두 세션에서 연달아(5초 이내에) 실행시켜보면 서로 다른 IP가 배정되는 것을 볼 수 있다. 이때 LOCK 힌트를 주석처리하고 다시 테스트해보면 IP가 중복 배정되는 현상을 확인할 수 있다.



2. Application Lock을 사용하는 방법
Application Lock은 Critical Section 같은 동기화 개체의 MSSQL 버젼이라고 보면 된다.
퍼포먼스적인 측면에서 볼 땐 Application Lock이 더 바람직할 수도 있겠다. (업데이트락은 다른 곳에서 동시에 사용하고 있을 가능성도 있으니까..)

DECLARE @IP VARCHAR(20)
BEGIN TRAN

-- EnterCriticalSection()?? ^^;;
EXEC sp_getapplock @Resource='TESTLOCK', @LockMode='Exclusive'

SELECT @IP = IP_ADDR
FROM IP_TABLE
WHERE USER = '' AND ID = (SELECT MAX(ID) FROM IP_TABLE WHERE USER = '')

WAITFOR DELAY '0:0:5'  -- SLEEP!!

UPDATE IP_TABLE SET USER = '홍길동' WHERE IP = @IP

-- LeaveCriticalSection
EXEC sp_releaseapplock @Resource='TESTLOCK'

COMMIT

SELECT @IP + ' 가 배정되었습니다.'