Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 09:01 PM, trafdev wrote:
> I've also replaced "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt})
> {ins_stmt}" to "INSERT INTO .. ON CONFLICT DO UPDATE ...", but no
> success - row level deadlocks still occur...
> Is there a way to tell Postgres to update rows in a specified order?
> Or maybe LOCK TABLE should be used?

My little voice says the below is the answer:

https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-FOR-UPDATE-SHARE

I just do not have enough coffee in me yet to apply it your situation
directly.

>
>> Sessions are running concurrently because of flexibility - they are two
>> different scheduled jobs launching at different times and performing
>> different set of operations.
>>
>> Of course I can play with scheduling timings and make them not intersect
>> with each other (which I've done already btw), but that's only a temp
>> solution.
>>
>> So how in PostgreSQL-world 2 or more transactions can update the same
>> table without deadlocking? I can't believe it's not possible, there must
>> be some sort of synchronization primitive. Does it support a "named
>> mutex" concept from a system-programming world? I bet there is something
>> more suitable.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com