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 de nouveaux loggings insérés régulièrement.

Ce tableau simple nous oblige à append continuellement de nouveaux loggings au fil du time. Le volume de transactions dans cette table devrait être assez élevé, et il pourrait également y avoir des imports de lots par lots périodiques (> 1000), ce qui peut prendre plusieurs secondes.

À partir de ces données, nous faisons ensuite un set d'instructions select regroupant différentes colonnes pour renvoyer les valeurs requirejses.

De notre test initial, nous avons trouvé un goulot d'étranglement à être lié à SQL Server qui bloque nos SELECT au milieu d'une transaction d'INSERTS.

Voici un exemple simple qui peut être exécuté pour illustrer le problème.

– Table DB simple

create table LOCK_TEST ( LOCK_TEST_ID int identity , AMOUNT int);

– Lancer ceci dans une window de requête

 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écutez ceci en parallèle

 select SUM(AMOUNT) from LOCK_TEST;

Je m'attendrais à ce que Query 2 revienne tout de suite, avec 0 jusqu'à ce que la requête 1 se termine, puis affichez 2. Nous ne voulons jamais voir 1 returnné à partir de la 2ème requête.

La réponse que nous avons regardée se rapporte à WITH (NOLOCK) sur l'instruction select. Mais cela viole les limites transactionnelles, et les informations renvoyées peuvent être de nature financière et nous ne souhaitons pas voir de détails non-validés dans nos requêtes.

Mon problème semble être sur le côté INSERT …
Pourquoi l'instruction INSERT bloque-t-elle l'instruction SELECT même si elle ne modifie aucune donnée existante?

Question points bonus: Est-ce une "fonctionnalité" de SQL Server, ou findions-nous cela sur d'autres bases de données?

MISE À JOUR J'ai maintenant eu le time de find une database locale d'Oracle et d'exécuter le même test simple. Ce test est conforme à mes attentes.

C'est-à-dire que je peux exécuter une requête aussi souvent que je le souhaite, et il returnnera null jusqu'à ce que la première transaction soit validée, puis returnnera 2.

Existe-t-il un moyen de faire fonctionner SQL Server comme ça? ou devons-nous passer à Oracle?

Ce comportement de locking est une fonctionnalité de SQL Server. Avec 2005 et au-delà, vous pouvez utiliser le versioning au niveau de la ligne (qui est ce qui est utilisé par défaut sur Oracle) pour get le même résultat et ne pas bloquer vos sélections. Ceci met la contrainte supplémentaire sur tempdb parce que tempdb maintient le versioning de niveau de rangée, alors assurez-vous que vous vous y adaptez. Pour que SQL se comporte comme vous le souhaitez, exécutez ceci:

 ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

C'est un comportement complètement standard dans SQL Server et Sybase (au less).

Les modifications de données (Insérer, mettre à jour, supprimer) nécessitent des verrous exclusifs . cela provoque le blocage des lecteurs:

Avec un verrou exclusif (X), aucune autre transaction ne peut modifier datatables; les opérations de lecture ne peuvent avoir lieu qu'à l'aide de l'indicateur NOLOCK ou lire le niveau d'isolement non-validé.

Avec SQL Server 2005 et versions ultérieures, ils ont introduit l'isolation des snapshots (aka versioning des lignes). À partir de MS BOL: Présentation des niveaux d'isolation basés sur les versions de la ligne . Cela signifie qu'un lecteur a les dernières données validées, mais si vous voulez ensuite écrire, disons, vous pouvez find que datatables sont erronées car elles ont changé dans la transaction de blocage.

Maintenant, c'est pourquoi la meilleure pratique est de garder les transactions courtes. Par exemple, ne traitez que ce dont vous avez besoin entre BEGIN / COMMIT, n'envoyez pas d'e-mails à partir de triggersurs, etc.

Modifier:

Le locking comme ceci se produit tout le time dans SQL Server. Cependant, le locking pendant trop longtime devient un blocage qui réduit les performances. Trop long est subjectif, évidemment.

Cela existe aussi dans MySQL. Voici la logique derrière cela: si vous effectuez un SELECT sur certaines données, vous vous attendez à ce qu'il fonctionne sur un set de données à jour. C'est pourquoi INSERT génère un verrou au niveau de la table (à less que le moteur ne soit capable d'effectuer un locking au niveau de la ligne, comme le peut innodb). Il y a des manières de désactiver ce comportement, de sorte que vous pouvez faire des "lectures sales", mais elles sont toutes spécifiques au logiciel (ou même au moteur de database).

J'ai également rencontré ce problème. Après enquête, il semblait que ce n'était pas datatables qui étaient verrouillées en soi, mais l'index en cluster qui était en train d'être modifié à la suite de l'insertion. Comme l'index clusterisé réside sur les pages de données, les lignes de données associées sont également verrouillées.