Pourquoi SQL Server 2008 bloque-t-il les SELECT sur les INSERT à longue transaction?
Nous essayons d'avoir une table transactionnelle qui ne prend que les nouveaux enregistrements insérés sur une base régulière.
Cette table simple exige que nous ajoutons en permanence de nouveaux enregistrements au fil du temps. Le volume de transactions dans ce tableau devrait être assez élevé, et aussi il pourrait y avoir des périodiques lot importations de transactions (>1000), ce qui peut prendre plusieurs secondes.
Partir de ces données, nous procédons à un ensemble d'instructions select regroupement des différentes colonnes à renvoyer les valeurs requises.
De nos premiers tests, nous avons trouvé un goulot d'étranglement pour être liés à SQL Server qui bloque notre sélect, quand, au milieu d'une transaction d'INSERTS.
Ci-dessous est un exemple simple qui peut être exécuté pour illustrer le problème.
-- Simple de la Table DB
create table LOCK_TEST (
LOCK_TEST_ID int identity ,
AMOUNT int);
-- Exécuter cette requête 1 fenêtre
begin tran
insert into LOCK_TEST (AMOUNT) values (1);
WAITFOR DELAY '00:00:15' ---- 15 Second Delay
insert into LOCK_TEST (AMOUNT) values (1);
commit
-- Dans la Requête 2 exécuter en parallèle
select SUM(AMOUNT)
from LOCK_TEST;
Je en droit d'attendre de la Requête 2 pour retourner tout de suite, avec des 0 jusqu'à la requête 1 se termine, et de montrer ensuite les 2. Nous ne voulons pas voir 1 retourné à partir de la 2ème requête.
La réponse que nous avons cherché à rattacher à (NOLOCK) sur l'instruction select. Mais cela est une violation de la transactionnel limites, et le retour d'information peut être de nature financière et nous ne souhaitons pas voir tout uncommited de détails dans nos requêtes.
Mon problème semble être sur la plaquette côté...
Pourquoi ne l'INSERTION du bloc de l'instruction SELECT, même si ce n'est pas la modification de toutes les données existantes?
Points de Bonus à la question: Est-ce une "fonctionnalité" de SQL Server, ou allions-nous trouver cela sur d'autres Base de données de saveurs aussi?
Mise à JOUR
J'ai maintenant eu le temps de trouver un local de base de données oracle et exécuter le même test simple. Ce test est que je m'attends.
Ie je peux exécuter la requête aussi souvent que je veux, et il sera de retour null jusqu'à ce que la 1ère validation de transaction, puis renvoie 2.
Est-il un moyen de faire de SQL Server fonctionne comme cela? ou avons-nous besoin de se déplacer à Oracle?
source d'informationauteur stevemac | 2009-04-27
Vous devez vous connecter pour publier un commentaire.
ce comportement de verrouillage est une fonctionnalité de SQL Server.
Avec 2005 et au-dessus, vous pouvez utiliser ligne niveau de la gestion des versions (qui est ce qui est utilisé par défaut sur Oracle) pour obtenir le même résultat & ne pas bloquer votre sélectionne. Cela met la pression supplémentaire sur la base de données tempdb, car la base de données tempdb maintient la ligne de niveau de la gestion des versions, alors assurez-vous de vous accueillir pour cette.
Pour rendre SQL se comportent de la façon dont vous le souhaitez, exécutez ceci:
Ce qui est totalement norme de comportement dans SQL Server et Sybase (au moins).
Des modifications de données (Insert, update, delete) exiger les verrous exclusifs. cela provoque des lecteurs d'être bloqué:
Avec SQL Server 2005 et au-dessus, ils ont introduit l'isolement d'instantané (aka versioning). À partir de MS BOL: La Compréhension De Versions De Ligne, En Fonction Des Niveaux D'Isolement. Cela signifie qu'un lecteur a dernières données validées, mais ensuite, si vous voulez écrire de nouveau, dire, alors vous pouvez trouver les données qui est faux, car il a changé dans le blocage de la transaction.
Maintenant, c'est pourquoi la meilleure pratique consiste à conserver les transactions courtes. Par exemple, seuls les processus de ce que vous devez entre BEGIN/COMMIT, ne pas envoyer des e-mails à partir de déclencheurs etc.
Edit:
De verrouillage, comme cela se produit tout le temps dans SQL Server. Cependant, verrouillage pendant trop longtemps devient le blocage qui risque de réduire les performances. Trop de temps est subjectif, évidemment.
Cela existe en MySQL. Voici la logique derrière elle: si vous procédez à une sélection sur certaines données, vous vous attendez à fonctionner sur un jour un jeu de données. C'est pourquoi INSÉRER les résultats dans un tableau de niveau de verrouillage (qui est moins que le moteur est capable d'effectuer le verrouillage de ligne, comme innodb). Il existe des moyens pour désactiver ce comportement, de sorte que vous pouvez le faire "sale lit", mais ils sont tous les logiciels (ou même moteur de base de données) spécifiques.
J'ai aussi rencontré ce problème. Après enquête, il me semblait que ce n'était pas les données qui a été verrouillé en soi, mais l'index cluster est en cours de modification en conséquence de l'insertion. Depuis l'index cluster réside sur les pages de données, les lignes de données sont verrouillées.