Re: table locks

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