Re: Problème de lock

From: REISS Thomas DSIC DESP <thomas(dot)reiss(at)interieur(dot)gouv(dot)fr>
To: SOUCHARD Jean-Michel DSIC BI <jean-michel(dot)souchard(at)interieur(dot)gouv(dot)fr>
Cc: pgsql-fr-generale(at)postgresql(dot)org, MOREAU Michel-Patrice DSIC SDEL <michel-patrice(dot)moreau(at)interieur(dot)gouv(dot)fr>, ROELTGEN Pierre-Andre DSIC DESP <pierre-andre(dot)roeltgen(at)interieur(dot)gouv(dot)fr>
Subject: Re: Problème de lock
Date: 2006-09-28 11:15:54
Message-ID: 451BAEEA.1010203@interieur.gouv.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-fr-generale

Bonjour à tous,

J'ai été amené à travailler également sur le problème décrit par
Jean-Michel et j'ai réussi à reproduire le problème. Vous trouverez
ci-joint un script SQL permettant de jouer un scénario identique, pour
observer le comportement de PG, il est nécessaire de positionner le
paramètre log_statement à all dans le fichier postgresql.conf.

Dans le cas qui nous concerne, des données sont insérées dans une table
détails au moyen d'un COPY. Cette table détails fait référence à une
table maître, au moyen d'une clé étrangère.
Voici deux tables d'exemples:
CREATE TABLE maitre (
id integer PRIMARY KEY,
bidon varchar(30)
);

CREATE TABLE details (
id integer PRIMARY KEY,
maitre_id integer REFERENCES maitre (id) ON DELETE CASCADE ON UPDATE
CASCADE,
bidon varchar(30)
);

Lors d'une mise à jour de la table détail, PostgreSQL doit vérifier la
validité de clé étrangère et accéder en lecture à la table maître.
Jusque là, c'est normal.
Après avoir insérer un certain nombre de lignes dans la table détails
via COPY, PostgreSQL pose un verrous sur la table maître au moyen d'un
SELECT FOR UPDATE. Il est à noter que cela n'est valable qu'à partir de
PostgreSQL 8.0.6. Voici un extrait des logs de 3 versions différentes :

Avec PostgreSQL 8.0.6
TRACE: instruction : BEGIN;
TRACE: durée : 0.100 ms
TRACE: instruction : COPY details ( id, maitre_id, bidon ) FROM STDIN;
TRACE: instruction : SELECT 1 FROM ONLY "public"."maitre" x WHERE "id"
= $1 FOR UPDATE OF x
CONTEXTE : instruction SQL «SELECT 1 FROM ONLY "public"."maitre" x
WHERE "id" = $1 FOR UPDATE OF x»
TRACE: durée : 33246.426 ms

Avec PostgreSQL 8.0.5
TRACE: instruction : BEGIN;
TRACE: instruction : COPY details ( id, maitre_id, bidon ) FROM STDIN;
TRACE: instruction : COMMIT;

Avec PostgreSQL 8.0.8
TRACE: instruction : BEGIN;
TRACE: durée : 0.262 ms
TRACE: instruction : COPY details ( id, maitre_id, bidon ) FROM STDIN;
TRACE: instruction : SELECT 1 FROM ONLY "public"."maitre" x WHERE "id"
= $1 FOR UPDATE OF x
CONTEXTE : instruction SQL «SELECT 1 FROM ONLY "public"."maitre" x
WHERE "id" = $1 FOR UPDATE OF x»
TRACE: durée : 13587.175 ms

Il me semble qu'il y a un petit problème fonctionnel à ce niveau là.
Etant donné qu'il n'y a aucune mise à jour sur la table maître, pourquoi
PostgreSQL pose-t-il un verrou via un SELECT FOR UPDATE ? Un verrou de
type ACCESS SHARE n'est pas suffisant dans cette situation ? (voir
http://www.postgresql.org/docs/8.0/interactive/explicit-locking.html).

Je vous remercie d'avance pour vos commentaires. Si quelque chose n'est
pas clair dans mon explication, n'hésitez pas à me le faire savoir.

Cordialement,
Thomas Reiss

SOUCHARD Jean-Michel (DSIC BI) a écrit :
>
> Bonjour à tous,
>
> On me demande d'examiner un problème de blocage d'application écrite
> en PHP par une société de service (je n'ai pas encore de visibilité
> sur le code). Dans le code PHP, la mise à jour des tables de la base
> se fait par DELETE ... puis des recalculs importants et la base est
> chargée par COPY à partir de l'entrée standard. Le problème c'est que
> plusieurs utilisateurs peuvent en même temps lancer cette
> fonctionnalité. Ce qui entraîne un blocage de l'application et
> retourne l'erreur suivante :
>
> /[WARNING] pg_end_copy(): Query failed: ERREUR: Bloquage détecté DETAIL:/
> /Le processus 12671 attend ShareLock sur la transaction 1664756; bloqué/
> /par le processus 12676. Le processus 12676 attend ShareLock sur la/
> /transaction 1664757; bloqué par le processus 12671. CONTEXT:
> instruction/
> /SQL «SELECT 1 FROM ONLY "public"."ref_paragraphe" x WHERE/
> /"paragraphe_id" = $1 FOR UPDATE OF x»/
> //MBGP/site/bgp2_2006/bgp2/classes/agent.class.php 706/
>
> /[WARNING] Cannot modify header information - headers already sent/
> //MBGP/site/bgp2_2006/copix/utils/copix/core/CopixCoordination.class.php/
> /215/
>
> Avant de trouver d'autres solutions, notamment modifications de code
> ou organisationnelles, je désirais savoir si il y a un paramétrage
> spécial évitant de planter en bloquage quand on lance plusieurs COPY
> en même temps sur la même table (il s'agit de lignes différentes dans
> les tables). Un traitement peut impacter jusqu'à 400*30000 lignes (12
> millions de lignes), raison, je pense, de l'utilisation de DELETE/COPY
> plutôt que DELETE/INSERT ou UPDATE simple.
>
> D'avance, merci
> JM Souchard
>

--

Thomas REISS
Ministère de l'Intérieur et de l'Aménagement du Territoire
SG/DSIC/SCSIC/DESP/BSTN
E-mail: thomas(dot)reiss(at)interieur(dot)gouv(dot)fr

Attachment Content-Type Size
copy_lock.sql text/plain 1.2 KB

In response to

Responses

Browse pgsql-fr-generale by date

  From Date Subject
Next Message REISS Thomas DSIC DESP 2006-09-29 15:54:15 Re: Problème de lock
Previous Message Sébastien Lardière 2006-09-22 07:38:49 Re: Sortie de livres sur PostgreSQL?