Skip site navigation (1) Skip section navigation (2)

Re: Problème de lock

From: REISS Thomas DSIC DESP <thomas(dot)reiss(at)interieur(dot)gouv(dot)fr>
To: REISS Thomas DSIC DESP <thomas(dot)reiss(at)interieur(dot)gouv(dot)fr>
Cc: SOUCHARD Jean-Michel DSIC BI <jean-michel(dot)souchard(at)interieur(dot)gouv(dot)fr>,pgsql-fr-generale(at)postgresql(dot)org,ROELTGEN Pierre-Andre DSIC DESP <pierre-andre(dot)roeltgen(at)interieur(dot)gouv(dot)fr>
Subject: Re: Problème de lock
Date: 2006-09-29 15:54:15
Message-ID: 451D41A7.5070306@interieur.gouv.fr (view raw or flat)
Thread:
Lists: pgsql-fr-generale
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 


In response to

pgsql-fr-generale by date

Next:From: VincentLemaireDate: 2006-10-10 07:02:22
Subject: Gestion d'utilisateurs
Previous:From: REISS Thomas DSIC DESPDate: 2006-09-28 11:15:54
Subject: Re: Problème de lock

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group