Re: User Permissions

SHELTON,MICHAEL (Non-HP-Boise,ex1) wrote:
> I don't have an answer for your issue, just a question on your process:
>
> Why do you lock the table instead of using a transaction? The transaction
> would prevent the data from changing until you either applied your changes
> (insert or update) and commited the transaction or rolled back the
> transaction (due to an error or something).

He is most probably doing so to achieve the every so often
desired IF_EXISTS_UPDATE_ELSE_INSERT functionality.

A function in PostgreSQL is allways guaranteed to be covered
by a transaction. But that doesn't matter. If you select a
key for update and based on if you found it decide to update
or insert, you have a possible race condition.

Doing

SELECT x FROM y WHERE z = 666 FOR UPDATE

will succeed in multiple transactions simultaneously as long
as there is no row with z = 666. So the entire code section
of checking if the key exists until update or insert has to
be covered by a mutex, and there is no other mutex mechanism
available than a table lock.

Even if it's not standard, I would be for a proprietary
extension that actually implements some sort of mutex for
stored procedures.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com