CODEWITHSUNDEEP
postgresql
KIM
KIM

Reputation: 1254

select table from AccessExclusiveLock locked table

I'm testing postgresql lock mechanism on my pgsql-9.1 on centos-5.8.

What I'm wondering is how can I select from table which is exclusively locked caused by <IDLE> in transaction uncommited.

The case is below...

In session1

postgres=# create table test_table(col1 char);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into test_table values('1');
INSERT 0 1
--uncommited

In session2

postgres=# alter table test_table add column c2 char;
-- It has been locked....

in session3

postgres=# select t.relname, l.locktype,page,pid,virtualtransaction,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;
   relname    | locktype | page | pid  | virtualtransaction |        mode         | granted 
--------------+----------+------+------+--------------------+---------------------+---------
 pg_class     | relation |      | 9940 | 2/715754           | AccessShareLock     | t
 pg_index     | relation |      | 9940 | 2/715754           | AccessShareLock     | t
 pg_namespace | relation |      | 9940 | 2/715754           | AccessShareLock     | t
 test_table   | relation |      | 9660 | 9/671042           | RowExclusiveLock    | t
 test_table   | relation |      | 9639 | 7/707191           | AccessExclusiveLock | f
(5 rows)

postgres=# select col1 from test_table;
--It's not possible to select from exclusively locked table

What I need is to get size of the relation regardless of locking happened. I'm bushing around set transaction isolation level but I didn't catch any solutions till now.

Any advice would be very appreciated.

Thanks in advance.

Upvotes: 0

Views: 1094

Answers (1)

jjanes
jjanes

Reputation: 44137

Your table is not access exclusively locked, it has an AccessExclusiveLock pending but not granted, behind a granted AccessShareLock. Although it does have about the same effect, new AccessShareLock queue up behind it. They could in theory jump the queue and be granted, but that risks the starvation of AccessExclusiveLock requests so it is not done.

If an estimate is good enough, you could do

select reltuples from pg_class where oid='test_table'::regclass;

Otherwise, about the only options is to kill one of the two processes that is holding the table lock hostage, either the one holding the AccessShare or the one wanting the AccessExclusive. (Or hack the PostgreSQL source code.)

Upvotes: 1

Related Questions