Re: table locks

Hello!

I need table locks to provide application logic. Just very common lock types with very common behavior - Shared and Exclusive.

Which PostgreSQL's lock modes should be preferred in order to avoid conflicts with autovacuum and suchlike?

Or, may be, the best option for me is a contrib module?

Thanks a lot.

--
Best regards
Ilja Golshtein

On Mon, Dec 25, 2006 at 02:58:26PM +0300, Ilja Golshtein wrote:
> Hello!
>
> I need table locks to provide application logic. Just very common lock types with very common behavior - Shared and Exclusive.
>
> Which PostgreSQL's lock modes should be preferred in order to avoid conflicts with autovacuum and suchlike?

The documentation on locks clearly lists what lock types conflict with
what.

But seriously, why block autovacuum? It no visible effect on the table.
Why do you want to lock at all? Lock-free designs are always better.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

>But seriously, why block autovacuum? It no visible effect on the table.

I DO NOT want to block autovacuum.
I need lock modes do not interfere with anything else just to use in my application.

user_locks is fine, while it does not provide waiting (only immediate Yes or No).

>Why do you want to lock at all?

It's long and sad story ;(

--
Best regards
Ilja Golshtein

"Ilja Golshtein" <ilejn(at)yandex(dot)ru> writes:
>> Why do you want to lock at all?

> It's long and sad story ;(

If you don't explain what you're trying to accomplish, you're unlikely
to get useful advice.

regards, tom lane

>>> Why do you want to lock at all?
>
>> It's long and sad story ;(
>
>If you don't explain what you're trying to accomplish, you're unlikely
>to get useful advice.

Tom,

I need table level locks for cooperative usage in my application.

LOCK TABLE table_name in EXCLUSIVE MODE
and
LOCK TABLE table_name in SHARED MODE
perfectly suit my needs.

The only drawback - interference with VACUUM and other system processes
with obvious performance/response time penalty.

So I need very simple thing: locks behave exactly like EXCLUSIVE and SHARED
I can use to control my own application processes.

Honestly I've already introduced such locks with syntax
LOCK TABLE table_name in APPLICATION EXCLUSIVE MODE
and
LOCK TABLE table_name in APPLICATION SHARED MODE
Does publishing of this patch make any sense?

I can explain what is the reason of table locks usage (briefly I need this thing to provide transaction isolation).
Story is long and sad indeed, though I can retell it if it seems to be useful.

Thanks a lot for your response.

--
Best regards
Ilja Golshtein

"Ilja Golshtein" <ilejn(at)yandex(dot)ru> writes:
> I need table level locks for cooperative usage in my application.

> LOCK TABLE table_name in EXCLUSIVE MODE
> and
> LOCK TABLE table_name in SHARED MODE
> perfectly suit my needs.

The question is *why* you feel you need that, ie what are you using
these for? As was already mentioned upthread, it's usually better
to avoid explicit locking altogether, if you can.

> The only drawback - interference with VACUUM and other system processes
> with obvious performance/response time penalty.

I can hardly imagine an ordinary lock type that doesn't conflict with
anything at all ... ALTER/DROP TABLE being the obvious counterexamples.
If you don't want your "shared" lock to conflict with VACUUM then you
could use ACCESS SHARE instead of SHARE, and it would still block
EXCLUSIVE.

> Honestly I've already introduced such locks with syntax
> LOCK TABLE table_name in APPLICATION EXCLUSIVE MODE
> and
> LOCK TABLE table_name in APPLICATION SHARED MODE
> Does publishing of this patch make any sense?

It's quite unlikely to get accepted, considering that advisory locks
already seem to cover the territory. (8.2 has blocking versions of
those calls BTW.)

regards, tom lane

tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>The question is *why* you feel you need that, ie what are you using
>these for? As was already mentioned upthread, it's usually better
>to avoid explicit locking altogether, if you can.

Scenario 1. One has objects belong to a metaclass. Certain operations changes metaclass while some other operations are sensitive to these changes and could not be executed during changes.
Metaclass corresponds with a table and it is very convenient to be able to lock such a table exclusively or shared.

Scenario 2. One should calculate number of items (or amount of money on several accounts) and do some actions based on these calculations. Calculations and analysis done on user side. Data analysis and data modification should be done in the same transaction because of obvious reason. The problem it is not enough, and serializable isolation level [may be] required here. Other problem one cannon use PostgreSQL▓s serializable transaction isolation because of number of conflicts.

My own case is similar with Scenario 2 while it is rather special. In my company PostgreSQL is used as a backend (among other DBMSs) by an application. This application expects DBMS provide real and proper serializable isolation level. Sometimes this problem could be solved (or bypassed), though my abilities to change application logic are limited and sometimes the only option is table locks.

I have very long experience with Oracle. During those years I▓ve never used table locks and never thought about such a thing at all, so I understand my questions look peculiar. Actually they are not as strange as they probably seem.

>> The only drawback - interference with VACUUM and other system processes
>> with obvious performance/response time penalty.
>
>I can hardly imagine an ordinary lock type that doesn't conflict with
>anything at all ... ALTER/DROP TABLE being the obvious counterexamples.

That▓s true and DELETE/UPDATE/INSERT statements are other counterexamples. The idea is I don▓t care about anything bypass application logic. It is not perfect, but it is acceptable. It's nature of advisory locks after all.

>If you don't want your "shared" lock to conflict with VACUUM then you
>could use ACCESS SHARE instead of SHARE, and it would still block
>EXCLUSIVE.

So you suggest I use ACCESS SHARE instead of SHARE. It means I should use ACCESS EXCLUSIVE instead of EXCLUSIVE, right? Not sure it is better pair then SHARED/EXCLUSIVE because ACCESS EXCLUSIVE blocks even SELECTs, while I give it a try.

>It's quite unlikely to get accepted, considering that advisory locks
>already seem to cover the territory. (8.2 has blocking versions of
>those calls BTW.)

Thank you for information about advisory locks. It is cool we have blocking versions now. Next step is advisory locks with transaction behavior, right? It would be very very nice.

Not sure my English is good enough to put adjectives in proper order ;) , but what I need is advisory [table] blocking transaction lock. Table-level is Ok for me while general form is probably better.

--
Best regards
Ilja Golshtein