Search Postgresql Archives

how can we resolving locking state....

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;
     <------- no commit/rollback
     <------- this will be Blocker

 

2> session 2,3,4
###########################
$ psql mydb
delete from citytest;
  <--- waiting 
  <--- 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    <### Blocker
post1     5591  4921  .. postgres: post1 mydb 127.0.0.1(33982) DELETE waiting <### blocking
post1     5592  4921  .. postgres: post1 mydb 127.0.0.1(33983) DELETE waiting <### blocking
post1     5593  4921  .. postgres: post1 mydb 127.0.0.1(33984) DELETE waiting <### 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님의 블로그
안녕하세요.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux