Re: Problème de lock

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