Discussion :

[StringBuilder]

Bonjour,

J'ai un traitement sur lequel je n'ai pas la main (ni les requêtes exécutées, ni le code appelant).

Il permet de modifier un certain nombre de données dans un certain nombre de tables.

Visiblement, toutes les modifications ne sont pas faites depuis la même connexion à la base de données, et ça me pose des soucis de Lock.

J'ai la procédure suivante qui est lancée une première fois dans une transaction sur une session :

Code sql :

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

1
2
3
4
 
ALTER PROCEDURE [dbo].[TE_ISI_NextId] @table VARCHAR(32), @id BIGINT OUTPUT 
AS
    UPDATE TE_ISI_MM_ID_TABLE SET mmid=mmid+1, @id = mmid+1 WHERE tablename=@table

Pendant l'exécution de cette transaction, depuis une autre session (c'est tout du moins ce que je comprends), il y a une mise à jour t'une table sur laquelle j'ai mis un trigger :

Code sql :

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
ALTER TRIGGER [dbo].[TE_ISI_C008_UPDATE] 
ON [dbo].[TE_ISI_C008] 
AFTER INSERT, UPDATE, DELETE
AS
	declare @staNo bigint, @repId bigint, @oj_StaNo bigint, @oj_SerNo bigint, @c011_newid bigint;
BEGIN
	set nocount on;
 
	if (select count(*) from inserted) > 0 or (select count(*) from deleted) > 0
	begin;
		-- Lorsque le statut d'un indice change, alors le statut du dossier doit être modifié en conséquence.
		-- Le statut de l'affaire doit être modifié en cascade avec les mêmes règles
		-- La règle de base est "Le statut du dossier est égal au 'max' du statut des indices".
		with dest (id_oj, max_status)
		as
		(
			select C008.ID_OJ, max(
				case KA.ExtKey
					when 'ETUDE' then 2
					when 'COMMANDE' then 3
					when 'PERDU' then 0
					when 'NON_RETENU' then 1
				end
			)
			from TE_ISI_C008 C008
			inner join TE_ISI_KA KA on KA.KatNr = 1056 and KA.Code = C008.F7013 and KA.SpracheNr = 0
			where C008.ID_OJ in (
				select i.ID_OJ from inserted i
				union
				select d.ID_OJ from deleted d
			)
			and C008.DEL = 0
			group by C008.ID_OJ
		)
		update oj
		set oj.f7077 = case dest.max_status
							when 2 then 1
							when 3 then 2
							when 0 then 3
							when 1 then 4
					   end					
		from te_isi_oj oj
		inner join dest on dest.id_oj = oj.id
		where oj.f7077 <> case dest.max_status
							when 2 then 1
							when 3 then 2
							when 0 then 3
							when 1 then 4
					   end
		and oj.Objektart = 2;
 
		-- On applique la même règle à l'affaire
		with ori (id, statut)
		as
		(
			select do.ID_OJ, case max(case do.F7077
						when 1 then 1
						when 2 then 3
						when 3 then 2
						when 4 then 0
					end) when 0 then 8 when 1 then 9 when 2 then 5 when 3 then 4 end
			from te_isi_oj do
			where do.Objektart = 2 and do.LosKZ = 0
			and do.ID_OJ in (
				select i.ID_OJ_1 from inserted i
				union
				select d.ID_OJ_1 from deleted d
			)
			group by do.ID_OJ
		)
		update af
			set af.F7080 = ori.statut
		from te_isi_oj af
		inner join ori on ori.id = af.id
		where af.F7080 <> ori.statut;
 
		-- Historisation du changement de statut
		if (@@rowcount > 0)
		begin
			--exec [dbo].[TE_ISI_NextId] 'TE_ISI_C011', @c011_newid output;
 
			--insert into TE_ISI_C011 (id, new_dt, upd_dt, new_sno, del, com, id_oj, id_fi, F7007, F7008, F7009, F7010, F7011, F7012_DT)
			--select @c011_newid, format(getdate(),'yyyyMMddHHmmssfff'), format(getdate(),'yyyyMMddHHmmssfff'), 1, 0, 0, id, ID_FI_250, F7021, F7078, F7079, F7080, F7081, format(getdate(),'yyyyMMddHHmmssfff')
			--from te_isi_oj
			--where id in (
			--	select i.ID_OJ_1 from inserted i
			--	union
			--	select d.ID_OJ_1 from deleted d
			--);
			select 1;
		end;
 
		-- Si on vient de passer une étude à "Gagnée", alors on doit mettre à jour Movex
		if (select count(*) from inserted) = 1 and (select count(*) from deleted) <= 1
		begin
			if (select count(*) from inserted C008 inner join te_isi_ka ka on KA.KatNr = 1056 and KA.Code = C008.F7013 and KA.SpracheNr = 0 and ka.extkey = 'COMMANDE') = 1
				and (select count(*) from deleted C008 inner join te_isi_ka ka on KA.KatNr = 1056 and KA.Code = C008.F7013 and KA.SpracheNr = 0 and ka.extkey = 'COMMANDE') = 0
			begin
 
				select @stano = dbo.IdToStaNo(i.id), @repId = oj.F7151, @oj_StaNo = dbo.IdToStaNo(oj.id), @oj_SerNo = dbo.IdToSerNo(oj.id) from inserted i inner join te_isi_oj oj on oj.id = i.id_oj;
				exec dbo.ksp_maj_affaire_movex @stano, @repId, @oj_StaNo, @oj_SerNo;
			end;
		end;
	end;
 
	set nocount off;
end;

Ce qui nous intéresse, c'est la partie en commentaire vers la fin "Historisation du changement de statut".

Il y a un nouvel appel à la même procédure stockée.
Si je le met en commentaire, mais pas l'insert (avec une valeur arbitraire comme ID) ça fonctionne.
En revanche, si le réactive l'appel à la procédure stockée, même si le INSERT est en commentaire, ça bloque tout.

C'est comme ça que j'en ai déduit que ma table TE_ISI_MM_ID_TABLE était verrouillée par un premier appel depuis une autre transaction en cours.

Une chose dont je suis sûr : le traitement concurrent ne met pas à jour la même ligne (car j'ai justement dû rajouter ce bloc dans mon trigger car aucune ligne n'était générée dans la table C011).

Ai-je la possibilité d'ajouter un hint ou autre sur ma procédure stockée de façon à ce que la PS ne verrouille pas la table, voir même, ne verrouille QUE la ligne qu'elle modifie effectivement ?

Car là je sèche

[StringBuilder]

J'ai trouvé un souci similaire sur StackOverflow :
https://stackoverflow.com/questions/...n-mssql-server

J'étais effectivement dans le même cas : la table TE_ISI_MM_ID_TABLE n'avait pas de clé primaire sur tablename mais un index unique portant sur... (tablename, mmid) <= les deux seules colonnes de la table

Ainsi, logiquement, SQL Server ne pouvait pas trop, lors du premier update, savoir à l'avance quelle ligne allait être mise à jour.

J'ai donc rajouté une clé primaire :

Code sql :

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

alter table TE_ISI_MM_ID_TABLE add primary key (tablename);

Maintenant, lorsqu'on lance :

Code sql :

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

UPDATE TE_ISI_MM_ID_TABLE WITH (ROWLOCK) SET mmid=mmid+1, @id = mmid+1 WHERE tablename=@table

SQL Server devrait savoir qu'il ne va mettre à jour qu'une et qu'une seule ligne, et savoir donc, à la demande du hint, ne locker que LA ligne qu'on modifie.

Et pourtant, rien n'y fait, ça bloque toujours

Plus ça va, plus je me demande si le contenu de la procédure stockée n'est pas hardcodé dans le premier traitement, et si la PS n'est pas tout simplement inutilisée pour faire la première mise à jour qui lock mon trigger.

D'où une réflexion bien merdique qui me vient à l'esprit...

Est-ce possible de lancer une requête "delayed", avec retour immédiat, peut importe qu'elle échoue ou non ?

L'idée serait de demander depuis le trigger "ok, crée-moi celle ligne plus tard, quand tu auras du temps", mais de rendre la main tout de suite au trigger sans bloquer.

Ainsi, la transaction appelante se terminera tranquillement, et la l'insertion pourra se faire dans sa propre transaction sans lien avec le trigger...

J'imagine bien pouvoir lancer un Shell qui ferait un osql, mais si y'a plus propre, je serais preneur

PS : La ligne que je cherche à insérer, c'est juste un log, donc si elle ne s'insère pas tout de suite, ou si elle échoue, c'est pas bien mais c'est pas grave non plus, ça ne remet en aucun cas en cause la transaction globale.

[StringBuilder]

Bon, au pays des application merdiques, on écrit du code merdique...

Du coup je me suis passé de la procédure qui me donne un nouvel index.
Je m'en crée un moi-même sur une plage qui ne peut fonctionnellement pas être utilisée par l'outil.
Et voilà

Code SQL :

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
 
		-- Historisation du changement de statut
		if (@@rowcount > 0)
		begin
			-- [dbo].[TE_ISI_NextId] ne fonctionne pas à cause d'une sombre histoire de lock
			--exec [dbo].[TE_ISI_NextId] 'TE_ISI_C011', @c011_newid output;
			-- Du coup on simule la création de lignes depuis la station 3 qui n'existe pas...
			select @c011_newid = isnull(max(id), 0x300000000) + 1 from TE_ISI_C011 where id / 0x100000000 = 3;
 
			insert into TE_ISI_C011 (id, new_dt, upd_dt, new_sno, del, com, id_oj, id_fi, F7007, F7008, F7009, F7010, F7011, F7012_DT)
			select @c011_newid, format(getdate(),'yyyyMMddHHmmssfff'), format(getdate(),'yyyyMMddHHmmssfff'), 1, 0, 0, id, ID_FI_250, F7021, F7078, F7079, F7080, F7081, format(getdate(),'yyyyMMddHHmmssfff')
			from te_isi_oj
			where id in (
				select i.ID_OJ_1 from inserted i
				union
				select d.ID_OJ_1 from deleted d
			);
		end;

Ca marche, et j'espère je jamais avoir à me repencher sur ce trigger