J'essaye de créer une procédure stockée pour recréer une table à partir de zéro, avec un changement possible de schéma (y compris les additions / suppressions possibles de colonnes), en utilisant un DROP TABLE
suivi d'un SELECT INTO
, comme ceci:
BEGIN TRAN DROP TABLE [MyTable] SELECT (...) INTO [MyTable] FROM (...) COMMIT
Mon souci est que des erreurs pourraient être générées si quelqu'un essaie d'accéder à la table après qu'elle a été abandonnée mais avant que le SELECT INTO
soit terminé. Y at-il un moyen de verrouiller [MyTable]
d'une manière qui persistera à travers le DROP? Au lieu de DROP/SELECT INTO
, je pourrais TRUNCATE/INSERT INTO
, mais cela ne permettrait pas de changer le schéma. SELECT INTO
est pratique dans ma situation car il permet de déterminer automatiquement le nouveau schéma. Y a-t-il un moyen de faire ce travail en toute security?
Aussi, je voudrais être sûr que les tables sources dans "FROM (…)" ne sont pas bloquées pendant ce process.
Si vous essayez d'apporter une modification significative à la table (comme append une colonne au milieu de colonnes existantes, pas à la fin) en utilisant SSMS et voir quel script il génère, vous verrez que SSMS utilise sp_rename
.
La structure générale du script de SSMS:
Tout cela dans une transaction.
Cela devrait garder le time quand les tables sont verrouillées au minimum.
BEGIN TRANSACTION SELECT (...) INTO dbo.Temp_MyTable FROM (...) DROP TABLE dbo.MyTable EXECUTE sp_rename N'dbo.Temp_MyTable', N'dbo.MyTable', 'OBJECT' COMMIT
DROP TABLE MyTable
acquiert un locking de modification de schéma (Sch-M) jusqu'à la fin de la transaction, de sorte que toutes les autres requêtes utilisant MyTable
attente. Même si d'autres requêtes utilisent le niveau d'isolation READ UNCOMMITTED
(ou le fameux indice WITH (NOLOCK)
).
Voir aussi Modes de locking MSDN:
Verrous de schéma
Le moteur de database utilise des verrous de modification de schéma (Sch-M) lors d'une opération de langage de définition de données de table (DDL), telle que l'ajout d'une colonne ou la suppression d'une table. Pendant le time qu'il est tenu, le verrou Sch-M empêche l'access simultané à la table. Cela signifie que le verrou Sch-M bloque toutes les opérations extérieures jusqu'à ce que le verrou soit libéré.