Discussion :

[Serguei_TARASSOV]

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.

Exactement, si tu n'utilise pas ORM (i.e. NHibernate) ou un autre framework qui s'en occupe, le dév supplémentaire sera significatif.

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 ?

Ce que est plus important, il faut que "matable" aie une clé cluster. Mais en tous cas même Microsoft préconise ce que j'ai dit déjà : MAJ/suppresion par les batches.

[Serguei_TARASSOV]

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.

Il vaut mieux faire un cycle (hors transaction) :

Code :

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

1
2
3
4
WHILE 1 = 1 BEGIN
    DELETE TOP (100) FROM Matable WHERE TYPE = 'B' 
    IF @@ROWCOUNT = 0 BREAK
END

[J0r_x]

Il vaut mieux faire un cycle (hors transaction) :

Code :

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

1
2
3
4
WHILE 1 = 1 BEGIN
    DELETE TOP (100) FROM Matable WHERE TYPE = 'B' 
    IF @@ROWCOUNT = 0 BREAK
END

En faisant ceci le DELETE est bloqué.

[Serguei_TARASSOV]

En faisant ceci le DELETE est bloqué.

Même sur la table de 1000 lignes de 'A' et 1000 de 'B' cela ne s'est reproduit pas.
Il faut assurer, qu'il n'y a pas d'autres verrous sinon le blocage est tout à fait normal.

Code :

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

1
2
3
4
5
SELECT rsc_type, req_mode, rsc_text FROM sys.syslockinfo WHERE rsc_objid = object_id('Matable')
WHILE 1 = 1 BEGIN
    DELETE TOP (100) FROM matable WHERE TYPE = 'B'
    IF @@ROWCOUNT = 0 BREAK
END

Voir syslockinfo valeurs

L'info "idéal" d'après syslockinfo devrait être comme cela

rsc_type req_mode rsc_text
-------- -------- --------------------------------
6 7 1:164
7 4 (6d7811f5b762)
5 8

[elsuket]

Bonjour,

Effectivement procéder par lots peut être une solution.
En revanche j'aurai plutôt :

- stocké la colonne de clé primaire dans une table à part
- posé un index cluster dessus
- ajouté une colonne de type entier qui est soit le modulo de la même valeur que la taille du batch, soit un ROW_NUMBER() OVER(PARTITION BY ... ORDER BY) et posé un index dessus.

Ensuite avec une CTE, on prend les lignes qui ont pour modulo la valeur 1, et on tente de les supprimer de la table cible. Si ça ne fonctionne pas, on prend les modulo de valeur 2, et ainsi de suite.
Quand ça a fonctionné, on supprime les lignes de même modulo dans la table auxiliaire.

Quelque chose du style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SELECT	ma_table_PK
	, ma_table_PK % 1000 AS modulo
INTO	dbo.ma_table_AUX
FROM	dbo.ma_table
GO
 
ALTER TABLE dbo.ma_table_AUX
ADD CONSTRAINT PK_ma_table_AUX PRIMARY KEY (ma_table_PK)
GO
 
CREATE INDEX IX_ma_table_AUX__modulo
ON dbo.ma_table_AUX(modulo)
GO
----------------------------------------------
-- Attendre 30 secondes qu'un verrou soit libéré
-- C'est une option de session qui n'affecte donc pas les autres
SET LOCK_TIMEOUT 30000
GO
 
DECLARE @modulo smallint = 0
	, @err_msg nvarchar(2048)
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.ma_table_AUX
)
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
			;WITH
				CTE AS
				(
					SELECT	ma_table_PK
					FROM	dbo.ma_table_AUX
					WHERE	modulo = @modulo
				)
			DELETE		FROM dbo.ma_table_AUX
			FROM		CTE AS C
			INNER JOIN	dbo.ma_table_AUX AS A
						ON C.ma_table_PK = A.ma_table_PK
 
			DELETE	FROM dbo.ma_table_AUX
			WHERE	modulo = @modulo
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SET @err_msg = ERROR_MESSAGE()
 
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION
		END
 
		RAISERROR(@err_msg, 1, 1) WITH NOWAIT
	END CATCH
 
	SET @modulo += 1
END

@++

[J0r_x]

J'obtiens ça donc c'est idéal non ?

rsc_type req_mode rsc_text
-------- -------- --------------------------------
5 8
7 4 (ab00d9828be2)
6 7 1:79211

Et pourtant le delete top(100) bloque.

[Serguei_TARASSOV]

J'obtiens ça donc c'est idéal non ?

rsc_type req_mode rsc_text
-------- -------- --------------------------------
5 8
7 4 (ab00d9828be2)
6 7 1:79211

Et pourtant le delete top(100) bloque.

Normalement, cet info nous dit que la transaction de lecture ne bloque qu'une clé et a l'intention de bloquer la page par "IU".

Alors, c'est la transaction d'écriture qui pose de pb. Normalement, elle verrou les clés et pose l'intention de verrouiller la page correspondant par "IX".
Selon la matrice de compatibilité, IU n'est pas compatible avec IX.
Donc, si ta ligne de type 'A' est sur la page qui contient même une seule ligne de type 'B', cela bloquera la transaction de suppression.

Il faut donc
1) défragmenter (rebuild) ta clé cluster afin de minimiser la mixe des types 'A' et 'B' sur les mêmes pages en prenant la colonne de type comme la première de la clé composite (ce que réduit la sélectivité puisque dans la clé les colonnes plus sélectives doit avancer)
2) varier la taille de batch (100-10 etc), mais cela ne garantit pas l’absence des collisions en 100%
3) Penser à la solution de verrouillages au niveau applicatif (i.e. la table des verrous des utilisateurs ou versioning des lignes) et non pas au niveau SQL Server

[Rei Ichido]

Et sinon, faire un lock fonctionnel ? Une bête table (id_fiche, id_utilisateur), clé primaire sur id_fiche, et qu'on interroge avant d'accéder en lecture / modification à une fiche ?
Parce que là, si je comprend bien, vous tentez de faire de la lecture avec récupération d'erreur dans le cas du lock au bout du timeout ?

[J0r_x]

C'est ça.

[mikedavem]

Normalement, cet info nous dit que la transaction de lecture ne bloque qu'une clé et a l'intention de bloquer la page par "IU".

Alors, c'est la transaction d'écriture qui pose de pb. Normalement, elle verrou les clés et pose l'intention de verrouiller la page correspondant par "IX".
Selon la matrice de compatibilité, IU n'est pas compatible avec IX.
Donc, si ta ligne de type 'A' est sur la page qui contient même une seule ligne de type 'B', cela bloquera la transaction de suppression.

Il faut donc
1) défragmenter (rebuild) ta clé cluster afin de minimiser la mixe des types 'A' et 'B' sur les mêmes pages en prenant la colonne de type comme la première de la clé composite (ce que réduit la sélectivité puisque dans la clé les colonnes plus sélectives doit avancer)
2) varier la taille de batch (100-10 etc), mais cela ne garantit pas l’absence des collisions en 100%
3) Penser à la solution de verrouillages au niveau applicatif (i.e. la table des verrous des utilisateurs ou versioning des lignes) et non pas au niveau SQL Server

Je suis assez d'accord avec cela. Le blocage par hint (UPDLOCK, ROWLOCK) est là pour bloquer les accès concurrents en modification. On pose intentionnellement un verrou de type U + IX. Le problème de la maintenance des index ou des collisions hasardeuses font que cette méthode est au final plus qu'aléatoire. Le verrouillage applicatif sera beaucoup plus adapté dans ce cas.

++

[J0r_x]

Je vois bien, je croyais que MSSQL Serveur permettez de gérer ce genre de chose facilement, je trouve dommage de rajouter une couche supplémentaire.
Merci pour toutes ces informations

[Serguei_TARASSOV]

Je vois bien, je croyais que MSSQL Serveur permettez de gérer ce genre de chose facilement, je trouve dommage de rajouter une couche supplémentaire.
Merci pour toutes ces informations

SGBDR manipules les ensembles et donc dans la majorité des cas s'est inutile et inefficace de chercher les solutions qui se basent sur le traitement des lignes solitaires. Il faut juste prendre en tête que le monde relationnel est orthogonal à celui d'objet/procédural. Ce n'est ni bon ni mauvais, c'est comme ça...