|
|
게시됨 2016. 7. 20. 오후 12:37:53
|
|
|
수문 개요
1. 왜 자물쇠를 도입하는가
여러 사용자가 동시에 데이터베이스에 작업을 수행할 때 다음과 같은 데이터 불일치가 발생합니다:
누락된 업데이트
두 사용자인 A와 B가 동일한 데이터를 읽고 수정하며, 한 사용자의 수정 결과가 티켓 예약 시스템과 같은 다른 수정 결과를 파괴합니다
더러운 독서
사용자 A가 데이터를 수정한 후 사용자 B가 데이터를 읽어내지만, 사용자 A가 어떤 이유에서인지 데이터 수정을 취소하면 데이터가 원래 값으로 돌아갑니다
반복해서 읽지 마세요
사용자 A가 데이터를 읽고, 사용자 B가 데이터를 읽고 수정합니다
동시성 제어의 주요 방법은 블로킹으로, 데이터 불일치를 방지하기 위해 일정 기간 동안 사용자가 특정 작업을 수행하는 것을 금지하는 것입니다
2. 수문 분류
잠금장치는 두 가지 범주로 나뉩니다:
1 . 데이터베이스 시스템의 관점에서 보면, 독점 잠금(즉, 배타적 잠금), 공유 잠금, 그리고 업데이트 잠금으로 나뉩니다
MS - SQL Server는 다음과 같은 리소스 락 패턴을 사용합니다.
잠금 모드 설명
Share(s)는 데이터를 변경하거나 업데이트하지 않는 작업(읽기 전용 작업)에 사용됩니다. 예를 들어 SELECT 문이 있습니다.
업데이트 (U)는 업데이트 가능한 리소스에 사용됩니다. 여러 세션이 읽히고, 잠기며, 발생할 수 있는 자원 업데이트가 발생할 때 흔히 발생하는 교착 상태를 방지합니다.
독점(X)은 INSERT, UPDATE, DELETE와 같은 데이터 수정 작업에 사용됩니다. 동일한 자원에서 동시에 여러 번의 업데이트가 이루어지지 않도록 하세요.
의도 잠금은 잠금장치의 계층 구조를 설정하는 데 사용됩니다. 의도 잠금의 유형은 다음과 같습니다: 의도 공유(Intent Shared, IS), 의도 배제(Intent Exclusive, IX), 그리고 의도 배제(Intent Exclusive, SIX).
스키마 락은 테이블 스키마에 의존하는 작업을 수행할 때 사용됩니다. 스키마 락의 유형은 스키마 수정(Sch -M)과 스키마 안정성(Sch -S)입니다.
대량 업데이트(BU)는 대량의 데이터를 테이블에 복사하고 TABLOCK 힌트를 지정할 때 사용됩니다.
공유 자물쇠
공유(s) 락은 동시 트랜잭션이 자원을 읽을 수 있도록 허용합니다. 자원에 공유(S) 잠금이 존재하면, 다른 어떤 트랜잭션도 데이터를 수정할 수 없습니다. 데이터가 읽히는 즉시 자원에 대한 공유 (S) 잠금을 해제하세요. 단, 트랜잭션 격리 수준이 반복 가능 또는 그 이상으로 설정되어 있지 않거나, 트랜잭션 수명 동안 잠금 힌트를 가지고 공유된 (S) 잠금이 유지되는 경우는 예외입니다.
업데이트 락
업데이트(U) 잠금은 일반적인 형태의 교착 상태를 방지합니다. 일반적인 업데이트 패턴은 레코드를 읽고, 자원(페이지 또는 행)에 대한 공유(S) 잠금을 받은 후, 한 행을 수정하는 것으로 구성됩니다. 이 경우 잠금을 독점적(X) 잠금으로 변환해야 합니다. 두 트랜잭션이 자원에 공유 모드 락을 획득한 후 동시에 데이터를 업데이트하려 하면, 한 트랜잭션은 락을 배타적(X) 락으로 변환하려고 시도합니다. 공유 모드에서 독점 잠금으로의 전환은 잠시 기다려야 하는데, 이는 한 트랜잭션의 독점 잠금이 다른 거래의 공유 모드 잠금과 호환되지 않기 때문입니다; 잠금 대기 상태가 발생합니다. 두 번째 트랜잭션은 업데이트를 위한 독점적 (X) 잠금을 얻으려 시도합니다. 교착 상태는 두 트랜잭션 모두 독점적(X) 락으로 전환되고, 각 트랜잭션이 다른 트랜잭션이 공유 모드 락을 해제할 때까지 기다리기 때문에 발생합니다.
이 잠재적 교착 상태를 피하려면 업데이트된 (U) 잠금장치를 사용하세요. 한 번에 한 번에 한 번의 트랜잭션만 자원에 대해 업데이트된 (U) 잠금을 받을 수 있습니다. 트랜잭션이 자원을 변경하면 업데이트(U) 락은 배타적(X) 락으로 변환됩니다. 그렇지 않으면 잠금장치가 공유 잠금장치로 전환됩니다.
독점 잠금장치
배타적(X) 잠금은 동시 트랜잭션이 자원에 접근하는 것을 방지합니다. 다른 트랜잭션은 배타적(X) 잠금으로 잠긴 데이터를 읽거나 수정할 수 없습니다.
의도 잠금장치
의도 잠금은 SQL 서버가 계층 구조 내 일부 기본 자원에 대해 공유(S) 락 또는 배타적(X) 잠금을 획득해야 함을 나타냅니다. 예를 들어, 테이블 수준에 공유-의도 잠금이 설치되면 거래가 테이블의 페이지나 행에 share(S) 잠금을 걸려고 함을 나타냅니다. 테이블 수준에서 의도 잠금을 설정하면 해당 페이지가 포함된 테이블에 대해 다른 트랜잭션이 독점적(X) 잠금을 획득하는 것을 방지합니다. 의도 잠금은 SQL Server가 테이블 수준에서만 의도 잠금을 검사하여 트랜잭션이 해당 테이블에서 잠금을 안전하게 획득할 수 있는지 판단하기 때문에 성능을 향상시킬 수 있습니다. 테이블의 각 행이나 페이지에 대한 잠금을 확인해 트랜잭션이 전체 테이블을 잠글 수 있는지 확인하는 대신,
의도 잠금에는 의도 공유(IS), 의도 독점적(IX), 의도 배타적 공유(SIX)가 포함됩니다.
잠금 모드 설명
의도 공유(Intent Sharing, IS)는 각 자원에 S-락을 걸어 읽기 계층 내 일부 기본 자원에 대한 의도를 나타냅니다.
의도 배타(Intent Exexcive, IX)는 트랜잭션의 의도가 계층 구조 내 일부 기본 자원을 수정하는 것임을 나타내며, 각 자원에 X-락을 걸어 수정하는 것입니다. IX는 IS의 슈퍼셋이다.
독점 의도 공유 (SIX)는 트랜잭션의 의도가 계층 구조 내 모든 기본 자원을 읽고, 각 자원에 IX 잠금을 걸어 일부 기본 자원을 수정하는 것을 의미합니다. 최상위 자원에 대해 동시 IS 잠금을 허용하세요. 예를 들어, 테이블의 SIX 락은 테이블에 SIX 락을 두어 동시 IS 락을 허용하고, 현재 수정된 페이지에 IX 락을 걸어 수정된 행에 X 락을 부여합니다. 각 자원은 일정 시간 동안 하나의 SIX 락만 가질 수 있어 다른 트랜잭션이 자원을 업데이트하지 못하게 하지만, 다른 트랜잭션은 테이블 수준의 IS 락을 획득하여 계층 구조의 기본 자원을 읽을 수 있습니다.
독점 잠금: 잠금 작업을 수행하는 프로그램만 사용할 수 있으며, 그 외의 연산은 허용되지 않습니다. 데이터 업데이트 명령을 실행할 때, SQL Server는 자동으로 배타적 락을 사용합니다. 객체에 다른 락이 존재할 때는 배타적 락을 추가할 수 없습니다.
공유 잠금: 공유 잠금에 의해 잠긴 자원은 다른 사용자가 읽을 수 있지만, 다른 사용자는 이를 수정할 수 없습니다.
업데이트 잠금: SQL Server가 데이터를 업데이트할 준비가 되면 먼저 데이터 객체를 잠가, 데이터를 수정할 수 없고 읽을 수 있도록 합니다. SQL Server가 데이터를 업데이트하고 싶다고 판단하면 자동으로 업데이트 락을 배타적 락으로 대체하며, 객체에 다른 락이 있을 때는 업데이트용 락을 추가할 수 없습니다.
2 . 프로그래머의 관점에서 보면, 이 규칙은 낙관적 잠금과 비관적 잠금으로 나뉩니다.
낙관주의 잠금장치: 잠금장치의 작업을 전적으로 데이터베이스에 의존합니다.
비관적 잠금: 프로그래머는 데이터나 객체 자체에 대한 잠금 처리를 관리합니다.
MS - SQLSERVER는 여러 사용자가 동시에 데이터베이스에서 수정을 수행할 때 비관적인 동시성 제어를 구현하기 위해 락을 사용합니다
3. 자물쇠의 입자 크기
잠금 세분성은 차단된 대상의 크기이며, 작은 차단 세분성은 동시성이 높지만 오버헤드가 크고, 큰 차단 세분성은 동시성이 낮지만 오버헤드는 작습니다
SQL Server는 행, 페이지, 키, 키 범위, 인덱스, 테이블 또는 데이터베이스에 대한 잠금 세분성을 지원합니다
자원 설명
RID 행 식별자. 테이블에서 한 행을 개별적으로 잠그는 용도로요.
인덱스에 키 줄 잠금. 직렬화 가능한 트랜잭션에서 키 범위를 보호하는 데 사용됩니다.
8킬로바이트(KB) 분량의 데이터 페이지 또는 인덱스 페이지.
확장 디스크 8개의 인접한 데이터 페이지 또는 인덱스 페이지로 이루어진 세트입니다.
표: 모든 데이터와 인덱스를 포함한 전체 표입니다.
DB 데이터베이스.
4. 잠금 시간 길이
잠금이 유지되는 시간은 요청된 수준에서 자원을 보호하는 데 필요한 시간입니다.
읽기 작업을 보호하는 데 사용되는 공유 잠금의 홀드 시간은 트랜잭션 격리 수준에 따라 달라집니다. 기본 트랜잭션 격리 수준인 READ COMMITTED가 적용되면, 공유 잠금은 읽기 페이지 지속 시간 동안만 제어됩니다. 스캔에서는 자물쇠가 다음 페이지에서 획득될 때까지 해제되지 않습니다. HOLDLOCK 프롬프트를 지정하거나 트랜잭션 격리 레벨을 REPEATABLE READ 또는 SERIALIZABLE로 설정하면, 트랜잭션이 종료될 때까지 락이 해제되지 않습니다.
커서에 설정된 동시성 옵션에 따라, 커서는 추출 파일을 보호하기 위해 공유 모드에서 스크롤 락을 획득할 수 있습니다. 스크롤 락이 필요할 때는 커서가 추출되거나 닫힐 때까지 스크롤 락이 해제되지 않습니다. 하지만 HOLDLOCK을 지정하면 스크롤 락은 거래가 끝날 때까지 해제되지 않습니다.
업데이트를 보호하기 위해 사용하는 독점 잠금은 거래 종료 시점까지 해제되지 않습니다.
만약 어떤 연결이 다른 연결이 제어하는 잠금과 충돌하는 잠금을 획득하려 하면, 잠금을 획득하려는 연결은 다음과 같은 기간 동안 차단됩니다:
충돌하는 잠금이 해제되고, 연결이 요청된 잠금을 획득합니다.
연결 타임아웃이 만료되었습니다. 기본적으로 타임아웃 간격은 없지만, 일부 앱은 무한 대기 시간을 막기 위해 타임아웃 간격을 설정합니다
SQL Server에서 잠금의 다섯 가지 맞춤화
1 교착 상태 처리 및 교착 상태 우선순위 설정
교착 상태는 여러 사용자가 서로 다른 차단을 신청하면서 끝없이 기다리는 현상으로, 신청자가 차단 권한의 일부를 가지고 다른 사용자가 가진 부분 차단을 기다리기 때문입니다
SET DEADLOCK_PRIORITY을 사용해 교착 상태 시 세션의 반응을 제어할 수 있습니다. 두 프로세스가 데이터를 잠그고, 각 프로세스가 자신의 잠금을 해제할 수 없으면, 상대방이 자신의 잠금을 해제할 때까지 그 상태가 발생한다.
2 타임아웃을 처리하고 타임아웃 시간을 고정하세요.
@@LOCK_TIMEOUT 현재 세션의 현재 잠금 타임아웃 설정을 밀리초 단위로 반환합니다
SET LOCK_TIMEOUT 설정은 애플리케이션이 자원을 차단하기 위해 문장이 기다리는 최대 시간을 설정할 수 있게 합니다. 문장의 대기 시간이 LOCK_TIMEOUT 설정보다 길면, 시스템은 자동으로 차단 문장을 취소하고 잠금 요청 타임아웃 기간이 초과되었다는 오류 메시지 1222를 애플리케이션에 반환합니다
본보기
다음 예시에서 잠금 타임아웃 기간은 1,800밀리초로 설정되어 있습니다.
SET LOCK_TIMEOUT1800
3) 트랜잭션 격리 레벨을 설정합니다.
4) SELECT, INSERT, UPDATE, DELETE 문에 대해 테이블 수준 락 힌트를 사용하세요.
5) 인덱스의 잠금 세분성을 구성
sp_indexoption 시스템 저장 프로시저를 사용해 인덱싱을 위한 잠금 세분성을 설정할 수 있습니다
6. 잠금 정보 보기
1 EXEC SP_LOCK 잠금 정보 보고를 수행
2 쿼리 분석기에서 Ctrl + 2를 눌러 잠금 정보를 확인하세요
7. 사용 주의사항
교착 상태를 피하는 방법
1. 트랜잭션을 사용할 때는 트랜잭션의 논리적 처리 과정을 단축하고, 트랜잭션을 조기에 제출하거나 롤백하세요.
2 교착 상태 타임아웃 매개변수를 합리적인 범위로 설정하세요: 3분 - 10분; 시간이 지나면 공정이 멈추는 것을 방지하기 위해 자동으로 작업이 중단됩니다;
3. 프로그램 최적화, 교착 상태 확인 및 피하기;
4. 정확한 버전을 작성하기 전에 모든 스크립트와 SP를 신중히 테스트하세요.
5 모든 SP는 오류 처리(@error를 통해) 반드시 있어야 합니다
6 SQL SERVER 트랜잭션의 기본 수준을 변경하지 마십시오. 강제 잠금은 권장하지 않습니다
문제 해결하기 행 테이블 데이터베이스를 잠그는 방법
8. 잠금장치에 관한 여러 질문
1 테이블의 한 줄을 잠그는 방법
트랜잭션 격리 수준 설정 READUNCOMMITD
SELECT *ROWLOCK 테이블에서, ID = 1
2 데이터베이스에서 테이블 잠금하기
*테이블에서 WITH(홀드락) 선택
잠금 진술:
sybase:
업데이트 테이블 집합 col1 = col1 여기서 1= 0;
MSSQL:
1=0인 표 (tablockx)에서 col1을 선택합니다;
oracle:
독점 모드의 잠금(LOCK) 테이블 ;
잠금이 잠긴 후에는 잠긴 사용자가 잠금 해제할 때까지 아무도 작동할 수 없으며, 커밋 또는 롤백으로 잠금 해제가 이루어집니다
몇 가지 예시가 인상을 더 깊게 만드는 데 도움이 됩니다
세트 테이블1(A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) 배타적 잠금장치
두 개의 새로운 연결
첫 번째 연결에서 다음 문장을 실행하세요
트란 시작
업데이트 표1
집합 A= ' aa '
여기서 B= ' b² '
지연 대기' 00:00:30' --30초 대기
커밋 트랜
두 번째 연결에서 다음 문장을 실행하세요
트란 시작
*표1에서 선택
여기서 B= ' b² '
커밋 트랜
위의 두 문장이 동시에 실행될 경우, 선택 쿼리는 업데이트가 실행될 때까지 기다려야 하며, 즉 30초를 기다려야 합니다
2) 공유 잠금
첫 번째 연결에서 다음 문장을 실행하세요
트란 시작
select *table1에서 홀드락 - 홀드락이 인위적으로 잠금에 추가됩니다
여기서 B= ' b² '
지연 대기' 00:00:30' --30초 대기
커밋 트랜
두 번째 연결에서 다음 문장을 실행하세요
트란 시작
표1에서 A,C 선택하세요
여기서 B= ' b² '
업데이트 표1
집합 A= ' aa '
여기서 B= ' b² '
커밋 트랜
위의 두 문장이 동시에 실행되면 두 번째 연결에서 선택 쿼리를 실행할 수 있습니다
업데이트는 첫 트랜잭션이 공유 락을 해제하고 독점 락으로 전환할 때까지 30초를 기다려야 실행됩니다
3) 교착 상태
추가된 표2(D,E)
D E
D1 E1
D2 E2
첫 번째 연결에서 다음 문장을 실행하세요
트란 시작
업데이트 표1
집합 A= ' aa '
여기서 B= ' b² '
지연 대기 시간 00:00:30'
업데이트 표2
집합 D= ' d5 '
여기서 E= ' e1 '
커밋 트랜
두 번째 연결에서 다음 문장을 실행하세요
트란 시작
업데이트 표2
집합 D= ' d5 '
여기서 E= ' e1 '
지연 대기 시간 00:00:10'
업데이트 표1
집합 A= ' aa '
여기서 B= ' b² '
커밋 트랜
동시에 시스템은 교착 상태를 감지하고 프로세스를 중단합니다
덧붙이자면:
SQL Server 2000에서 지원하는 테이블 수준 잠금 힌트
HOLDLOCK은 전체 트랜잭션이 완료될 때까지 공유 잠금을 유지하며, 잠긴 객체가 필요 없을 때 즉시 해제되어야 하며, 이는 직렬화 가능한 트랜잭션 격리 수준과 같습니다
NOLOCK 문은 공유 락을 발행하지 않고 실행되며, 이는 READ UNCOMMITTED의 트랜잭션 격리 레벨과 동일한 더티 리드를 허용합니다
PAGLOCK은 하나의 테이블 잠금을 사용하는 다중 페이지 잠금을 사용합니다
READPAST는 SQL 서버가 잠긴 라인을 건너뛰고 트랜잭션을 실행할 수 있게 하며, READ Uncommitd의 트랜잭션 격리 레벨에서는 페이지, 존, 테이블 락은 건너뛰지 않고 RID 락만 건너뛸 수 있습니다
ROWLOCK은 rowlock의 사용을 강제합니다
TABLOCKX는 거래 중 다른 트랜잭션이 테이블을 사용하지 못하도록 배타적 테이블 수준 잠금을 강제합니다
UPLOCK은 공유 잠금이 없는 테이블을 읽을 때 업데이트를 강제로 사용합니다
앱 잠금:
애플리케이션 잠금은 SQL Server 자체가 생성하는 잠금이 아니라 클라이언트 코드에 의해 생성된 잠금입니다
애플리케이션 락을 처리하는 두 가지 프로세스
sp_getapplock 잠금 애플리케이션 자원
sp_releaseapplock 애플리케이션 자원 잠금 해제
참고: 데이터베이스에서 테이블을 잠그는 것의 차이점
SELECT *FROM table WITH( HOLDLOCK) 다른 트랜잭션은 테이블을 읽을 수 있지만, 업데이트하거나 삭제할 수는 없습니다
SELECT *FROM table WITH(TABLOCKX) 다른 트랜잭션은 테이블을 읽거나 업데이트하거나 삭제할 수 없습니다
|
이전의:그 c... http://localhost:111/xxx.svc 듣는 종착점이 없었다.다음:SQL 락 NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
