ablog

不器用で落着きのない技術者のメモ

Amazon Redshift でロックを保持しているセッションとロック待ちしているセッションを確認する方法

Redshift でロックを保持しているセッションとロック待ちしているセッションは lock_wait.sql で確認すると便利。マネジメントコンソールで Redshift の「クエリ」タブではロックを保持しているクエリは表示されるがロック待ちしているクエリは表示されない。

準備

  • テーブルを作成してレコードを insert する。
CREATE TABLE testtab1(id int primary key, name varchar(32));
INSERT INTO testtab1 (id, name) VALUES(1, 'foo');
INSERT INTO testtab1 (id, name) VALUES(2, 'bar');

検証

BEGIN;
UPDATE testtab1 SET name='fff' WHERE id=1;
UPDATE testtab1 SET name='bbb' WHERE id=2;

確認

  • ロックを保持しているセッションと待たされているセッションを確認する。
    • pid: 75517 は granted が True なのでロックを保持していて、pid:79568 は False なので待たされている。
    • 一行目の pid: 75517 は num_blocking から1セッションを待たせていて、pidlist から pid:79568 を待たされていることが分かる。
    • 三行目の pid:79568 は waiting から 2239秒(37分)待たされていることが分かる。
# \i lock_wait.sql
  xid   |  pid  | username | dbname | relation | schemaname | objectname |         mode          | granted | obj_type |         txn_start          | block_sec | block_min | block_hr | waiting | max_sec_blocking | num_blocking | pidlist
--------+-------+----------+--------+----------+------------+------------+-----------------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+---------
 863579 | 75517 | awsuser  | mydb   |   170038 | public     | testtab1   | ShareRowExclusiveLock | True    | relation | 2018-07-21 14:17:07.367098 |   2307.24 |     38.45 |     0.64 |         |          2239.24 |            1 | 79568★待たされているセッションの pid
 863579 | 75517 | awsuser  | mydb   |   170038 | public     | testtab1   | AccessShareLock       | True    | relation | 2018-07-21 14:17:07.367098 |   2307.24 |     38.45 |     0.64 |         |                  |              |
 863608 | 79568 | awsuser  | mydb   |   170038 | public     | testtab1   | ShareRowExclusiveLock | False   | relation | 2018-07-21 14:18:11.388639 |           |           |          | 2239.24★待たされている秒数 |                  |              |
(3 rows)
/**********************************************************************************************
Purpose: Return instances of table filter for all or a given table in the past 7 days
Columns:
pid: 				Process/Session Id
username:			User name
dbname:				Database
relation:			Object id
schemaname: 		Schema
objectname:			Object Name
mode:				Lock Mode (AcessShareLock, AccessExclusiveLock, etc)
granted:			Granted (True or False)
obj_type:			Type of Object
txn_start:			Start Time of the transaction that asked for the lock
block_sec:			Seconds Holding the Lock
block_min:  		Minutes Holding the lock
block_hr: 			Hours Holding the lock
waiting:			Seconds waiting for the lock	
max_sec_blocking:	Peak of seconds blocking other sessions
num_blocking:		Number of sessions blocked by this lock
pidlist:			List of Sessions being blocked by this lock
  • マネジメントコンソールにはロック待ちしているセッションは表示されない。

参考


2023/4/20 追記:
ロック待ち時間は Queue time、Exec time にも入らない。

  • 12分間ロック待ちさせ、commit してロックを解放する。
# \i lock_wait.sql
   xid   |    pid     | username | dbname | relation | schemaname | objectname |         mode          | granted | obj_type |         txn_start          | block_sec | block_min | block_hr | waiting | max_sec_blocking | num_blocking |  pidlist
---------+------------+----------+--------+----------+------------+------------+-----------------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+------------
 1778410 | 1073988027 | awsuser  | dev    |   287674 | public     | testtab1   | ShareRowExclusiveLock | True    | relation | 2023-04-19 22:41:16.415865 |    729.97 |     ★12.17 |      0.2 |         |           700.97 |            1 | 1073857081
 1778410 | 1073988027 | awsuser  | dev    |   287674 | public     | testtab1   | AccessShareLock       | True    | relation | 2023-04-19 22:41:16.415865 |    729.97 |     ★12.17 |      0.2 |         |                  |              |
 1778509 | 1073857081 | awsuser  | dev    |   287674 | public     | testtab1   | ShareRowExclusiveLock | False   | relation | 2023-04-19 22:41:45.922862 |           |           |          |  700.97 |                  |              |
  • "UPDATE testtab1 SET name='bbb' WHERE id=2" の開始時間はロック解放後の時間になっている。
# select database, query, substring(querytxt, 1, 50), starttime, endtime from stl_query where database = 'dev' and querytxt like '%UPDATE%' order by starttime;
             database             |  query  |                     substring                      |         starttime          |          endtime
----------------------------------+---------+----------------------------------------------------+----------------------------+----------------------------
 dev                              | 2411256 | /* hash: 7e521551146c1324c214c9dd6647f563 */ SELEC | 2023-04-18 22:55:35.932989 | 2023-04-18 22:55:37.244375
 dev                              | 2600136 | UPDATE testtab1 SET name='fff' WHERE id=1;         | 2023-04-19 22:41:16.432228 | 2023-04-19 22:41:16.674499
 dev                              | 2600292 | select database, substring(querytxt, 1, 50) , star | 2023-04-19 22:53:05.817674 | 2023-04-19 22:53:09.358149
 dev                              | 2600305 | ★UPDATE testtab1 SET name='bbb' WHERE id=2;         | ★2023-04-19 22:53:34.806416 | 2023-04-19 22:53:35.155323
 dev                              | 2600311 | select database, substring(querytxt, 1, 50) , star | 2023-04-19 22:53:48.480882 | 2023-04-19 22:53:48.878474
 dev                              | 2600421 | select query, database, substring(querytxt, 1, 50) | 2023-04-19 23:03:45.042256 | 2023-04-19 23:03:50.617899
  • STL_WLM_QUERY で Queue time、Exec time を確認する。
=# select * from stl_wlm_query where query = 2600305;
-[ RECORD 1 ]------------+-----------------------------------------------------------------
userid                   | 100
xid                      | 1778509
task                     | 2600157
query                    | 2600305
service_class            | 100
slot_count               | 1
service_class_start_time | 2023-04-19 22:53:34.807131
queue_start_time         | 2023-04-19 22:53:34.808162 ★
queue_end_time           | 2023-04-19 22:53:34.808162
total_queue_time         | 0 ★
exec_start_time          | 2023-04-19 22:53:34.808169 ★
exec_end_time            | 2023-04-19 22:53:35.154447
total_exec_time          | 346278 ★ 346ms(1秒未満)
service_class_end_time   | 2023-04-19 22:53:35.154447
final_state              | Completed
est_peak_mem             | 44040192
query_priority           | Normal
service_class_name       | Default queue
  • マネジメントコンソールの Duration はロック待ち時間を含むが、

  • runtime にはロック待ち時間は含まれない。