Re: table locks - Mailing list pgsql-general

tgl@sss.pgh.pa.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
sensitiveto 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
thesecalculations. Calculations and analysis done on user side. Data analysis and data modification should be done in
thesame transaction because of obvious reason. The problem it is not enough, and serializable isolation level [may be]
requiredhere. 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
otherDBMSs) by an application. This application expects DBMS provide real and proper serializable isolation level.
Sometimesthis problem could be solved (or bypassed), though my abilities to change application logic are limited and
sometimesthe 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
thingat 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
bypassapplication 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?
Notsure 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
withtransaction 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
transactionlock. Table-level is Ok for me while general form is probably better. 

--
Best regards
Ilja Golshtein