어떻게 내 테이블을 잠금되고 있는지 확인하려면?

내가 (NOLOCK)을 포함하지 않는 한 모든 갑자기 내 테이블에 남아있는 잠금의 어떤 종류를 나타내는 말에 데이터를 반환 할 수없는 SQL 테이블을 가지고있다.

나는 사실 테이블에 잠금 수 있다는 것을 식별 할 수는 sys.dm_tran_locks와 약간의 실험을했지만, 내가 어떻게 그들을 고정합니다 (는 sys.dm_tran_locks의 즉 요청 요소) 어떤 식별합니까?

편집 : 나는 이전 SQL 2005는 sp_lock에 대해 알고 있지만, 지금 특검팀은, AFAIK이이 같이 sys.dm_tran_locks 함께 할 수있는 올바른 방법으로 사용되지 않습니다 것을. 나는 SQL Server 2008 R2를 사용하고 있습니다.

해결법

==============================
1.당신의 SQLServer 관리 Studio (SSMS)에서 실행할 수있는 다음과 같은 시스템 저장 프로 시저를 살펴 보자 :

당신의 SQLServer 관리 Studio (SSMS)에서 실행할 수있는 다음과 같은 시스템 저장 프로 시저를 살펴 보자 :

또한, SSMS에서, 당신은 다른 방법으로 잠금 및 프로세스를 볼 수 있습니다

SSMS의 다른 버전은 다른 장소에서 활동 모니터를했습니다. 예를 들어, SSMS 2008 년과 2012을 사용하면 서버 노드를 마우스 오른쪽 버튼으로 클릭 할 때 상황에 맞는 메뉴에 있습니다.

==============================

2.어떤 수준에 잠긴 오브젝트 것을 가지고 사람의 좋은 개요를 제공하는 단일 쿼리로 I 결합 / 약식 sp_who를하고는 sp_lock을 "차단 / 차단 자"로 바로 점점하십시오.

어떤 수준에 잠긴 오브젝트 것을 가지고 사람의 좋은 개요를 제공하는 단일 쿼리로 I 결합 / 약식 sp_who를하고는 sp_lock을 "차단 / 차단 자"로 바로 점점하십시오.

--Create Procedure WhoLock
--AS
set nocount on
if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Needed for SQL 2008 onwards
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

(잠금 레벨 약어의 의미, 참조하십시오 예를 들어, https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx)

sp_WhoLock - sp_who를하고는 sp_lock을 결합 T-SQL 저장된 프로 시저 ... : 복사

NB 컬럼 [명령 시브 잠금] 잘못 될 수있다 -가 그 SPID에 대한 현재 커맨드를 나타낸다 하지만 X 잠금은 트랜잭션의 이전 명령에 의해 유발 될 수 있었다.

==============================

3.내가 함께 넣어 가지고있는 저장 프로 시저를 가지고, 그 거래뿐만 아니라 서버에서 실행되는 것을 볼 수도 잠금 및 차단하지만,와. 나는 마스터에 넣어했다. 내가 당신과 함께 공유합니다, 코드는 다음과 같습니다 :

내가 함께 넣어 가지고있는 저장 프로 시저를 가지고, 그 거래뿐만 아니라 서버에서 실행되는 것을 볼 수도 잠금 및 차단하지만,와. 나는 마스터에 넣어했다. 내가 당신과 함께 공유합니다, 코드는 다음과 같습니다 :

USE [master]
go


CREATE PROCEDURE [dbo].[sp_radhe] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


-- the current_processes
-- marcelo miorelli 
-- CCHQ 
-- 04 MAR 2013 Wednesday

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, '') AS login_name
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
CASE es.transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
+ COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
  and es.status = 'running'
ORDER BY es.session_id

end 

GO

이 절차는 지난 몇 년에 나에게 아주 좋은 일했다. 그냥 sp_radhe을 입력 실행

에 관한 마스터 데이터베이스에 sp_radhe 퍼팅

나는 다음과 같은 코드를 사용하고 그것을 시스템 저장 프로 시저를 만들

exec sys.sp_MS_marksystemobject 'sp_radhe'

당신은 아래 링크를 볼 수 있습니다

자신의 SQL 서버 시스템 저장 프로 시저 만들기

트랜잭션 격리 수준에 대해서

T-SQL 트랜잭션 격리 수준에 대한 질문은 당신이 물어 너무 수줍음 있었

조나단 Kehayias

==============================
4.
```
exec sp_lock
```
이 쿼리는 당신에게 기존의 잠금을 제공해야합니다.
```
exec sp_who SPID -- will give you some info
```
데 SPID를, 당신은 프로세스가 (더 많은 정보와 "죽 과정"을 죽일은 "세부 사항")을 테이블을 잠그는 것을 알아 활동 모니터 (프로세스 탭)을 확인 할 수있다.
==============================
5.당신은 또한 더 많은 정보를 제공 sp_who2를 사용할 수 있습니다

당신은 또한 더 많은 정보를 제공 sp_who2를 사용할 수 있습니다

여기에 몇 가지 정보가 http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting

==============================

6.이렇게하면 기존의 잠금 장치의 모든 세부 사항을 제공해야합니다.

이렇게하면 기존의 잠금 장치의 모든 세부 사항을 제공해야합니다.

DECLARE @tblVariable TABLE(SPID INT, Status VARCHAR(200), [Login] VARCHAR(200), HostName VARCHAR(200), 
    BlkBy VARCHAR(200), DBName VARCHAR(200), Command VARCHAR(200), CPUTime INT, 
    DiskIO INT, LastBatch VARCHAR(200), ProgramName VARCHAR(200), _SPID INT, 
    RequestID INT)

INSERT INTO @tblVariable
EXEC Master.dbo.sp_who2

SELECT v.*, t.TEXT 
FROM @tblVariable v
INNER JOIN sys.sysprocesses sp ON sp.spid = v.SPID
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS t
ORDER BY BlkBy DESC, CPUTime DESC

그런 다음,주의의 SPID 차단 테이블을 죽일 수 있습니다.

kill 104 -- Your SPID

==============================
7.반전!

반전!

당신은 독점 잠금을 보유 분산 트랜잭션을 고아 가질 수 있고 스크립트가 거래와 관련된 세션이 있다고 가정하면 당신이 그들을 볼 수 없습니다 (이 없다!). 이러한 거래를 식별하기 위해 아래의 스크립트를 실행합니다 :
```
;WITH ORPHANED_TRAN AS (
SELECT
    dat.name,
    dat.transaction_uow,
    ddt.database_transaction_begin_time,
    ddt.database_transaction_log_bytes_reserved,
    ddt.database_transaction_log_bytes_used
FROM
    sys.dm_tran_database_transactions ddt,
    sys.dm_tran_active_transactions dat,
    sys.dm_tran_locks dtl
WHERE
    ddt.transaction_id = dat.transaction_id AND
    dat.transaction_id = dtl.request_owner_id AND
    dtl.request_session_id = -2 AND
    dtl.request_mode = 'X'
)
SELECT DISTINCT * FROM ORPHANED_TRAN
```
당신이 거래를 확인하면, MSDTC에서 그것을 찾을 transaction_uow 열을 사용 중단하거나 커밋할지 여부를 결정합니다. 트랜잭션이 (옆에 물음표)에서의 의심으로 표시되어 있으면 당신은 아마 그것을 중단 할 것입니다.

또한 KILL 명령에 transaction_uow를 지정하여 워크 (UOW)의 단위를 죽일 수 :
```
KILL '<transaction_uow>'
```
참고 :

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017#arguments

https://www.mssqltips.com/sqlservertip/4142/how-to-kill-a-blocking-negative-spid-in-sql-server/
==============================
8.되지 않는 것으로 공식 문서에 따라,는 sp_lock는 표시입니다 :

되지 않는 것으로 공식 문서에 따라,는 sp_lock는 표시입니다 :

대신는 sys.dm_tran_locks를 사용하는 것이 좋습니다. 이 동적 관리 개체는 현재 활성화 된 잠금 관리자 리소스에 대한 정보를 반환합니다. 각 행은 부여 된 또는 부여 할 대기중인 잠금 잠금 관리자에 현재 활성화 된 요청을 나타냅니다.

그것은 일반적으로하지 않습니다는 sp_lock 다음 더 사용자 친화적 인 구문에 대한 자세한 내용을 반환합니다.

아담 Machanic 쓴 whoisactive 루틴은 사용자 환경에서 현재 활동을 확인하고 대기의 종류 / 잠금 쿼리를 둔화있는 것을보고 매우 좋다. 당신은 아주 쉽게 쿼리 및 기타 유용한 정보의 톤을 차단하고 손쉽게 찾을 수 있습니다.

최선을 다하고 읽기 - 예를 들어, 우리는 기본 SQL 서버 격리 수준에서 실행되는 다음과 같은 쿼리가 있다고 가정 해 보자. 각 쿼리는 별도의 쿼리 창에서 실행됩니다 :
```
-- creating sample data
CREATE TABLE [dbo].[DataSource]
(
    [RowID] INT PRIMARY KEY
   ,[RowValue] VARCHAR(12)
);

INSERT INTO [dbo].[DataSource]([RowID], [RowValue])
VALUES (1,  'samle data');

-- query window 1
BEGIN TRANSACTION;

    UPDATE [dbo].[DataSource]
    SET [RowValue] = 'new data'
    WHERE [RowID] = 1;

--COMMIT TRANSACTION;

-- query window 2
SELECT *
FROM [dbo].[DataSource];
```
이어서 sp_whoisactive 실행 (열의 일부만이 표시되어있다) :

당신은 쉽게 SELECT 문과 심지어는 T-SQL 코드를 차단하는 세션을 볼 수 있습니다. 루틴은 매개 변수를 많이 가지고, 그래서 당신은 자세한 내용은 문서를 확인할 수 있습니다.

우리가 쿼리 경우는 sys.dm_tran_locks 우리가 세션의 하나가 다른 세션에 의해 배타적 잠금이있는 자원의 공유 잠금을 기다리고 볼 수 있습니다보기 :
==============================
9.동료와 난 그냥 이것에 대한 도구를 만들었습니다. 그것은 당신의 세션이 생산하는 모든 잠금 장치의 시각적 표현이다. 그것을 시도 (http://www.sqllockfinder.com)를주고, 그것은 오픈 소스 (https://github.com/LucBos/SqlLockFinder)

동료와 난 그냥 이것에 대한 도구를 만들었습니다. 그것은 당신의 세션이 생산하는 모든 잠금 장치의 시각적 표현이다. 그것을 시도 (http://www.sqllockfinder.com)를주고, 그것은 오픈 소스 (https://github.com/LucBos/SqlLockFinder)

from https://stackoverflow.com/questions/8749426/how-to-find-out-what-is-locking-my-tables by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왜 빠른 일괄 삽입 / 업데이트는 무엇입니까? 어떻게 배치 업데이트 작업을? (0)	2020.05.19
[SQL] 쉼표의 차이 조인 분리와 MySQL의 구문에 가입 무엇입니까? [복제] (0)	2020.05.19
[SQL] 나는 H2는 메모리 데이터베이스의 스키마를 자동 작성 할 수 있습니까? (0)	2020.05.19
[SQL] SQL의 목적이 "있는 그대로"를 키워드 무엇입니까? (0)	2020.05.19
[SQL] IN 대 큰 행 집합에 가입하세요 (0)	2020.05.19

복붙노트

[SQL] 어떻게 내 테이블을 잠금되고 있는지 확인하려면?

어떻게 내 테이블을 잠금되고 있는지 확인하려면?

해결법

1.당신의 SQLServer 관리 Studio (SSMS)에서 실행할 수있는 다음과 같은 시스템 저장 프로 시저를 살펴 보자 :

2.어떤 수준에 잠긴 오브젝트 것을 가지고 사람의 좋은 개요를 제공하는 단일 쿼리로 I 결합 / 약식 sp_who를하고는 sp_lock을 "차단 / 차단 자"로 바로 점점하십시오.

3.내가 함께 넣어 가지고있는 저장 프로 시저를 가지고, 그 거래뿐만 아니라 서버에서 실행되는 것을 볼 수도 잠금 및 차단하지만,와. 나는 마스터에 넣어했다. 내가 당신과 함께 공유합니다, 코드는 다음과 같습니다 :

4.

5.당신은 또한 더 많은 정보를 제공 sp_who2를 사용할 수 있습니다

6.이렇게하면 기존의 잠금 장치의 모든 세부 사항을 제공해야합니다.

7.반전!

8.되지 않는 것으로 공식 문서에 따라,는 sp_lock는 표시입니다 :

9.동료와 난 그냥 이것에 대한 도구를 만들었습니다. 그것은 당신의 세션이 생산하는 모든 잠금 장치의 시각적 표현이다. 그것을 시도 (http://www.sqllockfinder.com)를주고, 그것은 오픈 소스 (https://github.com/LucBos/SqlLockFinder)

'SQL' 카테고리의 다른 글

티스토리툴바