Re: Lock problem
От | Victor Sterpu |
---|---|
Тема | Re: Lock problem |
Дата | |
Msg-id | em02a9d305-ad63-406f-bade-295b0aa173e4@victor-pc обсуждение исходный текст |
Ответ на | Re: Lock problem (Merlin Moncure <mmoncure@gmail.com>) |
Список | pgsql-general |
------ Original Message ------ From: "Merlin Moncure" <mmoncure@gmail.com> To: "Victor Sterpu" <victor@caido.ro> Cc: "PostgreSQL General" <pgsql-general@postgresql.org> Sent: 4/2/2014 6:49:28 PM Subject: Re: [GENERAL] Lock problem >On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu <victor@caido.ro> wrote: >> >> Hello >> >> I have a problem that it seems to be very hard to debug. >> Problem is from some postgresql locks. I use PostgreSQL 9.1.8. >> >> I runned this query to fid the locks: >> >> SELECT bl.pid AS blocked_pid, >> a.usename AS blocked_user, >> kl.pid AS blocking_pid, >> ka.usename AS blocking_user, >> a.current_query AS blocked_statement >> FROM pg_catalog.pg_locks bl >> JOIN pg_catalog.pg_stat_activity a ON a.procpid = bl.pid >> JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid >>AND kl.pid != bl.pid >> JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid >> WHERENOT bl.granted; >> >> The result is a recursive lock. >> Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665. >> These 2 inserts are in 2 separate transactions. >> Can this be a postgresql bug? >> >> blocked_pid blocked_user blocking_statement blocking_duration >>blocking_pid blocking_user blocked_statement blocked_duration > >> 10665 postgres <IDLE> in transaction > >"<IDLE> in transaction" is a locking red flag. It means your >application has opened a transaction and is sitting there holding the >transaction open. This is a very common cause of subtle application >locking bugs. It can be legit if the application is doing heavy >processing during a transaction or you simply raced to an idle >transaction in pg_stat_activity, but in my experience 95%+ of the time >it means transaction leakage which in turn leads to locking problems. > >merlin This is weird because all of my transaction have commit or rollback. I don't leave unterminated transactions. I can't see how this is happening. Is there a possibility that some network problems generate this problem? If this is the case is these some server protection for this situation? But why a unterminated transaction blocks all table operations?
В списке pgsql-general по дате отправления: