Postgres Pro
Downloads
Home   >   mailing lists

Update blocking another update - Mailing list pgsql-general

From Sachin Kotwal
Subject Update blocking another update
Date
Msg-id CA+N_YAfhrgDj_3E4aPYszeK--ho5mu91TQ7+8usTN1ca_KSRbg@mail.gmail.com
Whole thread Raw
Responses Re: Update blocking another update
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Sreekanth Palluru
Date:
Subject: Moving from pgFouine to pgBadger Issue with Total query duration metric
Next
From: Raghavendra Rao J S V
Date:
Subject: pg_basebackup is taking more time than expected