Re: Update blocking another update

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

2018-01-12 8:25 GMT+01:00 Sachin Kotwal <kotsachin@xxxxxxxxx>:

Hi All,

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

As long as two processes don't try to update the same row.

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

It is blocking another update operations.

It this expected behavior ? Please clarify .

Yes, it is expected. If you update all rows in a single statement, any other updates will be blocked till the update-all-rows statement is done.

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 ?

--

Guillaume.