Re: Problème de lock

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

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 

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