pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: ³ëÇö¼® <noh019@naver.com>
To: pgsql-admin@postgresql.org
Subject: how can we  resolving  locking state....
Date: Thu, 05 Aug 2010 17:51:10 +0900
Message-ID: <ee40f08c1c31cfd43906a5089c58abde@tweb30-2.nm> (raw)

hi..
 
we test locking PostgreSQL 8.4.4 on x86_64.
 
1> session 1
###########################
$ psql mydb
drop table citytest;
CREATE TABLE citytest (
 i_id integer ,
 city varchar(80)
) ;
ALTER TABLE citytest ADD CONSTRAINT citytest_pkey PRIMARY KEY (i_id);
insert into citytest values (1,'aaa');
insert into citytest values (2,'bbb');
begin;
delete from citytest;
 &lt;------- no commit/rollback
 &lt;------- this will be Blocker
 
2> session 2,3,4
###########################
$ psql mydb
delete from citytest;
 &lt;--- waiting 
 &lt;--- these are blocked....
 
3> os process
###########################
we just kill Blocker process for resolving locking....
 but, the other process also disappear ...
 
$ ps -ef| grep postgres
post1 4921 1 .. /u01/post1/bin/postgres -D /u02/post1data
post1 4924 4921 .. postgres: writer process 
post1 4925 4921 .. postgres: wal writer process 
post1 4926 4921 .. postgres: autovacuum launcher process 
post1 4927 4921 .. postgres: archiver process 
post1 4928 4921 .. postgres: stats collector process 
post1 5143 4921 .. postgres: post1 mydb [local] idle in transaction &lt;### Blocker
post1 5591 4921 .. postgres: post1 mydb 127.0.0.1(33982) DELETE waiting &lt;### blocking
post1 5592 4921 .. postgres: post1 mydb 127.0.0.1(33983) DELETE waiting &lt;### blocking
post1 5593 4921 .. postgres: post1 mydb 127.0.0.1(33984) DELETE waiting &lt;### blocking
post1 5738 329 .. grep postgres
$
$ kill -9 5143
$
$ ps -ef| grep postgres
post1 4921 1 0 17:35 pts/2 00:00:00 /u01/post1/bin/postgres -D /u02/post1data
post1 10905 4921 0 17:39 ? 00:00:00 postgres: writer process 
post1 10906 4921 0 17:39 ? 00:00:00 postgres: wal writer process 
post1 10907 4921 0 17:39 ? 00:00:00 postgres: autovacuum launcher process 
post1 10908 4921 0 17:39 ? 00:00:00 postgres: archiver process 
post1 10909 4921 0 17:39 ? 00:00:00 postgres: stats collector process 
post1 10989 329 0 17:39 pts/2 00:00:00 grep postgres
$
 
Could you teach me, Is this expected behavior ?? (disapper blocking process not only Blocker process)
and
Could you teach me, how can we eliminate just Blocker session...
 
Thanks....
 
 
 
 
 
 
 
 
 
 
 noh019´ÔÀÇ ºí·Î±×
 ¾È³çÇϼ¼¿ä.
 
 
 
 


Message-ID: <ee40f08c1c31cfd43906a5089c58abde@tweb30-2.nm>
Permalink:  ../ee40f08c1c31cfd43906a5089c58abde@tweb30-2.nm/
Also on:    postgresql.org/message-id/ee40f08c1c31cfd43906a5089c58abde@tweb30-2.nm

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: noh019@naver.com
  Subject: Re: how can we  resolving  locking state....
  In-Reply-To: <ee40f08c1c31cfd43906a5089c58abde@tweb30-2.nm>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox