Re: UPDATE ... RETURNING atomicity

On 05/24/2010 01:29 AM, Grzegorz Jaśkiewicz wrote:
> don't lock tables explicitly. That's a killer for (concurrent) performance.
> Just write queries properly, and use appropriate transaction level.
> And you are sorted.
Read Committed is fine, as long as I restart the UPDATE query RETURNING
nothing. The specifics of our app allow retrying the said query a few
times and if it still did not get the id (like during the improbable
total ID exhaustion), then pass through, this is considered a tolerable
soft error. I suspect retrying just a single query is less expensive
than retrying the failed serializable transaction, which is more
heavy-weight in nature (and in practice).

BTW, regarding your comment on avoiding to use explicit LOCKs: in one
place which wasn't speed-sensitive I had to use the strictest LOCK mode
because otherwise deadlocks occurred from time to time.