Thread: Exclusive row locks not release

Hi,

I have a case where exclusive row locks have been placed on a table and
I don't what process has the locks or how they might be released.

The locks are still there even after I have restarted the database.
Rebooting the server also made no difference.

I am running the latest pg version 9.1.2-1.

Here is the query I used to show the locks:
select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted
from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by
relation asc;

Here are the locks (excuse formatting), table name is EJB__TIMER__TBL:
relname    locktype    page    virtualtransaction    pid    mode    granted
pg_class    relation <null>    2/63    3961    AccessShareLock    true
pg_index    relation <null>    2/63    3961    AccessShareLock    true
pg_namespace    relation <null>    2/63    3961    AccessShareLock    true
EJB__TIMER__TBL    relation <null>    -1/1761142 <null>
RowExclusiveLock    true
EJB__TIMER__TBL    relation <null>    -1/1758118 <null>
RowExclusiveLock    true

According to pg_catalog.pg_stat_activity, there are no other connections
to the database.

Suggestions?

Thanks
Mark

Mark van Leeuwen <markvl@internode.on.net> writes:
> I have a case where exclusive row locks have been placed on a table and
> I don't what process has the locks or how they might be released.

> The locks are still there even after I have restarted the database.

Uncommitted prepared transaction, perhaps?  Look into pg_prepared_xacts.

            regards, tom lane

On 20/01/2012 4:40 PM, Tom Lane wrote:
> Mark van Leeuwen<markvl@internode.on.net>  writes:
>> I have a case where exclusive row locks have been placed on a table and
>> I don't what process has the locks or how they might be released.
>> The locks are still there even after I have restarted the database.
> Uncommitted prepared transaction, perhaps?  Look into pg_prepared_xacts.
>
>             regards, tom lane
>
Yes, that was it. Don't have much experience with Postgresql - had
expected all locks would have been released by stopping the database.

I used ROLLBACK PREPARED transaction_id to remove the locks.

Thanks for your help,
Mark

Hi,

Is it viable to have very many prepared transactions? As in tens of
thousands or even more?

The idea is so that a web application can do _persistent_
transactional stuff over multiple pages/accesses/sessions and have it
rolled back easily, or committed if desired. I'm thinking that it
might be better to do this instead of reinventing transactions at the
application layer.

Would it be better to have separate postgresql databases for this?
One for the persistent transactions stuff, and one for "normal"
stuff, which will manage the persistent transactions.

Regards,
Link.

On 2012-01-20, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:
> Hi,
>
> Is it viable to have very many prepared transactions? As in tens of
> thousands or even more?
>
> The idea is so that a web application can do _persistent_
> transactional stuff over multiple pages/accesses/sessions and have it
> rolled back easily, or committed if desired. I'm thinking that it
> might be better to do this instead of reinventing transactions at the
> application layer.

why not uses "session" like everyone else does,
don't load the database down with managing website logic.

> Would it be better to have separate postgresql databases for this?
> One for the persistent transactions stuff, and one for "normal"
> stuff, which will manage the persistent transactions.

what isolation level do you need for your "persistant transactions"?

--
⚂⚃ 100% natural