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
selecta 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@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com