Timeout en multi-utilisateur avec 2 tables

Bonjour,

Description rapide :
Un utilisateur 1 supprime une ligne d'un table A. Avant la fin de la transaction, un utilisateur 2 essaie d'ajouter une ligne dans une table B, il obtient un timeout.

Description détaillée :
Description du cas de test :
Table A : Id_A (clé primaire en auto_incrément)
Table B : Id_B (clé primaire en auto_incrément), Id_A (clé étrangère de la table A)
Toutes les tables sont en inno_db

Lancement de 2 transactions (utilisateurs distincts) en parallèle.

Scénario :
Utilisateur 1 démarre une transaction
Utilisateur 1 supprime une ligne A1 de la table A.
Utilisateur 2 démarre une transaction
Utilisateur 2 crée une ligne sur la table B avec comme valeur de Id_A A2 (une autre ligne que celle supprimée par l'utilisateur 1)
=> L'utilisateur 2 subi un timeout (à priori car la transaction de l'utilisateur 1 n'est pas terminée)
-> Pourquoi vu qu'il s'agit d'enregistrements différents ?

Merci de vos réponses et pistes éventuelles.

Salut Philippe Lorin.

Il nous faut :

1) Un exemple de votre requête bloquante pour l'utilisateur 1 et 2.

2) la valeur de "innodb_lock_wait_timeout", ainsi que : "connect-timeout", "interactive-timeout", "wait-timeout".

3) La valeur de votre "transaction-isolation".

4) la version mysql que vous utilisez.

@+

Bonjour et merci pour votre réponse. Voici le complément d'informations :

1) Un exemple de votre requête bloquante pour l'utilisateur 1 et 2.
Utilisateur 1 : delete from A where Id_A=1; (requête bien exécutée, mais on garde la transaction ouverte car il reste des actions à faire)
Utilisateur 2 :
select * from B where Id_B=3 for update;
insert into B (Id_B, Id_A) values (3,4); (cette requête est en timeout)

2) la valeur de "innodb_lock_wait_timeout", ainsi que : "connect-timeout", "interactive-timeout", "wait-timeout".
innodb_lock_wait_timeout : 50
connect-timeout : 10
interactive-timeout : non paramétré=> valeur par défaut
wait-timeout : non paramétré

3) La valeur de votre "transaction-isolation".
transaction-isolation : READ-COMMITTED

4) la version mysql que vous utilisez.
MySQL 5.6

Précision : il s'agit d'une application windows. Qui est fortement basée sur les transactions.

Merci

Salut philippelorin.

Merci pour ce complément d'information.

1) vous avez deux utilisateurs qui travaillent sur deux tables différentes. Normalement, il ne devrait pas y avoir de deadlock.

Avez-vous utilisez quelque part un "lock table" ?

Citation:

---

Envoyé par philippelorin

(requête bien exécutée, mais on garde la transaction ouverte car il reste des actions à faire)

---

Que voulez-vous dire par "la transaction ouverte" ? Que vous n'avez pas fait de "commit" juste après avoir effectué le delete pour l'utilisateur 1.

Afin d'éviter un blocage, il faut faire de temps en temps un "commit" pour valider le travail précédemment effectué.
Mais pas n'importe comment. Vous devez faire un commit par grappe de donnée cohérente.

Soit un client "toto" et un autre client "tata" et j'interviens dans deux tables "t1" et "t2".
Le schéma de mon intervention se fait ainsi :
--> client "toto".
--> mise à jour "T1".
--> mise à jour "T2".
--> "commit".

--> client "tata".
--> mise à jour "T1".
--> mise à jour "T2".
--> "commit".

Inversement, ne faites pas :
--> client "toto".
--> mise à jour "T1".
--> "commit".
--> mise à jour "T2".
--> "commit".

--> client "tata".
--> mise à jour "T1".
--> "commit".
--> mise à jour "T2".
--> "commit".

Car si vous avez un plantage système quand vous êtes sur la seconde table, peu importe le client, votre base ne sera plus intègre.
Par grappe, j'entends une validation (le commit) à la fin de vos manipulation sur un client donnée.

Autre remarque, il n'est pas nécessaire de faire cela pour chaque client, mais rien ne vous empêche de le faire disons tous les 10 clients.
A vous de juger ce qui est le plus pertinent.

2) j'ai eu un jour un problème de timeout sur une transaction un peu trop longue à mon gout.
Pour résoudre ce problème, j'ai augmenté la valeur de ces paramètres, que j'ai placé dans mon fichier "my.ini" :

Code:

---

1 2 3 4

innodb-lock-wait-timeout = 600 connect-timeout = 600 interactive-timeout = 600 wait-timeout = 600

---

Il faut dire que mon exécution était anormalement long et de ce fait, je n'arrivais jamais à le terminer.
Sauf en augmentant ces quatre paramètres à 600 secondes, soit 10 minutes.

Avez-vous essayé d'augmenter ces paramètres ?

3) Je n'utilise pas "transaction-isolation : READ-COMMITTED".
Comme précédemment, dans mon fichier "my.ini", j'ai mis :

Code:

---

transaction-isolation = SERIALIZABLE

---

Je suis resté dans le cas le plus général, mais au niveau de MySql et pas au niveau de php, si c'est le langage que vous utilisez.

@+

Merci pour votre réponse.

En fait, nous attendons une action de l'utilisateur pour valider ou annuler la transaction. De ce fait, nous ne maîtrisons pas la durée nécessaire de la transaction.

Ce que je ne comprends pas c'est pourquoi la suppression d'une ligne de la table A (delete A) bloque une création de la table B (insert B) alors que la clé étrangère de B ,'est pas celle que l'on a supprimé dans l'autre transaction.

Y a-t-il un réglage particulier de la base de données à effectuer pour éviter ce problème (autre que l'utilisation du READ UNCOMITTED) ?

Comment fonctionne précisément le verrouillage de clé suivante ?

Merci

Salut philippelorin.

Vous nous expliquez votre problème en croyant que cela fonctionne comme vous le supposez.
Je suis resté très général, et avec le peu d'information que j'ai à ma disposition, il m'est assez difficile de vous répondre.

Il nous faudrait une vue d'ensemble pour comprendre ce que vous faites.
Voici un exemple issue de la documention sur le deadlock :
--> http://dev.mysql.com/doc/refman/5.7/...ock-modes.html

Sur cet exemple, le verrou mis sur le select de l'utilisateur A, bloque l'utilisateur B sur le delete.
Tant que la table ne sera pas déverrouillée par l'utilisateur A, l'utilisateur B ne pourra pas effectuer la suppression.

Si l'on fait un verrouillage au niveau de la table, on procède ainsi :

Code:

---

1 2 3

LOCK TABLES table_1 WRITE, table_2 WRITE; INSERT INTO table_1 SELECT * FROM table_2; UNLOCK TABLES;

---

Ceci a surtout l'inconvénient de bloquer deux tables, mais à le mérite que vous êtes le seul à faire cette opération.

Quand vous faites un :

Code:

---

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

---

cela verrouille la sélection que vous avez faites. A la condition que personne d'autre ne l'ait faite avant vous.
Vous pouvez ensuite faire votre mise à jour sans aucune crainte.
Pour déverrouiller, vous devez valider le travaille que vous venez de faire en faisant un 'commit'.

Il y a plus simple que cela en utilisant :

Code:

---

1 2	SELECT * FROM parent WHERE NAME = 'Jones' for Update; UPDATE parent SET counter = counter + 1;

---

Le verrouillage des tables, ou des lignes est une opération assez délicate et peut dans certains cas provoquer des deadlock.

@+

Salut Artemus24,

Mon cas est inversé par rapport à l'exemple : j'ai une suppression de ligne qui à priori bloque une création.

Comme indiqué dans mon premier message, j'ai 2 tables :
Table A :

• colonne Id_A (clé primaire en auto_incrément)

Table B :

• colonne Id_B (clé primaire en auto_incrément),
• colonne Id_A (clé étrangère de la table A)

Toutes les tables sont en inno_db

Lancement de 2 transactions (utilisateurs distincts) en parallèle.

et voici le scénario :
Utilisateur 1 :

1. start transaction;
2. select * from A where Id_A=2 for update;
3. delete from A where Id_A=2;

l'utilisateur 1 ne ferme pas la transaction car il a d'autres actions à effectuer

Utilisateur 2 :

1. start transaction;
2. insert into B (Id_B, Id_A) values (1,40);

=> L'utilisateur 2 subi un timeout (à priori car la transaction de l'utilisateur 1 n'est pas terminée)

Je ne fais pas de verrouillage explicite autre que le select for update qui selon ce que j'ai compris ne devrait verrouiller que la ligne Id_A=2.
ps : j'ai essayé avec et sans le select for update de l'utilisateur 1 et le résultat est le même.

@+

Salut philippelorin.

Inutile de me répéter ce que je sais déjà ! :?

Citation:

---

Envoyé par philippelorin

Lancement de 2 transactions (utilisateurs distincts) en parallèle.

---

Hormis des problèmes de droits d'accès à la base de données, avoir deux utilisateurs distincts n'a aucune importance.

Ce qui est important, c'est d'avoir au même instant, deux session qui travaillent sur la même base de données.
Et donc, des problèmes concurrentiels. D'où savoir gérer les transactions et les verrous.

Je tiens à préciser quel est mon environnement avant d'essayer de reproduire ton problème.
1) même utilisateur, donc même fichier my.ini, mais deux sessions ouvertes avec le batch windows. Voici ce que je lance :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

@echo off   setlocal enableDelayedExpansion   chcp 1252 > nul   set PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH%   mysql --verbose --force @echo. pause exit

---

2) hors test, je vais créer une base de données de nom 'base' + deux tables. L'une de nom 'père' et l'autre de nom 'fils'.
Il y a une clef étrangère de la table 'fils' vers la table 'pere'.

Code:

---

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 109

-------------- SET AUTOCOMMIT = 0 --------------   -------------- START TRANSACTION --------------   -------------- SET session collation_connection=latin1_general_ci --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `pere` --------------   -------------- CREATE TABLE `pere` ( `num` integer unsigned NOT NULL AUTO_INCREMENT primary key, `lib` char(06) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `pere` (`lib`) VALUES ('Père 1'), ('Père 2'), ('Père 3') --------------   -------------- select * from pere --------------   +-----+--------+ | num | lib | +-----+--------+ | 1 | Père 1 | | 2 | Père 2 | | 3 | Père 3 | +-----+--------+ -------------- DROP TABLE IF EXISTS `fils` --------------   -------------- CREATE TABLE `fils` ( `pos` integer unsigned NOT NULL AUTO_INCREMENT primary key, `cle` integer unsigned NOT NULL, `chn` char(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 1'), (1, 'Père 1 - Fils 2'), (2, 'Père 2 - Fils 1'), (3, 'Père 3 - Fils 1'), (3, 'Père 1 - Fils 2') --------------   -------------- select * from fils --------------   +-----+-----+-----------------+ | pos | cle | chn | +-----+-----+-----------------+ | 1 | 1 | Père 1 - Fils 1 | | 2 | 1 | Père 1 - Fils 2 | | 3 | 2 | Père 2 - Fils 1 | | 4 | 3 | Père 3 - Fils 1 | | 5 | 3 | Père 1 - Fils 2 | +-----+-----+-----------------+ -------------- select * from pere as p inner join fils as f on f.cle = p.num --------------   +-----+--------+-----+-----+-----------------+ | num | lib | pos | cle | chn | +-----+--------+-----+-----+-----------------+ | 1 | Père 1 | 1 | 1 | Père 1 - Fils 1 | | 1 | Père 1 | 2 | 1 | Père 1 - Fils 2 | | 2 | Père 2 | 3 | 2 | Père 2 - Fils 1 | | 3 | Père 3 | 4 | 3 | Père 3 - Fils 1 | | 3 | Père 3 | 5 | 3 | Père 1 - Fils 2 | +-----+--------+-----+-----+-----------------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 1 --------------     Appuyez sur une touche pour continuer...

---

3) J'ouvre une session 1 et je fais le test que tu m'as indiqué :

Code:

---

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

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 114 Server version: 5.7.11-log MySQL Community Server (GPL)   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use base; Database changed mysql> SET AUTOCOMMIT = 0; -------------- SET AUTOCOMMIT = 0 --------------   Query OK, 0 rows affected (0.00 sec)   mysql> START TRANSACTION; -------------- START TRANSACTION --------------   Query OK, 0 rows affected (0.00 sec)   mysql> select * from pere where num = 2 for update; -------------- select * from pere where num = 2 for update --------------   +-----+--------+ | num | lib | +-----+--------+ | 2 | Père 2 | +-----+--------+ 1 row in set (0.00 sec)   mysql> delete from pere where num = 2; -------------- delete from pere where num = 2 --------------   Query OK, 1 row affected (0.00 sec)   mysql>

---

Rien de particulier à signaler !

4) j'ouvre une session 2 et je fais l'autre test, celui que tu m'as indiqué :

Code:

---

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

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 115 Server version: 5.7.11-log MySQL Community Server (GPL)   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use base; Database changed mysql> SET AUTOCOMMIT = 0; -------------- SET AUTOCOMMIT = 0 --------------   Query OK, 0 rows affected (0.00 sec)   mysql> START TRANSACTION; -------------- START TRANSACTION --------------   Query OK, 0 rows affected (0.00 sec)   mysql> insert into fils (`cle`,`chn`) values (1, 'Père 1 - Fils 3'); -------------- insert into fils (`cle`,`chn`) values (1, 'Père 1 - Fils 3') --------------   Query OK, 1 row affected (0.00 sec)   mysql> select * from pere; -------------- select * from pere --------------   ^C -- sending "KILL QUERY 115" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> mysql> select * from fils; -------------- select * from fils --------------   +-----+-----+-----------------+ | pos | cle | chn | +-----+-----+-----------------+ | 1 | 1 | Père 1 - Fils 1 | | 2 | 1 | Père 1 - Fils 2 | | 3 | 2 | Père 2 - Fils 1 | | 4 | 3 | Père 3 - Fils 1 | | 5 | 3 | Père 1 - Fils 2 | | 6 | 1 | Père 1 - Fils 3 | +-----+-----+-----------------+ 6 rows in set (0.00 sec)   mysql>

---

5) J'ai fait en plus un select sur la table père que j'ai dû avorter car il ne me rendait plus la main.
Comme j'ai mis 10 minutes de timeout, c'est normal que je fasse cela.
Bon, je n'ai pas attendu la fin des 10 minutes pour vérifier qu'il m'indique bien un dead lock.

J'ai fait aussi un select sur la table fils, sans aucun problème.

6) Pourquoi suis-je bloqué sur la table père ?
A cause du verrou installé ! Pour déverrouiller, je dois faire un commit ou un rollback, dans la session 1 :

Code:

---

1 2 3 4 5 6 7 8

mysql> commit; -------------- commit --------------   Query OK, 0 rows affected (0.32 sec)   mysql>

---

Et je refais le même test sur la session 2 :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> select * from pere; -------------- select * from pere --------------   +-----+--------+ | num | lib | +-----+--------+ | 1 | Père 1 | | 3 | Père 3 | +-----+--------+ 2 rows in set (0.00 sec)   mysql>

---

Le père dont "num = 2" a bien été détruit.
Comme tu le constates, il me donne bien le contenu de la table sans aucun attente.

7) comment résoudre ce problème ?
Normalement, je t'ai indiqué tout ce dont tu as besoin de savoir :

Code:

---

transaction-isolation = SERIALIZABLE

---

Puis augmenter le timeout comme je l'ai fait :

Code:

---

1 2 3 4

innodb-lock-wait-timeout = 600 connect-timeout = 600 interactive-timeout = 600 wait-timeout = 600

---

600 secondes, c'est 10 X 60 secondes, soit 10 minutes.

8) comme je l'ai indiqué dans mon post #4, tout l'intérêt se trouve dans le commit sur une grappe de données.
Fréquemment, cette validation des données est mal faite dans les traitements.

Pour remédier à ce problème, tu dois utiliser "serializable" pour le transaction-isolation", que tu devras placer dans ton fichier "my.ini".

En ce qui concerne le timeout, je te conseille de l'augmenter, juste pour laisser à MySql, le temps de finir le travail qu'il a commencé à faire.
Admettons que le temps nécessaire pour faire une validation d'une grappe nécessite disons 60 secondes. Oui, je sais, j'ai énormément exagéré ! :aie:
Fait en sorte de mettre au moins 70 secondes pour ce timeout et dans ce cas, tu n'auras plus de problème.

@+

Merci pour la réponse et les tests effectués.

Je n'ai pas vu dans la création de la table fils, la définition de la clé étrangère (foreign key) car c'est elle qui génère le problème.
Mais on voit quand même le timeout. Comme tu l'as indiqué en 6, le delete à installé un verrou sur la table père. OK mais pourquoi le verrou est -il mis sur toute la table père ? Il aurait suffit de le mettre sur la ligne supprimée uniquement. Y a-t-il un moyen de paramétrer MySQL pour ne verrouiller que l'enregistrement supprimé et non toute la table ? D'ailleurs, en regardant la liste des verrous, je ne le voyais pas celui ci.


Nous ne pouvons pas faire de commit ni rollback dans la session 1 car nous n'avons pas terminé les actions et attendons une réponse de l'utilisateur pour continuer l'action. (Nous utilisons windev qui fait un lien direct entre la base de donnée et l'IHM).

Encore merci pour le temps que vous consacrez à ce sujet.

@+

Salut philippelorin.

Citation:

---

Envoyé par philippelorin

Je n'ai pas vu dans la création de la table fils, la définition de la clé étrangère (foreign key) car c'est elle qui génère le problème.

---

Oups ! En effet, je ne sais pas trop ce que j'ai fait au moment de la sauvegarde, mais j'ai perdu le foreign key.
Je l'ai remis dans la table 'fils'. Je mets ci-après que la création de la table 'fils'.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

-- ============ -- Table `fils` -- ============ DROP TABLE IF EXISTS `fils`; CREATE TABLE `fils` ( `pos` integer unsigned NOT NULL AUTO_INCREMENT primary key, `cle` integer unsigned NOT NULL, `chn` char(15) NOT NULL, CONSTRAINT `FK_01` FOREIGN KEY (`cle`) REFERENCES `pere` (`num`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED;

---

Je tiens aussi à préciser que pour faire les tests, j'ai modifié les quatre paramètres afin de ne pas voir ma session avortée.
Je me retrouve comme si j'étais en timeout infinie !

9) j'ai refais le test de la session 1, cela ne change strictement rien.

10) j'ai refais le test de la session 2, et il y a quelques changements.

a) accès en select sur la table 'pere'.

Code:

---

1 2 3 4 5 6 7

select * from pere --------------   ^C -- sending "KILL QUERY 119" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> select * from fils;

---

C'est normal, car je demande l'accès aussi à la ligne 2, qui a été détruite et non validé.

b) je recommence le même test d'accès sur la table 'pere', mais en prenant tout sauf la ligne 2.

Code:

---

1 2 3 4 5 6 7 8 9 10

select * from pere where num in (1, 3) --------------   +-----+--------+ | num | lib | +-----+--------+ | 1 | Père 1 | | 3 | Père 3 | +-----+--------+ 2 rows in set (0.05 sec)

---

L'accès se fait correctement !

Citation:

---

Envoyé par philippelorin

Mais on voit quand même le timeout. Comme tu l'as indiqué en 6, le delete à installé un verrou sur la table père. OK mais pourquoi le verrou est-il mis sur toute la table père ?

---

Pas sur la totalité de la table 'pere', mais uniquement sur la ligne "num=2", puisque tu fais la suppression de cette ligne.

Citation:

---

Envoyé par philippelorin

Il aurait suffit de le mettre sur la ligne supprimée uniquement. Y a-t-il un moyen de paramétrer MySQL pour ne verrouiller que l'enregistrement supprimé et non toute la table ?

---

Mais c'est déjà le cas !

c) J'accède maintenant à la table 'fils' :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> select * from fils; -------------- select * from fils --------------   ^C -- sending "KILL QUERY 5" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> select * from fils where cle in (1,3); -------------- select * from fils where cle in (1,3) --------------   ^C -- sending "KILL QUERY 5" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql>

---

Je constate que j'ai un problème d'accès et ce, à cause de ma foreign key.
Ce qui me parait suspect, c'est que la foreign key est bloquée !

d) je fais à nouveau le même test que c) mais je me mets maintenant dans une autre "transaction isolation". Je suis toujours dans la session 2.

Code:

---

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

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -------------- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ --------------   Query OK, 0 rows affected (0.00 sec)   mysql> start transaction; -------------- start transaction --------------   Query OK, 0 rows affected (0.00 sec)   mysql> select * from fils; -------------- select * from fils --------------   ^C -- sending "KILL QUERY 5" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> select * from fils where cle in (1,3); -------------- select * from fils where cle in (1,3) --------------   ^C -- sending "KILL QUERY 5" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql>

---

Le blocage est encore présent, même si je demande un accès en lecture sur la table 'fils'.

e) idem que d) mais sur une autre "transaction isolatin" avec :
--> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--> SET SESSION TRANSACTION READ WRITE;
--> SET SESSION TRANSACTION READ ONLY;
Et à chaque fois, j'obtiens le même résultat.

f) cela demande quelques explications !

Quand tu définies :

Code:

---

1 2	select * from pere where num = 2 for update; delete from pere where num = 2;

---

tu es dans un mode "exclusif qui dans la littérature est noté "ix" pour "Intention exclusive".
L'autre mode noté "is" pour "Intention shared" est le mode partagé.

De ce fait, toute demande faite dans la session 2 sera rejeté et c'est normale.
Pour pourvoir faire quelque chose, il faut attendre la fin de la session 1, c'est-à-dire le commit.
Si tu désires resté dans ce mode exclusif, je te conseille d'augmenter le timeout.

Ensuite, pourquoi ne peut-on pas insérer une nouvelle ligne dont la table 'fils' quand le mode exclusif est en activité ?
MySql bloque l'index durant le mode exclusif de la session 1 et de ce fait, aucune insertion n'est possible.

Mais je rappelle que la durée d'un timeout est paramétrable.
Même si la validation d'une grappe prend beaucoup de temps, il faut savoir l'évaluer.
Et corriger la valeur des quatre paramètres que j'ai indiqué à une valeur supérieure afin de ne pas être virée.

Citation:

---

Envoyé par philippelorin

Nous ne pouvons pas faire de commit ni rollback dans la session 1 car nous n'avons pas terminé les actions et attendons une réponse de l'utilisateur pour continuer l'action. (Nous utilisons windev qui fait un lien direct entre la base de donnée et l'IHM).

---

g) je ne sais pas quoi répondre à cette question car cela sort d'un fonctionnement normale de MySql.
Autrement dit l'utilisateur part pisser, voire rencontre son patron et celui-ci l'invite à manger ... La seule solution est d'augmenter le timeout, je ne voie que cette solution.

Maintenant si tu ne fais ni un commit, ni un roolback, l'autre solution quand tu fais ce traitement est d'être le seul à travailler. Sous MySql, tu fais :

Code:

---

max-connections = 1

---

Il y a un accès pour le super utilisateur et un accès pour l'utilisation sans privilège.

@+

Salut philippelorin.

Soit deux solutions possibles pour résoudre ton problème :

1) tu fais en sorte d'être le seul à travailler sur ton projet.
Ce qui implique l'exclusivité au niveau des tâches qui peuvent s'exécuter en parallèle.

2) si tu manipules en exclusivité que la table 'pere', une solution consiste à ne pas travailler directement dessus, mais sur une copie.
Quand le traitement est terminé, tu bascules ta copie, en remplacement de la table 'pere' d'origine.
Vis-à-vis de la table 'fils', le seul problème reste sur la foreign key.
L'utilisateur pourra ajouter de nouvelles lignes sur la table 'fils' qui seront en relation sur les nouvelles lignes de la table 'pere', qu'après la recopie.
Par contre, tu risques d'avoir un problème d'intégrité vis-à-vis de la table 'fils', sur les lignes de la table 'pere' qui auront disparu.
Une manipulation sera nécessaire afin de recréer les foreign key.

Le problème de ton traitement est que tu désires faire ni un commit ni un rollback.
Comme tu fais un accès en exclusivité sur cette table 'pere', tu pénalises les autres utilisateurs qui en ont besoin.

Je pense que si tu rencontres ce problème d'accès concurrent, c'est que ton projet a été mal pensé dès le départ.
Je n'ai jamais vu créer des verrous exclusifs sur une ou plusieurs tables, sans faire dans la foulée, une validation par la suite.

L'autre solution hors traitement, consiste comme je l'ai dit, d'augmenter la durée du timeout.

@+

Encore merci pour tout,

En notant des différences dans nos actions, j'ai détecté un comportement étrange.

Concernant les options sur les foreign key, je laisse généralement tout à "No action" : On Update et On Delete (je préfère gérer moi-même, cela force à réfléchir à ce que l'on programme et évite de perdre des données si on n'a pas bien programmé).

Je relate ici 2 tests effectués pour lesquels je trouve la différence étrange (dans les 2 cas, j'ai laissé l'option de foreign key à CASCADE) :
Voici le script de création des tables et données :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE TABLE `pere` ( `num` int(10) unsigned NOT NULL AUTO_INCREMENT, `lib` char(6) COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;   CREATE TABLE `fils` ( `pos` int(10) unsigned NOT NULL AUTO_INCREMENT, `cle` int(10) unsigned NOT NULL, `chn` char(15) COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`pos`), KEY `FK_01` (`cle`), CONSTRAINT `FK_01` FOREIGN KEY (`cle`) REFERENCES `pere` (`num`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED;   INSERT INTO `pere` (`lib`) VALUES ('Père 1'), ('Père 2'), ('Père 3'); INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 1'), (1, 'Père 1 - Fils 2'), (3, 'Père 3 - Fils 1'), (3, 'Père 1 - Fils 2');

---

J'ai volontairement pas mis de fils sur le père n°2 (cela correspond à mon exemple d'origine).
Je relance le test initial, à savoir sur une session 1 :

Code:

---

1 2 3

START TRANSACTION; delete from pere where num = 2;

---

Tout se passe correctement, et sur une session 2 :

Code:

---

1 2 3

START TRANSACTION; insert into fils (`cle`,`chn`) values (1, 'Père 1 - Fils 3');

---

Le script est bloqué (il semble y avoir un verrou).
Je termine les sessions (rollback sur les 2 sessions) et ajoute un fils au pere 2 (celui que l'on supprime) :

Code:

---

1 2	INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1');

---

Et je relance les 2 scripts des 2 sessions comme précédemment :
Session 1 : tout se passe correctement;
Session 2 : tout se passe bien !
Résultat des courses :

• si dans la session 1, le delete effectue une suppression dans la table père uniquement, cela bloque l'ajout de ligne dans la table fils pour toutes les autres sessions
• si dans la session 1, le delete effectue une suppression dans la table père et par cascade dans la table fils, cela ne bloque pas l'ajout de ligne dans la table fils

Ce comportement me semble bien étrange. (C'est le premier cas qui me dérange)

@+

Salut philippelorin.

Citation:

---

Envoyé par philippelorin

En notant des différences dans nos actions, j'ai détecté un comportement étrange.

---

Je n'ai pas le même comportement que toi. Tu as dû faire une erreur quelque part.

Session 1

Code:

---

START TRANSACTION;

---

Tu es dans le mode transactionel. Chaque opération faite sur la table 'pere' va bloquer la ou les lignes en question, sur toutes les autres sessions.
Tu es en attente soit d'un commit, soit d'un rollback.

Code:

---

delete from pere where num = 2;

---

Seule la ligne num=2 sera bloquée, mais pas les autres.

Pas de validation, donc tu es en attente.

Session 2

Code:

---

START TRANSACTION;

---

Mode transactionnel aussi.

Code:

---

insert into fils (`cle`,`chn`) values (1, 'Père 1 - Fils 3');

---

Tu désires insérer une nouvelle ligne dans la table 'fils' mais avec comme clef étrangère, num=1.
Pas de blocage, donc tu peux faire l'insertion.

Session 2 - bis

Code:

---

INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1');

---

Tu n'es pas en mode transactionnel dans cette session.
Mais un mode transactionnel est en cours dans la session 1.
Tu désires faire une insertion avec num=2.
Résultat : blocage car session est en attente d'une validation.

Citation:

---

Envoyé par philippelorin

Concernant les options sur les foreign key, je laisse généralement tout à "No action" : On Update et On Delete (je préfère gérer moi-même, cela force à réfléchir à ce que l'on programme et évite de perdre des données si on n'a pas bien programmé).

---

Aucun rapport avec le mode transaction et les verrous.

Pour répondre à ta question, oui, si on supprime par inadvertance une ligne de la table 'pere', il y a automatiquement une répercussion sur la table 'fils'.
Une autre solution consiste à interdire, le delete ou le update sur la table 'pere'. Mais dans ce cas là, mettre "ON DELETE CASCADE ON UPDATE CASCADE" ne sert à rien.

Pour ton test, tu as dû commettre une erreur car je n'ai pas le même comportement que toi.

Pour ton problème, je ne voie pas comment le résoudre.
Tu demandes à être en mode transactionnel et faire quelques modifications sur ta table 'pere'.
Le problème est que tu ne désires pas valider le travail !!!
Déjà sur ce point, je ne comprends pas ce que tu essayes de faire.

Puis dans une autre session, tu voudrais que les utilisateurs ne soient pas bloqués.
Si on raisonne à l'envers, c'est-à-dire permettre au autres utilisateurs de ne pas être bloqués, alors il faut :
--> ne pas utiliser le mode transaction
--> être en autocommit.

Ou mieux, réduire le temps entre la modification et la validation ou le rejet de cette modification.
Comme je l'ai dit, augmente le timeout.

Pour mieux comprendre ce que tu essayes de faire, j'aimerai savoir pourquoi tu ne veux pas valider les modifications faites dans la session 1 ?

@+

Salut Artemus24,

L'ajout du fils ci-dessous était hors test mais un préparatif pour le second test :

Code:

---

INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1');

---

Le test est toujours le même :
Sur la session 1 :

Code:

---

1 2	START TRANSACTION; delete from pere where num = 2;

---

Et sur la session 2 :

Code:

---

1 2	START TRANSACTION; insert into fils (`cle`,`chn`) values (1, 'Père 1 - Fils 3');

---

Citation:

---

Je n'ai pas le même comportement que toi. Tu as dû faire une erreur quelque part.

---

Je viens de refaire le test et j'ai toujours un blocage sur la session 2. Il y a peut-être une différence de paramétrage de MySQL. Je suis en MySQL 5.6.
Voici les personnalisation de mon fichier my.ini (commentaires supprimés) :
(j'utilise MySQL pour windows mais j'ai déjà fait un test sur une version linux de MySQL et le résultat était le même).

Code:

---

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

[client] no-beep port=3306   [mysql] default-character-set=utf8   [mysqld] port=3306 datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data character-set-server=utf8 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" log-output=FILE general_log_file="C:\DEVELOP.log" slow-query-log=1 slow_query_log_file="C:\DEVELOP-slow.log" long_query_time=10 log-error="C:\DEVELOP.err" server-id=1 max_connections=151 query_cache_size=0 table_open_cache=2000 tmp_table_size=43M thread_cache_size=10 myisam_max_sort_file_size=100G myisam_sort_buffer_size=78M key_buffer_size=8M read_buffer_size=64K read_rnd_buffer_size=256K innodb_additional_mem_pool_size=6M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=3M innodb_buffer_pool_size=248M innodb_log_file_size=48M innodb_thread_concurrency=9 innodb_autoextend_increment=64 innodb_buffer_pool_instances=8 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 back_log=80 flush_time=0 join_buffer_size=256K max_allowed_packet=4M max_connect_errors=100 open_files_limit=4161 query_cache_type=0 sort_buffer_size=256K table_definition_cache=1400 binlog_row_event_max_size=8K sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000 innodb_autoinc_lock_mode = 2 innodb_table_locks = false innodb_print_all_deadlocks transaction-isolation = SERIALIZABLE autocommit = off innodb_lock_wait_timeout = 50 binlog-format = ROW

---

Peux-tu me communiquer les paramètres de ton serveur afin de tenter de trouver si une option est ,à l'origine du comportement différent ?

Citation:

---

Pour mieux comprendre ce que tu essayes de faire, j'aimerai savoir pourquoi tu ne veux pas valider les modifications faites dans la session 1 ?

---

Je suis dans une application Windows développée avec WinDEV. Les tables sont en lien direct avec les fenêtres de l'application. Lorsque l'utilisateur démarre une action, je démarre une session MySQL. Dans le cas précis, je présente des résultats à l'utilisateur et attend une réponse de celui-ci pour valider (commit) ou annuler (rollback) les modifications effectuées.

@+

Salut philippelorin.

Voici mon "fichier My.ini" :

Code:

---

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 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360

# ********************* # # # # Sphere "Bleu" # # # # ********************* #   # ==================== # # MySql Client # # ==================== #   [client] port = 3306 socket = mysql   # ----------------------- # # access features # # ----------------------- #   user = root password = root host = 127.0.0.1   # --------------- # # Charset # # --------------- #   default-character-set = latin1   # ==================== # # MySql Server # # ==================== #   [wampmysqld] port = 3306 socket = mysql   # ---------------- # # Begining # # ---------------- #   server-id = 1   # --------------- # # Charset # # --------------- #   character-set-server = latin1 collation-server = latin1_general_ci character-set-filesystem = latin1   init_connect = 'SET collation_connection = latin1_general_ci' init-connect = 'SET NAMES latin1 COLLATE latin1_general_ci;'   # ------------------- # # Date & Time # # ------------------- #   date-format = "%Y-%m-%d" datetime-format = "%Y-%m-%d %H:%i:%s"   default-time-zone = SYSTEM   explicit-defaults-for-timestamp = on   # ------------------- # # Directories # # ------------------- #   basedir = f:/Wamp/bin/mysql/mysql5.7.11/ datadir = f:/Wamp/bin/mysql/mysql5.7.11/data tmpdir = f:/Wamp/tmp   # -------------------------# # Full Text Search # # -------------------------#   ft-max-word-len = 10 ft-min-word-len = 1 ft-stopword-file = ""   # ---------------# # InnoDB # # ---------------#   default-storage-engine = InnoDB default-tmp-storage-engine = InnoDB   innodb-adaptive-hash-index = on   innodb-buffer-pool-dump-now = off innodb-buffer-pool-dump-at-shutdown = off innodb-buffer-pool-instances = 2 innodb-buffer-pool-load-at-startup = OFF innodb-buffer-pool-size = 20M   innodb-data-file-path = ibdata1:12M:autoextend innodb-data-home-dir = f:/Wamp/bin/mysql/mysql5.7.11/data/ibdata/ innodb-doublewrite = 1   innodb-file-format-check = on innodb-file-per-table = 1   innodb-flush-log-at-trx-commit = 1 #innodb-force-recovery = 1   innodb-ft-enable-stopword = off innodb-ft-max-token-size = 10 innodb-ft-min-token-size = 0   innodb-io-capacity = 2000 innodb-lock-wait-timeout = 600   innodb-log-buffer-size = 16M innodb-log-file-size = 20M innodb-log-files-in-group = 2 innodb-log-group-home-dir = f:/Wamp/bin/mysql/mysql5.7.11/data/ibdata/   innodb-max-dirty-pages-pct = 80 innodb-page-size = 16K innodb-purge-threads = 10 innodb-read-io-threads = 4 innodb-stats-on-metadata = 0 innodb-support-xa = 1 innodb-thread-concurrency = 16 innodb-write-io-threads = 4   # ---------------- # # Language # # ---------------- #   lc-messages = fr_FR lc-time-names = fr_FR   # ------------# # Log # # ------------#   #binlog-cache-size = 1M #binlog-format = mixed   #log-bin = f:/Wamp/logs/mysql_bin log-error = f:/Wamp/logs/mysql_error.log log-error-verbosity = 3   log-queries-not-using-indexes = 1 log-throttle-queries-not-using-indexes = 60   #general-log = on #general-log-file = f:/Wamp/logs/mysql_general.log   # ------------# # Max # # ------------#   max-allowed-packet = 16M max-connections = 20 max-connect-errors = 10 max-error-count = 64 max-heap-table-size = 1G max-user-connections = 0 max-sp-recursion-depth = 255   # --------------------# # Buffer-Size # # --------------------#   join-buffer-size = 512M read-buffer-size = 512M read-rnd-buffer-size = 512M sort-buffer-size = 512M   # ---------------# # MyIsam # # ---------------#   bulk-insert-buffer-size = 64M   key-buffer-size = 2G   myisam-max-sort-file-size = 1G myisam-recover-options = OFF myisam-repair-threads = 1 myisam-sort-buffer-size = 128M myisam-stats-method = nulls_unequal myisam-use-mmap = OFF   # -------------------# # Networking # # -------------------#   back-log = 50   net-buffer-length = 8K net-read-timeout = 60 net-write-timeout = 60   # ---------------------------# # Performance-Schema # # ---------------------------#   # performance-schema = ON # performance-schema-events-waits-history-size = 20 # performance-schema-events-waits-history-long-size = 1500   # --------------# # Query # # --------------#   query-alloc-block-size = 8M query-cache-limit = 1G query-cache-min-res-unit = 4096 query-cache-size = 128M query-cache-type = On query-cache-wlock-invalidate = Off   # -------------# # Skip # # -------------#   #skip-innodb-doublewrite   skip-external-locking skip-federated #skip-log-warnings skip-slave-start # skip-innodb-adaptive-hash-index   # --------------------# # Long & Slow # # --------------------#   long-query-time = 0   slow-query-log = 1 slow-query-log-file = f:/Wamp/logs/mysql_slow.log   # ------------# # Ssl # # ------------#   ssl ssl-ca = f:/Wamp/bin/apache/apache2.4.18/conf/certificat/ca/ca.crt ssl-cert = f:/Wamp/bin/apache/apache2.4.18/conf/certificat/site/localhost.crt ssl-key = f:/Wamp/bin/apache/apache2.4.18/conf/certificat/site/localhost.key   skip-ssl   # --------------# # Table # # --------------#   table-definition-cache = 512 table-open-cache = 512   # -------------------# # Temp Table # # -------------------#   tmp-table-size = 1G   # ---------------# # Thread # # ---------------#   thread-cache-size = 8 thread-stack = 256K   # ------------------------------# # Timeout Configuration # # ------------------------------#   connect-timeout = 600 interactive-timeout = 600 wait-timeout = 600   # --------------------# # Transaction # # --------------------#   transaction-isolation = SERIALIZABLE   # ---------------# # Tuning # # ---------------#   concurrent-insert = 2 open-files-limit = 8192   secure-file-priv = f:/wamp/tmp/   # =================== # # MySql Admin # # =================== #   [mysqldadmin] force   # ================== # # MySql Dump # # ================== #   [mysqldump] add-drop-database add-drop-table add-drop-trigger   default-character-set = utf8   flush-logs flush-privileges   lock-all-tables max-allowed-packet = 16M no-autocommit order-by-primary quick routines   skip-comments skip-opt skip-set-charset skip-tz-utc   triggers   # ===================== # # My Isam Check # # ===================== #   [myisamchk] key-buffer-size = 128M sort-buffer-size = 128M read-buffer = 2M write-buffer = 2M   # ====================== # # MySql Hot Copy # # ====================== #   [mysqlhotcopy] interactive-timeout   # ===================== # # MySql Monitor # # ===================== #   [mysql] no-auto-rehash #safe-updates skip-reconnect table   # ================== # # MySql Safe # # ================== #   [mysqld-safe] open-files-limit = 8192   log-error = f:/Wamp/logs/mysql_error.log

---

Je suis dans un environnement de test sur mon ordinateur personnel.
Je suis sous wampServer, et j'utilise MySql version 64 bits, VC++14 et release 5.7.11.

Citation:

---

Envoyé par philippelorin

Je suis dans une application Windows développée avec WinDEV. Les tables sont en lien direct avec les fenêtres de l'application. Lorsque l'utilisateur démarre une action, je démarre une session MySQL. Dans le cas précis, je présente des résultats à l'utilisateur et attend une réponse de celui-ci pour valider (commit) ou annuler (rollback) les modifications effectuées.

---

D'accord, j'avais compris ce que fait cet utilisateur. Mais ta réponse n'est pas ce que j'attendais.
Je voulais savoir si l'utilisateur est dans un fonctionnement normal ou pas de ton application ?
Est-ce pour un usage particulier où l'exclusivité est nécessaire afin de procéder à des modifications sur la base de données ?
C'est le contexte qui m'intéresse et non la façon dont tu t'y prends pour le faire.

@+

Salut Artemus24,

J'ai toujours le même problème et ne peux pas appliquer tel quel les paramètres car je suis en 5.6.25 32 bits sous windows.
Il faudra que j'installe une version identique pour comparer. Mais cette différence de comportement me dérange.

Citation:

---

Je voulais savoir si l'utilisateur est dans un fonctionnement normal ou pas de ton application ?

---

Oui il s'agit d'un fonctionnement normal de l'application.

Citation:

---

Est-ce pour un usage particulier où l'exclusivité est nécessaire afin de procéder à des modifications sur la base de données ?

---

J'ai besoin de l'exclusivité uniquement sur une ligne (celle supprimée ou modifiée) mais pas sur toute une table ou les autres tables.

Plusieurs utilisateurs utilisent l'application et chacun travaille sur ses dossiers (clients...). Si un utilisateur A supprime une donnée A, cela doit bloquer les autres utilisateurs sur la donnée A mais pas sur les autres données.

@+

Salut Philippe Lorin.

Je suis sous MySql 5.6.28. D'ailleurs, tu peux le voir dans l'entête à l'ouverture de la console.

Session 1 :

Code:

---

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

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.28-log MySQL Community Server (GPL)   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use base; Database changed mysql> mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -------------- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; -------------- SELECT @@GLOBAL.tx_isolation, @@tx_isolation --------------   +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | SERIALIZABLE | SERIALIZABLE | +-----------------------+----------------+ 1 row in set (0.00 sec)   mysql> mysql> SET AUTOCOMMIT = 0; -------------- SET AUTOCOMMIT = 0 --------------   Query OK, 0 rows affected (0.00 sec)   mysql> START TRANSACTION; -------------- START TRANSACTION --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> delete from pere where num = 2; -------------- delete from pere where num = 2 --------------   Query OK, 1 row affected (0.00 sec)   mysql> mysql>

---

Session 2 :

Code:

---

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

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.28-log MySQL Community Server (GPL)   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use base; Database changed mysql> mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -------------- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; -------------- SELECT @@GLOBAL.tx_isolation, @@tx_isolation --------------   +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | SERIALIZABLE | SERIALIZABLE | +-----------------------+----------------+ 1 row in set (0.00 sec)   mysql> mysql> SET AUTOCOMMIT = 0; -------------- SET AUTOCOMMIT = 0 --------------   Query OK, 0 rows affected (0.00 sec)   mysql> START TRANSACTION; -------------- START TRANSACTION --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 3'); -------------- INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 3') --------------   Query OK, 1 row affected (0.00 sec)   mysql> INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1'); -------------- INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1') --------------   ^C -- sending "KILL QUERY 6" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql>

---

Pas de blocage sur la première insertion (num=1) mais un blocage sur la seconde insertion (num=2). Comportement normal.

J'ai bien mis une contrainte sur la table 'fils'.

Code:

---

CONSTRAINT `FK_01` FOREIGN KEY (`cle`) REFERENCES `pere` (`num`) ON DELETE CASCADE ON UPDATE CASCADE

---

Si tu supprimes cette contrainte, il n'y a plus aucun blocage sur la table 'fils'. Mais tu peux avoir un problème d'intégrité.
Sauf qu'en ajoutant la contrainte telle quelle, mais en faisant un :

Code:

---

table ignore fils add constraint ...

---

mysql ne va pas toucher à ce qui existe, mais va supprimer en cascade ce qui aura disparu dans la table 'pere'.

@+

Bonjour Artemus24,

Lors de la suppression de la session 1. La ligne supprimée avait-elle un fils dans la table fils ? Si oui, peux tu essayer sans fils car c'est dans ce cas où j'ai un blocage sur toutes les insertions dans la table fils dans la session 2.

Merci,

@+

Bonjour,

C'est bien la table référançante (table fils dans l'exemple) qui est verrouillée.

Vous utilisez un niveau d'isolation des transaction élevé, ce qui génère beaucoup de verrous et vous utilisez une SGBD qui gère visiblement les verrous avec une granularité trop large.
Enfin, et c'est le principal problème, il n'est pas concevable qu'une transaction SQL ayant posé des verrous attende une action utilisateur, qui ne viendra peut-être jamais... Une transaction doit durer le moins longtemps possible : elle doit se limiter au strict nécessaire afin de ne pas compromettre l'intégrité des données. Il vous faut changer ce fonctionnement au plus vite sinon vos problèmes ne sont pas prêts de se résoudre.

Salut à tous.

Citation:

---

Envoyé par philippelorin

Lors de la suppression de la session 1. La ligne supprimée avait-elle un fils dans la table fils ?

---

Oui, car je n'ai pas modifié mon jeu d'essai.

Citation:

---

Envoyé par philippelorin

Si oui, peux tu essayer sans fils car c'est dans ce cas où j'ai un blocage sur toutes les insertions dans la table fils dans la session 2.

---

Je suppose que je dois faire cela dans la version mysql 5.6.28.

A tout hasard, je te donne la recréation de la base de données, comme jeu d'essai.

Code:

---

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

-------------- SET AUTOCOMMIT = 0 --------------   -------------- START TRANSACTION --------------   -------------- SET session collation_connection=latin1_general_ci --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `pere` --------------   -------------- CREATE TABLE `pere` ( `num` integer unsigned NOT NULL AUTO_INCREMENT primary key, `lib` char(06) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `pere` (`lib`) VALUES ('Père 1'), ('Père 2'), ('Père 3') --------------   -------------- select * from pere --------------   +-----+--------+ | num | lib | +-----+--------+ | 1 | Père 1 | | 2 | Père 2 | | 3 | Père 3 | +-----+--------+ -------------- DROP TABLE IF EXISTS `fils` --------------   -------------- CREATE TABLE `fils` ( `pos` integer unsigned NOT NULL AUTO_INCREMENT primary key, `cle` integer unsigned NOT NULL, `chn` char(15) NOT NULL, CONSTRAINT `FK_01` FOREIGN KEY (`cle`) REFERENCES `pere` (`num`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 1'), (1, 'Père 1 - Fils 2'), (3, 'Père 3 - Fils 1'), (3, 'Père 3 - Fils 2') --------------   -------------- select * from fils --------------   +-----+-----+-----------------+ | pos | cle | chn | +-----+-----+-----------------+ | 1 | 1 | Père 1 - Fils 1 | | 2 | 1 | Père 1 - Fils 2 | | 3 | 3 | Père 3 - Fils 1 | | 4 | 3 | Père 3 - Fils 2 | +-----+-----+-----------------+ -------------- select * from pere as p inner join fils as f on f.cle = p.num --------------   +-----+--------+-----+-----+-----------------+ | num | lib | pos | cle | chn | +-----+--------+-----+-----+-----------------+ | 1 | Père 1 | 1 | 1 | Père 1 - Fils 1 | | 1 | Père 1 | 2 | 1 | Père 1 - Fils 2 | | 3 | Père 3 | 3 | 3 | Père 3 - Fils 1 | | 3 | Père 3 | 4 | 3 | Père 3 - Fils 2 | +-----+--------+-----+-----+-----------------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 1 --------------     Appuyez sur une touche pour continuer...

---

Voici le résultat de la session 1 :

Code:

---

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

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.6.28-log MySQL Community Server (GPL)   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use base; Database changed mysql> mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -------------- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; -------------- SELECT @@GLOBAL.tx_isolation, @@tx_isolation --------------   +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | SERIALIZABLE | SERIALIZABLE | +-----------------------+----------------+ 1 row in set (0.00 sec)   mysql> mysql> SET AUTOCOMMIT = 0; -------------- SET AUTOCOMMIT = 0 --------------   Query OK, 0 rows affected (0.00 sec)   mysql> START TRANSACTION; -------------- START TRANSACTION --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> delete from pere where num = 2; -------------- delete from pere where num = 2 --------------   Query OK, 1 row affected (0.00 sec)   mysql> mysql>

---

et voici le résultat de la session 2 :

Code:

---

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

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.6.28-log MySQL Community Server (GPL)   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> use base; Database changed mysql> mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -------------- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; -------------- SELECT @@GLOBAL.tx_isolation, @@tx_isolation --------------   +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | SERIALIZABLE | SERIALIZABLE | +-----------------------+----------------+ 1 row in set (0.00 sec)   mysql> mysql> SET AUTOCOMMIT = 0; -------------- SET AUTOCOMMIT = 0 --------------   Query OK, 0 rows affected (0.00 sec)   mysql> START TRANSACTION; -------------- START TRANSACTION --------------   Query OK, 0 rows affected (0.00 sec)   mysql> mysql> INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 3'); -------------- INSERT INTO `fils` (`cle`,`chn`) VALUES (1, 'Père 1 - Fils 3') --------------   ^C -- sending "KILL QUERY 15" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1'); -------------- INSERT INTO `fils` (`cle`,`chn`) VALUES (2, 'Père 2 - Fils 1') --------------   ^C -- sending "KILL QUERY 15" to server ... ^C -- query aborted ERROR 1317 (70100): Query execution was interrupted mysql> mysql>

---

En effet, si je supprime une ligne de la table 'pere' (num=2), la table 'fils' est cette fois-ci totalement bloquée !
Je vais voir ce que je peux faire afin de débloquer cette situation.

Citation:

---

Envoyé par aieeeuuuuu

C'est bien la table référençante (table fils dans l'exemple) qui est verrouillée.

---

Dans le dernier exemple de Philippe lorin, oui, c'est le cas.

Citation:

---

Envoyé par aieeeuuuuu

Vous utilisez un niveau d'isolation des transaction élevé, ce qui génère beaucoup de verrous et vous utilisez une SGBD qui gère visiblement les verrous avec une granularité trop large.

---

Ce que demande, justement Philippe Lorin, c'est de pouvoir bloquer la table 'fils', uniquement sur les clefs étrangères dont les lignes en références ont été détruites dans la table 'pere', mais non encore validée (commit).

Citation:

---

Envoyé par aieeeuuuuu

Enfin, et c'est le principal problème, il n'est pas concevable qu'une transaction SQL ayant posé des verrous attende une action utilisateur, qui ne viendra peut-être jamais...

---

Justement, je ne comprends pas aussi ce cas de vigure !

Citation:

---

Envoyé par aieeeuuuuu

Une transaction doit durer le moins longtemps possible : elle doit se limiter au strict nécessaire afin de ne pas compromettre l'intégrité des données.

---

Oui, je suis d'accord. Pour contourner son problème, la solution que j'ai proposé est d'augmenter le timeout.

Citation:

---

Envoyé par aieeeuuuuu

Il vous faut changer ce fonctionnement au plus vite sinon vos problèmes ne sont pas prêts de se résoudre.

---

Je suis du même avis !

Soit il demande l'exclusivité lors de cette phase de travail, et de ce fait, il ne va bloquer personne, vu qu'il sera le seul à travailler.
Soit il augmente le timeout. Cela ne va pas résoudre l'attente de l'autre utilisateur qui est bloqué, mais au moins la demande sera mis en attente. Je sais, cela peut-être très long.
Soit il revoit son traitement afin de rendre la transaction la plus courte possible.

P.S.: je ne sais pas comment résoudre son problème car sa demande est très particulière. :?

@+