欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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