출처 : http://www.devpia.com
1.Lock의 간단한 이해..

어느 웹팀이 웹페이지 개발을 하고 있는데 하나의 컴퓨터에서 터미날서비스를 이용하여 작업을 하고 있다.

하지만 A 라는 사람이 Default라는 화일을 사용하고 있고 B라는 사람이 이것을 열려고 한다면 어떻게 될것인가??

두사용자가 모두 쓰기를 해버린다면 작업은 엉망이 될것이다. 그것을 방지하기 위해서 VS.NET툴에 소스세이프라는 것을 이용하여

한사람이 작업을 할때는 다른이가 접근을 막아주는 기능을 한다.

이것이 바로 Lock의 개념인것이다.

쉽게 다른사람이 이 파일을 쓸때 다른 사람이 쓰지 못하도록 막는것.

멀티 스레드같은 프로그램에서도 많이 사용하는 개념이기도 하고

DataBase의 데이터 처리 역시 같은 개념인 것이다

2. NoLock과 DeadLock의 이해..

NoLock에 관해서 먼저 이해를 해보도록 하겠다.

Lock을 안걸면 되는거지 NoLock은 또 무엇인가??

이렇게 이해가 와 닿을수 있다. 하지만 ADO에서 지원하는 자동 트랙잭션 처리와 같은 경우가 있기에 NoLock이 필요한 것이다

그럼 왜 Lock을 걸지 않느냐..?? 한가지 예를 들어 설명 하도록 하겠다.

어느 경매사이트에 마감 1분을 남겨두고 있다. 헌데 벤츠가 50원이다.. 사람들이 미친듯이 입찰을 하려고 달려들 것이다.

그 숫자가 만명이라고 가정해보자 입찰을 할때 Update를 시킬텐데..수만명의 사용자가 그것의 처리를 기달려야 한다면..

그 차는 헐갑에 넘어 가고 말것이다.

정말 쉬운 예로 게시판에 글을 읽었을 때 조회수가 update가 된다. 헌데 이글을 수만명의 사람들이 동시에 접근을 한다고 한다.

그럼 한사람 한사람의 업데이트를 기달리게 될 것이고 사이트의 효율성이 떨어질것이다.

등등의 많은 예들이 NoLock의 필요성을 말해준다.


다음은 DeadLock의 관한 이해를 보도록 하겠다.

DeadLock은 쉽게 표현하자면 재귀함수와 같은 무한루프라고 표현에 가깝다. 이것 역시 간단한 예제를 통해 예를 들어 보겠다.

Transaction1 (A ->B)처리

Transaction2 (B ->A)처리

저기서 A와 B에 락이 걸려있다고 가정해보자.. 어떠한가? 서로 잡고 마냥 기달리기만 하지 않겠는가?

1번은 A를 잡고 있는 상태로 2번에서 잡고 있는 상태로 B를 놔주기를 기다릴것이고 2번은 B를 잡고 있는채로 A를 놔주기를

바라고 있을것이다. 이것이 잘 운영되던 사이트가 속도가 느려지고 이유도 모르게 서비스가 중지 되는 가장큰 이유가 된다.

반드시 처리해야할.. 이것이 바로 DeadLock 이라는 것이다.

3.DeadLock의 해결..

데드락을 해결하기 위해서는 3가지의 방법이 존재한다. 첫번째는 타임아웃을 주어 어느 일정시간 동안 락을 기달리다가 반응이 없으면

롤백시키는 즉 타임아웃을 설정하는 것이다.

EX)

-락타임 아웃의 설정

SET LOCK_TIMEOUT 10000

- 락 타임아웃 확인

SELECT @@LOCK_TIMEOUT

- 트랜잭션 코드 추가

데드락을 해결할수 있는 첫번째 방법이였다.

두번째는 프로시저에 우위를 설정해 주는것이다. 이방법은 우위가 낮은 프로시저를 먼저

취소시킨다는 것이다. 우선 순위의 레벨은 Low와 Normal로 나타낼수 있고 Priority라는것을 이용하여 설정 할수가 있다.

EX)

SET DEADLOCK_PRIORITY LOW

go

... 트랜잭션 구문



그리고 세번재 방법은 한 프로시저가 너무 오랫동안 사용하고 있다 의심되는것을 강제로 종료 시키는 방법이다.

이것은 KILL이라는 SQL명령어를 이용하여 사용 할 수 있다.

4.DeadLock 예방하기

데드락 발생시 처리해야 하는것도 중요하지만 데드락을 미리 예방을 하여 락과 블러킹의 횟수를 줄이는것 역시 중요하다.

그럼 데드락을 예방법 첫번재로 트랜잭션안의 구문의 처리 순서를 일치 시킨다는 것이다.

예를들어 보겠다.

트랜잭션1=> A작업->B작업->C작업

트랜잭션2=> C작업->A작업->B작업

이런식으로 처리 되어진다면 둘이 동시에 처리가 된다면 분명 데드락이 발생 할 것이다. 여기서 우리가 트랙잭션2의 작업의

순서를 1과 똑같이 A작업->B작업->C작업 으로 처리를 한다면 데드락의 발생 비율을 줄일수 있을 것이다.

그리고 두번째는 트랙잭션의 처리속도를 최대한 짧게 해준다는 것이다. 짧으면 짧을수록 락 발생 확률도 줄여 들기 때문이다.

이것은 너무 당연한 것일수 있고 가장 중요한것이 된다. 그리고 마지막으로 트랜잭션의 격리 수준을 최대한 낮게 해 주는것이다.

되도록 낮은 격리 수준을 사용하면 데드락은 물론 성능도 보다 향상시 킬수 있기 때문이다.

데드락의 예방은 무엇보다도 가장 기복적으로 락의 유지 시간을 보다 짧게 해주는것이 가장 중요할 것이다.

*TIP(출서: 모사이트였는데-_-)

HOLDLOCK : 필요한 테이블, 행 또는 데이터 페이지가 더 이상 필요 없게 되자마자 해제하지 않고 트랜잭션이 완료될 때까지 공유 잠금을 보유한다. HOLDLOCK은 SERIALIZABLE과 같은 의미.

NOLOCK : 공유 잠금을 실행하거나 단독 잠금을 유지하지 않음. 이 옵션을 적용하면 커밋되지 않은 트랜잭션이나 읽는 중 롤백된 페이지 집합을 읽을 수 있음. 커밋되지 않은 읽기가 가능합니다. SELECT 명령문에만 적용됨..

PAGLOCK 주로 단일 테이블 잠금이 취해지는 곳에서 페이지 잠금을 사용함.

READCOMMITTED : READ COMMITTED 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법을 사용하여 스캔을 수행함. 기본적으로, SQL Server 2000은 이 격리 수준에서 실행됨

READPAST : 잠겨 있는 행을 건너뜀 이 옵션을 사용하면 다른 트랜잭션이 이러한 행에 대해 잠금을 해제할 때까지 기다리지 않고 다른 트랜잭션에 의해 잠겨 있는 행을 건너뜀. 그렇지 않으면 일반적으로 결과 집합에 나타남. READPAST 잠금 참고는 READ COMMITTED 격리 수준에서 작동하는 트랜잭션에만 적용되며 행 수준 잠금 뒤만 읽음. SELECT 문에만 적용됨

READUNCOMMITTED == NOLOCK

REPEATABLEREAD : REPEATABLE READ 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법으로 스캔을 수행함.

ROWLOCK : 성긴 페이지 잠금 및 테이블 수준의 잠금 대신 행 수준 잠금을 사용함

RERIALIZABLE : SERIALIZABLE 격리 수준에서 실행되는 트랜잭션과 같은 잠금 방법으로 스캔을 수행함. HOLDLOCK과 같음

TABLOCK : 세부적인 행 또는 페이지 수준 잠금 대신 테이블 잠금을 사용함. SQL Server는 명령문이 끝날 때까지 이 잠금을 보유함. 그러나 HOLDLOCK을 함께 지정했으면 트랜잭션이 끝날 때까지 잠금이 보유됨.

TABLOCKX : 테이블에 대해 단독 잠금을 사용함. 이 잠금을 사용하면 다른 트랜잭션이 테이블을 읽거나 업데이트할 수 없고 명령문이나 트랜잭션이 끝날 때까지 보유됨

UPDLOCK : 테이블을 읽는 중 공유 잠금 대신 업데이트 잠금을 사용하며 명령문이나 트랜잭션이 끝날 때까지 보유됩니다. UPDLOCK을 사용하면 다른 트랜잭션이 읽는 것을 차단하지 않고 데이터를 읽을 수 있고 마지막으로 읽은 후 데이터가 변경되지 않으며 나중에 업데이트할 수 있습니다.

XLOCK : 명령문에 의해 처리되는 모든 데이터에 대해 트랜잭션이 끝날 때까지 보유될 단독 잠금을 사용합니다. 이 잠금은 PAGLOCK 또는 TABLOCK으로 지정할 수 있으며 이 경우 단독 잠금이 해당 세부성 수준에 적용됩니다.

데이터 원본 이름(DSN)은 데이터에 액세스하는 데 필요한 드라이브와 기타 정보를 참조하기 위해 ODBC(Open Database Connectivity)가 사용하는 논리 이름이다.
Internet Information Services에서 Microsoft SQL Server 데이터베이스와 같은 ODBC 데이터 원본에 연결하기 위해 사용한다.
이 이름을 설정하려면 제어판에서 ODBC 도구를 사용한다.

ODBC DSN 항목을 사용하여 연결 문자열 값을 외부적으로 저장하면 연결 문자열에 필요한 정보가 간단해진다.
이렇게 하면 데이터 원본이 코드 자체에 완전히 투명하게 변경된다.

* Windows 2000에서 시스템 DSN을 만드는 방법

1. 시작을 누르고 프로그램, 관리 도구를 차례로 가리킨 다음 데이터 원본(ODBC)을 두 번 누른다.

참고: Windows 2000 Professional에서는 시작을 누르고 설정을 가리키고 제어판을 누르고 관리 도구를 두 번 누른 다음 데이터 원본(ODBC)을 두 번 누른다.

2. 시스템 DSN 탭을 누른다.
3. 추가를 누른다.
4. 연결하려는 데이터베이스 유형에 해당하는 데이터베이스 드라이버를 누른 다음 마침을 누른다.
5. 데이터 원본 이름을 입력한다. 나중에 이 이름을 사용해야 하므로 기억하기 쉬운 이름을 입력한다.
6. 선택을 누른다.
7. 올바른 데이터베이스를 누른 다음 확인을 누른다.
8. 다음 두 대화 상자에서 확인을 누른다.

주의할 점

반드시 시스템 DSN을 만들어야 한다.
ADO(ActiveX Data Objects)는 사용자(또는 로컬) DSN을 인식하지 못한다.
레지스트리에 설정을 저장하기 때문에 시스템 DSN은 하드 디스크에 있는 파일에 연결 매개 변수를 저장하는 파일 DSN보다 성능이 약간 빠르다.

출처 : http://www.sqlworld.pe.kr

SQL Server의 데이터베이스를 다른 서버로 옮기기는 쉽습니다. 항상 문제가 되는 것은 다른 서버 로그인 정보를 옮기는 것입니다.(물론 몇개 안되면 다시 만들면 되지만) Master 데이터베이스를 백업받아 다른 서버에 리스토어 하면 되지만 그리 쉬운 방법은 아닙니다. DTS를 이용해서 로그인 정보를 다른 서버로 옮길 수 있으나 SQL Server 7.0에서는 패스워드를 정확히 옮기지 못하는 걸로 압니다. SQL Server 2000에서는 정확하게 로그인 정보를 옮길 수 있습니다.

이런 이유로 MS에서 SQL Server 7.0 간의 로그인 정보 이동을 위하여 제공하는 스크립트가 있습니다. 이 스크립트를 소개하고자 합니다.


1. 아래 스크립트를 수행하여 시스템 저장프로시져 sp_hexadecimal를 만듭니다.

USE master
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (255)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (255)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name

OPEN login_curs
FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd

IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END

SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr

IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group

IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END

ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END

END

ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar, ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary, ' + @txtpwd + ')'
PRINT @tmpstr
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @encryptopt = '
END

ELSE BEGIN
-- Null password
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @encryptopt = '
END

IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END

END

FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd
END

CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO



2. EXEC master..sp_help_revlogin 를 수행하면 로그인 정보를 만드는 스크립트가 만들어집니다.

3. 이 스크립트를 복사하여 새로운 서버에서 수행합니다.

※ 이 내용는 http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP의 내용을 참고한 것임을 알려드립니다.