SELECT FOR 支持 SKIP LOCKED

背景信息

SELECT用于从表或视图中查询数据。

SELECT FOR 锁定子句表示对SELECT检索出来的行进行加锁。这样避免它们在当前事务结束前被其他事务修改或者删除。

功能描述

本文档仅介绍 SELECT 语法兼容 PostgreSQL 的特性。

Vastbase支持在 SELECT … FOR …子句中使用SKIP LOCKED选项，表示跳过其它未提交的事务锁定的记录，可以避免由于多个使用者同时访问表引起的锁争用问题。

注意事项

以下仅介绍PostgreSQL兼容模式下的SKIP LOCKED功能。其它兼容模式下使用此功能时，锁的行为不完全相同，请注意区分。
跳过锁定的行会导致数据的不一致，因此 SKIP LOCKED 不适用于数据库一致性（Consistency）的操作。

语法格式

SELECT ... FROM ... [ { FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N | SKIP LOCKED]} [...] ] ;

完整语法及参数含义详见《开发者指南》中的SQL语法：SELECT。

参数说明

FOR UPDATE

FOR UPDATE子句将对SELECT检索出来的行进行加锁。即其它企图 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束。这样能够避免被加锁的对象在当前事务结束前被其他事务修改或者删除。
FOR NO KEY UPDATE

FOR NO KEY UPDATE行为与FOR UPDATE类似，不过获得的锁较弱：这种锁将不会阻塞尝试在相同行上获得锁的SELECT FOR KEY SHARE命令。任何不获取FOR UPDATE锁的UPDATE也会获得这种锁模式。
FOR SHARE

FOR SHARE的行为类似，只是它在每个检索出来的行上要求一个共享锁，而不是一个排他锁。一个共享锁阻塞其它事务执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，不阻塞SELECT FOR SHARE或者SELECT FOR KEY SHARE。
FOR KEY SHARE

FOR KEY SHARE行为与FOR SHARE类似，不过锁较弱：SELECT FOR UPDATE会被阻塞，但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE，但不会阻塞其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。
OF table_name

如果在锁定子句中明确指定了表名称table_name，则只有这些指定的表被锁定，其他在SELECT中使用的表将不会被锁定。否则，将锁定该命令中所有使用的表。
WAIT N

如果被选择的行不能立即被锁住，发生锁冲突时，等待N秒后报错。
NOWAIT

为了避免操作等待其他事务提交，可使用NOWAIT选项，如果被选择的行不能立即被锁住，将会立即报错，而不是等待。
SKIP LOCKED

为了避免由于多个使用者同时访问表引起的锁争用问题，可使用SKIP LOCKED选项跳过其它未提交的事务锁定的记录。

示例

此示例涉及多个事务的操作，因此需要同时开启3个客户端会话。

1、在任意会话中创建测试表并插入数据。

CREATE TABLE tb1_1172696(id_1 int,id_2 int,id_3 int);
insert into tb1_1172696 values(1,2,3),(2,3,4),(3,4,5);

2、在事务1中，使用SELECT查询时指定SKIP LOCKED以锁定表的某一列。

begin;  --开启事务1
select * from tb1_1172696 where id_1=2 FOR UPDATE SKIP LOCKED;

查看到的表数据如下：

 id_1 | id_2 | id_3
------+------+------
    2 |    3 |    4
(1 row)

3、在事务2中，执行若干查询，插入和更新操作。

begin;  --开启事务2
select * from tb1_1172696 where id_1=2 FOR UPDATE SKIP LOCKED;  --查询a
select * from tb1_1172696 order by 1 FOR UPDATE SKIP LOCKED;  --查询b
select * from tb1_1172696 where id_1=2;  --查询c
insert into tb1_1172696 values(4,5,6);
insert into tb1_1172696 values(5,6,7),(6,3,8);
update tb1_1172696 set id_3=100 where id_2=4;  --更新语句1
update tb1_1172696 set id_3=1000 where id_2=3;  --更新语句2

查询a、查询b、查询c依次返回如下内容；插入命令执行成功；更新语句1执行成功，更新语句2发生锁冲突，被阻塞。

 id_1 | id_2 | id_3
------+------+------
(0 rows)

 id_1 | id_2 | id_3
------+------+------
    1 |    2 |    3
    3 |    4 |    5
(2 rows)

 id_1 | id_2 | id_3
------+------+------
    2 |    3 |    4
(1 row)

4、在事务3中，删除一条数据。

begin;
delete from tb1_1172696 where id_1=1;

上述删除命令指定的记录已在事务2中被锁定，此条语句执行被阻塞。

5、按照顺序，依次提交事务1、事务2、事务3。提交事务的命令如下：

end;

提交事务1后，事务2中的更新语句2执行成功；事务3中的删除命令仍处于阻塞状态；
提交事务2后，事务3中的删除命令执行成功。

6、所有事务依次提交完成后，在任意会话中查看测试表数据：

select * from tb1_1172696 order by 1;

返回结果如下，事务2和事务3中的插入、更新、删除命令均正确执行：

 id_1 | id_2 | id_3
------+------+------
    2 |    3 | 1000
    3 |    4 |  100
    4 |    5 |    6
    5 |    6 |    7
    6 |    3 | 1000
(5 rows)

VastbaseG100