OS : Microsoft Windows 2000 Server / Advanced Server
SQL : Microsoft SQL Server 2000 Standard / Enterprise Edition
그동안 Oralce을 많이 이용했었습니다.
저의 개념으로 이해가 안되는 부분이 있어서 질문드립니다.
현재 제가 아는 System이 MS SQL Server 2000 Standard를 사용하고 있는데
원인은 정확히 파악할 수 없으나 수시로(LOCK)이 걸린다고 합니다.
Strored Procedure는 몇개 이용하고 있습니다. 이 SP는 스케쥴러(작업)에 의해 Call되어지고 있구요...
해결책으로 애플리케이션이든 SP든 모든 Query에 (NOLOCK)을 추가해 주었더군요.
DB를 알고있는 관계자들이
"MS SQL Server는 기본적으로 Select문을 날리면 Lock이 걸린다"
"Default다"
라고들 합니다.
책을 찾아보고 인터넷을 뒤져봐도 이런내용은 없는것 같은디...
맞는 이야기인지,
맞다면 왜 단순 Select문을 던지는데 기본적으로 Lock을 잡아야 하는지 답변 좀 부탁 합니다.
----------------------------------------
대학원에서 간단히 테이블 한 두개 만들어 놓고 VB로 연결해서 DB Programming을 해 본게 전부라 오라클에 대한 지식이 거의 없는터라 오라클에서 왜 그렇게 동작하는지 정말 그런지 뭐 그런건 잘 모른채 SQL Server의 격리 수준에 대한 내용이 생각나서 답변해 주기위해 좀 더 검색해 보며 내가 알고 있는 내용을 정리하던 중, 오라클과 SQL Server의 병행제어 방법이 다르다는 것을 알게 되었다.
혹 관심이 있는 분들이나 나 자신을 위해 간단히 정리해 보려 합니다.
처음 위 질문을 보고, 'SQL Server의 기본 isolation level이 read committed니가 그런거겠지... 그럼 Oracle은 그게 아닌가?'라고 생각하고 Oracle쪽을 찾다가 다음과 같은 글을 보았습니다.
'SQL Server와 Oracle의 격리와 병행제어에는 차이가 있습니다....'
그래서 당연히 default isolation level이 달라서 나타나는 차이라고 생각하고 답글을 올려버렸습니다. --;; (사실 상당히 주의하고 답글을 올리는 편임에도 불구하고 실수를 했죠).
그리고 이 내용에 흥미가 생겨 블로그에 정리하다가 그게 아니라는걸 알게되었습니다.
다시 검색하다가 아래의 내용을 보았습니다.
'The default isolation level for Oracle is read committed'
쩝 --;; 그럼 isolation level이 같은데 왜 그럴까?? 차이는 병행제어 알고리즘의 차이입니다.
데이터의 일관성과 데이터의 동시성
여러 가지 정의가 있겠지만, Oracle 9i의 교재에서는 다음과 같이 정의하고 있습니다.
Data concurrency means that many users can access data at the same time.
Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.
동시성이란 다수의 사용자가 동시에 데이터에 접근할 수 있어야 한다는 의미이고, 일관성이란, 각각의 사용자가 자신의 트랜잭션이나 다른 사람의 트랜잭션에 의해 변경된 내용을 포함하여 일관된 값을 본다라는 의미라고 합니다.
다수의 사용자가 데이터베이스에 동시에 접근하게 될 경우, 각각의 사용자가 다른 데이터에 접근을 한다면 문제 없겠지만, 같은 데이터에 접근을 한다면 잘못된 데이터의 변경을 막기 위해선 반드시 어떠한 제어를 해 주어야 합니다.
그것이 병행 제어(concurrency control)입니다.
대학원 수업시간에 배운 내용 중 기억 나는 부분만 소개하자면...
병행 제어 알고리즘은 다음과 같은 분류의 알고리즘이 있습니다.
* 낙관적 병행제어 알고리즘 : 다른 사용자가 동시에 같은 데이터에 접근할 경우가 적다고 보고 구현한 알고리즘
* 비관적 병행제어 알고리즘 : 낙관적과는 반대로 다른 사용자가 동시에 같은 데이터에 접근할 경우가 많다고 보고 구현한 알고리즘
위의 내용은 머리속에 남은 조그마한 지식의 일부분이고..
대학원때 배웠던 교제 중, Distributed Systems Concepts and Design 이라는 책에 나온 내용을 소개하자면,
병행제어 알고리즘에는 다음과 같은 것 들이 있습니다.
1. Locking (비관적 병행제어 알고리즘)
2. optimistic concurrency control (낙관적 병행제어 알고리즘)
3. timestamp ordering (비관적 병행제어 알고리즘)
각 알고리즘에 대한 소개는 skip하고 ^^''
SQL Server에서 사용하는 병행제어 알고리즘은 Locking입니다.
그러나 Oracle에서 사용하는 병행제어 알고리즘은 일반적인 Locking과는 조금 다릅니다. Oracle에서는 Multiversion Concurrency Control이라고 말하는 방법을 사용하는데, 중요한 것은 SQL Server와 다른 알고리즘을 사용 한다는 것입니다.
아래의 내용은 첨부된 문서의 내용 중 일부입니다.
오라클의 잠금 정책 요약입니다.
* 오라클은 변경의 경우에만 테이블의 행단위로 잠금을 한다. 블록이나테이블 수준으로잠금을 확대하지는 않는다.
* 오라클은 단지 데이터를 읽기 위해서 잠금을 하지 않는다. 즉, 간단한 읽기에 의한 데이터 행들에 잠금을 두지는 않는다.
* 데이터 기록기(writer)는 데이터 판독기(reader)를 방해하지 않는다. 즉, 읽기는 쓰기에 의해서 방해받지 않는다. 이는 기본적으로 읽기가 쓰기에 의해서 차단되는 거의 모든 다른 데이터베이스들과 구별된다.
* 데이터 기록기는 다른 데이터 기록기가 먼저 접근하고자 하는 데이터에 대해서 잠금을 했을 때에만 차단된다.
* 데이터 판독기는 데이터 기록기에 의해서 전혀 차단되지 않는다.
그러므로, SQL Server에서는 read T가 write T와 충돌이 발생하여, 둘 중 하나의 T는 blocking이 걸리게 되지만, Oracle에서는 read T와 write T가 서로 충돌하지 않습니다.
Historically, the concurrency control model in SQL Server at the server level has been pessimistic and based on locking. While locking is still the best concurrency control choice for most applications, it can introduce significant blocking problems for a small set of applications.
The biggest problem arises when locking causes the writer-block-reader or reader-block-writer problem. If a transaction changes a row, it holds exclusive locks on the changed data. The default behavior in SQL Server is that no other transactions can read the row until the writer commits. Alternatively, SQL Server supports ‘read uncommitted isolation’, which can be requested by the application either by setting the isolation level for the connection or by specifying the NOLOCK table hint. This nonlocking scan should always be carefully considered prior to use, because it is not guaranteed to return transactional consistent results.
간단히 정리해 보면...
"SQL Server는 병행제어 모델로 비관적 알고리즘인 locking을 사용해 왔다. locking은 아직까지 대부분의 application에서 병행제어 방법으로는 가장 좋은 방법이긴 하지만 작은 규모의 application에서 중요한 blocking 문제를 야기시킬 수 있다.
가장 큰 문제점은 locking이 writer-block-reader 혹은 reader-block-writer의 원인이 되는 것이다. 기본적으로 SQL Server에서는 writer가 commit하기 전까지 어떤 트랜잭션도 row를 읽을 수 없다. 대신 application에서 isolotion level을 지정하거나 NOLOCK 테이블 힌트를 이용해 요청할 수 있는 'read uncommitted isolation'을 지원한다. 일관성 있는 결과를 보장하지 않으므로 이런 방법으로 locking하지 않은 채 사용하는 것은 주의해야 한다."
대략 정리했는데, 큰 틀은 맞겠지만 소소하게 틀린 부분은 많을테니 잘못된 부분 지적해 주시면 감사하고 이해하고 보시거나 ㅋㅋ
나머지는 시간날 때 날 잡고...
그리고 Oracle 최신 버전에는 혹시 다른 변화가 있는지도 같이 한번...
Oracle 10i의 메뉴얼 중 Concurrency Control 관련 부분 메뉴얼은 여기
파일은
invalid-file