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.