Update blocking another update

[Sachin Kotwal <kotsachin@xxxxxxxxx>]

Hi All,

As update operation is "ROW Exclusive" It should not block another update operation.

In below case we are updating all values for in one column.

It is blocking another update operations.

It this expected behavior ? Please clarify .

1. Setup database by initializing with pgbench with some scale.
pgbench -p 5432 -d postgres -s 500 -i -n

2. pgbench -p 5432 -d postgres -T 1200 -n -c 10

3. update pgbench_accounts set name ='dummy';

4. Cancel running pgbench with Ctrl + C.

5. After canceling pgbench process , we are still able to see running transaction by pgbench as below :

select datname, pid, usename, application_name, query_start, state, query from pg_stat_activity where state='active' and query not ilike 'END;';
 datname  | pid  | usename  | application_name |           query_start            | state  |
    query
----------+------+----------+------------------+----------------------------------+--------+------------------------------------------------------------------
--------------------------------------------------------------------------------
 postgres | 6409 | postgres | psql             | 2018-01-11 06:11:37.180834+05:30 | active | select datname, pid, usename, application_name, query_start, stat
e, query from pg_stat_activity where state='active' and query not ilike 'END;';
 postgres | 6426 | postgres | psql             | 2018-01-11 05:58:22.246781+05:30 | active | update pgbench_accounts set name ='dummy';
 postgres | 6434 | postgres | pgbench          | 2018-01-11 06:00:22.665211+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + 1802 WHERE aid
= 390426;
 postgres | 6435 | postgres | pgbench          | 2018-01-11 06:00:54.866775+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + 3517 WHERE aid
= 865230;
 postgres | 6436 | postgres | pgbench          | 2018-01-11 06:00:32.504115+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + -2488 WHERE aid
 = 469975;
 postgres | 6437 | postgres | pgbench          | 2018-01-11 05:59:54.809692+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + -3039 WHERE aid
 = 385296;
 postgres | 6438 | postgres | pgbench          | 2018-01-11 06:00:16.971491+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + 1489 WHERE aid
= 87498;
 postgres | 6439 | postgres | pgbench          | 2018-01-11 05:59:22.330281+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + 507 WHERE aid =
 69858;
 postgres | 6440 | postgres | pgbench          | 2018-01-11 05:59:53.27686+05:30  | active | UPDATE pgbench_accounts SET abalance = abalance + 4955 WHERE aid
= 378685;
 postgres | 6441 | postgres | pgbench          | 2018-01-11 06:00:41.727319+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + -3107 WHERE aid
 = 722157;
 postgres | 6442 | postgres | pgbench          | 2018-01-11 06:00:48.311869+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + 2679 WHERE aid
= 304148;
 postgres | 6443 | postgres | pgbench          | 2018-01-11 06:00:04.269291+05:30 | active | UPDATE pgbench_accounts SET abalance = abalance + -2954 WHERE aid
 = 400782;
(12 rows)

Ideally it should cancel queries thrown by pgbench once pgbench process is canceled.

Is this problem with pgbench ?

--

Thanks and Regards,

Sachin Kotwal