Thread: It sorta works, but I'm confused about locking
I've got this new notify code almost working, but...
What exactly is the protocol for locking a table that you intend to
modify? The old notify code just did RelationSetLockForWrite(),
but it's not clear to me that that works correctly --- for one thing,
it doesn't seem to offer any way of detecting failure to acquire the
lock. (RelationSetLockForWrite calls MultiLockReln, which *does*
return a boolean, but RelationSetLockForWrite ignores it...) Also,
it's not at all clear whether I should call RelationUnsetLockForWrite
at the end of the routine or not; some existing code does, some doesn't.
I'm concerned because interlocking of the specialized NOTIFY-related
statements seems to work fine, but they seem not to be interlocked
against user operations on the pg_listener table.
For example, this works as I'd expect:
psql#1 psql#2
begin;
listen z;
notify z;
(hangs up until #1 commits)
end;
because "listen" acquires a write lock on the pg_listener table, which
the notify has to wait for.
But this doesn't work as I'd expect:
psql#1 psql#2
begin;
select * from pg_listener;
notify z;
(completes immediately)
end;
Seems to me the "select" should acquire a read lock that would prevent
the #2 backend from writing pg_listener until the end of #1's transaction.
Is there a bug here, or is there some special definition of user access
to a system table that means the select isn't acquiring a read lock?
Selects and updates on ordinary user tables seem to interlock fine...
regards, tom lane
> > I've got this new notify code almost working, but... Could you plase send it to me ? > What exactly is the protocol for locking a table that you intend to > modify? The old notify code just did RelationSetLockForWrite(), > but it's not clear to me that that works correctly --- for one thing, > it doesn't seem to offer any way of detecting failure to acquire the > lock. (RelationSetLockForWrite calls MultiLockReln, which *does* > return a boolean, but RelationSetLockForWrite ignores it...) Also, > it's not at all clear whether I should call RelationUnsetLockForWrite > at the end of the routine or not; some existing code does, some doesn't. It is not done where there is an immediate CommitTransactionCommand which already releases the locks. > I'm concerned because interlocking of the specialized NOTIFY-related > statements seems to work fine, but they seem not to be interlocked > against user operations on the pg_listener table. > > For example, this works as I'd expect: > > psql#1 psql#2 > > begin; > listen z; > > notify z; > (hangs up until #1 commits) > > end; > > because "listen" acquires a write lock on the pg_listener table, which > the notify has to wait for. > > But this doesn't work as I'd expect: > > psql#1 psql#2 > > begin; > select * from pg_listener; > > notify z; > (completes immediately) > > end; > > Seems to me the "select" should acquire a read lock that would prevent > the #2 backend from writing pg_listener until the end of #1's transaction. > > Is there a bug here, or is there some special definition of user access > to a system table that means the select isn't acquiring a read lock? > Selects and updates on ordinary user tables seem to interlock fine... Very strange, it seems a bug. But users should not know about pg_listener. -- Massimo Dal Zotto +----------------------------------------------------------------------+ | Massimo Dal Zotto email: dz@cs.unitn.it | | Via Marconi, 141 phone: ++39-461-534251 | | 38057 Pergine Valsugana (TN) www: http://www.cs.unitn.it/~dz/ | | Italy pgp: finger dz@tango.cs.unitn.it | +----------------------------------------------------------------------+
Massimo Dal Zotto <dz@cs.unitn.it> writes:
>> it's not at all clear whether I should call RelationUnsetLockForWrite
>> at the end of the routine or not; some existing code does, some doesn't.
> It is not done where there is an immediate CommitTransactionCommand which
> already releases the locks.
Hmm. OK, but I guess I don't really understand why it's ever valid to
release a lock before the end of the transaction --- and
CommitTransactionCommand isn't necessarily the end of the transaction
anyway, if you're inside a transaction block.
Basically: it seems to me it's a bug to call UnsetLock (any flavor)
from *anywhere* except transaction commit. If this is not so, why not?
regards, tom lane
> I've got this new notify code almost working, but... > > What exactly is the protocol for locking a table that you intend to > modify? The old notify code just did RelationSetLockForWrite(), > but it's not clear to me that that works correctly --- for one thing, > it doesn't seem to offer any way of detecting failure to acquire the > lock. (RelationSetLockForWrite calls MultiLockReln, which *does* > return a boolean, but RelationSetLockForWrite ignores it...) Also, > it's not at all clear whether I should call RelationUnsetLockForWrite > at the end of the routine or not; some existing code does, some doesn't. Welcome to PostgreSQL. This is the type of thing I saw when doing the mega-patch, were people were not doing things consistently, because some coders do not understand what is happening inside the code, and just write something that works, sort of. I recommend you check out what is currently done properly, and fix the ones that are incorrect. I can imagine some cases where you would want to get a lock and keep it until the end of the transaction, and other times when you would want to release it before transaction end. > > I'm concerned because interlocking of the specialized NOTIFY-related > statements seems to work fine, but they seem not to be interlocked > against user operations on the pg_listener table. > > For example, this works as I'd expect: > > psql#1 psql#2 > > begin; > listen z; > > notify z; > (hangs up until #1 commits) > > end; > > because "listen" acquires a write lock on the pg_listener table, which > the notify has to wait for. > > But this doesn't work as I'd expect: > > psql#1 psql#2 > > begin; > select * from pg_listener; > > notify z; > (completes immediately) > > end; > > Seems to me the "select" should acquire a read lock that would prevent > the #2 backend from writing pg_listener until the end of #1's transaction. > > Is there a bug here, or is there some special definition of user access > to a system table that means the select isn't acquiring a read lock? > Selects and updates on ordinary user tables seem to interlock fine... Select certainly should be locking. Something is wrong, but I am not sure what. If you want me to check into it, let me know. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I recommend you check out what is currently done properly, and fix the
> ones that are incorrect.
Well, yes. The question was how to tell which is which :-)
> I can imagine some cases where you would want to get a lock and keep it
> until the end of the transaction, and other times when you would want to
> release it before transaction end.
I guess I'm not understanding something. How can it ever be correct
practice to release a lock before transaction end? For example, if I
write some changes in a table, and then release the lock, wouldn't that
allow other backends to see the not-yet-committed changes? What if I
then abort my transaction? Now the other backends have acted on
information they should never have seen at all.
Releasing a read lock strikes me as just as dangerous but for more
subtle reasons --- once you have read a table, what you have read
ought to look the same until the end of your transaction.
Since there is an unset-write-lock function, I assume it must have
valid uses, but I don't see what they are.
>> Is there a bug here, or is there some special definition of user access
>> to a system table that means the select isn't acquiring a read lock?
>> Selects and updates on ordinary user tables seem to interlock fine...
> Select certainly should be locking. Something is wrong, but I am not
> sure what. If you want me to check into it, let me know.
Please. (Note that I saw this with my revised version of async.c;
I believe you will see the same behavior with the currently-checked-in
code, but do not have the time to rebuild that version to make sure.)
regards, tom lane
Tom Lane wrote:
>
> > I can imagine some cases where you would want to get a lock and keep it
> > until the end of the transaction, and other times when you would want to
> > release it before transaction end.
>
> I guess I'm not understanding something. How can it ever be correct
> practice to release a lock before transaction end? For example, if I
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Postgres does this for system tables only.
> write some changes in a table, and then release the lock, wouldn't that
> allow other backends to see the not-yet-committed changes? What if I
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No. Backend uses xmin/xmax to know is tuple visible or not.
> then abort my transaction? Now the other backends have acted on
> information they should never have seen at all.
Backend will see that xmin(xmax) is not committed.
Vadim
Vadim Mikheev <vadim@krs.ru> writes:
> Tom Lane wrote:
>> I guess I'm not understanding something. How can it ever be correct
>> practice to release a lock before transaction end? For example, if I
>> write some changes in a table, and then release the lock, wouldn't that
>> allow other backends to see the not-yet-committed changes? What if I
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> No. Backend uses xmin/xmax to know is tuple visible or not.
Ah, some light dawns. Thanks for the clue!
> Postgres does this for system tables only.
OK, so what is good coding practice? Always release write lock after
operating on a system table? Or is that only good some of the time,
and if so what's the consideration?
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes: > > I recommend you check out what is currently done properly, and fix the > > ones that are incorrect. > > Well, yes. The question was how to tell which is which :-) I didn't say it was easy. :-) > > > I can imagine some cases where you would want to get a lock and keep it > > until the end of the transaction, and other times when you would want to > > release it before transaction end. > > I guess I'm not understanding something. How can it ever be correct > practice to release a lock before transaction end? For example, if I > write some changes in a table, and then release the lock, wouldn't that > allow other backends to see the not-yet-committed changes? What if I > then abort my transaction? Now the other backends have acted on > information they should never have seen at all. > > Releasing a read lock strikes me as just as dangerous but for more > subtle reasons --- once you have read a table, what you have read > ought to look the same until the end of your transaction. > > Since there is an unset-write-lock function, I assume it must have > valid uses, but I don't see what they are. Suppose you want to update a table as part of internal query processing. You lock it, update it, and unlock it. You just need the lock while you are updating it. > > >> Is there a bug here, or is there some special definition of user access > >> to a system table that means the select isn't acquiring a read lock? > >> Selects and updates on ordinary user tables seem to interlock fine... > > > Select certainly should be locking. Something is wrong, but I am not > > sure what. If you want me to check into it, let me know. > > Please. (Note that I saw this with my revised version of async.c; > I believe you will see the same behavior with the currently-checked-in > code, but do not have the time to rebuild that version to make sure.) -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026