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

how can we resolving locking state....

From: =?EUC-KR?B?s+vH9ryu?=<noh019(at)naver(dot)com>
To: <pgeu-general(at)postgresql(dot)org>
Subject: how can we resolving locking state....
Date: 2010-08-05 08:56:18
Message-ID: 15c6e295ec333ccb411eb032bcfdfa00@tweb36-2.nm (view raw or flat)
Thread:
Lists: pgeu-general
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님의 블로그
 안녕하세요.
 
 
 
 

Responses

pgeu-general by date

Next:From: Andreas 'ads' ScherbaumDate: 2010-08-05 10:20:31
Subject: Re: how can we resolving locking state....
Previous:From: Dave PageDate: 2010-07-29 09:40:25
Subject: PGDay.EU Logo required!

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