Discussion :

[aserx]

Bonjour,

J'ai un problème d'accès concurrents à ma base de données.

- Un traitement (java/hibernate) tourne en permanence pour synchroniser un schéma A avec celui d'un autre serveur : il effectue des SELECT sur ces données => verrou ACCESS SHARE.

- Ponctuellement, un autre traitement (java/jdbc) est lancé pour copier des données dans un schéma B de la même base. Cela nécessite de désactiver les contraintes du schéma A car certaines tables du schéma A référencent des tables du schéma B : il effectue un ALTER TABLE DROP CONSTRAINT => verrou ACCESS EXCLUSIVE.

Dans certains cas, il se produit donc des blocages.

D'après la doc, j'ai cru comprendre que Postgres doit essayer de s'en sortir tout seul en annulant une des transactions. Or je n'ai pas l'impression que cela soit ce qui se passe car mon application reste bloquée. Au niveau applicatif, je n'ai pas moyen de détecter cette situation pour annuler moi-même la transaction car aucune Exception n'est générée par JDBC.

Pour information, les 2 traitements sont lancés par une application tournant sur un serveur applicatif JBOSS.

Avez-vous des idées pour gérer cette situation ?

Cordialement

[SQLpro]

Oui, débarrez vous d'Hibernate qui est une usine à gaz contre productive.
A +

[aserx]

Bonjour,

Je vous remercie pour votre réponse et pour cet excellent article : je suis entièrement d'accord avec vous concernant l'usine à gaz que constitue hibernate : je suis toujours en train de me demander ce qu'il peut bien être en train de faire à ma base de données et en tant que responsable BD c'est assez frustrant de ne pas avoir la main sur sa base (index ou contraintes qui apparaissent / disparaissent et temps passé à remettre la base d'aplomb après qu'un développeur se soit connecté en mode "create", etc).

Nous avons effectivement supprimé hibernate : le traitement réalisé précédemment par hibernate a été supprimé au bénéfice d'un traitement JDBC. Depuis vendredi, le problème ne s'est pas reproduit.

Je m'interroge cependant sur ce qui a pu causer ce blocage. En fait, ce qui me chagrine plutôt, ce n'est pas le blocage en lui-même (il est illusoire de croire que tous les cas de blocages ont été écartés et qu'aucun blocage ne pourra se produire en prod) mais plutôt le fait que ce blocage n'ai pas été résolu par postgres et a nécessité que je supprime les verrous à la main, puis redémarre mon serveur car même après la suppression des verrous, mes traitement JDBC refusaient de ce lancer pour cause de blocage. Même mes sauvegardes quotidiennes (réalisées par pg_dump) ont échoué.

J'aimerais identifier ce qui a pu se passer pour éviter que cela se produise en production. Si vous avez des pistes, je suis intéressée.

Cordialement

[estofilo]

Même mes sauvegardes quotidiennes (réalisées par pg_dump) ont échoué.

Avec quel(s) message(s) d'erreur?

[aserx]

Bonjour,

Je n'avais pas encore activé les traces sur les LOCKS sur ce serveur (je l'ai fait depuis, mais c'est trop tard...) mais mon fichier dump est vide. D'après l'état du serveur dans pgAdmin, je pense que pg_dump tentait de poser un verrou explicite sur les tables qui étaient déjà bloquées.

J'avais toute une liste de verrous posés sur les 2 tables qui posaient problème :
- ceux posés par le traitement hibernate : 4 verrous posés par 2 requêtes SELECT différentes (1 ACCESS SHARE + 1 ACCESS EXCLUSIVE pour chaque requête, et je ne comprends la raison du ACCESS EXCLUSIVE sur un SELECT)
- ceux posés par mon traitement JDBC : 4 verrous posés par 2 requêtes ALTER TABLE différentes (1 ACCESS SHARE + 1 ACCESS EXCLUSIVE pour chaque requête, et cette fois je ne comprends la raison du ACCESS SHARE sur un ALTER TABLE)
=> Tous ces verrous ont été créés au même moment.
- Ensuite lors de mon pg_dump à 23h (soit quelques heures plus tard), j'ai une dizaine de verrous ACCESS EXCLUSIVE créés par un LOCK TABLE, toujours sur les 2 mêmes tables (pourtant je ne lance qu'un seul pg_dump).

Avec tous ces verrous, le lancement du traitement JDBC générait l'erreur suivante dans JBoss :

Code :

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

1
2
3
4
java.sql.SQLException: Unable to obtain lock in 60 seconds: org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@dd9a23d
	at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.tryLock(BaseWrapperManagedConnection.java:267)
	at org.jboss.resource.adapter.jdbc.WrappedConnection.lock(WrappedConnection.java:79)
	at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:170)

J'ai supprimé les verrous et relancé le traitement JDBC. J'avais toujours les erreurs suivantes dans JBoss :

Code :

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

1
2
3
java.sql.SQLException: Connection is not associated with a managed connection.org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6@e2b7c55
	at org.jboss.resource.adapter.jdbc.WrappedConnection.lock(WrappedConnection.java:81)
	at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:170)

Pour finir j'ai redémarré JBoss et le problème a été résolu.

Cordialement,

[estofilo]

L'auto-résolution des problèmes de verrou n'existe que dans le cas d'un deadlock, qui est le cas où deux transactions se bloquent mutuellement.
Dans tous les autres cas, les verrous posés dans une transaction disparaissent au pire à la fin de cette transaction. Si pg_dump a un problème pour faire un LOCK plusieurs heures après un traitement, c'est que la connexion que ce traitement a utilisé n'a pas été libérée et même que la transaction associée n'a pas été terminée non plus.

[aserx]

Bonjour,

Je suis bien dans la cas d'un deadlock : les 2 premiers traitements (le traitement hibernate et le traitement JDBC) se sont bloqués mutuellement.

Comme il n'y a pas eu de résolution du deadlock par PostgreSQL, les transactions ne sont pas terminées et les verrous existent toujours. Donc plusieurs heures après, quand mon pg_dump s'est réalisé, il n'a pas pu poser ses verrous et a échoué.

Ce que je ne m'explique pas, c'est la raison pour laquelle PostgreSQL n'a pas résolu mon deadlock. Faut-il le paramétrer explicitement dans le fichier conf ?

Cordialement,

[estofilo]

Il y a un paramètre deadlock_timeout qui par défaut doit être à 1s, vérifiable par:

Code :

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

show deadlock_timeout;

Si ce paramètre est mauvais il se peut que les deadlocks ne soient pas détectés rapidement mais sinon ça ne doit pas arriver.

Voici ce qui se passe en cas de deadlock, session1 et session2 désignant deux connexions différentes sur la même base:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
session1=> begin;
BEGIN
session1=>lock table a;
LOCK TABLE
session2=> begin;
BEGIN
session2=> lock table b;
LOCK TABLE
session1=> lock table b; (ici session1  bloque en attendant session2)
session2=> lock table a;

Au bout d'une seconde, session2 part en erreur:

ERROR: deadlock detected
DETAIL: Process 31795 waits for AccessExclusiveLock on relation 4647179 of database 4399653; blocked by process 31793.
Process 31793 waits for AccessExclusiveLock on relation 4703528 of database 4399653; blocked by process 31795.

[aserx]

Bonjour,

Mon deadlock_timeout est bien celui par défaut (1s).

Dans mon cas il semble que la session2 ne soit pas partie en erreur : en effet, les verrous étaient toujours là le lendemain.

Dans votre exemple, lorsque la session2 part en erreur, est-ce que cette erreur est détectée/détectable par JDBC afin que celui-ci puisse lever une exception ? Car dans mon cas, aucune exception n'a été levée non plus, ce qui fait que je n'ai pas fait de ROLLBACK dans mon code JAVA et je me suis retrouvée avec une base inconsistante avec mon traitement réalisé en partie.

En revanche je n'ai pas compris exactement le fonctionnement du paramètre max_locks_per_transaction. En effet, j'ai :
- max_locks_per_transaction = 64
- max_connections = 100
- max_prepared_transactions = 5
Et dans le pire des traitements, je peux avoir pratiquement 300 tables verrouillées simultanément au court de la même transaction. Et si, comme je l'ai constaté lorsque mon blocage s'est produit, plusieurs verrous sont créés sur la même table, le nombre total de verrous peut augmenter très rapidement. Si j'ai bien compris, le nombre de verrous peut dépasser max_locks_per_transaction, mais que se passe-t-il si le nombre total de verrous générés par une seule transaction dépasse max_locks_per_transaction * (max_connections + max_prepared_transactions) ? Ceci peut-il être la source de mon blocage ?

Cordialement,

[estofilo]

Dans mon cas il semble que la session2 ne soit pas partie en erreur : en effet, les verrous étaient toujours là le lendemain.

Dans votre exemple, lorsque la session2 part en erreur, est-ce que cette erreur est détectée/détectable par JDBC afin que celui-ci puisse lever une exception ? Car dans mon cas, aucune exception n'a été levée non plus, ce qui fait que je n'ai pas fait de ROLLBACK dans mon code JAVA et je me suis retrouvée avec une base inconsistante avec mon traitement réalisé en partie.

L'erreur que subit une des deux sessions est une erreur SQL classique qui doit remonter comme les autres, et aussi qui interdit de continuer à faire des ordres SQL dans la transaction tant que le code n'a pas fait un rollback.

Il faut voir que si les verrous sont toujours là le lendemain du traitement, c'est que la transaction est toujours là, et donc en principe que le code tourne toujours. A ce propos, quand tu évoques le fait de supprimer les verrous, quelle manip fais-tu techniquement parlant?

[aserx]

Dans Outils/Etat du serveur de pgAdmin, j'ai sélectionné les verrous dans la vue "Verrous" et j'ai cliqué sur le bouton "Annuler la requête". Mes transactions étaient toujours présentes et ont disparu de la vue "Transaction" en même temps que les processus correspondant à mes sessions dans la vue "Activité" quand j'ai supprimé les verrous.

Du coup j'ai regretté d'avoir tout supprimé en même temps car j'aurais aimé voir ce qui se passait si je n'en débloquais qu'une des 2. Mais à vrai dire j'ai fait la manip' un peu pour voir ce qui allait se produire...