SELECT FOR UPDATE avec SQL Server

Question

Je suis l'aide de Microsoft SQL Server 2005 de la base de données avec le niveau d'isolation READ_COMMITTED et READ_COMMITTED_SNAPSHOT=ON.

Maintenant je veux utiliser:

SELECT * FROM <tablename> FOR UPDATE

...alors que d'autres connexions de base de données bloquer lorsque vous essayez d'accéder à la même ligne "mises à JOUR".

J'ai essayé:

SELECT * FROM <tablename> WITH (updlock) WHERE id=1

...mais cela bloque toutes les autres connexions de même pour la sélection d'un id d'autre que "1".

Qui est la bonne astuce pour faire un SELECT FOR UPDATE comme on le sait pour Oracle, DB2, MySql?

EDIT 2009-10-03:

Ce sont les instructions pour créer la table et l'index:

CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, 
    Terminal BIGINT, Status SMALLINT );
ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id )
CREATE INDEX I108_FkTerminal ON example ( Terminal )
CREATE INDEX I108_Key ON example ( TransactionId )

Beaucoup de processus en parallèle ce faire, SELECT:

SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?

EDIT 2009-10-05:

Pour une meilleure vue d'ensemble que j'ai écrit tous essayé des solutions dans le tableau suivant:

mécanisme | SELECT sur les différents blocs en ligne | SELECT sur la même ligne de blocs
-----------------------+--------------------------------+--------------------------
ROWLOCK | non | non
updlock, rowlock | oui | oui
xlock,rowlock | oui | oui
repeatableread | non | non
DBCC TRACEON (1211,-1) | oui | oui
rowlock,xlock,holdlock | oui | oui
updlock,holdlock | oui | oui
UPDLOCK,READPAST | non | non

Je suis à la recherche d' | non | oui

Answer 1

Récemment, j'ai eu un problème d'interblocage parce que Sql Server verrouille plus que nécessaire (page). Vous ne pouvez rien faire contre cela. Nous avons maintenant des exceptions d'interblocage ... et j'aimerais avoir Oracle à la place.

Answer 2

J'ai un problème similaire, je veux verrouiller seulement 1 ligne. Autant que je sache, UPDLOCK option, SQLSERVER verrouille toutes les lignes qu'il doit lire dans l'ordre pour obtenir la ligne. Donc, si vous ne définissez pas d'index pour accéder directement à la ligne, tous les précédée lignes seront verrouillés. Dans votre exemple:

Asume que vous avez une table nommée TBL avec un idchamp. Vous souhaitez verrouiller la ligne avec id=10. Vous avez besoin de définir un indice pour le champ id (ou toutes les autres disciplines qui sont impliqués dans la vous de choisir):

CREATE INDEX TBLINDEX ON TBL ( id )

Et puis, votre requête pour verrouiller UNIQUEMENT les lignes que vous lisez est:

SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

Si vous n'utilisez pas l'INDEX(TBLINDEX), SQLSERVER besoin de lire toutes les lignes à partir du début de la table pour trouver votre ligne avec id=10, de sorte que ces lignes seront verrouillés.

Answer 3

Vous ne pouvez pas avoir l'isolation de capture instantanée et le blocage des lectures en même temps. L’isolation des instantanés a pour but d’ empêcher le blocage des lectures.

Answer 4

La réponse pourrait se plonger dans les entrailles du SGBD. Il dépend de la façon dont le moteur de requête (qui exécute le plan de requête généré par l'optimiseur SQL) fonctionne.

Cependant, une explication possible (applicable à au moins certaines versions de certains SGBD - pas nécessairement pour MS SQL Server) est qu'il n'y a pas d'index sur la colonne ID, de sorte que tout le processus en essayant de travailler une requête avec 'WHERE id = ?"finit par faire un balayage séquentiel de la table, et que l'analyse séquentielle des hits de la serrure de votre procédé. Vous pouvez également rencontrer des problèmes si le SGBD s'applique au niveau de la page de blocage par défaut; verrouillage de l'un des verrous de ligne l'intégralité de la page et toutes les lignes sur cette page.

Il y a certains égards, on pourrait discréditer ce que la source du problème. Regardez le plan de requête; l'étude de l'index, essayez votre SÉLECTIONNEZ l'ID de 1000000 au lieu de 1 et de voir si d'autres processus sont toujours bloquées.

Answer 5

peut-être faire de mvcc permanente pourrait le résoudre (par opposition à certains de ce lot: SET TRANSACTION ISOLATION LEVEL INSTANTANÉ):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDIT: octobre 14]

Après lecture de ceci: Mieux simultanéité dans l'Oracle de SQL Server? et ceci: http://msdn.microsoft.com/en-us/library/ms175095.aspx

Lorsque le READ_COMMITTED_SNAPSHOT option de base de données est activé, le les mécanismes utilisés à l'appui de l'option sont immédiatement activés. Lorsque réglage de la READ_COMMITTED_SNAPSHOT option, seule la connexion de l'exécution de la commande ALTER DATABASE est autorisée dans la base de données. Il ne doit pas être autre ouvrir la connexion à la base de données jusqu'à MODIFIER la BASE de données est complète. L' la base de données n'a pas à être dans le mode mono-utilisateur.

je suis venu à la conclusion que vous avez besoin de définir deux drapeaux afin d'activer mssql du MVCC de façon permanente sur une base de données:

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;