BUG #16820: PG will have a deadlock when multiple rows are updated concurrently - Mailing list pgsql-bugs

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.