Thread: Update blocking another update
Hi All,
As update operation is "ROW Exclusive" It should not block another update operation.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';
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)
--
Sachin Kotwal
2018-01-12 8:25 GMT+01:00 Sachin Kotwal <kotsachin@gmail.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.
It this expected behavior ? Please clarify .In below case we are updating all values for in one column.It is blocking another update operations.
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.
Is this problem with pgbench ?5. After canceling pgbench process , we are still able to see running transaction by pgbench as below :4. Cancel running pgbench with Ctrl + C.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';
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.
--
Guillaume.