AWS - redshift 中的锁

AWS Redshift 是云中数据仓库服务。通过大规模并行处理、列式数据存储和非常高效且具有针对性的数据压缩编码方案的组合，实现高效存储和最优查询性能。

AWS Redshift中有三种锁定模式：[1]
AccessExclusiveLock：主要在 DDL 操作过程中获取，如 ALTER TABLE、DROP 或 TRUNCATE。AccessExclusiveLock 将阻止其他所有锁定尝试。
AccessShareLock：在 UNLOAD、SELECT、UPDATE 或 DELETE 操作过程中获取。AccessShareLock 仅阻止 AccessExclusiveLock 尝试。AccessShareLock 不会阻止尝试对表进行读取和写入操作的其他会话。
ShareRowExclusiveLock：在 COPY、INSERT、UPDATE 或 DELETE 操作过程中获取。ShareRowExclusiveLock 阻止 AccessExclusiveLock 和其他 ShareRowExclusiveLock 尝试，但不会阻止 AccessShareLock 尝试。

这与一般的RDBMS不一样

当出现阻塞时，可以通过 以下语句进行查询，并 kill session。[1]

查询锁

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a 
left join (select pid,relation,granted from pg_locks group by 1,2,3) b 
on a.relation=b.relation and a.granted='f' and b.granted='t' 
left join (select * from stv_tbl_perm where slice=0) c 
on a.relation=c.id 
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

kill session

select pg_terminate_backend(PID);

那么什么情况下会出现锁呢。 在Redshift中，是表锁。而且在插入时也会发生锁。

Session 1：
testdb=# begin;
BEGIN
testdb=# insert into test_01(id,col1) values (1,'a');
INSERT 0 1

这时不提交，在开启 session 2 ，并尝试插入表。

Session 2：
testdb=# begin;
BEGIN
testdb=# insert into test_01 (id , col1) values (2, 'b');

这时发生等待，通过上述SQL查看锁情况。
开启监控Session，查看锁情况

testdb=# select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;
 txn_owner | txn_db |   xid   |  pid  |         txn_start          |       lock_mode       | table_id |    tablename
    | granted | blocking_pid |        txn_duration
-----------+--------+---------+-------+----------------------------+-----------------------+----------+--------------
----+---------+--------------+-----------------------------
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |    54440 | stv_tbl_perm
    | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |    54320 | stv_sessions
    | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |   168944 | svv_transacti
ons | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539951 | 22433 | 2020-05-28 06:26:14.038574 | AccessShareLock       |   177033 | test_01
    | t       |              | 0 days 0 hrs 0 mins 39 secs
 dbadmin   | testdb | 3539951 | 22433 | 2020-05-28 06:26:14.038574 | ShareRowExclusiveLock |   177033 | test_01
    | t       |              | 0 days 0 hrs 0 mins 39 secs
 dbadmin   | testdb | 3539961 | 22937 | 2020-05-28 06:26:18.39494  | ShareRowExclusiveLock |   177033 | test_01
    | f       |        22433 | 0 days 0 hrs 0 mins 35 secs
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |    16913 | pg_locks
    | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |     1259 | pg_class
    | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |    54470 | stv_transacti
ons | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539977 | 17056 | 2020-05-28 06:26:53.492043 | AccessShareLock       |   100382 |
    | t       |              | 0 days 0 hrs 0 mins 0 secs
 dbadmin   | testdb | 3539951 | 22433 | 2020-05-28 06:26:14.038574 | AccessShareLock       |   100382 |
    | t       |              | 0 days 0 hrs 0 mins 39 secs
 dbadmin   | testdb | 3539961 | 22937 | 2020-05-28 06:26:18.39494  | AccessShareLock       |   100382 |
    | t       |              | 0 days 0 hrs 0 mins 35 secs
(12 rows)

回到Session 1，进行Commit。

testdb=# insert into test_01(id,col1) values (1,'a');
INSERT 0 1
testdb=# commit;
COMMIT

看到Session 2中插入成功。

testdb=# insert into test_01 (id , col1) values (2, 'b');
INSERT 0 1

==========
[1] https://amazonaws-china.com/cn/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/