Verrouillage escalade-que se passe-t-il ici?

Vous pouvez vérifier si vous devez inclure L'instruction LOCK_ESCALATION dans votre script en comparant cette valeur avant et après l'exécution de la partie principale de votre script:

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

Dans mon cas, modifier la table pour supprimer ou ajouter une contrainte ne semble pas modifier cette valeur.

La réponse de Justin Grant explique quoi LOCK_ESCALATION le paramètre le fait en général, mais manque un détail important et n'explique pas pourquoi SSMS génère le code qui le définit. Surtout, il semble très étrange que le LOCK_ESCALATION soit défini comme une dernière instruction dans le script.

J'ai fait quelques tests et voici ma compréhension de ce qui se passe ici.

Version courte

L'instruction ALTER TABLE qui ajoute, supprime ou modifie une colonne prend implicitement une modification de schéma (SCH-M) verrouille la table, ce qui n'a rien à voir avec le réglage LOCK_ESCALATION d'une table. LOCK_ESCALATION affecte le comportement de verrouillage pendant les instructions DML(INSERT, UPDATE, DELETE, etc.), pas pendant les instructions DDL (ALTER). SCH-m lock est toujours un verrou de l'objet de base de données entier, table dans cet exemple.

C'est probablement d'où vient la confusion.

SSMS ajoute l'instruction ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...) à son script dans tous les cas, même lorsqu'elle n'est pas nécessaire. Dans les cas où cette déclaration est nécessaire, il est ajouté pour préserver le réglage actuel de la table, ne pas verrouiller la table d'une manière spécifique lors de la modification de la table schéma qui se produit dans ce script.

En d'autres termes, la table est verrouillée avec le verrou SCH-M sur la première instruction ALTER TABLE ALTER COLUMN alors que tout le travail de modification du schéma de la table est terminé. La dernière instruction ALTER TABLE SET LOCK_ESCALATION ne l'affecte pas. Cela n'affecte que les futures instructions DML(INSERT, UPDATE, DELETE, etc.) pour que table.

À première vue, il semble que SET LOCK_ESCALATION = TABLE ait quelque chose à voir avec le fait que nous changeons toute la table (nous modifions son schéma ici), mais c'est trompeur.

Version Longue

Lors de la modification de la table dans certains cas, SSMS génère un script qui recréera la table entière et dans certains cas plus simples (comme l'ajout ou la suppression d'une colonne), le script ne recréera pas la table.

Prenons cet exemple de tableau comme exemple:

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

Chaque table a un paramètre LOCK_ESCALATION, qui est défini sur TABLE par défaut. Changeons-le ici:

ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)

Maintenant, si j'essaie de changer le type Col1 dans SSMS table designer, SSMS génère un script qui recrée la table entière:

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

Vous pouvez voir ci-dessus qu'il définit LOCK_ESCALATION pour la table nouvellement créée. SSMS le fait pour préserver le réglage actuel de la table. SSMS génère cette ligne, même si la valeur actuelle du paramètre est la valeur par défaut TABLE valeur. Juste pour être sûr et explicite et éviter d'éventuels problèmes futurs si à l'avenir cette valeur par défaut change, je suppose. Cela fait sens.

Dans cet exemple, il est vraiment nécessaire de générer l'instruction SET LOCK_ESCALATION, car la table est créée à nouveau et son paramètre doit être préservé.

Si j'essaie d'apporter une modification simple à la table en utilisant SSMS table designer, par exemple en ajoutant une nouvelle colonne, SSMS génère un script qui ne recréera pas la 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

Comme vous pouvez voir, il ajoute toujours l'instruction ALTER TABLE SET LOCK_ESCALATION, même si dans ce cas ce n'est pas nécessaire du tout. Le premier ALTER TABLE ... ADD ne change pas le réglage actuel. Je suppose que les développeurs SSMS ont décidé qu'il ne vaut pas la peine d'essayer de déterminer dans quels cas cette instruction ALTER TABLE SET LOCK_ESCALATION est redondante et de la générer toujours, juste pour être sûr. Il n'y a pas de mal à ajouter cette déclaration à chaque fois.

Encore une fois, le paramètre LOCK_ESCALATION à l'échelle de la table n'est pas pertinent alors que le schéma de la table change via ALTER TABLE déclaration. LOCK_ESCALATION paramètre affecte uniquement le comportement de verrouillage des instructions DML, comme UPDATE.

Enfin, une citation de ALTER TABLE, mettre l'accent sur le mien:

Les modifications spécifiées dans ALTER TABLE sont mises en œuvre immédiatement. Si les modifications nécessitent des modifications des lignes de la table, modifier Table met à jour les lignes. ALTER TABLE acquiert une modification de schéma (SCH-M) verrouillez sur la table pour vous assurer qu'aucune autre Référence de connexions même les métadonnées pour le tableau pendant le changement , sauf index en ligne opérations qui nécessitent un verrou SCH-M très court à la fin. Dans un ALTER TABLE ... opération de commutation, le verrou est acquis à la fois sur la source et les tables cibles. Les modifications apportées à la table sont enregistrées et entièrement récupérable. Changements qui affectent toutes les lignes dans très grand tables, telles que la suppression d'une colonne ou, sur certaines éditions de SQL Server, l'ajout D'une colonne NOT NULL avec une valeur par défaut peut prendre beaucoup de temps pour complète et générer de nombreux enregistrements de journal. Ces instructions ALTER TABLE doit être exécuté avec le même soin que tout INSERT, UPDATE ou DELETE déclaration qui affecte de nombreuses lignes.