[위로]

<원인>

• 트랜잭션에 의한 Select,Insert,Update,Delete 등이 안됨
• 격리 수준 조정의 필요

<SQL문>

CREATE PROC sp_block
AS
set nocount on
IF NOT EXISTS (SELECT * FROM sysprocesses WHERE spid IN
(SELECT blocked FROM sysprocesses )
)
BEGIN
PRINT 'No one is blocked'
RETURN
END

SELECT spid, blocked, status,
loginame,
hostname,
dbname = substring(db_name(dbid), 1, 10)
, cmd
into #tmp
FROM sysprocesses
WHERE blocked <> 0
OR ( spid IN (SELECT blocked FROM sysprocesses))
order by spid

select * from #tmp --차단된 프로세스를 보여준다.

--이제 dbcc inputbuffer를 수행한다.
CREATE TABLE #dbcc (
id int identity
, spid int null
, eventType varchar(255) null
, parameters int null
, eventInfo varchar(255) null
)

DECLARE c1 CURSOR READ_ONLY
FOR SELECT spid FROM #tmp

DECLARE @spid int
OPEN c1

FETCH NEXT FROM c1 INTO @spid
WHILE (@@fetch_status =0)
BEGIN
DECLARE @message varchar(100)
SELECT @message = 'dbcc inputbuffer (' + convert(varchar(30), @spid) + ')'
-- print @message
INSERT #dbcc (eventType, parameters, eventInfo)
exec (@message)
UPDATE #dbcc SET spid = @spid WHERE id = @@identity
FETCH NEXT FROM c1 INTO @spid
END

DEALLOCATE c1

SELECT * FROM #dbcc

<조치 사항>

1. 차단된 프로세스 Kill (최악의 사항)
2. 격리 수준 조정 (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)
3. 임시 Table 사용 금지 (테이블 변수 사용)

<기타>

1. 작업 스케쥴을 통한 정기적인 모니터링 가능 (1분 단위로 수행, Lock 발생시 메일 발송 및 Lock 현황 저장

• /* 5초 이상 되는 Block을 모니터링합니다. */
  SELECT spid FROM master..sysprocesses WHERE blocked <> 0 OR ( spid IN (SELECT blocked FROM master..sysprocesses))

  if ( @@ROWCOUNT > 0 )
  begin
  
  --5>3>2>1 로 변경
  WAITFOR DELAY '00:00:05'

  if not exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.TBLLock') )
  CREATE TABLE tempdb.dbo.TBLLock (id int identity , spid int null , eventType varchar(255) null , parameters int null , eventInfo varchar(255) null,CreateDate datetime not null DEFAULT getdate())

  SELECT spid into #tmp FROM master..sysprocesses WHERE blocked <> 0 OR ( spid IN (SELECT blocked FROM master..sysprocesses))
  
  DECLARE c1 CURSOR READ_ONLY
  FOR SELECT spid FROM #tmp
  
  DECLARE @spid int
  OPEN c1
  
  FETCH NEXT FROM c1 INTO @spid
  WHILE (@@fetch_status =0)
  BEGIN
  DECLARE @message varchar(100)
  SELECT @message = 'dbcc inputbuffer (' + convert(varchar(30), @spid) + ')'
  -- print @message

  INSERT tempdb.dbo.TBLLock (eventType, parameters, eventInfo) exec (@message) -- Lock 현황 저장

  UPDATE tempdb.dbo.TBLLock SET spid = @spid WHERE id = @@identity

  select * From -- 메일 발송
  openRowset ('SQLOLEDB', 'Server=~~;UID=~~;PWD=~~;Database=~~',
  'EXEC dbo.pSendMail
  ''cskwak@interpark.com'' ,
  ''cskwak@interpark.com'' ,
  '' Lock 발생'' ,
  '' Lock 발생 '' '
  )
  FETCH NEXT FROM c1 INTO @spid
  END
  DEALLOCATE c1
  end