Discussion :

[TeKaBloK]

Bonjour,

je développe une application PHP avec une base de données SQL Server.
Je ne sais pas comment gérer des transactions simultanées d'insert de données dans une table.

Comment je peux faire pour que lorsque deux utilisateurs importent des données (insert massif dans plusieurs tables avec une transaction qui permet de rollback en cas de probleme), l'un ne bloque pas l'autre?

Je ne suis pas sûr de devoir utiliser une transaction puisque celle-ci m'empêche de pouvoir insérer en parallèle...

Y'a-t-il un moyen de garder la transaction et de ne pas mettre de lock de la table sachant que la BDD doit rester 'intègre'...
J'ai vu que je pouvais configurer l'isolation de la transaction mais je ne suis pas familier avec ce genre de pratique.

Qu'est-ce que vous me recommandez?

Merci
Romain

[SQLpro]

Vous ne pouvez pas insérer en parallèle simultanément depuis deux sessions (ou connexions). Dans les SGBDR, et quelque soit le SGBDR, une table ne peut pas être mise à jour simultanément par différents accès.
Toute mise à jour pose un verrou exclusif empêchant toute autre mise à jour ou lecture de la table.

Décrivez un peu plus votre problème sur le plan pratique et non théorique, car je pense que vous êtes à côté de la plaque en matière de conception de votre système !

A +

[hmira]

Le fait de ne pas utiliser les transactions ne doit jamais être avancé comme argument (ou comme argumentaire) pour justifier la résolution, ou l'évitement, des inter-blocages entre sessions.
En effet, le concept de transaction est fondamental pour maintenir la cohérence et l'intégrité des données.
Le SGBD, SQL Server en l'occurrence, garanti les fameuses propriétés ACID (Atomicité, Cohérence, Isolation et Durabilité) d'une transaction.

Cependant, vous pouvez choisir un niveau d'isolation (donc ajuster la lettre I de ACID), parmi les 5 niveaux ci-dessous :
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SNAPSHOT
SERIALIZABLE

Le niveau d'isolation que vous aurez choisi, doit d'une part, en fonction des traitements envisagés, assurer et garantir l'intégrité des données et, d'autre part, permettre un meilleur accès simultané (ou accès concurrent) aux données. Vous ne devez pas raisonner uniquement sous l'ongle d'accès concurrent et inter-blocage vous devez prendre en compte l'aspect transaction et intégrité des données.

Autres remarques ou suggestions :

- Il faut veiller à ce qu'il n'y ait pas de « télescopage » (ou de recouvrement) dans les plages de clés des enregistrements insérés depuis les différentes sessions des utilisateurs. Dans le cas contraire, vous aurez assurément des blocages.

- Si les clés sont attribuées au moment de l'insertion, il vaut mieux opter pour les clés Auto incrément IDENTITY. Si vous devez attribuer, vous-même, des clés uniques sachez qu'il s'agit d'une opération très subtile et délicate et je vous conseille, sur ce point, de lire l’excellent article de SQLPro traitant du sujet « Compteurs relatifs »
http://blog.developpez.com/sqlpro/p9...tifs-avec-sql/

- Si vous optez pour le niveau d'isolation READ COMMITED (il s'agit du niveau d'isolation par défaut, et sera vraisemblablement le mieux approprié pour vous, vous pouvez réduire considérablement les blocages en utilisant l'option de base de données : READ_COMMITTED_SNAPSHOT

Code :

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

ALTER DATABASE Nom_de_votre_base SET READ_COMMITTED_SNAPSHOT ON;

Si vous définissez l'option de base de données READ_COMMITTED_SNAPSHOT à ON, le moteur de base de données utilise par défaut la gestion de versions de ligne et l'isolation d'instantané (snapshot), au lieu d'utiliser des verrous pour protéger les données, ce qui réduit considérablement les blocages, avec une contre partie bien sûr, celle qui nécessite que la base tempdb ait les reins solides ! Puisque la base tempdb sera davantage sollicitée ...

A+

[TeKaBloK]

D'un point de vue pratique:

mon application doit donner la possibilité à plusieurs utilisateurs (un trentaine) d'importer des fichiers Excel contenant des données de facturation (un mois de facturation avec des clients à créer) tous les mois. Les clients non connus sont créés à la volée dans la base de données (les clients peuvent très bien apparaître dans plusieurs fichiers).

L'application va lire un fichier Excel et récupérer les données. Ensuite l'application (un seul et même utilisateur systeme) insère les données et crée les clients (si l'utilisateur le souhaite, il peut annuler l'import). La lecture de fichier Excel peut prendre un peu de temps.

Je souhaite donc que mes utilisateurs puissent importer des fichiers en même temps. Pour se faire il faudrait qu'ils puissent insérer en même temps mais les transactions m'en empêche.

Je vais tenter de changer l'isolation des transactions comme hmira le propose afin de voir si cela marche.

[hmira]

Attention : Un client non connu, figurant dans plusieurs fichiers, et qui doit donc être créé à la volée, ne pourra pas être créé dans des transactions parallèles avec le même identifiant.

En effet, les transactions sont par définition étanches et 2 transactions parallèles tenteront, vraisemblablement, dans votre scénario, de créer le même nouveau client avec le même identifiant (on peut supposer que vous n'envisagez pas de créer les nouveaux clients en double ou en triple ! …) .

Dans cette situation, vous aurez fatalement et assurément des blocages. Ces blocages vont se manifester avant même l’apparition du message :
Msg 2627, Niveau 14, État 1, Ligne 2
Violation de la contrainte UNIQUE KEY PK_Clients Impossible d'insérer une clé en double dans l'objet 'dbo.Clients'.
L'instruction a été arrêtée.

Le dit message d'erreur n'apparaitra qu'une fois la première transaction ayant créé le nouveau client (soit plusieurs minutes après, selon la taille et le volumes de la transaction ! ) eut été validée.

Donc, tout ce ci pour vous dire que vous ne pourrez pas créer les mêmes nouveaux clients, avec des identifiants uniques, dans des transactions parallèles, avec la possibilité donnée à l'application de faire un rollback.

Et l'option de base de données READ_COMMITTED_SNAPSHOT que je vous ai indiquée ne pourra rien pour vous ! Celle-ci permet de résoudre et d'apporter une solution au problème de blocage des requêtes de lectures effectuées dans les autres transactions, mais pas celles des opérations d'écriture qui elles restent identiques et inchangées.

Il vous faudra donc trouver une autre solution pour insérer les nouveaux clients.

A+

PS : Concernant les opérations d'écritures, on retombe sur la remarque juste et sensé de SQLPro citée ci-haut.

[TeKaBloK]

Un client non connu, figurant dans plusieurs fichiers, et qui doit donc être créé à la volée, ne pourra pas être créé dans des transactions parallèles avec le même identifiant.

En fait, avant de créer le client, je regarde s'il n'a pas déjà été créé, j'ai une contrainte d'unicité sur le nom, le code postal et le pays du client. S'il est déjà créé, je lui ajoute la facturation. Après j'ai une table qui stocke la relation entre un import de fichier et l'ajout de facturation à celui-ci. Cela me permet de savoir si un client a été créé en "une seule fois" ou que de la facturation lui a été ajouté. Comme ca, si je dois annuler l'import du fichier, je ne supprime pas le client s'il dépend de plusieurs imports.

Le problème, c'est que je ne pense pas que le rollback soit possible dans ce cas, non ? Si je crée un client avec un import et qu'un autre import ajoute de la facturation a ce même client, et que le 1er import est annulé, alors la suppression du client est impossible et ca va planté lors du rollback, non ?

Si je n'utilise pas de transactions, ça devrait pouvoir marcher, non ? Je gérerai le "rollback" moi même dans le code.

[hmira]

Je pense que vous avez bien saisi et compris la nature du problème.

En revanche, je suis certain que la solution que vous envisagez n'est pas la bonne.

Ces dessous quelques éléments de réponses :

- Il faut veiller, en premier lieu, à rajouter les contraintes FK (Foreign Key ) entre les différentes tables (entre facturation et client, entre lien import/client et client , etc.. )

- Chaque traitement d'importation, lancé en parallèle, doit être décomposé en 3 phases faisant l'objet chacune d'une transaction (implicite ou explicite) séparée :
* Phase 1 : insertion des nouveaux client s'il n'existent pas déjà dans la base
* Phase 2 : importation des données de facturation
* Phase 3 : suppression des clients pour lesquels la phase 2 a été annulée ET pour lesquels il n'existe pas de données liées (données de facturation, importation etc..) (données qui peuvent être générées entre-temps par d'autre traitements parallèles sur le même client)

- Chacune des 3 phases ci-dessus doit impérativement intégrer la gestion des exceptions TRY..CATCH
En effet, il ne faut se fier uniquement aux clauses where de type :
INSERT … WHERE NOT EXISTS …
ou
DELETE .... WHERE NOT EXISTS …. pour supprimer en toute sécurité une entité pour laquelle on croit avoir vérifié qu'elle n'est pas liée à d'autres entités.

C'est une erreur malheureusement assez répondu chez les développeurs qui ne comprennent pas pourquoi le programme plante en générant l'erreur, comme illustrée dans l'exemple ci-dessous, alors même qu'ils avaient rajouté WHERE NOT EXISTS ..
Exemple :

Code :

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

1
2
3
4
5
6
7
8
BEGIN TRAN 
DELETE C
FROM Client C 
WHERE NOT EXISTS (SELECT *  
                  from Facture F 
                  WHERE F.IdClient = C.IdClient) 
-- .....                   
COMMIT

Msg 547, Niveau 16, État 0, Ligne 2
L'instruction DELETE est en conflit avec la contrainte REFERENCE "FK_Facture_Client". Le conflit s'est produit dans la base de données "MaBase", table "dbo.Facture", column 'IdClient'.

La réponse est simple. Bien souvent, la requête DELETE se met en attente d'une autre transaction :
INSERT (IdFacture, IdClient, ..) …
Exemple :

Code :

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

1
2
3
4
5
6
7
BEGIN TRAN 
INSERT INTO Facture 
(IdFacture, IdClient, Libelle ) 
values 
(2, 1, 'Facture 2 / Client 1') 
-- .... 
COMMIT

après le commit de l'Insert ( IdFacture, IdClient), la requête DELETE reprend la main (c.à.d pose un verrou exclusif sur les enregistrements à supprimer), sauf qu'entre temps la clause WHERE NOT EXITS n'est plus vrai (une facture vient d'être ajoutée au client depuis un autre traitement !) et l'instruction DELETE génère immédiatement l'erreur indiquée ci-dessus et fait planter le traitement. D’où l'intérêt de rajouter la gestion des exceptions TRY..CATCH

A+