Thread: Good name for new lock type for VACUUM?
Awhile ago I said that I wanted to create a new flavor of table-level
lock for concurrent VACUUM to get on a table. RowExclusiveLock is
not the right thing because it is not self-exclusive, whereas we don't
want more than one VACUUM mangling a table at a time. But anything
higher locks out concurrent writers, which we don't want either.
So we need an intermediate lock type that will conflict with itself
as well as with ShareLock and above. (It must conflict with ShareLock
since we don't want new indexes being created during VACUUM either...)
I'm having a hard time coming up with a name, though. I originally
called it "VacuumLock" but naming it after its primary use seems bogus.
Some other possibilities that I don't much like either:
SchemaLock --- basically we're locking down the table schemaWriteShareLock --- sharing access with writers
Any better ideas out there? Where did the existing lock type names
come from, anyway? (Not SQL92 or SQL99, for sure.)
BTW, I'm assuming that I should make the new lock type available
at the user level as a LOCK TABLE option. Any objections to that?
regards, tom lane
> Any better ideas out there? Names were always hard for me -:) > Where did the existing lock type names > come from, anyway? (Not SQL92 or SQL99, for sure.) Oracle. Except for Access Exclusive/Share Locks. Vadim
Tom Lane wrote: >Awhile ago I said that I wanted to create a new flavor of table-level >lock for concurrent VACUUM to get on a table. RowExclusiveLock is >not the right thing because it is not self-exclusive, whereas we don't >want more than one VACUUM mangling a table at a time. But anything >higher locks out concurrent writers, which we don't want either. >So we need an intermediate lock type that will conflict with itself >as well as with ShareLock and above. (It must conflict with ShareLock >since we don't want new indexes being created during VACUUM either...) > *snip* > >BTW, I'm assuming that I should make the new lock type available >at the user level as a LOCK TABLE option. Any objections to that? > I think that type of lock would best be kept to the system level. *thinking out loud* If your goal is to have it used more often, then user level might provide more opportunities for testing. However, I can't really think of any situation where it would be beneficial to a user. The rest of the locks seem to take care of everything else. Is it going to timeout? If a connection is dropped by a user, will the lock release?
Thomas Swan <tswan@olemiss.edu> writes:
> I think that type of lock would best be kept to the system level.
Why?
I don't have a scenario offhand where it'd be useful, but if we've
discovered it's useful for VACUUM then there may be cases where a lock
with these properties would be useful to users as well. Besides, we
have several lock types that are exposed to users even though we've
found no uses for them at the system level.
> Is it going to timeout? If a connection is dropped by a user, will the
> lock release?
No, and yes, same as any other lock.
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
> I've been staring at the lock names for an hour now and the best name I've
> come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise
> exclusive" (the implication being that update would allow select as well),
> or some permutation thereof.
Okay with me, unless someone else comes up with a better idea...
regards, tom lane
Tom Lane writes: > Awhile ago I said that I wanted to create a new flavor of table-level > lock for concurrent VACUUM to get on a table. > I'm having a hard time coming up with a name, though. I originally > called it "VacuumLock" but naming it after its primary use seems bogus. Not that a name like "share row exclusive" is any less bogus. ;-) I've been staring at the lock names for an hour now and the best name I've come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise exclusive" (the implication being that update would allow select as well), or some permutation thereof. Any other constructs that follow the existing patterns lead to significantly less desirable names like EXCLUSIVE ROW EXCLUSIVE == like ROW EXCLUSIVE, but self-exclusive, or ROW EXCLUSIVE SHARE == like SHARE, but allows ROW EXCLUSIVE -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Tom Lane writes: > > > Awhile ago I said that I wanted to create a new flavor of table-level > > lock for concurrent VACUUM to get on a table. > > > I'm having a hard time coming up with a name, though. I originally > > called it "VacuumLock" but naming it after its primary use seems bogus. > > Not that a name like "share row exclusive" is any less bogus. ;-) > > I've been staring at the lock names for an hour now and the best name I've > come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise > exclusive" (the implication being that update would allow select as well), > or some permutation thereof. > > Any other constructs that follow the existing patterns lead to > significantly less desirable names like > > EXCLUSIVE ROW EXCLUSIVE == like ROW EXCLUSIVE, but self-exclusive, or > > ROW EXCLUSIVE SHARE == like SHARE, but allows ROW EXCLUSIVE Sounds good. I documented the lock types as best I could in the LOCK manual page. I think that is as good as we can do to explain them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Tom Lane > > Peter Eisentraut <peter_e@gmx.net> writes: > > I've been staring at the lock names for an hour now and the > best name I've > > come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise > > exclusive" (the implication being that update would allow > select as well), > > or some permutation thereof. > > Okay with me, unless someone else comes up with a better idea... > I have no better idea but I hope to leave VacuumLock as an alias because I can't remember others. regards, Hiroshi Inoue
> -----Original Message----- > From: Hiroshi Inoue > > -----Original Message----- > > From: Tom Lane > > > > Peter Eisentraut <peter_e@gmx.net> writes: > > > I've been staring at the lock names for an hour now and the > > best name I've > > > come up with is SHARE UPDATE EXCLUSIVE, as in "share update, otherwise > > > exclusive" (the implication being that update would allow > > select as well), > > > or some permutation thereof. > > > > Okay with me, unless someone else comes up with a better idea... > > > > I have no better idea but I hope to leave VacuumLock as an alias > because I can't remember others. > Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock which conflicts with only itself ? regards, HIroshi Inoue
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
> which conflicts with only itself ?
*Only* itself? What would that be useful for? Not for locking
tables, anyway --- if you don't conflict with AccessExclusiveLock
then it's possible for someone to drop the table while you're
working on it.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock > > which conflicts with only itself ? > > *Only* itself? What would that be useful for? Isn't VacuumLock = RowExclusiveLock + SelfExclusiveLock for the table ? regards, Hiroshi Inoue
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock
> which conflicts with only itself ?
>>
>> *Only* itself? What would that be useful for?
> Isn't VacuumLock = RowExclusiveLock + SelfExclusiveLock
> for the table ?
Oh, I see, you're suggesting acquiring two separate locks on the table.
Hmm. There would be a risk of deadlock if two processes tried to
acquire these locks in different orders. That's not a big problem for
VACUUM, since all processes would presumably be executing the same
VACUUM code. But it raises questions about just how useful this lock
type would be in general-purpose use. You could never acquire *only*
this lock type, it'd have to be combined with something else, so it
seems like any usage would have to be carefully examined for deadlocks.
Still, it's an interesting alternative. Comments anyone?
regards, tom lane
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Isn't it a better idea to have a separate 'SELF EXCLUSIVE' lock > > which conflicts with only itself ? > >> > >> *Only* itself? What would that be useful for? > > > Isn't VacuumLock = RowExclusiveLock + SelfExclusiveLock > > for the table ? > > Oh, I see, you're suggesting acquiring two separate locks on the table. > Hmm. There would be a risk of deadlock if two processes tried to > acquire these locks in different orders. That's not a big problem for > VACUUM, since all processes would presumably be executing the same > VACUUM code. But it raises questions about just how useful this lock > type would be in general-purpose use. You could never acquire *only* > this lock type, it'd have to be combined with something else, so it > seems like any usage would have to be carefully examined for deadlocks. > > Still, it's an interesting alternative. Comments anyone? SelfExclusiveLock is clear and can't be confused with other lock types. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Still, it's an interesting alternative. Comments anyone?
> SelfExclusiveLock is clear and can't be confused with other lock types.
It could possibly be made a little less dangerous if "SelfExclusiveLock"
were defined to conflict with itself and AccessExclusiveLock (and
nothing else). That would at least mean that holding SelfExclusiveLock
would guarantee the table not go away under you; so there might be
scenarios where holding just that lock would make sense.
Still, I'm not sure that this lock type is as flexible as it seems at
first glance. What you'd probably really want it to do is guarantee
that no other instance of your same operation (whatever it is) is
running, and then you'd acquire another lock type to lock out other
operations that you couldn't run in parallel with. Sounds great,
except there'd only be one SelfExclusiveLock per table. So, for
example, your operation would conflict with VACUUM whether you wanted
it to or not.
Between that and the need-two-locks hazards, I'm unconvinced that this
is a better idea.
regards, tom lane
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Still, it's an interesting alternative. Comments anyone? > > > SelfExclusiveLock is clear and can't be confused with other lock types. > > It could possibly be made a little less dangerous if "SelfExclusiveLock" > were defined to conflict with itself and AccessExclusiveLock (and > nothing else). That would at least mean that holding SelfExclusiveLock > would guarantee the table not go away under you; so there might be > scenarios where holding just that lock would make sense. > > Still, I'm not sure that this lock type is as flexible as it seems at > first glance. I don't think "SelfExclusiveLock" is an excellent lock either. However it seems to point out the reason why we couldn't place(name) "VacuumLock" properly in our locking system. regards, Hiroshi Inoue