Discussion :

[sky_perrinos]

Bonjour à tous,

Je suis dans une entreprise du web, j'écris des procédures stockées t-sql qui servent de web services et qui sont donc appelées par un grand nombre de clients simultanément.
Je rencontre un problème de concurrence d'appel à un de mes web services: on doit l'appeler plusieurs fois avec la même variable @nouveau_id, mais a des fins statistiques je ne veux l'enregistrer qu'une fois en table

Il contient la simple instruction :

Code :

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

1
2
3
4
5
6
7
8
9
 
insert into ma_table(id, date)  /* avec id primary key sur ma_table */
  select distinct @nouveau_id, getdate()
  from ma_table
  where @nouveau_id is not null
  and not exists (
    select id
    from ma_table
    where id = @nouveau_id)

Je pensais de cette manière être tranquille et être sûr que je n'aurais jamais d'insertion double de clé primaire, pourtant j'ai régulièrement des erreurs à l'insertion pour violation de clé primaire. La seule explication que j'ai trouvée est des appels simultanés de mon web service.
J'ai essayé de jouer avec les niveaux d'isolation (j'ai à peu près tout essayé), mais rien n'a changé, j'ai toujours des tentatives d'insertion double.

Je voulais savoir s'il y avait un moyen de poser manuellement un verrou sur la table, pour entourer mon instruction SQL et être sûr de ne pas avoir de concurrence du tout.
Sinon, y a t'il un autre moyen d'éviter ce problème?

En vous remerciant, Thomas

[SQLpro]

Le plus simple et le plus efficace est de mettre une contrainte d'unicité et de rejeter l'insertion en cas de viol de la contrainte.

Tout autre technique ne pourra que nuire, voir donner des temps de réponse de plus en plus catastrophiques.


A +

[darkelend]

Sinon il y aurait la possibilité de mettre la colonne en auto-incrément ou sequence.

[sky_perrinos]

Bonjour SQL Pro,
Ma colonne "id" est clé primaire de la table, elle fait donc office de contrainte d'unicité, non? D'ailleurs j'ai bien une erreur "violation primary key"

[sky_perrinos]

Bonjour darkelend,
Je ne peux pas faire de l'autoincrément, ce n'est pas l'idée du tout:
Le besoin est de connaître le nombre de user unique par jour sur toutes nos plateformes confondues (web, mobile, pad, sur tous les os). Un user, de part la multitude de nos plateformes peut interroger notre service par n'importe quel web service, il n'y a pas de point d'entrée obligatoire. Pour l'enregistrer, une fois par jour, nous avons donc intégrer le bout de code ci-dessous au début de chacun de nos webs services:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
/* si l'id du user n'est pas enregistré à ce jour, on l'enregistre dans la table de tracking */
insert into TableTracking (id_user, dateAction)				
  select distinct @id_user, CONVERT(date, getdate())
  from TableTracking
  where not exists (
    select id_user
    from TableTracking
    where id_user = @id_user
    and dateAction = CONVERT(date,getdate()))

malgré la clause "not exists" à même l'instruction d'insertion, nous avons des tentatives d'insertion sur des clés doubles (j'ai une erreur "violation primary key"). Nous avons interprété cela par des appels concurrents effectués par un même user, mais ça se trouve le diagnostic n'est pas bon

[DotNetMatt]

Le problème c'est que si par exemple l'utilisateur 1 fait une action le 1/1/2013 tu auras cette ligne :

id_user = 1
dateAction = 1/1/2013

Si le lendemain il refait une action :

id_user = 1
dateAction = 2/1/2013

Ca plante, car l'id_user 1 est déjà utilisé.

Dans ce cas il faudrait :
- soit mettre une clef primaire composée des deux colonnes id_user et dateAction (la valeur de dateAction permettra de différencier les lignes),
- soit rajouter une colonne ID auto-incrémentée (ou séquence si Oracle) qui servira de clef primaire et qui permettra de distinguer clairement et arbitrairement les enregistrements.

[sky_perrinos]

Bonjour DotNetMatt

Désolé j'ai été un peu rapide dans les explications, ma clé primaire est bien sûr faite sur les deux colonnes id/date:

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE [dbo].[TableTracking](
	[id_user] [uniqueidentifier] NOT NULL,
	[dateAction] [date] NOT NULL,
 CONSTRAINT [pk_id_user_dateAction] PRIMARY KEY CLUSTERED (
	[id_user] ASC,
	[dateAction] ASC)

[SQL_EVAN]

A mon avis, votre problème vient du fait que la colonne [dateAction] a un type de données DATE. Donc ça peut arriver que le même [id_user] se connecte plusieurs sur un jour.

Il faudra mettre à jour votre table et passer la colonne [dateAction] en DATETIME ou bien SMALLDATETIME

Code :

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

1
2
3
4
5
6
CREATE TABLE [dbo].[TableTracking](
	[id_user] [uniqueidentifier] NOT NULL,
	[dateAction] [datetime] NOT NULL
 CONSTRAINT [pk_id_user_dateAction] PRIMARY KEY CLUSTERED (
	[id_user] ASC,
	[dateAction] ASC))

[Waldar]

Ce que vous faites est bien curieux :

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO TableTracking (id_user, dateAction)				
  SELECT DISTINCT @id_user, CONVERT(date, getdate())
  FROM TableTracking
  WHERE NOT EXISTS (
    SELECT id_user
    FROM TableTracking
    WHERE id_user = @id_user
    AND dateAction = CONVERT(date,getdate()))

Pourquoi lire TableTracking deux fois, dont un première fois en la surchargeant de constantes et en appliquant un distinct ?
Cette requête va être de plus en plus longue, et au final pour rien.

Faites plus simplement :

Code :

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

INSERT INTO TableTracking (id_user, dateAction) VALUES (@id_user,CONVERT(date,getdate()));

Si le couple existe déjà il sera rejeté par la PK.
Ignorez simplement l'erreur dans ce cas bien précis.

Si vraiment vous ne voulez pas l'erreur :

Code :

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

1
2
3
4
5
6
7
INSERT INTO TableTracking (id_user, dateAction)				
  SELECT @id_user, CONVERT(date, getdate())
  WHERE NOT EXISTS (
    SELECT null
    FROM TableTracking
    WHERE id_user = @id_user
    AND dateAction = CONVERT(date,getdate()))

[sky_perrinos]

Bonjour SQL_Evan

Le besoin est d'enregistrer les passages de visiteurs à la journée.
On doit pouvoir savoir que le user à l'id 23 est venu lundi, mardi et vendredi.
C'est pourquoi cette colonne contient un format date et non datetime, le but étant de ne pas avoir plusieurs entrée par jour pour un même user.

C'est un service web avec énormémént de traffic, chaque user appellant plusieurs dizaines de fois à chacune de ses visites nos web services et avec plusieurs centaines de milliers de visites par jour. Pour des problèmes de volume de données, on ne veut enregistrer qu'une fois son passage par jour, d'où l'enregistrement en date et non en datetime

[mikedavem]

Je me range à l'avis de Waldar (et SQLPro) ... vu que la contrainte d'unicité (à voir si celle-ci correspond au besoin de ton contexte) jouera pleinement son rôle ici. Cela sera beaucoup plus performant de gérer l'exception que de vérifier si l'enregistrement n'existe pas pour l'insérer.

++

[sky_perrinos]

Bonjour Waldar,
En réalité je fait déjà ce que vous suggérez: j'ai entouré mon instruction insert par un try catch pour ne pas que le web service plante, l'exception est donc gérée, mais elle apparaît donc toujours dans les logs du serveur.

Mon patron fait la chasse à ce genre de développement et à toutes traces d'exception dans les logs du serveur, il dit que ce n'est qu'une solution de contournement (et je le comprends...)

En revanche je vais implanter la requête telle que vous l'avez écrite, effectivement elle est beaucoup moins chargée que ce que j'ai pu faire!! Peut-être cela réglera t'il le problème, je vous tiens au courant!

[mikedavem]

Mais pourquoi ne pas faire le TRY CATCH directement dans SQL Server ? Tu n'aurais pas d'erreur dans les logs de votre serveur applicatif ...

++

[SQL_EVAN]

Vous dites que cet INSERT est appelé pour un utilisateur plusieurs fois par jour. Votre clé primary crée la contrainte qui oblige qu'une connection par jour par utilisateur.

Je reste sur ce que j'ai dit. C'est à cause du type DATE.

Pour faire vos statistiques de connexions par utilisateur par jour une fois que vous l'avez remplacé par un datetime vous auriez qu'à faire un

Code :

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

SELECT DISTINCT id_user, CONVERT(dateAction, DATE) FROM TableTracking

c'est tres simple en réalité

[sky_perrinos]

Mais pourquoi ne pas faire le TRY CATCH directement dans SQL Server ? Tu n'aurais pas d'erreur dans les logs de votre serveur applicatif ...

++

Bonjour mikedavem, le try catch est directement dans ma procédure stockée, nous faisons la chasse à toutes les erreurs, pas seulement les applicatives, mais aussi celles de SQL Server

[sky_perrinos]

Votre clé primary crée la contrainte qui oblige qu'une connection par jour par utilisateur

tout à fait, et c'est bien ce que je veux: être sûr que je n'ai qu'une insertion par jour par utilisateur

De plus, d'avoir l'insertion en datetime et de faire ensuite un select distinct sur convert(date, dateAction) permettrait en effet de résoudre ce problème, mais cette solution ne convient car vu le nombre d'appels de cette instruction elle insérerait plusieurs centaines de milliers de ligne par jour (l'instruction serait appellée entre 50 et 100 fois par user en datetime, au lieu d'une seule insertion en date tout court)

[sky_perrinos]

Bonjour à tous, ne pensez-vous pas qu'utiliser l'indicateur de verrouillage le plus fort TABLOCKX pourrait résoudre le problème d'appel concurrentiel:

Code :

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

1
2
3
4
5
6
7
8
 
INSERT INTO TableTracking (id_user, dateAction)				
  SELECT @id_user, CONVERT(date, getdate())
  WHERE NOT EXISTS (
    SELECT NULL
    FROM TableTracking WITH(TABLOCKX)
    WHERE id_user = @id_user
    AND dateAction = CONVERT(date,getdate()))

Est-ce que cela agirait sur les performances?

[Waldar]

Ah oui, plus aucun problème. Mais votre base va ramer et vos utilisateurs ne pourront plus de connecter !

Le TRY / CATCH, c'est utile en développement pour déboguer.
En production, c'est synonyme de "je ne suis pas sûr de ce que j'ai codé donc je vérifie tout et tant pis si ça flingue les performances".

La solution à votre problème est très simple :

1. Pas de TRY / CATCH.
2. Exécutez l'insert (celui avec VALUES).

Mais ce n'est que de la redite des précédents messages.

[sky_perrinos]

Waldar,
Merci pour ses précisions, je vais continuer les tests de performance.

Sinon, pour la solution que vous préconisez, sans try/catch et en utilisant l'insert avec values, vous aviez dit que "Si le couple existe déjà il sera rejeté par la PK.
Ignorez simplement l'erreur dans ce cas bien précis.":
Je pensais que c'est en faisant le try/catch que j'ignorais l'erreur, sans try/catch, toute ma procédure stockée plante, je ne fais pas que "sauter" l'insert. Y a t'il un autre moyen d'ignorer l'erreur?
Quoiqu'il en soit, l'erreur apparaîtra toujours dans les logs du moteur sql et le boss ne laissera pas passer...

[sky_perrinos]

Merci à tous pour toutes vos solutions proposées, je devrais m'en sortir avec ça.
Toutefois, pour des raisons de cultures personnelles, savez-vous comment est-il possible que l'instruction générique suivante puisse provoquer une erreur de tentative d'insertion de clé primaire déjà existante:

Code :

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

1
2
3
4
5
6
7
8
9
 
/* table avec colonne id qui est primary key */
insert into table(id)
  select @new_id
  where @new_id is not null
  and not exists (
    select null
    from table
    where id = @new_id)

Comment est-il possible que la clause "where not exists" soit respectée car @new_id n'est pas trouvé et que l'insert plante parcequ'il y a déjà id=@new_id dans la table!!!??? Je ne comprends pas du tout...
Est-ce bien un problème de concurrence d'appel? Quelle solution avons nous à notre disposition pour éviter cela?


Un grand merci à tous pour l'intérêt que vous avez porté à ma question