SELECT: FOR UPDATE

说明

指定对扫描经过的行加排它锁

语法

select_for_update ::=

参数

table_name

一个基本表或视图。

iconst

当发现扫描的行中存在被加锁的行，等待 iconst 时间，单位为秒。

注解

FOR UPDATE指定对扫描经过的行加排它锁。若不详细指定表名，则对扫描经过的所有行都会加排它锁，若指定，则只会对指定的表中扫描经过的行加排它锁。若对一个视图指定FOR UPDATE，则会对视图所依赖的所有基本表中扫描经过的行加排它锁。

UNION / INTERSECT / EXCEPT 这些集合操作中的子集合不能是 SELECT FOR UPDATE 语句。比如以下语句是非法的:

SELECT a, b FROM tab1 FOR UPDATE OF tab1 UNION ALL SELECT a, b FROM tab2;

使用 NOWAIT、WAIT iconst、SKIP LOCKED 之间的区别：

  • 使用 NOWAIT 时，当发现扫描的行中存在被加锁的行，不等待，立刻返回错误。
  • 使用 WAIT iconst 时，当发现扫描的行中存在被加锁的行，等待 iconst 时间，单位为秒。当超过了这个的时间，仍然有行处于加锁的状态，返回错误。
  • 使用 SKIP LOCKED 时，当发现扫描的行中存在被加锁的行，跳过加锁行，返回剩余的未加锁行。

示例

示例1: 使用 FOR UPDATE 防止死锁

--  FOR UPDATE 子句最常见的用途是用于防止死锁。
--  为说明这一问题，考虑有两个客户端，并发的执行以下的语句序列：

BEGIN;

SELECT * FROM tab1 WHERE a = 'aaa';

UPDATE tab1 SET b = 23 WHERE a = 'aaa';

COMMIT;

-- 若当前事务隔离级别为可重复读或可串行化级别，则若两个客户端以以下的顺序交叉执行上述语句：

-- 客户端1: BEGIN;

-- 客户端2: BEGIN;

-- 客户端1: SELECT * FROM tab1 WHERE a = 'aaa';

-- 客户端2: SELECT * FROM tab1 WHERE a = 'aaa';

-- 客户端1: UPDATE tab1 SET b = 23 WHERE a = 'aaa';

-- 客户端2: UPDATE tab1 SET b = 23 WHERE a = 'aaa';

-- 则会发生死锁，这是由于在执行SELECT语句时两个客户端都对tab1表中'aaa'对应的行加了共享锁，在执行UPDATE语句时又都需要对同一行加排它锁，由此就产生了锁的循环等待。

-- 可以通过对 SELECT 语句增加 FOR UPDATE 解决死锁问题，即将上述的 SELECT 语句修改为：

SELECT * FROM tab1 WHERE a = 'aaa' FOR UPDATE;

-- 这样在执行SELECT语句时就会对tab1表中'aaa'对应的行加排它锁。这样，在客户端1执行了SELECT语句后，客户端2也去执行SELECT语句时就会被阻塞，到客户端1成功执行了UPDATE语句并提交事务后，客户端2又会被重新激活并继续执行。

示例2：使用 FOR UPDATE; FOR UPDATE NOWAIT; FOR UPDATE WAIT TIME; FOR UPDATE SKIP LOCKED; 加锁与查询

drop table tab2;
create table tab2(c1 int, c2 varchar(30));
insert into tab2 values(1, 'aaa'),(2, 'aaa'),(3, 'bbb'),(4, 'ccc'),(5, 'ddd');


-- 这里可以查出符合条件的有两条数据
select * from tab2 where c2 = 'aaa';
C1(int)      |C2(varchar)      |
------------------------------
1            |aaa              |
------------------------------
2            |aaa              |
总数目：2
 
-- 会话1中对 c1 = 1 的一条数据加锁
begin;

select * from tab2 where c1 = 1 for update;
C1(int)      |C2(varchar)      |
------------------------------
1            |aaa              |
总数目：1

 

-- 会话 2 中设置 skip locked 可以看到此时只能查到 c1 = 2 的一条数据
[2]select * from tab2 where c2 = 'aaa' for update skip locked;
C1(int)      |C2(varchar)      |
------------------------------
2            |aaa              |
总数目：1


-- 会话 2 中设置 nowait 直接返回资源被占用错误
[2]select * from tab2 where c2 = 'aaa' for update nowait;
ERROR, 物理表操作出错, 资源已被占用; 执行操作时出现 WAIT 超时



-- 会话 2 中设置 wait 2 等待两秒后返回资源被占用错误
[2]select * from tab2 where c2 = 'aaa' for update wait 2;
ERROR, 物理表操作出错, 资源已被占用; 执行操作时出现 WAIT 超时

 
commit;

 
-- 会话 2 中可以查到两条数据
[2]select * from tab2 where c2 = 'aaa' for update skip locked;
C1(int)      |C2(varchar)      |
------------------------------
1            |aaa              |
------------------------------
2            |aaa              |
总数目：2

[2]select * from tab2 where c2 = 'aaa' for update nowait;
C1(int)      |C2(varchar)      |
------------------------------
1            |aaa              |
------------------------------
2            |aaa              |
总数目：2

[2]select * from tab2 where c2 = 'aaa' for update wait 3;
C1(int)      |C2(varchar)      |
------------------------------
1            |aaa              |
------------------------------
2            |aaa              |
总数目：2
 
drop table tab2;