얼마전 친구가 쪽지를 보내 왔다. 보내준 쿼리에는 이상이 없는듯 한데 데이터가 하나도 안나온단다. 그래서 원격으로 확인해 주었다. 별로 복잡한 쿼리도 아닌데 Blocking 계속 당하고 있었다. 그래서 한번 정리해 보았다.
CREATE DATABASE ReadCommittedSnapshotTest
GO
USE ReadCommittedSnapshotTest
GO
CREATE TABLE Inventory (
ItemID int NOT NULL
, LocationID int NOT NULL
, Qty int
, PRIMARY KEY (ItemID, LocationID)
)
CREATE NONCLUSTERED INDEX ncixLocationItem ON Inventory(LocationID, ItemID)
GONext, we’ll insert some data.
SET NOCOUNT ON
INSERT INTO Inventory (ItemID, LocationID, Qty)
VALUES(rand() * 1000 + 1, 1, rand() * 40 + 1)
INSERT INTO Inventory (ItemID, LocationID, Qty)
VALUES(rand() * 1000 + 1, 2, rand() * 40 + 1)
INSERT INTO Inventory (ItemID, LocationID, Qty)
VALUES(rand() * 1000 + 1, 3, rand() * 40 + 1)
GO 2000
1. SQL 2000 Blocking TEST
--세션1
BEGIN TRAN
UPDATE Inventory
SET Qty = 5
WHERE ItemID = 796 AND LocationID = 1
--ROLLBACK TRAN
COMMIT TRAN
--세션2
SELECT * FROM Inventory WHERE ItemID = 796
SELECT * FROM Inventory WITH(NOLOCK) WHERE ItemID = 796
SELECT * FROM Inventory WITH(READUNCOMMITTED) WHERE ItemID = 796
2. SQL 2005 새로운기능(RCSI)
-- (Read committed snapshot isolation) 설정
--RCSI 설정/해제(주의점다른세션에서아래DB 접근해제후실행)
ALTER DATABASE ReadCommittedSnapshotTest
SET READ_COMMITTED_SNAPSHOT ON/OFF;
go
3. Linked Server whth(nolock) 사용
보통 개발시 four-part name 를 이용하여 접근하여 많이 사용한다.
하지만 이럴 경우 원격 쿼리로 인하여 Lock이 발생하는 경우가 있다.
이를 방지 하기 위해 OPENQUERY 를 사용 해야 한다.
Four-part name
SELECT * FROM
[LINKEDSERVER].[ReadCommittedSnapshotTest].DBO.Inventory
WHERE ItemID = 796
--OPENQUERY 이용 With(Nolock) 사용
SELECT * FROM
OPENQUERY([LINKEDSERVER], 'SELECT * FROM dbo.Inventory WITH(NOLOCK) WHERE ItemID = 796')
