2018-01-12 8:25 GMT+01:00 Sachin Kotwal <kotsac...@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.
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.
