From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | trafdev <trafdev(at)mail(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements |
Date: | 2016-07-03 16:11:13 |
Message-ID: | 16828058-9215-1a87-2e8a-16df381a8154@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | James Keener | 2016-07-03 16:26:22 | GRANTable Row Permissions |
Previous Message | Adrian Klaver | 2016-07-03 16:04:47 | Re: 9.3 to 9.5 upgrade problems |