Re: Update blocking another update

2018-01-12 8:25 GMT+01:00 Sachin Kotwal <kotsachin(at)gmail(dot)com>:

> 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.