DeadLock MSDN
http://msdn.microsoft.com/en-us/library/ms178104(SQL.105).aspx
(마지막 그림은 화살표가 잘못 그려져 있다. babo msdn....-_-)
Request wait 는 spid -> resource 로
owner 는 spid <- resource 방향으로 그릴것
내가본 가장 좋은 글들
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/13/751343.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
Work-around
http://msdn.microsoft.com/en-us/library/aa175791(v=sql.80).aspx
정상적인 경우만.....
DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION
BEGIN TRY
INSERT Authors VALUES
(@au_id, @au_lname, '', '', '', '', '',
'11111', 0)
WAITFOR DELAY '00:00:05'
SELECT * FROM authors WHERE au_lname LIKE 'Test%'
COMMIT
BREAK
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
ROLLBACK
SET @Tries = @Tries + 1
CONTINUE
END CATCH;
END
특이현상들
1. 리소스 없는 Deadlock
리소스 없는 deadlock 이 발생하면 row lock 이 가능한지 인덱스 상황을 점검하는것이 좋다.
row lock 이 불가능한 상황에서 여러 세션에서 update 를 치면 리소스가 없는 deadlock 이 발생하는 현상이 발생하게 된다.
CREATE TABLE TBLX
(IDX INT )
CREATE CLUSTERED INDEX CL_TBLX ON TBLX (IDX)
SELECT ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, *
FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID ('TBLX')
EXEC SP_INDEXOPTION TBLX, DISALLOWROWLOCKS, 1;
SELECT ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, *
FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID ('TBLX')
-- ALTER TABLE DBO.TBLX SET (LOCK_ESCALATION = DISABLE )
2. 중첩Tran에 의한 DeadLock
dbcc traceon (1204, 1222, -1) 옵션으로 추적 후 그린것
http://www.sqler.com/511218
예전에 정리한 내글
http://cafe.naver.com/sqlmvp/465
sp_lock2
http://sqlsql.tistory.com/8
클러스터 인덱스와 비클러스터 인덱스 업데이트 deadlock 제거
drop table tblx
go
WITH TEMP AS
(
SELECT TOP 10000
CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS INT) IDX
, 'PNAME_'+RIGHT(REPLICATE('0',7)+CAST(CAST(ABS(CHECKSUM(NEWID())) % 1000 AS INT) AS VARCHAR(10)),7)+CHAR(ASCII('A') + ABS(CHECKSUM(NEWID()))%10) PRODUCTNAME
FROM SYS.OBJECTS A1
CROSS JOIN SYS.OBJECTS A2
CROSS JOIN SYS.OBJECTS A3
CROSS JOIN SYS.OBJECTS A4
CROSS JOIN SYS.OBJECTS A5
)
SELECT IDX, PRODUCTNAME, CAST(SUBSTRING(PRODUCTNAME,7,7) AS INT) PRODUCTID
, CAST(ABS(CHECKSUM(NEWID())) % 100 AS INT) SHOPID
, CAST(ABS(CHECKSUM(NEWID())) % 100 AS INT) PRICE
, cast ('a' as char(3000)) d
, DATEADD(MINUTE, CAST(ABS(CHECKSUM(NEWID())) % 20000 AS INT) * -1, GETDATE()) INSERTTIME
INTO TBLX
FROM TEMP
GO
select * from tblx
create clustered index cl_tblx on tblx (idx)
create nonclustered index nc_tblx_01 on tblx (shopid)
update a
set a.inserttime = getdate()
from (
select idx
from tblx with (nolock)
where shopid = 61
) b
join tblx a
on a.idx = b.idx
