pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16820: PG will have a deadlock when multiple rows are updated concurrently
2+ messages / 2 participants
[nested] [flat]

* BUG #16820: PG will have a deadlock when multiple rows are updated concurrently
@ 2021-01-13 07:43  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2021-01-13 07:43 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: abcxiaod@126.com

The following bug has been logged on the website:

Bug reference:      16820
Logged by:          yi Ding
Email address:      abcxiaod@126.com
PostgreSQL version: 10.13
Operating system:   linux
Description:        

The test is as follows:
1、the test table:
postgres=# selet *from zxin_cardcapacity;
a    |    b   |    usecapacity
----+-----+---------------
1   |    1   |  99998933863
2   |    1   |  99960281190
3   |    1   |  99960808567
4   |    1   |    9999836457
5   |    1   |    9999836457
6   |    1   |    9999836457
7   |    1   |    9999836457
8   |    1   |    9999836457
9   |    1   |    9999836457
10  |    1   |    9999836457
11  |    1   |    9999836457
12  |    1   |    9999836457
13 |    1   |    9999836457
14 |    1   |    9999836457
15 |    1   |    9999836457

2、Pressure measurement tools:
Use pgbench to initiate 100 concurrent:
$pgbench -c 100 -T 180000 -rf update.sql postgres

3. Test results:
(1) As long as the data of a single update exceeds 1 row, a deadlock will
occur:
update zxin_cardcapacity set usecapacity = usecapacity - 2 where a >2;
Pg_locks related records during deadlock:
locktype  | database |relatio |page|tuple|virtualxid|transactionid|classid |
objid | objsubid | virtualtransaction |  pid   |      mode       | granted |
fastpath
transactionid |          |           |      |       |            |       
546872 |         |       |          |        23/1480                 |
166197 | ExclusiveLock    | t       | f
 transactionid |          |          |      |       |            |       
546875 |         |       |          |        23/1480                 |
166197 | ShareLock        | f       | f
 transactionid |          |          |      |       |            |       
546872 |         |       |          |        20/1596                 |
166189 | ShareLock        | f       | f
 transactionid |          |          |      |       |            |       
546875 |         |       |          |        20/1596                 |
166189 | ExclusiveLock    | t       | f

Deadlock data:
postgres=# select xmax,xmin,* from zxin_cardcapacity ;
  xmax  |  xmin  | a | b | usecapacity
--------+--------+---+---+-------------
 546875 | 546867 | 3 | 1 | 99999602775
 546872 | 546867 | 2 | 1 | 99999075398

(2) Update one row first, then update multiple rows, there will be no
deadlock:
do $$
begin
update zxin_cardcapacity set usecapacity = usecapacity - 2 where a = 1;
update zxin_cardcapacity set usecapacity = usecapacity - 2 where a >=2;
end $$;

(3) If the entire table is updated without sorting, a deadlock will occur:
do $$
declare
v_1 record;
begin
for v_1 in select a from zxin_cardcapacity
loop
update zxin_cardcapacity set usecapacity = usecapacity - 2 where a =
v_1.a;
end loop;
end $$;

(4) After sorting, the entire table is updated cyclically without
deadlock:
do $$
declare
v_1 record;
begin
for v_1 in select a from zxin_cardcapacity order by a
loop
update zxin_cardcapacity set usecapacity = usecapacity - 2 where a =
v_1.a;
end loop;
end $$;


After analysis, we believe that the deadlock problem is caused by PG's
unique MVCC and locking mechanism.
1. Whenever a data row is updated in PG, the physical location of the row
changes, resulting in high concurrency scenarios, each query or update
operation, the order of the returned data rows is different.
2. When PG is updating data, in order to increase efficiency and realize
multi-session parallel update, the data rows in the table are locked row by
row.
The combination of the above two reasons leads to a deadlock when multiple
rows of data are updated concurrently.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16820: PG will have a deadlock when multiple rows are updated concurrently
@ 2021-01-13 16:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2021-01-13 16:26 UTC (permalink / raw)
  To: abcxiaod@126.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> (1) As long as the data of a single update exceeds 1 row, a deadlock will
> occur:
> update zxin_cardcapacity set usecapacity = usecapacity - 2 where a >2;

I see no particular bug here.  If you want to have multiple transactions
updating the same rows concurrently, it's up to you to ensure that each
transaction touches the rows in the same order, otherwise of course there
will be a deadlock.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2021-01-13 16:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-13 07:43 BUG #16820: PG will have a deadlock when multiple rows are updated concurrently PG Bug reporting form <noreply@postgresql.org>
2021-01-13 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox