Re: Problème de lock

[REISS Thomas DSIC DESP <thomas(dot)reiss(at)interieur(dot)gouv(dot)fr>]

Rebonjour à tous,

Je me répond à moi-même. En fouillant les archives de la liste BUGS, je 
suis tombé sur ce message de Jan Wieck:

> IMO the problem is in ri_trigger.c around line 390:
>         /* ----------
>          * The query string built is
>          *   SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
>          * The type id's for the $ parameters are those of the
>          * corresponding FK attributes. Thus, SPI_prepare could
>          * eventually fail if the parser cannot identify some way
>          * how to compare these two types by '='.
>          * ----------
>          */
>
> Any ideas if this is a bug or simply strict SQL standard?
   It  does  a  SELECT  ...  FOR  UPDATE because we don't have a
   SELECT ... AND PLEASE DO NOT REMOVE.


Nous allons très certainement préconiser de faire les mises à jour via 
INSERT.

Bonne réception,
Thomas

REISS Thomas DSIC DESP a écrit :
> 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
>
> ------------------------------------------------------------------------
>
> 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)
> );
>
> -- donnees de reference
> INSERT INTO maitre (id, bidon) VALUES (1, 'Bela Lugosi''s Dead');
> INSERT INTO maitre (id, bidon) VALUES (2, 'Dark entries');
> INSERT INTO maitre (id, bidon) VALUES (3, 'The Passion Of Lovers');
> INSERT INTO maitre (id, bidon) VALUES (4, 'She''s In Parties');
> INSERT INTO maitre (id, bidon) VALUES (5, 'Ziggy Stardust');
> INSERT INTO maitre (id, bidon) VALUES (6, 'Telegram Sam');
> INSERT INTO maitre (id, bidon) VALUES (7, 'Kick In The Eye');
> INSERT INTO maitre (id, bidon) VALUES (8, 'The Sanity Assassin');
> INSERT INTO maitre (id, bidon) VALUES (9, 'Terror Couple Kill The Colonel');
> INSERT INTO maitre (id, bidon) VALUES (10, 'Searching For Satori');
>
>
> -- Test du COPY
> --
> COPY details ( id, maitre_id, bidon ) FROM STDIN;
> 1	1	'1'
> 2	2	'2'
> 3	3	'3'
> 4	4	'4'
> 5	5	'5'
> \.
>
> DROP TABLE details;
> CREATE TABLE details (
> id integer PRIMARY KEY,
> maitre_id integer REFERENCES maitre (id) ON DELETE SET NULL ON UPDATE SET NULL,
> bidon varchar(30)
> );
>
>
> COPY details ( id, maitre_id, bidon ) FROM STDIN;
> 1	1	'1'
> 2	2	'2'
> 3	3	'3'
> 4	4	'4'
> 5	5	'5'
> \.
>
> -- fin
>   
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>   

--

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