From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: abcxiaod@126.com
Subject: BUG #16820: PG will have a deadlock when multiple rows are updated concurrently
Date: Wed, 13 Jan 2021 07:43:43 +0000
Message-ID: <16820-2f08e0bd1687ee34@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, abcxiaod@126.com
Subject: Re: BUG #16820: PG will have a deadlock when multiple rows are updated concurrently
In-Reply-To: <16820-2f08e0bd1687ee34@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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