Update blocking another update

Поиск
Список
Период
Сортировка
От Sachin Kotwal
Тема Update blocking another update
Дата
Msg-id CA+N_YAfhrgDj_3E4aPYszeK--ho5mu91TQ7+8usTN1ca_KSRbg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Update blocking another update  (Guillaume Lelarge <guillaume@lelarge.info>)
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Sreekanth Palluru
Дата:
Сообщение: Moving from pgFouine to pgBadger Issue with Total query duration metric
Следующее
От: Raghavendra Rao J S V
Дата:
Сообщение: pg_basebackup is taking more time than expected