Обсуждение: queston about locking

I'm writing a python script to update some tables in a db. My problem is
that I need to lock a couple of tables, perform several operations and
read the corresponding output.

I was thinking about lock in exclusive mode, but in the documentation I
found that it is valid only in a transaction. But since I need to
execute a command and read the output and so forth, I think I cannot use
a transaction.

What would you use to lock the table?

Ottavio Campana wrote:
> I'm writing a python script to update some tables in a db. My
> problem is
> that I need to lock a couple of tables, perform several operations and
> read the corresponding output.
>
> I was thinking about lock in exclusive mode, but in the
> documentation I
> found that it is valid only in a transaction. But since I need to
> execute a command and read the output and so forth, I think I
> cannot use
> a transaction.
>
> What would you use to lock the table?

What makes you think that you "need to lock a couple of tables"?

Maybe there is a different, less invasive way to solve your
problem. What do you want to achieve?

Yours,
Laurenz Albe

Albe Laurenz ha scritto:
> Ottavio Campana wrote:
>> I'm writing a python script to update some tables in a db. My
>> problem is
>> that I need to lock a couple of tables, perform several operations and
>> read the corresponding output.
>>
>> I was thinking about lock in exclusive mode, but in the
>> documentation I
>> found that it is valid only in a transaction. But since I need to
>> execute a command and read the output and so forth, I think I
>> cannot use
>> a transaction.
>>
>> What would you use to lock the table?
>
> What makes you think that you "need to lock a couple of tables"?

the point is that for each table I have a copy I previously made and I
want to create an incremental backup. My problem is that I don't want
the original table to change, so I lock it.

I admin that exclusive lock is probably too much.

Does share mode block inser/update/delete but allows reading?

Thanks.

PS: By the way, I just discovered that with python psycopg2 the cursor
is wrapped in a transaction, so locking works. So the problem is only
the correct lock level.

On Fri, Sep 21, 2007 at 11:53:54AM +0200, Ottavio Campana wrote:
> the point is that for each table I have a copy I previously made and I
> want to create an incremental backup. My problem is that I don't want
> the original table to change, so I lock it.
>
> I admin that exclusive lock is probably too much.

Why not just use SERLIALISED transaction mode, then your program won't
see any changes, while other programs can still use it normally. That's
how pg_dump generates consistant backups.

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