Sélectionner pour la mise à jour avec SQL Server
j'utilise une base de données Microsoft SQL Server 2005 avec un niveau d'isolation READ_COMMITTED et READ_COMMITTED_SNAPSHOT=ON .
maintenant je veux utiliser:
SELECT * FROM <tablename> FOR UPDATE
... de sorte que les autres connexions de base de données bloquent l'accès à la même ligne"pour mise à jour".
j'ai essayé:
SELECT * FROM <tablename> WITH (updlock) WHERE id=1
...mais cela bloque toutes les autres connexions même pour sélectionner une id autre que "1".
qui est l'indication correcte pour faire un SELECT FOR UPDATE comme connu pour Oracle, DB2, MySql?
MODIFIER 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 parallèles font ce SELECT :
SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?
MODIFIER 2009-10-05:
pour un meilleur aperçu j'ai écrit tout essayé solutions dans le tableau suivant:
mechanism | SELECT on different row blocks | SELECT on same row blocks -----------------------+--------------------------------+-------------------------- ROWLOCK | no | no updlock, rowlock | yes | yes xlock,rowlock | yes | yes repeatableread | no | no DBCC TRACEON (1211,-1) | yes | yes rowlock,xlock,holdlock | yes | yes updlock,holdlock | yes | yes UPDLOCK,READPAST | no | no I'm looking for | no | yes
18 réponses
récemment, j'ai eu un problème d'impasse parce que Sql Serveur verrouille plus que nécessaire (page). Vous ne pouvez vraiment rien faire contre elle. Nous sommes maintenant confrontés à des exceptions... et J'aimerais avoir Oracle à la place.
Edit: Nous utilisons l'isolement instantané en attendant, ce qui résout beaucoup, mais pas tous les problèmes. Malheureusement, pour pouvoir utiliser snapshot isolation il doit être autorisé par le serveur de base de données, ce qui peut causer inutile problèmes sur le site des clients. Maintenant, nous ne sommes pas seulement en train de repérer des exceptions d'impasse (qui peuvent encore se produire, bien sûr), mais aussi des problèmes de concurrence instantanée pour répéter des transactions à partir de processus de fond (qui ne peuvent pas être répétés par l'utilisateur). Mais cela fonctionne encore beaucoup mieux qu'avant.
j'ai un problème similaire, je veux verrouiller seulement 1 ligne.
Pour autant que je sache, avec l'option UPDLOCK , SQLSERVER verrouille toutes les lignes qu'il doit lire 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:
suppose que vous avez une table nommée TBL avec un champ id .
Vous voulez verrouiller la rangée avec id=10 .
Vous devez définir un index pour le champ id (ou toute autres champs qui sont impliqués dans vous sélectionnez):
CREATE INDEX TBLINDEX ON TBL ( id )
et ensuite, votre requête pour verrouiller seulement les lignes que vous lisez est:
SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.
si vous n'utilisez pas L'option INDEX(TBLINDEX), SQLSERVER doit lire toutes les lignes depuis le début du tableau pour trouver votre ligne avec id=10 , donc ces lignes seront verrouillées.
vous ne pouvez pas avoir des lectures d'isolement et de blocage de snapshot en même temps. Le but de l'isolement instantané est de empêcher lire bloc.
la réponse complète pourrait plonger dans les internes du SGBD. Cela dépend du fonctionnement du moteur de requête (qui exécute le plan de requête généré par L'optimiseur SQL).
cependant, une explication possible (applicable au moins à certaines versions de certains SGBD - pas nécessairement à MS SQL Server) est qu'il n'y a pas d'index sur la colonne ID, donc tout processus essayant de travailler une requête avec ' WHERE id = ? ' dedans finit par faire un balayage séquentiel de la table, et que scan séquentiel touche la serrure que votre processus appliqué. Vous pouvez également rencontrer des problèmes si le SGBD applique le verrouillage au niveau de la page par défaut; le verrouillage d'une rangée verrouille toute la page et toutes les rangées sur cette page.
il y a des moyens de démystifier cette source de problèmes. Regardez le plan de requête; étudiez les index; essayez votre SELECT avec un ID de 1000000 au lieu de 1 et voyez si d'autres processus sont toujours bloqués.
peut-être rendre le mvcc permanent pourrait le résoudre (par opposition à un lot spécifique seulement: définir le niveau D'isolement de la TRANSACTION SNAPSHOT):
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;
[EDIT: October 14]
après avoir lu ceci: meilleure concurrence dans Oracle que SQL Server? et ceci: http://msdn.microsoft.com/en-us/library/ms175095.aspx
lorsque le READ_COMMITTED_SNAPSHOT l'option de base de données est activée, le mécanismes utilisés pour appuyer l'option sont immédiatement activés. Lorsque définition du READ_COMMITTED_SNAPSHOT option, seule l'exécution de la connexion la commande ALTER DATABASE est autorisée dans la base de données. Il ne doit pas être autre connexion ouverte dans la base de données JUSQU'à ce que la base de données ALTER soit complète. Le la base de données n'a pas à être dans le mode mono-utilisateur.
je suis venu à la conclusion que vous avez besoin pour mettre deux drapeaux afin d'activer le MVCC de mssql de façon permanente sur une base de données Donnée:
ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;
OK, un simple select wil par défaut utiliser "READ Committed" isolation de la transaction qui se bloque et donc s'arrête écrit à cet ensemble. Vous pouvez changer le niveau d'isolation de la transaction avec
Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
Select ...
Commit Tran
ceux-ci sont expliqués en détail dans SQL Server BOL
votre prochain problème est que par défaut SQL Server 2K5 va escalader les serrures si vous avez plus de ~2500 serrures ou utiliser plus de 40% de la mémoire "normale" dans la transaction de serrure. Le l'escalade va à la page, puis à la table de verrouillage
vous pouvez arrêter cette escalade en activant "trace flag" 1211t, voir BOL pour plus d'informations
je suppose que vous ne voulez pas qu'une autre session soit capable de lire la ligne pendant que cette requête spécifique est en cours d'exécution...
envelopper votre SELECT dans une transaction tout en utilisant (XLOCK,READPAST) Conseil de verrouillage obtiendra les résultats que vous voulez. Assurez-vous simplement que ces autres lectures simultanées ne sont pas utilisées avec (NOLOCK). READPAST permet à d'autres sessions d'effectuer le même SELECT mais sur d'autres lignes.
BEGIN TRAN
SELECT *
FROM <tablename> WITH (XLOCK,READPAST)
WHERE RowId = @SomeId
-- Do SOMETHING
UPDATE <tablename>
SET <column>=@somevalue
WHERE RowId=@SomeId
COMMIT
serrures D'Application sont une façon de rouler votre propre serrure avec granularité personnalisée tout en évitant "utiles" escalade de serrure. Voir sp_getapplock .
crée une fausse mise à jour pour renforcer le rowlock.
UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1
si ce n'est pas verrouiller votre rang, Dieu sait ce qui le fera.
après ce " UPDATE "vous pouvez faire votre SELECT (ROWLOCK) et les mises à jour suivantes.
essayez d'utiliser:
SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK
cela devrait rendre la serrure exclusive et la conserver pendant toute la durée de la transaction.
selon cet article , la solution est d'utiliser L'indication avec(REPEATABLEREAD).
revisitez toutes vos requêtes, peut-être que vous avez une requête qui sélectionne sans ROWLOCK/pour l'indice de mise à jour de la même table que vous avez Sélectionner pour la mise à jour.
MSSQL augmente souvent ces serrures de rangée aux serrures au niveau de la page (même les serrures au niveau de la table, si vous n'avez pas l'index sur le champ vous posez des questions), voir cette explication . Puisque vous demandez pour la mise à jour, je pourrais supposer que vous avez besoin de niveau de transaction(par exemple, financière, inventaire, etc) robustesse. Donc, les conseils sur ce site n'est pas applicable à votre problème. C'est juste un aperçu pourquoi MSSQL escalade serrures .
Si vous utilisez déjà MSSQL 2005 (et plus), ils sont basés sur MVCC, je pense que vous ne devriez pas avoir de problème avec le niveau de ligne de verrouillage en utilisant ROWLOCK/UPDLOCK hint. Mais si vous utilisez déjà MSSQL 2005 et plus, essayez de vérifier certaines de vos requêtes qui interrogent la même table que vous voulez mettre à jour si elles escalate serrures en cochant les champs sur leur clause WHERE s'ils ont index.
P.
J'utilise PostgreSQL, il utilise aussi MVCC have pour UPDATE, Je ne rencontre pas le même problème. Les escalades de serrures est ce que MVCC résout, donc je serais surpris si MSSQL 2005 encore escalader les serrures sur la table avec où les clauses qui n'ont pas d'index sur ses champs. Si c'est toujours le cas pour le MSSQL 2005, essayez de vérifier Champs sur où les clauses ont index.
avertissement: ma dernière utilisation de MSSQL est la version 2000 seulement.
Question - Est-ce que ce cas s'est avéré être le résultat de l'escalade de verrouillage (c.-à-d. Si vous tracez avec le profileur pour les événements d'escalade de verrouillage, est-ce vraiment ce qui se passe pour causer le blocage)? Si c'est le cas, il y a une explication complète et une solution de contournement (plutôt extrême) en activant un indicateur de trace au niveau de l'instance pour empêcher l'escalade du verrou. Voir http://support.microsoft.com/kb/323630 indicateur de trace 1211
mais, qui aura probablement involontairement des effets secondaires.
si vous verrouillez délibérément une rangée et la garder verrouillée pendant une période prolongée, alors utiliser le mécanisme de verrouillage interne pour les transactions n'est pas la meilleure méthode (dans SQL Server au moins). Toute l'optimisation dans SQL Server est orientée vers les transactions courtes - entrer, faire une mise à jour, sortir. C'est la raison de l'escalade des verrous.
Donc, si l'intention est de "vérifier" une ligne pour une période prolongée, au lieu pour le verrouillage transactionnel, il est préférable d'utiliser une colonne avec des valeurs et une simple mise à jour pour marquer les lignes comme verrouillées ou non.
j'ai résolu le verrou problème d'une manière complètement différente. J'ai réalisé que sql server n'était pas capable de gérer une telle serrure de manière satisfaisante. J'ai choisi de résoudre cela d'un point de vue programmatique par l'utilisation d'un mutex... waitForLock... releaseLock...
avez-vous essayé READPAST?
J'ai utilisé UPDLOCK et READPAST ensemble en traitant une table comme une file d'attente.
Que Diriez-vous d'essayer de faire une simple mise à jour sur cette ligne en premier (sans changer vraiment de données)? Après cela, vous pouvez procéder avec la ligne comme dans a été sélectionné pour la mise à jour.
UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */
Modifier : vous devriez envelopper dans une opération de cours
Edit 2 : une autre solution est d'utiliser le niveau D'isolation sérialisable