Re: Can pessimistic locking be emulated?

I was referring to 10.3 in the administrator's guide, regarding the
pg_lock view. According to the documentation, the view only contains
table level locks. However, the view also contains an xid for
transactions. The unclear part, at least to me, was what the role of
the xid was in the view and if it could be used to produce a list of
locked tuples somehow. The xid is referred to as a 'lockable object'.
I wasn't sure of the xid's role in the mix. I see now how it all works
together.

In my case, being able to view outstanding row level locks would be
enormously useful. I'm assuming this is not possible for structural or
performance reasons. I'm aware of the possible nasty side affects of
repeated query calls to the lock manager. I'm also aware what I'm
asking about may be folly or silly, my understanding of how mvcc and
transactions work together is not very refined.

A curious thought struck me: does the pg_lock view follow the mvcc
rules, i.e. if you query the pg_lock view inside a transaction, and an
external effect introduces new locks into the server are you able to see
those locks?

Merlin

> > 1. Can you query if a tuple is locked by another transaction (the
> > documentation unclearly suggests this can't be done via the pg_lock
> > view) before executing select for update...?

> Where did you find this?

>
> Regards, Christoph
>

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:
> In my case, being able to view outstanding row level locks would be
> enormously useful.

The only way to do that would be to grovel through every table in the
database, looking for rows that are marked locked by transactions that
are still alive.

> A curious thought struck me: does the pg_lock view follow the mvcc
> rules,

No, not really. If it did I don't think it'd be real useful ...

regards, tom lane

> In my case, being able to view outstanding row level locks would be
> enormously useful. I'm assuming this is not possible for structural or

Agreed -- but they're stored on the row themselves. You might be able
to write a function which executes dirty reads on the table and tells
you if the row is locked or not, but it's not going to be simple.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt(at)rbt(dot)ca> writes:
> Agreed -- but they're stored on the row themselves. You might be able
> to write a function which executes dirty reads on the table and tells
> you if the row is locked or not, but it's not going to be simple.

Actually, I don't think you need a dirty read at all. A locked row
can't be deleted as well (because there's only one xmax slot), so if you
can see it (ie, you think its xmin is committed) then you can in
principle find out whether it's locked or not. We just don't expose the
info at the moment. (You can see xmax at the user level, but you can't
easily tell if xmax is trying to delete the row or just lock it, because
you don't have access to the infomask bit that would tell you.)

regards, tom lane

On Thu, 2003-02-27 at 15:02, Tom Lane wrote:
> Rod Taylor <rbt(at)rbt(dot)ca> writes:
> > Agreed -- but they're stored on the row themselves. You might be able
> > to write a function which executes dirty reads on the table and tells
> > you if the row is locked or not, but it's not going to be simple.
>
> Actually, I don't think you need a dirty read at all. A locked row

I see. That will make it quite a bit easier then. Perhaps I'll write a
function sometime. It would make it useful for fetching things out of a
persistent work queue. Right now I deal with userlocks -- but those can
be clumsy.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc