SELECT FOR UPDATE
有时应用会从数据库选择一些数据,然后对它们做一些处理并且最终将一些被更改的数据存回到数据库。这里的问题是两个人可能选择相同的未处理数据,那么对于那些已处理行的更改将被覆盖。简而言之,将会发生一种竞争的情况。
为了解决这一问题,可以使用SELECT FOR UPDATE
:
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT * FROM test WHERE id = 5 FOR UPDATE; | |
返回5 | UPDATE test SET id = 7 WHERE id = 5; |
等待事务1 | |
UPDATE test SET id = 6 WHERE id = 5; | 等待事务1 |
COMMIT; | 等待事务1 |
执行完毕 | |
COMMIT; | |
SELECT id FROM test; | |
返回6 |
SELECT FOR UPDATE NOWAIT
SELECT FOR UPDATE
将会像UPDATE
那样锁住行,这意味着不会发生并发的更改。如果一个SELECT FOR UPDATE
在等待其他某个SELECT FOR UPDATE
,那前者必须等到后者结束,如果后者不结束,前者可能会永远等待。
为了避免这种情况,可以使用SELECT FOR UPDATE NOWAIT
:
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT * FROM test WHERE id = 6 FOR UPDATE NOWAIT; | |
返回6 | SELECT * FROM test WHERE id = 6 FOR UPDATE NOWAIT; |
ERROR: could not obtain lock on row in relation “test” | |
COMMIT; | ROLLBACK; |
用户如果觉得NOWAIT不够灵活,可以使用lock_timeout,它表示用户想要花在等待锁上的时间,可以在会话级别上设置这个参数:
SET
lock_timeout
TO
5000;
SELECT FOR UPDATE SKIP LOCKED
假设我们想填满一架200个座位的飞机,很多人想要并发地预订座位。这个时候就会发生:
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT * FROM flight LIMIT 1 FOR UPDATE; | |
SELECT * FROM flight LIMIT 1 FOR UPDATE; | |
等待事务1 | |
等待事务1 |
这里的问题在于在一个时间点上只能有一个座位被预订。可以用来预订的座位实际上有200个,但是每一个人都必须等待第一个人。这个时候可以使用SELECT FOR UPDATE SKIP LOCKED
。
CREATE TABLE t_flight (
id int
);
INSERT INTO t_flight (
id
)
SELECT
*
FROM
generate_series(1, 200);
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT * FROM t_flight LIMIT 2 FOR UPDATE SKIP LOCKED; | |
返回1、2 | SELECT * FROM t_flight LIMIT 2 FOR UPDATE SKIP LOCKED; |
返回3、4 |
FOR UPDATE会影响外键
先创建两个测试表:
CREATE TABLE t_currency (
id int,
name text,
PRIMARY KEY (id)
);
INSERT INTO t_currency
VALUES (1, 'EUR');
INSERT INTO t_currency
VALUES (2, 'USD');
CREATE TABLE t_account (
id int,
currency_id int REFERENCES t_currency (id) ON UPDATE CASCADE ON DELETE CASCADE,
balance numeric
);
INSERT INTO t_account
VALUES (1, 1, 100);
INSERT INTO t_account
VALUES (2, 1, 200);
事务1 | 事务 2 |
---|---|
BEGIN; | BEGIN; |
SELECT * FROM t_account FOR UPDATE; | |
UPDATE t_currency SET id = id * 10; | |
等待事务1 |
在t_account表上有SELECT FOR UPDATE
,但t_currency表上的UPDATE
却被阻塞了。这样做是有必要的,否则就会有机会破坏外键约束。
在FOR UPDATE
之外,还有FOR SHARE
、FOR NO KEY UPDATE
以及FOR KEY SHARE
:
- FOR NO KEY UPDATE:这种模式很像
FOR UPDATE
。不过,这种锁要更弱一些并且因此能和SELECT FOR SHARE
共存。 - FOR SHARE:
FOR UPDATE
是一种相当强的锁,它假设用户肯定会去更改行。FOR SHARE
与之不同,因为可以有多于一个事务在同一时刻都持有FOR SHARE
锁。 - FOR KEY SHARE:这种模式的行为类似于
FOR SHARE
,不过这种锁较弱,它将阻塞FOR UPDATE
但不会阻塞FOR NO KEY UPDATE
。