Re: handling concurrency right why am i wrong?

Christian Brennsteiner <eingfoan(at)yahoo(dot)de> writes:
> i have a simple updateable view V with a status field S.
> ...
> each clients tries to ----------------- update V set S ='$MYCLIENTID' where
> S = 'TOBEPROCESSED'
> in this way i try to reserve the current available data TOBEPROCESSED for
> one client and then process it.
> when i do this i sometimes (if they overlap) get the following exception:
> Stacktrace: java.sql.SQLException: ERROR: deadlock detected

There are no "simple updateable views" in Postgres. Your problem is
probably traceable to some aspect of the view update rule, or possibly
something about foreign keys or other actions that have to be taken
pursuant to the update on the underlying table. But since you haven't
shown us any of the schema details, it's impossible to do more than
guess.

In general it seems like you're trying to reinvent a queuing mechanism.
You'd be better off adopting one of the existing ones, as getting this
both right and high-performing is harder than one might think.

regards, tom lane