1. Lock의 유형
Lock Compatibility
다음은 Lock에 종류에 대해서 알아보도록 하겠습니다.
Lock의 종류에 대해서 알아보기 전에 우선 Data Manipulation Language에 대해서 다시 한 번 돌아보도록 하겠습니다. DML에는 어떠한 구문이 있는지 기억이 나시는지 모르겠습니다.
물론 Data를 반환하는 SELECT문, 입력연산을 수행하는 INSERT문, 갱신연산을 위한 UPDATE문, 삭제연산을 위한 DELETE문 등으로 나뉠 수 있다고 말씀을 드렸습니다.
이는 특성상 2가지로 분류를 할 수 있습니다. 우선 Data에 변경작업은 가하지 않고, 다만 Data,에 대한 반환작업만을 하는 SELELCT 구문과 Data에 대한 변경작업을 하는 INSERT, UPDATE, DELETE 등으로 나눌 수 있습니다. Transaction 역시 이러한 DML문장을 이용하므로 Transaction에 대해서 Lock을 거는 것 또한 비슷합니다.
| 종류 | 구문 | 특성 |
| Shared Lock(공유 Lock) - Read Lock | SELECT | 다른 Lock과 공유됨. |
| Exclusive Lock(단독 Lock) - Write Lock | INSERT, UPDATE, DELETE | 다른 Lock과는 공유되지 않음 |
SELECT문을 수행하게 되면 걸리는 Lock은 Shared Lock. 즉 공유 Lock이라고 하는 것이 걸리게 됩니다. 이는 Read Lock(읽기 Lock)라고도 합니다. 즉, SELECT문과 같이 읽기 작업을 하는 Data영역에 대해서 Lock을 거는 것을 말합니다. 기본적으로 공유 Lock은 읽기 작업 동안에만 걸리게 됩니다. 즉, SELECT문에 의해서 Data를 반환 받고 나면 바로 Lock은 해제되게 됩니다.
다음의 Exclusive Lock은 INSERT, UPDATE, DELETE 구문과 같이 Data에 대해서 변경작업을 하거나 쓰기 작업을 하는 동안 걸리게 됩니다. 이는 Transaction 전체에 대해서 Lock을 가지고 있고, Commit이나 Rollback으로 해당 Transaction을 종료하게 되면 Lock은 해제되게 됩니다.
이 두 개의 Lock 사이에는 가지고 있는 특성에 따라서 Lock을 공유할 수도, 공유하지 않을 수도 있습니다. 이러한 특성을 표현을 한 것이 다음의 표입니다.
| X(Exclusive Lock) | Shared Lock(S) | |
| X(Exclusive Lock) | × | × |
| Shared Lock(S) | × | ○ |
보시는 바와 같이 단독 Lock(Exclusive Lock)이 걸려있는 경우에는 어떠한 Lock도 같이 Lock을 걸 수 없습니다. 공유 Lock(Shared Lock)이 걸려 있는 경우에는 공유 Lock은 같이 Lock을 걸어서 사용을 할 수 있지만, Exclusive Lock을 걸 수는 없습니다.
조금 헤깔리는데요.
하나하나 설명을 드리도록 하겠습니다.
우선 Transaction A에서는 고객정보 Table에 Insert 작업(입력연산)을 수행하고 있습니다. 이러한 경우라면 Exclusive Lock가 걸리게 되는 데요. 이때 Transaction B가 같은 고객정보 Table에 Update 작업(갱신연산)을 수행하려고 합니다. 이러한 경우에 Transaction B는 Update 작업을 할 수 없게 됩니다. 이는 Transaction A가 이미 독점 Lock을 걸어서 다른 Lock을 공유할 수 없게끔 만들었기 때문에, Transaction B는 Transaction A가 작업을 마치게 될 때까지, 기다려야만 합니다.
만약 이 때 Transaction B가 갱신연산을 포기하고, 어떤 Data가 들어 있는지 SELECT문을 이용하여, Data를 반환하려 한다면, 즉 공유 Lock를 걸려고 하면 어떻게 될까요?
표에서 보시는 바와 같이 공유 Lock을 걸어달라는 요청 또한 Transaction A는 무시하게 됩니다. 즉, 고객정보 Table의 Data는 변경 중이니, Data를 보여줄 수도 없고, 다른 Transaction이 변경도 할 수 없다는 것입니다.
그럼 Transaction A가 SELECT문을 이용하여 공유 Lock을 걸었을 경우에는 어떨까요?
먼저 Transaction B가 삭제연산을 수행하려고, 독점 Lock을 요청했을 경우에는 요청을 무시하게 됩니다. 즉, Transaction A가 현재 Data를 보고 읽고 있기 때문에, 읽기 작업이 끝날 때까지 기다려야만 합니다. 그래서 Transaction B는 다시 SELECT문을 이용하여 Data반환 작업을 실행하였습니다. 이렇게 같이 읽기 작업을 하기 위해 공유 Lock을 요청하였을 경우에는 공유 Lock을 허용하게 됩니다. 현재 읽고 있는 Data에 대해서 어떠한 변경작업도 가해지지 않기 때문입니다.
이외에도 특수한 형태의 Lock들이 있습니다. 굳이 이 모든 Lock을 다 이해하고 넘어가실 필요는 없습니다. 다만 이런 것들이 추가적으로 더 있다는 것만 알고 넘어가도록 하겠습니다.
- Intent Lock(IX)
SQL Server에서 내부적으로 Lock의 충돌을 줄이기 위해서 사용됩니다. 내재된 잠금은 SQL Server가 계층 아래쪽에 있는 일부 리소스에 대해 공유(S) 잠금 또는 단독(X) 잠금을 얻으려 할 때 발생합니다. 예를 들어, 내재된 공유 잠금을 테이블 수준에서 설정한다는 것은 트랜잭션이 해당 테이블의 페이지 또는 행에 대해 공유(S) 잠금을 설정하려고 하는 것입니다. 테이블 수준에서 내재된 잠금을 설정하면 이후에 다른 트랜잭션이 해당 페이지를 포함하는 테이블에 대해 단독(X) 잠금을 얻을 수 없습니다. SQL Server는 테이블 수준에서만 내재된 잠금을 확인하여 트랜잭션이 해당 테이블에 대해 잠금을 얻을 수 있는지 확인하므로 내재된 잠금을 사용하면 성능이 향상됩니다. 이 경우 테이블의 모든 행 또는 페이지 잠금을 확인하여 트랜잭션이 전체 테이블을 잠글 수 있는지 확인할 필요가 없습니다.
- Update Lock(U)
업데이트(U) 잠금을 사용하면 일반적인 형태의 교착 상태가 방지됩니다. 일반적인 업데이트 패턴은 레코드를 읽고, 리소스(페이지 또는 행)에 대한 공유(S) 잠금을 얻은 다음 행을 수정하는 트랜잭션으로 구성되는데 행을 수정할 때는 단독(X) 잠금으로 잠금을 변환해야 합니다. 두 트랜잭션에서 리소스에 대해 공유 모드 잠금을 얻은 다음 데이터를 동시에 업데이트하려고 하면 한 트랜잭션이 단독(X) 잠금으로 잠금을 변환하려 합니다. 한 트랜잭션의 단독 잠금은 다른 트랜잭션의 공유 모드 잠금과 호환되지 않으므로 공유 모드를 단독 모드로 변환할 때는 잠금 대기가 발생합니다. 두 번째 트랜잭션이 해당 업데이트에 대해 단독(X) 잠금을 얻으려고 합니다. 이 경우 두 트랜잭션 모두 단독(X) 잠금으로 변환 중이고 각각 상대 트랜잭션이 공유 모드 잠금을 해제하기를 기다리므로 교착 상태가 발생합니다.
이러한 교착 상태를 방지하려면 업데이트(U) 잠금을 사용합니다. 한 번에 한 트랜잭션만 리소스에 대한 업데이트(U) 잠금을 얻을 수 있습니다. 트랜잭션이 리소스를 수정하면 업데이트(U) 잠금이 단독(X) 잠금으로 변환되고 그렇지 않으면 잠금이 공유 모드 잠금으로 변환됩니다.
- Schema Lock
스키마 수정(Sch-M) 잠금은 열을 추가하거나 테이블을 삭제하는 등 테이블 데이터 정의 언어(DDL) 작업이 수행 중일 때 사용됩니다.
스키마 안정성(Sch-S) 잠금은 쿼리를 컴파일할 때 사용됩니다. 스키마 안정성(Sch-S) 잠금은 단독(X) 잠금 등 다른 트랜잭션 잠금을 차단하지 않습니다. 따라서 쿼리가 컴파일되는 동안 테이블에 대한 단독(X) 잠금을 포함하여 다른 트랜잭션을 계속 실행할 수 있습니다. 그러나 테이블에서 DDL 작업은 수행할 수 없습니다.
- Bulk Update Lock
대량 업데이트(BU) 잠금은 테이블로 데이터를 대량 복사하고 TABLOCK 참고가 지정되거나 sp_tableoption을 사용하여 table lock on bulk load 테이블 옵션이 설정될 때 사용됩니다. 대량 업데이트(BU) 잠금을 사용하면 여러 프로세스가 데이터를 동시에 같은 테이블로 대량 복사할 수는 있지만, 데이터를 대량 복사하지 않는 다른 프로세스가 테이블에 액세스하는 것을 방지할 수 있습니다.
| IS | S | U | IX | SIX | X | |
| Intent shared(IS) | ○ | ○ | ○ | ○ | ○ | X |
| Shared(S) | ○ | ○ | ○ | X | X | X |
| Updated(U) | ○ | ○ | X | X | X | X |
| Intent Exclusive(IX) | ○ | X | X | ○ | X | X |
| Shared with intent exclusive(SIX) | ○ | X | X | X | X | X |
| Exclusive(X) | X | X | X | X | X | X |
2. Managing Locks
이제 SQL Server에서 Lock을 사용하는 방법에 대해서 알아보도록 하겠습니다. 먼저 앞서 설명을 드렸던 Lock이 없는 경우에 발생할 수 있는 문제점에 대해서 Lock을 사용하면, 어떻게 해결할 수 있는지. 그 종류와 어떻게 사용하면 해결되는지에 대해서 알아보고, Lock을 사용하면서 발생될 수 있는 문제점에 대해서도 알아보도록 하겠습니다.
마지막으로 Lock에 대한 현황을 볼 수 있는 여러 Option들에 대해서도 살펴보기로 하겠습니다. 먼저 Lock을 사용하는 방법에 대해서 알아보도록 하겠습니다.
- Session-Level Locking Options
SQL Server에서 Lock은 4가지의 Option으로 제공이 됩니다. 이 4가지 Option은 그 Lock의 간섭의 정도(degree of interference)에 따라서 사용되어지게 됩니다.
이를 level of isolation 또는 isolation level이라고 합니다. 저희는 고립화 수준이라는 용어를 사용하도록 하겠습니다. 고립화 수준은 SQL Server에서 정의한 것이 아니라, ANSI-SQL에서 정의한 것을 기준으로 SQL Server에서 각 Level에 해당하는 Lock를 지원하는 것입니다.
고립화 수준(isolation level)은 주어진 Transaction이 병행수행되는 Transaction의 부분으로 허용될 수 있는 간섭의 정도를 말합니다.
이러한 간섭의 정도가 어느 정도로 강력한지에 따라서 4가지 Level로 나누게 됩니다.
이러한 Isolation Level은 Session별로 정의가 가능합니다. Session은 사용자가 SQL Server로 연결되어 있는 일정 시간을 말하는 것입니다. Session에 대해서는 실습을 통해서 직접 보여드리도록 하겠습니다.
이제 Isolation Level에 대해서 하나하나 알아보도록 하겠습니다.
| ANSI - SQL Isolation Level | SQL Isolation Level | 발생되는 문제점 |
| Level 0 | READ UNCOMMITTED | Dirty Read, Nonrepeatable Read, Phantom Read |
| Level 1 | READ COMMITTED | Nonrepeatable Read, Phantom Read |
| Level 2 | REPEATABLE | Phantom Read |
| Level 3 | SERIALIZABLE |
먼저 READ COMMITTED입니다. READ COMMITTED는 MSSQL Server의 기본적인 Isolation Level입니다. 따라서 지금까지 Transaction을 정의하면서 아무런 Option도 지정하지 않았는데, 이는 아무런 Level도 지정을 하지 않으면 기본값으로 READ COMMITTED가 설정되어 사용되기 때문에 저희도 모르는 사이에 Isolation Level을 설정하여 사용하고 있었던 것입니다.
이는 변경된 Data에 대해서는 Exclusive Lock을 지정하게 됩니다. 따라서 해당 Transaction에 대해서 Commit이나 Rollback을 이용하여 Transaction을 종료하지 않은 경우에는 해당 Data를 읽어 올 수도, 변경할 수도 없습니다.
그렇다면 앞서 설명 드렸던 문제점 중에서 두개의 문제는 해결하였습니다.
READ COMMITTED으로 정의된 Transaction에서는 Lost Update와 Dirty Read는 발생되지 않게 될 것입니다. 그 이유는 실습을 통해서 설명을 드리도록 하겠습니다.
다음은 READ UNCOMMITTED입니다. READ UNCOMMITTED는 Dirty Read를 가능하게 합니다. 즉, Transaction 내의 해당 Data가 변경되었더라도, 다른 Transaction에서 해당 Data를 읽어 갈 수 있도록 Shared Lock을 허용하게 됩니다. 하지만 이미 변경한 Data에 대해서, 다른 Transaction에서 변경을 할 수 없도록 Exclusive Lock을 지원합니다. 즉 Lost Update에 대한 문제는 해결하지만, Dirty Read에 대한 문제는 발생하게 됩니다. 따라서 잘못된 Data를 반환할 수 있는 문제점을 가지고 있습니다.
다음은 REPEATABLE READ입니다. REPEATABLE READ는 SELECT한 Data에 대해서 설정된 Shared Lock이 Transaction이 종료되는 시점까지 연장되는 것을 말합니다. 즉 Transaction 안에서 SELECT문을 이용하여 반환된 Data에 대해서 다른 Transaction에서 반환을 요구하는 SELECT문장을 실행하였을 경우에는 앞서 실행되었던 Transaction이 종료될 때까지 기다려야만 합니다. 물론 해당 Data에 대한 변경은 불가능합니다.
즉, 이는 반복적으로 Data를 읽어도 같은 Data를 읽어 올 수 있도록 지원하는 Isolation Level입니다. 따라서 이는 Nonrepeatable Read라는 문제점을 해결하여 줍니다. 하지만, 이는 SELECT로 읽어온 Data에 대해서만 적용이 되기 때문에, 입력되는 Data에 대해서는 간섭을 할 수 없습니다. 따라서 Phantom Read라는 문제점은 아직도 발생되게 됩니다.
마지막으로 SERIALIZABLE은 가장 강력한 Isolation Level입니다. SERIALIZABLE은 SELECT절에 의해서 반환된 Data에 대해서 SELECT나 변경은 물론 INSERT도 불가능하게 하는 Isolation Level입니다. 따라서 이는 Phantom Read의 문제점 까지도 보완을 할 수 있습니다.
이를 정리하면 다음의 표와 같이 정리를 할 수 있습니다.
| Isolation Level | 발생되는 문제 | 해결되는 문제 | SELECT | INSERT | UPDATE | DELETE |
| READ COMMITTED | Nonrepetable Read Phantom Read | Lost Update Dirty Read | X | X | X | ○ |
| READ UNCOMMITTED | Dirty Read Nonrepeaable Read Phantom Read | Lost Update | ○ | X | X | ○ |
| REPEATABLE READ | Phantom Read | Lost Update Dirty Read Nonrepeatable Read | X | X | X | ○ |
| SERIALIZABLE | - | Lost Update Dirty Read Nonrepeatable Read Phantom Read | X | X | X | X |
어떻게 활용이 되는지에 대해서는 실습을 통해서 설명을 드리도록 하겠습니다.
Transaction에 Isolation Level을 정의하는 기본적인 구문은 다음과 같습니다.
SET TRANSACTION ISOLATION LEVEL |
보시는 것과 같이 Transaction을 시작하는 문장 앞에 Transaction의 Isolation Level을 지정하는 문장을 사용하여 해당하는 Transaction의 Isolation Level을 설정할 수 있습니다.
이렇게 Transaction의 Isolation Level을 적절하게 설정하여 사용을 한다면, Data의 일치성이나 효율적인 Data의 관리를 할 수 있을 것입니다. 하지만, Isolation Level이 강력할수록 좋은 것은 아닙니다. 예를 들어, 테이프 Table에 대해서 Serializable로 설정을 하고 모든 Data에 대해서 검색을 하였다면, 다른 사용자가 테이프에 대한 내역은 절대 볼 수 없게 됩니다. 이러한 경우 Transaction을 짧게 사용하였다면, 다른 사용자가 자신이 다른 Transaction의 Lock에 의해서 기다리고 있다는 사실 조차도 모르겠지만, 만약 사용자가 Transaction을 시작하고 종료도 하지 않은 채, 점심식사를 하러 갔다든지, 아니면 퇴근을 하였을 경우에는 다른 사용자는 테이프에 대한 Data는 아무 것도 반환을 할 수 없습니다. Transaction이 끝나는 시간까지 기다려야만 합니다. 따라서 이러한 경우, Lock에 의해서 Data를 반환하지 못하거나 갱신을 할 수 없다면, 해당 Transaction이 끝나기를 기다릴 수 있는 시간을 설정하여주면 됩니다.
이를 Locking Timeout이라고 합니다. 다음은 LOCKING TIMEOUT의 기본적인 구문입니다.
SET LOCK_TIMEOUT timeout_period |
Timeout_period의 값은 milliseconds 단위입니다. 실습을 통해서 사용 예를 보여드리도록 하겠습니다.
이러한 Timeout 속성에 대해서는 @@lock_timeout이라는 전역변수를 이용해서 정보를 얻을 수 있습니다.
원본링크 : http://www.mssql.org/LectureSQL/01_sql7/lock_02.asp
'공부방 > DB' 카테고리의 다른 글
| [MS-SQL] SA 비밀번호 변경 (0) | 2015.03.25 |
|---|---|
| [MS-SQL] SELECT ~ INTO, INSERT ~ INTO (0) | 2015.03.16 |
| [MS-SQL] 데이터 형식 변환 (0) | 2014.08.28 |
| [MS-SQL] 테이블 명, 컬럼명 변경하기 (0) | 2014.07.04 |
| [DB] 서버정보 보기 (0) | 2013.12.13 |