Search Postgresql Archives

Re: how can we resolving locking state....

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

 



Le 05/08/2010 10:52, 노현석 a écrit :
>  [...]
> 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...
>  

Never use "kill -9" on a PostgreSQL process. If you do this, PostgreSQL
will stop all its processes, and will try to restart.

On 8.4, you can use pg_cancel_backend(pid) to cancel a query, and
pg_terminate_backend(pid) to terminate a connection. See
http://www.postgresql.org/docs/8.4/static/functions-admin.html for details.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[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