Discussion :

[philippelorin]

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.

[Artemus24]

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.

@+

[philippelorin]

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

[Artemus24]

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" ?

(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 :

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

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 :

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

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.

@+

[philippelorin]

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

[Artemus24]

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 :

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

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 :

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

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 :

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

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.

@+

[philippelorin]

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.

@+

[Artemus24]

Salut philippelorin.

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

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 :

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

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 :

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
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 :

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
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 :

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
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 :

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

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 :

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

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 :

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

transaction-isolation = SERIALIZABLE

Puis augmenter le timeout comme je l'ai fait :

Code :

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

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é !
Fait en sorte de mettre au moins 70 secondes pour ce timeout et dans ce cas, tu n'auras plus de problème.

@+

[philippelorin]

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.

@+

[Artemus24]

Salut 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 :

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

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 :

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

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 :

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

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 !

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.

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 :

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
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 :

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
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 :

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

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.

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 :

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

max-connections = 1

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

@+

[Artemus24]

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.

@+

[philippelorin]

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 :

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
 
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 :

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

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

Tout se passe correctement, et sur une session 2 :

Code :

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

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 :

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

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)

@+

[Artemus24]

Salut 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 :

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

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 :

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

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 :

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

START TRANSACTION;

Mode transactionnel aussi.

Code :

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

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 :

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

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.

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 ?

@+

[philippelorin]

Salut Artemus24,

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

Code :

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

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

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

Code :

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

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

Et sur la session 2 :

Code :

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

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

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 :

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
 
[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 ?

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.

@+

[Artemus24]

Salut philippelorin.

Voici mon "fichier My.ini" :

Code :

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
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.

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.

@+

[philippelorin]

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.

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.

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.

@+

[Artemus24]

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 :

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
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 :

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
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 :

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

  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 :

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

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'.

@+

[philippelorin]

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,

@+

[aieeeuuuuu]

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.

[Artemus24]

Salut à tous.

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.

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 :

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
--------------
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 :

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
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 :

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
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.

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.

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).

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 !

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.

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.

@+