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