Thread: 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?
Attachment
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.
Attachment
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.