BUG #16820: PG will have a deadlock when multiple rows are updated concurrently

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16820: PG will have a deadlock when multiple rows are updated concurrently

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      16820
Logged by:          yi Ding
Email address:      [hidden email]
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 | Threaded
Open this post in threaded view
|

Re: BUG #16820: PG will have a deadlock when multiple rows are updated concurrently

Tom Lane-2
PG Bug reporting form <[hidden email]> 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