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

Generacion de Lock

From: Javier Fritz Aliste <jfritz(dot)aliste(at)gmail(dot)com>
To: pgsql-es-ayuda <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: Generacion de Lock
Date: 2010-12-28 17:45:06
Message-ID: AANLkTikgufKU9ENRbC6RpEMtiyAaZbvdbOBbx77Wk9cZ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
Hola a todos.

Hace algun tiempo hice una consulta sobre lock's que se generan en mmis
bases de datos, aun no he logrado determinar cual es el motivo de la
generacion de estos bloqueos, he notado que estos se produce principalmente
cuando el rendimiento de las comunicaciones baja, el problema es que genera
cuelgues de la aplicacion de todos quienes acceden a los datos, incluso en
aquellos que poseen conexiones adecuadas.

Alguna pista de como solucionarlo?, he revisado la aplicacion y he validado
que cierre todos los statemts que envia a la base, en general ocurre como es
de esperase con objetos de alto transito de datos, pej. reserva de folio,
(tabla usada reservar el uso de recursos), movimientos de inventario, entre
otros.


Al ejecutar : select relation,virtualtransaction, pid, mode, granted from
pg_locks

Obtengo:

 relation | virtualtransaction |  pid  |           mode           | granted
----------+--------------------+-------+--------------------------+---------
    10969 | 43/171225          | 19493 | AccessShareLock          | t
   107233 | 26/352591          | 14372 | AccessShareLock          | t
   107271 | 15/492454          | 16364 | AccessShareLock          | t
   107274 | 15/492454          | 16364 | AccessShareLock          | t
   107280 | 15/492454          | 16364 | AccessShareLock          | t
   107298 | 15/492454          | 16364 | AccessShareLock          | t
   107304 | 15/492454          | 16364 | AccessShareLock          | t
   107307 | 15/492454          | 16364 | AccessShareLock          | t
   107307 | 23/432504          | 16688 | AccessShareLock          | t
   107307 | 15/492454          | 16364 | RowShareLock             | t
   107313 | 15/492454          | 16364 | AccessShareLock          | t
   107319 | 15/492454          | 16364 | AccessShareLock          | t
   107319 | 26/352591          | 14372 | AccessShareLock          | t
   107319 | 23/432504          | 16688 | AccessShareLock          | t
   107333 | 15/492454          | 16364 | AccessShareLock          | t
   107339 | 15/492454          | 16364 | RowExclusiveLock         | t
   107339 | 15/492454          | 16364 | AccessShareLock          | t
   107339 | 23/432504          | 16688 | AccessShareLock          | t
   107339 | 26/352591          | 14372 | AccessShareLock          | t
   107339 | 26/352591          | 14372 | RowExclusiveLock         | t
   107347 | 26/352591          | 14372 | AccessShareLock          | t
   107355 | 15/492454          | 16364 | RowExclusiveLock         | t
   107355 | 26/352591          | 14372 | AccessShareLock          | t
   107355 | 26/352591          | 14372 | RowShareLock             | t
   107355 | 26/352591          | 14372 | RowExclusiveLock         | t
   107355 | 23/432504          | 16688 | AccessShareLock          | t
   107355 | 23/432504          | 16688 | RowExclusiveLock         | t
   107355 | 15/492454          | 16364 | AccessShareLock          | t
   107398 | 15/492454          | 16364 | AccessShareLock          | t
   107398 | 26/352591          | 14372 | AccessShareLock          | t
   107398 | 23/432504          | 16688 | AccessShareLock          | t
   107444 | 15/492454          | 16364 | AccessShareLock          | t
   107463 | 15/492454          | 16364 | AccessShareLock          | t
   107463 | 26/352591          | 14372 | RowExclusiveLock         | t
   107480 | 15/492454          | 16364 | AccessShareLock          | t
   107508 | 26/352591          | 14372 | AccessShareLock          | t
   107511 | 26/352591          | 14372 | AccessShareLock          | t
   107517 | 15/492454          | 16364 | RowExclusiveLock         | t
   107517 | 23/432504          | 16688 | RowExclusiveLock         | t
   107517 | 26/352591          | 14372 | RowExclusiveLock         | t
   107523 | 15/492454          | 16364 | AccessShareLock          | t
   107523 | 23/432504          | 16688 | AccessShareLock          | t
   107523 | 26/352591          | 14372 | AccessShareLock          | t
   107541 | 26/352591          | 14372 | AccessShareLock          | t
   107541 | 23/432504          | 16688 | AccessShareLock          | t
   107541 | 13/497501          | 19290 | ShareUpdateExclusiveLock | t
   107582 | 15/492454          | 16364 | AccessShareLock          | t
   107582 | 23/432504          | 16688 | AccessShareLock          | t
   107582 | 26/352591          | 14372 | AccessShareLock          | t
   107620 | 23/432504          | 16688 | AccessShareLock          | t
   107620 | 26/352591          | 14372 | AccessShareLock          | t
   107620 | 26/352591          | 14372 | RowExclusiveLock         | t
   107620 | 15/492454          | 16364 | RowExclusiveLock         | t
   107620 | 23/432504          | 16688 | RowExclusiveLock         | t
   107634 | 15/492454          | 16364 | AccessShareLock          | t
   107665 | 26/352591          | 14372 | AccessShareLock          | t
   107665 | 23/432504          | 16688 | AccessShareLock          | t
   107682 | 15/492454          | 16364 | AccessShareLock          | t
   107682 | 23/432504          | 16688 | AccessShareLock          | t
   107682 | 26/352591          | 14372 | AccessShareLock          | t
   107704 | 15/492454          | 16364 | AccessShareLock          | t
   107704 | 23/432504          | 16688 | AccessShareLock          | t
   107704 | 26/352591          | 14372 | AccessShareLock          | t
   107710 | 23/432504          | 16688 | AccessShareLock          | t
   107710 | 15/492454          | 16364 | AccessShareLock          | t
   107710 | 26/352591          | 14372 | AccessShareLock          | t
   109171 | 26/352591          | 14372 | AccessShareLock          | t
   109171 | 23/432504          | 16688 | AccessShareLock          | t
   109171 | 15/492454          | 16364 | AccessShareLock          | t
   109245 | 26/352591          | 14372 | AccessShareLock          | t
   109247 | 26/352591          | 14372 | AccessShareLock          | t
   109257 | 15/492454          | 16364 | AccessShareLock          | t
   109259 | 15/492454          | 16364 | AccessShareLock          | t
   109261 | 15/492454          | 16364 | AccessShareLock          | t
   109263 | 15/492454          | 16364 | AccessShareLock          | t
   109265 | 15/492454          | 16364 | AccessShareLock          | t
   109275 | 15/492454          | 16364 | AccessShareLock          | t
   109277 | 15/492454          | 16364 | AccessShareLock          | t
   109279 | 15/492454          | 16364 | AccessShareLock          | t
   109281 | 15/492454          | 16364 | AccessShareLock          | t
   109283 | 23/432504          | 16688 | AccessShareLock          | t
   109283 | 15/492454          | 16364 | AccessShareLock          | t
   109285 | 15/492454          | 16364 | AccessShareLock          | t
   109285 | 23/432504          | 16688 | AccessShareLock          | t
   109287 | 15/492454          | 16364 | AccessShareLock          | t
   109289 | 15/492454          | 16364 | AccessShareLock          | t
   109295 | 23/432504          | 16688 | AccessShareLock          | t
   109295 | 26/352591          | 14372 | AccessShareLock          | t
   109295 | 15/492454          | 16364 | AccessShareLock          | t
   109297 | 23/432504          | 16688 | AccessShareLock          | t
   109297 | 26/352591          | 14372 | AccessShareLock          | t
   109297 | 15/492454          | 16364 | AccessShareLock          | t
   109303 | 15/492454          | 16364 | AccessShareLock          | t
   109305 | 15/492454          | 16364 | AccessShareLock          | t
   109307 | 23/432504          | 16688 | AccessShareLock          | t
   109307 | 26/352591          | 14372 | RowExclusiveLock         | t
   109307 | 26/352591          | 14372 | AccessShareLock          | t
   109307 | 15/492454          | 16364 | AccessShareLock          | t
   109307 | 15/492454          | 16364 | RowExclusiveLock         | t
   109309 | 26/352591          | 14372 | AccessShareLock          | t
   109311 | 23/432504          | 16688 | RowExclusiveLock         | t
   109311 | 23/432504          | 16688 | AccessShareLock          | t
   109311 | 26/352591          | 14372 | RowExclusiveLock         | t
   109311 | 26/352591          | 14372 | AccessShareLock          | t
   109311 | 15/492454          | 16364 | RowExclusiveLock         | t
   109311 | 15/492454          | 16364 | AccessShareLock          | t
   109343 | 15/492454          | 16364 | AccessShareLock          | t
   109349 | 26/352591          | 14372 | RowExclusiveLock         | t
   109349 | 15/492454          | 16364 | AccessShareLock          | t
   109351 | 15/492454          | 16364 | AccessShareLock          | t
   109351 | 26/352591          | 14372 | RowExclusiveLock         | t
   109357 | 15/492454          | 16364 | AccessShareLock          | t
   109371 | 26/352591          | 14372 | AccessShareLock          | t
   109373 | 26/352591          | 14372 | AccessShareLock          | t
   109375 | 26/352591          | 14372 | AccessShareLock          | t
   109385 | 26/352591          | 14372 | AccessShareLock          | t
   109385 | 23/432504          | 16688 | AccessShareLock          | t
   109385 | 13/497501          | 19290 | RowExclusiveLock         | t
   109411 | 26/352591          | 14372 | AccessShareLock          | t
   109411 | 15/492454          | 16364 | AccessShareLock          | t
   109411 | 23/432504          | 16688 | AccessShareLock          | t
   109433 | 15/492454          | 16364 | AccessShareLock          | t
   109451 | 23/432504          | 16688 | AccessShareLock          | t
   109451 | 26/352591          | 14372 | AccessShareLock          | t
   109455 | 26/352591          | 14372 | AccessShareLock          | t
   109455 | 15/492454          | 16364 | AccessShareLock          | t
   109455 | 23/432504          | 16688 | AccessShareLock          | t
   109457 | 15/492454          | 16364 | AccessShareLock          | t
   109457 | 23/432504          | 16688 | AccessShareLock          | t
   109457 | 26/352591          | 14372 | AccessShareLock          | t
   109463 | 26/352591          | 14372 | AccessShareLock          | t
   109463 | 15/492454          | 16364 | AccessShareLock          | t
   109463 | 23/432504          | 16688 | AccessShareLock          | t
   109465 | 15/492454          | 16364 | AccessShareLock          | t
   109465 | 23/432504          | 16688 | AccessShareLock          | t
   109465 | 26/352591          | 14372 | AccessShareLock          | t
   109467 | 23/432504          | 16688 | AccessShareLock          | t
   109467 | 15/492454          | 16364 | AccessShareLock          | t
   109467 | 26/352591          | 14372 | AccessShareLock          | t
   109469 | 15/492454          | 16364 | AccessShareLock          | t
   109469 | 23/432504          | 16688 | AccessShareLock          | t
   109469 | 26/352591          | 14372 | AccessShareLock          | t
   109502 | 26/352591          | 14372 | RowExclusiveLock         | t
   109502 | 23/432504          | 16688 | AccessShareLock          | t
   109502 | 15/492454          | 16364 | RowExclusiveLock         | t
   109502 | 15/492454          | 16364 | AccessShareLock          | t
   109502 | 26/352591          | 14372 | AccessShareLock          | t
   109503 | 23/432504          | 16688 | AccessShareLock          | t
   109503 | 26/352591          | 14372 | AccessShareLock          | t
   109503 | 15/492454          | 16364 | AccessShareLock          | t
   109503 | 26/352591          | 14372 | RowExclusiveLock         | t
   109503 | 15/492454          | 16364 | RowExclusiveLock         | t
   109719 | 26/352591          | 14372 | AccessShareLock          | t
   109719 | 15/492454          | 16364 | AccessShareLock          | t
   109719 | 23/432504          | 16688 | AccessShareLock          | t
   109721 | 26/352591          | 14372 | RowExclusiveLock         | t
   109721 | 15/492454          | 16364 | RowExclusiveLock         | t
   109721 | 23/432504          | 16688 | RowExclusiveLock         | t
   119614 | 26/352591          | 14372 | RowExclusiveLock         | t
   119614 | 15/492454          | 16364 | RowExclusiveLock         | t
   119614 | 23/432504          | 16688 | AccessShareLock          | t
   119614 | 23/432504          | 16688 | RowExclusiveLock         | t
   119614 | 26/352591          | 14372 | AccessShareLock          | t
   120468 | 26/352591          | 14372 | AccessExclusiveLock      | t
          | 15/492454          | 16364 | ExclusiveLock            | t
          | 43/171225          | 19493 | ExclusiveLock            | t
          | 13/497501          | 19290 | ExclusiveLock            | t
          | 23/432504          | 16688 | ExclusiveLock            | t
          | 23/432504          | 16688 | ExclusiveLock            | t
          | 26/352591          | 14372 | ExclusiveLock            | t
          | 15/492454          | 16364 | ExclusiveLock            | t
          | 26/352591          | 14372 | ExclusiveLock            | t
(172 filas)



-- 
Javier Fritz

Responses

pgsql-es-ayuda by date

Next:From: Alvaro HerreraDate: 2010-12-28 18:15:33
Subject: Re: Generacion de Lock
Previous:From: ramirexDate: 2010-12-28 17:28:32
Subject: Re: conoces un web hosting gratuito

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