Discussion :

[J0r_x]

Bonjour,

J'ai une table dont la clé primaire est composé de deux colonnes (TYPE + NUMERO) :

Matable (
TYPE char(2)
NUMERO INT
LIBELLE char(50)
)

Je vais en modification sur une ligne de ma table, j'ouvre une transaction et je bloque la ligne (ROWLOCK + UPDLOCK) pour TYPE = 'A' & NUMERO = 1
Si je veux simultanément faire une suppression de toutes les lignes avec TYPE = 'B' j'obtiens un blocage LCK_M_U.
Si je supprime unitairement les lignes qui ont TYPE = 'B' ça fonctionne.
Pourquoi ne puis-je pas faire un delete de masse alors que le ROWLOCK est appliqué ?

[mikedavem]

Quels sont les verrous posés sur ta table lorsque tu ouvres une transaction et que tu "bloques" ta ligne ?

Que te donne le résultat de la DMV sys.dm_tran_locks ?

++

[J0r_x]

Résultat de sys.dm_tran_locks.

OBJECT 5 1057789409 0 IX LOCK GRANT 1 33554432 63 0 0 TRANSACTION 1193355 00000000-0000-0000-0000-000000000000 0x13CFADD0:1:1 0x2EBE4800
METADATA DATABASE_PRINCIPAL 5 principal_id = 1 0 0 Sch-S LOCK GRANT 6 33554432 63 0 0 TRANSACTION 1193355 00000000-0000-0000-0000-000000000000 0x13CFADD0:1:1 0x3EC57100
KEY 5 (a900d22343af) 7,20576E+16 0 U LOCK GRANT 0 33554432 63 0 0 TRANSACTION 1193355 00000000-0000-0000-0000-000000000000 0x13CFADD0:1:1 0x2EBE6DE0

[SQLpro]

Pourquoi voulez-vous verrouiller à la main ce que le SGBDR fait, bien mieux que vous de manière optimisé et performant ?

Laissez le faire. Une INSERT, UPDATE ou DELETE est une transaction !

A lire sur ce qu'est une transaction : http://sqlpro.developpez.com/cours/sqlaz/techniques/

A +

[J0r_x]

Le verrouillage à la main me sert à bloquer des fiches de saisies pour qu'un seul utilisateur puisse modifier une fiche.

[mikedavem]

En principe si tu n'as pas besoin de forcer le mode de verrouillage dans ton cas. Le verrouillage se fera automatiquement au niveau ligne (ou clé ici) si ton prédicat est suffisamment sélectif.

Et que te donne un sys.dm_tran_locks lorsque tu tentes ta suppression en masse ?

++

[SQLpro]

Vous êtes donc en traine de vous auto bloquer...

Vous ne pouvez pas à la fois bloquer en lecture et bloquer en écriture. Un verrou de lecture bloque les écritures et vice versa...

Je pense que vous devriez vous former au SGBD rerlationnel. On est pas dans du cobol avec des fichiers et des lignes !!!!

A +

[J0r_x]

J'avais compris que je m'auto bloqué, mais je trouve ça quand même extraordinaire de ne pas pouvoir bloquer une ligne d'un enregistrement et dans supprimer 10 autres en mêmes temps ...

[mikedavem]

C'est tout à fait possible. Cela fonctionne à certaines conditions. Il faut simplement voir quel est le mode de verrouillage utilisé par vos 2 transactions.

++

[J0r_x]

Quelle mode de verrouillage faut il utiliser pour effectuer cette manipulation ?

[iberserk]

Il faut simplement voir quel est le mode de verrouillage utilisé par vos 2 transactions.

Répondez à la question au lieu qu'en poser une autre...

[J0r_x]

Puisque cela fonctionne à certaine condition, autant me donner les verrouillages qui fonctionne plutôt que j'explique mes verrouillage qui ne fonctionne pas pour qu'on me dise de faire autrement, c'est quand même plus simple.

[mikedavem]

Je pense qu'il faut revenir à ton besoin avant de te répondre.

Que veux-tu faire précisément au niveau fonctionnel ...

Une personne qui rentre dans une fiche doit pouvoir la "bloquer" si j'ai bien compris (une fiche est représentée par une ligne de données dans notre cas).
Maintenant si une autre personne entre sur cette même fiche que se passe-t-il ? Il peut lire les données ? Il est bloqué ? Un message d'erreur ou d'information doit lui arriver et lui signaler que quelqu'un d'autre gère déjà cette même fiche ?

Peux-tu nous en dire plus ?

++

[J0r_x]

Quand on arrive sur une fiche, on bloque la fiche (ligne dans la table SQL (UPDLOCK + ROWLOCK), si un autre utilisateur arrive dessus, on essaye de bloquer la ligne, comme celle-ci est déjà bloqué on reçoit une erreur 1222 et on affiche un message "L'enregistrement est bloqué par un autre utilisateur".
Ceci marche parfaitement depuis des années.
Le problème c'est lorsque je veux faire un UPDATE ou DELETE de masse sur la table dont une ligne est bloqué, même si la ligne bloquée n'est pas concerné par cette UPDATE ou DELETE, celui ne s'exécute pas et est bloqué.

[Serguei_TARASSOV]

Bonjour,

J'ai une table dont la clé primaire est composé de deux colonnes (TYPE + NUMERO) :

Matable (
TYPE char(2)
NUMERO INT
LIBELLE char(50)
)

Je vais en modification sur une ligne de ma table, j'ouvre une transaction et je bloque la ligne (ROWLOCK + UPDLOCK) pour TYPE = 'A' & NUMERO = 1
Si je veux simultanément faire une suppression de toutes les lignes avec TYPE = 'B' j'obtiens un blocage LCK_M_U.
Si je supprime unitairement les lignes qui ont TYPE = 'B' ça fonctionne.
Pourquoi ne puis-je pas faire un delete de masse alors que le ROWLOCK est appliqué ?

Attention, le hint n'est que la recommandation pour le moteur qui peut être ignorée.

Alors, il est très probable, qu'il s'agit d'un "lock escalation".
Vu le nombre des lignes à supprimer, le moteur descend du nivaux verrouillage "ligne" vers les pages et puis vers une table entière.

Dans ce cas la solution possible est la suppression par lots i.e par 10 lignes dans un cycle.

[mikedavem]

Alors, il est très probable, qu'il s'agit d'un "lock escalation".

Effectivement il se peut que le type de verrou change et que du coup le DELETE se retrouve bloqué par un lock de type U. C'est la raison pour laquelle je demandais à J0r_x de nous donner un état de la DMV sys.dm_tran_locks en lançait ses 2 transactions.

Quand on arrive sur une fiche, on bloque la fiche (ligne dans la table SQL (UPDLOCK + ROWLOCK), si un autre utilisateur arrive dessus, on essaye de bloquer la ligne, comme celle-ci est déjà bloqué on reçoit une erreur 1222 et on affiche un message "L'enregistrement est bloqué par un autre utilisateur".
Ceci marche parfaitement depuis des années.
Le problème c'est lorsque je veux faire un UPDATE ou DELETE de masse sur la table dont une ligne est bloqué, même si la ligne bloquée n'est pas concerné par cette UPDATE ou DELETE, celui ne s'exécute pas et est bloqué.

Il y a d'autres manière plus propre de faire cela je pense en utilisant un système d'horodatage avec libération de la ressource au bout de X temps pour éviter qu'un utilisateur bloque trop longtemps une ressource. (Par exemple un utilisateur qui oublie de quitter une fiche en partant et verrouille sa session)

++

[J0r_x]

Il est pas possible d'éviter le lock escalation sans changer le type de verrouillage qui est déjà effectué ?

[Serguei_TARASSOV]

Il est pas possible d'éviter le lock escalation sans changer le type de verrouillage qui est déjà effectué ?

Essais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE Matable SET LOCK_ESCALATION = DISABLE

ce que ne garantie pas l'escalade dans 100% des cas quand même.

Je suis plutôt d'accord avec mikedavem, il vaut mieux modifier la logique afin d'éviter les blocages directes (i.e. numéro de la version d'une ligne).

[J0r_x]

La méthode de version complexifie grandement la gestion et en plus cela n’empêche l'utilisateur de faire la modification de l'enregistrement que lors de l'UPDATE, alors qu'il a saisie les informations dans la fiche.

EDIT :
Le LOCK_ESCALATION = DISABLE n’empêche pas le blocage, après les tables ne sont pas partitionnées est-ce que ça a un rapport ?

[J0r_x]

En faisant ceci ça à l'air de fonctionner bizarre ...

DELETE FROM Matable WHERE TYPE = 'B' AND NUMERO IN(
SELECT TOP 10000 NUMERO FROM Matable WHERE TYPE = 'B')

On va voir ce que ça donne dans le temps.