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

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

---

SQL Server 2008에서 테이블을 변경하는 동안 (열 제거) 변경 스크립트 생성 버튼을 클릭하고 생성 된 변경 스크립트가 열을 삭제하고 "go"라고 말한 다음 설정하는 것처럼 보이는 추가 ALTER TABLE 문을 실행하는 것을 알았습니다. 테이블의 잠금 에스컬레이션을 "TABLE"로 예:

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

또한 이것이 변경 스크립트가 마지막으로 수행하는 것입니다. 여기서 무엇을하고 있으며 왜 LOCK_ESCALATION을 TABLE로 설정합니까?

---

" 잠금 에스컬레이션 "은 SQL이 대규모 업데이트에 대한 잠금을 처리하는 방법입니다. SQL이 많은 행을 변경하려는 경우 데이터베이스 엔진이 여러 작은 항목 (예 : 행 잠금)을 잠그는 대신 더 적은 수의 더 큰 잠금 (예 : 전체 테이블)을 취하는 것이 더 효율적입니다.

그러나 전체 테이블을 잠그면 다른 쿼리가 오랫동안 잠길 수 있으므로 테이블이 크면 문제가 될 수 있습니다. 즉, 많은 작은 단위 잠금이 더 적은 (또는 하나의) 세분화 된 잠금보다 느리고 테이블의 다른 부분을 잠그는 여러 쿼리가 있으면 한 프로세스가 다른 프로세스에서 대기중인 경우 교착 상태가 발생할 가능성이 있습니다.

LOCK_ESCALATION잠금 에스컬레이션을 제어 할 수있는 SQL 2008의 새로운 테이블 레벨 옵션 이 있습니다. 기본값 인 "TABLE"을 사용하면 잠금이 테이블 수준까지 에스컬레이션 될 수 있습니다. DISABLE은 대부분의 경우 전체 테이블에 대한 잠금 에스컬레이션을 방지합니다. AUTO는 테이블이 파티션 된 경우를 제외하고 테이블 잠금을 허용하며,이 경우 잠금은 파티션 레벨까지만 이루어집니다. 자세한 내용은 이 블로그 게시물 을 참조하십시오.

SQL 2008에서는 TABLE이 기본값이므로 IDE가 테이블을 다시 만들 때이 설정을 추가 할 것으로 생각됩니다. SQL 2005에서는 LOCK_ESCALATION이 지원되지 않으므로 스크립트를 실행하려고 할 경우이를 제거해야합니다. 2005 년 사례. 또한 TABLE이 기본값이므로 스크립트를 다시 실행할 때 해당 줄을 안전하게 제거 할 수 있습니다.

또한이 설정이 적용되기 전에 SQL 2005에서 모든 잠금이 테이블 수준으로 에스컬레이션 될 수 있습니다. 즉, "TABLE"이 SQL 2005의 유일한 설정입니다.

---

스크립트의 주요 부분을 실행하기 전후에이 값을 비교하여 스크립트에 LOCK_ESCALATION 문을 포함해야하는지 확인할 수 있습니다.

SELECT lock_escalation_desc FROM sys.tables WHERE name='yourtablename'

필자의 경우 테이블을 변경하여 제약 조건을 삭제하거나 추가 해도이 값이 수정되지 않는 것 같습니다.

---

저스틴 그랜트 (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

You can see above that it sets LOCK_ESCALATION for the newly created table. SSMS does it to preserve the current setting of the table. SSMS generates this line, even if the current value of the setting is the default TABLE value. Just to be safe and explicit and prevent possible future problems if in the future this default changes, I guess. This makes sense.

In this example it is really needed to generate the SET LOCK_ESCALATION statement, because the table is created afresh and its setting has to be preserved.

If I try to make a simple change to the table using SSMS table designer, such as adding a new column, then SSMS generates a script that doesn't re-create the table:

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

As you can see, it still adds the ALTER TABLE SET LOCK_ESCALATION statement, even though in this case it is not needed at all. The first ALTER TABLE ... ADD doesn't change the current setting. I guess, SSMS developers decided that it is not worth the effort to try to determine in what cases this ALTER TABLE SET LOCK_ESCALATION statement is redundant and generate it always, just to be safe. There is no harm in adding this statement every time.

Once again, the table-wide LOCK_ESCALATION setting is irrelevant while the table schema changes via the ALTER TABLE statement. LOCK_ESCALATION setting affects only the locking behaviour of DML statements, like UPDATE.

Finally, a quote from ALTER TABLE, emphasise mine:

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

참고URL : https://stackoverflow.com/questions/1703597/lock-escalation-whats-happening-here