잠금 에스컬레이션 - 여기서 무슨 일이 발생합니까?

잠금 에스컬레이션 - 여기서 무슨 일이 발생합니까?

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

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

이것이 변경 스크립트가 수행하는 마지막 작업이라는 점도 유념해야 합니다.LOCK_ESCALATION을 TABLE로 설정하는 이유는 무엇입니까?

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

그러나 전체 테이블을 잠그면 다른 쿼리가 오랫동안 잠길 수 있기 때문에 큰 테이블이 있을 때 문제가 될 수 있습니다.이것이 트레이드오프입니다. 많은 소립형 잠금 장치는 더 적은 수의(또는 하나의) 거친 잠금 장치보다 느리며, 테이블의 다른 부분을 잠그는 여러 개의 쿼리를 사용하면 한 프로세스가 다른 프로세스에서 대기 중인 경우 교착 상태에 빠질 가능성이 있습니다.

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

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

또한 이 설정이 존재하기 전의 SQL 2005에서는 모든 잠금이 테이블 수준으로 상승할 수 있습니다. 즉, "TABLE"이 SQL 2005의 유일한 설정이었습니다.

저스틴 그랜트의 대답은 일반적으로 설정이 무엇을 하는지 설명하지만 한 가지 중요한 세부 사항을 놓치고 SSMS가 이를 설정하는 코드를 생성하는 이유를 설명하지 못합니다.특히, 그것은 매우 이상하게 보입니다.LOCK_ESCALATION는 스크립트의 마지막 문으로 설정됩니다.

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

짧은 버전

그ALTER TABLE을 추가, 또는 수정는 "SCH-M", "SCH-M" 관련이 없습니다.LOCK_ESCALATION상차림 LOCK_ESCALATION문(DML 문) 을 미칩니다(DML 문이는동잠줍영니향다을작에동금안하동().INSERT,UPDATE,DELETE등), DDL 문 동안은 안 됩니다.ALTERSCH-M 잠금은 항상 전체 데이터베이스 개체(이 예제에서는 테이블)의 잠금입니다.

여기서 혼란이 발생할 가능성이 높습니다.

는 SSMS를 합니다.ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...)스크립트에 대한 설명은 필요하지 않은 경우에도 모든 경우에 제공됩니다.이 문이 필요한 경우, 이 문은 테이블의 현재 설정을 보존하기 위해 추가되며, 해당 스크립트에서 발생하는 테이블 스키마를 변경하는 동안 특정 방법으로 테이블을 잠그지 않습니다.

즉, 테이블이 첫 번째 SCH-M 잠금으로 잠겨 있습니다.ALTER TABLE ALTER COLUMN테이블 스키마를 변경하는 모든 작업이 완료되는 동안 문. 후의최.ALTER TABLE SET LOCK_ESCALATION진술은 그것에 영향을 주지 않습니다. DML을 미칩니다(DML 문은 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는 설정의 현재 값이 기본값인 경우에도 이 라인을 생성합니다.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은 테이블에서 스키마 수정(SCH-M) 잠금을 획득하여 변경 중에 테이블에 대한 메타데이터조차 다른 연결이 참조하지 않도록 합니다. 단, 마지막에 매우 짧은 SCH-M 잠금이 필요한 온라인 인덱스 작업은 예외입니다.ALTER TABLE에서…SWITCH 작동, 잠금은 소스 테이블과 대상 테이블 모두에서 획득됩니다.테이블에 대한 수정 사항이 기록되고 완전히 복구할 수 있습니다.열을 삭제하거나 SQL Server 일부 버전에서 기본값으로 NULL이 아닌 열을 추가하는 등 매우 큰 테이블의 모든 행에 영향을 미치는 변경은 완료하고 많은 로그 레코드를 생성하는 데 오랜 시간이 걸릴 수 있습니다.이러한 ALTER TABLE 문은 많은 행에 영향을 미치는 INSERT, UPDATE 또는 DELETE 문과 동일한 주의를 기울여 실행해야 합니다.

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

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

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

언급URL : https://stackoverflow.com/questions/1703597/lock-escalation-whats-happening-here