PostgreSQL select for update指定列(兼容oracle)
程序员文章站
2022-09-14 13:51:26
我们可以使用select for update语句来指定锁住某一张表,在oracle中我们可以在for update语句后指定某一列,用来单独锁定指定列的数据。oracle例子:建表:SQL> create table t1(id int, c2 varchar(20), c3 int, c4 float, c5 float); Table created.SQL> create table t2(id int, c6 int); Table created.SQL&g...
我们可以使用select for update语句来指定锁住某一张表,在oracle中我们可以在for update语句后指定某一列,用来单独锁定指定列的数据。
oracle例子:
建表:
SQL> create table t1(id int, c2 varchar(20), c3 int, c4 float, c5 float);
Table created.
SQL> create table t2(id int, c6 int);
Table created.
SQL> insert into t1 values (1, 'SA_REP', 1, 100, 1);
1 row created.
SQL> insert into t1 values (1, 'SA_REP123', 1, 100, 1);
1 row created.
SQL> insert into t2 values (1, 2500);
1 row created.
查询:
我们使用下列查询用来只锁住c4列。
SQL> SELECT e.c3, e.c4, e.c5
2 FROM t1 e JOIN t2 d
USING (id)
WHERE c2 = 'SA_REP'
AND c6 = 2500
3 4 5 6 FOR UPDATE OF e.c4
7 ORDER BY e.c3;
C3 C4 C5
---------- ---------- ----------
1 100 1
PostgreSQL兼容方法:
建表:
create table t1(id int, c2 text, c3 int, c4 float, c5 float);
create table t2(id int, c6 int);
insert into t1 values (1, 'SA_REP', 1, 100, 1);
insert into t1 values (1, 'SA_REP123', 1, 100, 1);
insert into t2 values (1, 2500);
pg中使用方法和oracle类似,只是需要将order by语法放到前面,并且将列名换成表名。
bill=# SELECT e.c3, e.c4, e.c5
bill-# FROM t1 e JOIN t2 d
bill-# USING (id)
bill-# WHERE c2 = 'SA_REP'
bill-# AND c6 = 2500
bill-# ORDER BY e.c3
bill-# FOR UPDATE OF e ;
c3 | c4 | c5
----+-----+----
1 | 100 | 1
(1 row)
验证:
我们可以验证下pg中是否只锁定了指定的行。
1、安装pgrowlocks插件
bill=# create extension pgrowlocks;
CREATE EXTENSION
2、观察
t1表被锁:
bill=# select * from pgrowlocks('t1');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+--------+----------------+--------
(0,1) | 1037 | f | {1037} | {"For Update"} | {2022}
(1 row)
t2表没有被锁:
bill=# select * from pgrowlocks('t2');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+------+-------+------
(0 rows)
我们还可以再看看t1表中具体被锁住的数据:
bill=# SELECT * FROM t1 AS a, pgrowlocks('t1') AS p
bill-# WHERE p.locked_row = a.ctid;
id | c2 | c3 | c4 | c5 | locked_row | locker | multi | xids | modes | pids
----+--------+----+-----+----+------------+--------+-------+--------+----------------+--------
1 | SA_REP | 1 | 100 | 1 | (0,1) | 1037 | f | {1037} | {"For Update"} | {2022}
(1 row)
除此之外,pg中for update子句还有其它的选项:
- UPDATE – 当前事务可以改所有字段
- NO KEY UPDATE – 当前事务可以改除referenced KEY以外的字段
- SHARE – 其他事务不能改所有字段
- KEY SHARE – 其他事务不能改referenced KEY字段
参考资料:
https://www.postgresql.org/docs/12/static/pgrowlocks.html
本文地址:https://blog.csdn.net/weixin_39540651/article/details/107165844