Re: [SQL] User Permissions

I'm a fairly newcomer to Postgres (been using it for about a month now) but
I've been using databases for years. It has been my experience that the
transaction is definitely more flexible than a table lock (especially if you
cover multiple tables -- then it does all the locking for you). In your
case if you were only updating a single row in a table, then the transaction
would either lock the row only (if possible) or the page (if possible) or
the entire table (if the others were not possible) then it would release it
once the update is complete.

Now, in the case of a select, then update/insert it will try an "optimistic"
(hope I'm getting my terminology correct here) lock on the table (which
will allow others to "read" the data or also apply "optimistic" locks --
again for SELECT purposes -- but won't allow them to "change" or "write" the
data locked -- less obtrusive this way, helps speed up concurrent access to
tables). Then when you go to update whatever row, it would get an
"exclusive" lock meaning no one is allowed to even "read" the data let alone
"write" it. Again, the granularity (row vs page vs table) of the lock
depends on the PG implementation itself.

Hopefully one of the authors of PG will respond and correct/clarify anything
I've said here.

Hope this helps!

Mike

-----Original Message-----
From: Braum Meakes [mailto:braum(at)telus(dot)net]
Sent: Thursday, January 17, 2002 4:29 PM
To: SHELTON,MICHAEL (Non-HP-Boise,ex1)
Subject: RE: [SQL] User Permissions

Basically I'm locking the table so that it doesn't change in between the
initial select statement and the resulting update or insert statement. I
got the impression from the PostGres manual that postgres will only lock
the table in the lowest necessary mode, which for selects is Access Share.

Would a transaction provide the lock level I need, or would I need to use
the Set Transaction statement (which if I understand correctly is the SQL92
implementation of something similar to Lock)?

Thanks for the reply,

Braum