Re: Problème de lock

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

SOUCHARD Jean-Michel DSIC BI wrote:

> [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

Mais la commande COPY en soit ne fait rien pour bloquer les écritures
concurrentes, ce serait au moins mentionné dans la doc.
Dans le contexte d'erreur que vous montrez, on voit que la requête sur laquelle
il y a échec n'est pas un COPY mais un SELECT FOR UPDATE.
Comme ça se produit au niveau de la fonction pg_end_copy en php, ça semblerait
indiquer que ce serait plutôt un trigger (un statement trigger sur insert, par
exemple) qui poserait problème.Vous pouvez vérifier si vous avez un trigger sur
la table destination du COPY?

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

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

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