[sql] 에스컬레이션 잠금-여기서 무슨 일이 일어나고 있습니까?

저스틴 그랜트 (Justin Grant)의 답변 LOCK_ESCALATION은 일반적으로 설정이 수행하는 작업에 대해 설명 하지만 중요한 세부 사항이 누락되었으며 SSMS가 설정하는 코드를 생성하는 이유를 설명하지 않습니다. 특히 LOCK_ESCALATION스크립트에서 마지막 명령문으로 설정되어 있다는 것은 매우 이상하게 보입니다 .

나는 몇 가지 테스트를했고 여기에서 무슨 일이 일어나고 있는지에 대한 나의 이해가 있습니다.

짧은 버전

ALTER TABLE추가 문은 열이 암시 적으로 스키마가와는 아무 상관이없는 테이블에 (SCH-M) 잠금 수정합니다 떨어지거나 변경합니다 LOCK_ESCALATION테이블의 설정을. LOCK_ESCALATIONDML 문 (중 잠금 동작에 영향을 미치는 INSERT, UPDATE, DELETE하지 DDL 문 중, 등) ( ALTER). SCH-M 잠금은 항상이 예에서 전체 데이터베이스 개체, 테이블의 잠금입니다.

혼란의 원인이 될 수 있습니다.

SSMS는 ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...)필요하지 않더라도 모든 경우에 스크립트에 명령문을 추가합니다 . 이 명령문이 필요한 경우, 해당 스크립트에서 발생 하는 테이블 스키마로 변경하는 동안 특정 방식으로 테이블을 잠그지 않도록 테이블의 현재 설정을 유지하기 위해 추가됩니다 .

즉, 테이블 스키마를 변경하는 모든 작업이 수행되는 동안 첫 번째 명령문 에서 SCH-M 잠금으로 테이블 이 잠 깁니다 ALTER TABLE ALTER COLUMN. 마지막 ALTER TABLE SET LOCK_ESCALATION진술은 영향을 미치지 않습니다. 그것은 (단지 미래 DML 문에 영향을 미치는 INSERT, UPDATE, DELETE그 테이블 등).

언뜻보기 SET LOCK_ESCALATION = TABLE에 전체 테이블을 변경한다는 사실과 관련이있는 것처럼 보이지만 (여기서는 스키마를 변경하고 있음) 오해의 소지가 있습니다.

긴 버전

경우에 따라 테이블을 변경하는 경우 SSMS는 전체 테이블을 다시 생성하는 스크립트를 생성하고 일부 경우에는 열 추가 또는 삭제와 같이 스크립트가 테이블을 다시 생성하지 않습니다.

이 샘플 테이블을 예로 들어 보겠습니다.

CREATE TABLE [dbo].[Test](
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](50) NOT NULL,
    [Col2] [int] NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

각 테이블에는 기본적으로 LOCK_ESCALATION설정되어 TABLE있습니다. 여기에서 바꾸자 :

ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)

Col1SSMS 테이블 디자이너에서 형식 을 변경하려고하면 SSMS가 전체 테이블을 다시 만드는 스크립트를 생성합니다.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test
    (
    ID int NOT NULL,
    Col1 nvarchar(10) NOT NULL,
    Col2 int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Test SET (LOCK_ESCALATION = DISABLE)
GO
IF EXISTS(SELECT * FROM dbo.Test)
     EXEC('INSERT INTO dbo.Tmp_Test (ID, Col1, Col2)
        SELECT ID, CONVERT(nvarchar(10), Col1), Col2 FROM dbo.Test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT'
GO
ALTER TABLE dbo.Test ADD CONSTRAINT
    PK_Test PRIMARY KEY CLUSTERED
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

위에서 LOCK_ESCALATION새로 만든 테이블에 대해 설정 한 것을 볼 수 있습니다 . SSMS는 테이블의 현재 설정을 유지합니다. 설정의 현재 값이 기본값 인 경우에도 SSMS는이 줄을 생성 TABLE합니다. 미래 에이 기본값이 변경되면 안전하고 명시적이고 미래의 문제를 예방하기 위해. 이것은 말이됩니다.

이 예에서는 SET LOCK_ESCALATION테이블을 새로 작성하고 해당 설정을 유지해야하므로 명령문 을 생성해야 합니다.

새 열 추가와 같은 SSMS 테이블 디자이너를 사용하여 테이블을 간단하게 변경하려고하면 SSMS가 테이블을 다시 만들지 않는 스크립트를 생성합니다.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Test ADD
    NewCol nchar(10) NULL
GO
ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)
GO
COMMIT

보시다시피, ALTER TABLE SET LOCK_ESCALATION이 경우에는 전혀 필요하지 않지만 여전히 문을 추가합니다 . 첫 번째 ALTER TABLE ... ADD는 현재 설정을 변경하지 않습니다. SSMS 개발자들은이 ALTER TABLE SET LOCK_ESCALATION문장이 어떤 경우 에 중복 되어 있는지 확인 하고 항상 안전하게 생성 하려고 노력할 가치가 없다고 결정한 것 같습니다 . 이 문장을 추가 할 때마다 아무런 해가 없습니다.

다시 한번, 테이블 전체 LOCK_ESCALATION설정은 관련이 없으며 테이블 스키마는 ALTER TABLE명령문을 통해 변경됩니다 . LOCK_ESCALATION설정은와 같은 DML 문의 잠금 동작에만 영향을줍니다 UPDATE.

마지막으로의 인용문은 다음 ALTER TABLE을 강조합니다.

ALTER TABLE에 지정된 변경 사항은 즉시 구현됩니다. 변경시 테이블의 행을 수정해야하는 경우 ALTER TABLE은 행을 업데이트합니다. ALTER TABLE은 테이블에서 스키마 수정 (SCH-M) 잠금을 획득하여 변경 중에 테이블의 메타 데이터를 참조하는 다른 연결이 없는지 확인합니다.끝에 매우 짧은 SCH-M 잠금이 필요한 온라인 인덱스 작업을 제외하고. ALTER TABLE… SWITCH 작업에서 소스 테이블과 대상 테이블 모두에서 잠금이 획득됩니다. 테이블에 대한 수정 사항이 기록되고 완전히 복구 가능합니다. 열 삭제 또는 일부 SQL Server 버전에서 NOT NULL 열을 기본값으로 추가하는 등 매우 큰 테이블의 모든 행에 영향을주는 변경은 많은 로그 레코드를 완료하고 생성하는 데 시간이 오래 걸릴 수 있습니다. 이러한 ALTER TABLE 문은 많은 행에 영향을주는 INSERT, UPDATE 또는 DELETE 문과 동일한주의를 기울여 실행해야합니다.

---

답변