温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

怎么在postgresql中使用for update对行级锁进行测试

发布时间:2020-12-30 14:07:15 来源:亿速云 阅读:1097 作者:Leah 栏目:开发技术

这期内容当中小编将会给大家带来有关怎么在postgresql中使用for update对行级锁进行测试,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。

创建表:

CREATE TABLE db_user
(
 id character varying(50) NOT NULL,
 age integer,
 name character varying(100),
 roleid character varying,
 CONSTRAINT db_user_pkey PRIMARY KEY (id)
)

随便插入几条数据即可。

一、不加锁演示

1、打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行:

begin;

select * from db_user where name='lisi';

输出结果:

怎么在postgresql中使用for update对行级锁进行测试

2、再打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行:

begin;

select * from db_user where name='lisi';

输出结果:

怎么在postgresql中使用for update对行级锁进行测试

二、加锁演示(for update)

1、打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行:

begin;

select * from db_user where name='lisi' for update;

输出结果:

怎么在postgresql中使用for update对行级锁进行测试

2、再打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行:

begin;

select * from db_user where name='lisi' for update;

输出结果:

怎么在postgresql中使用for update对行级锁进行测试

查询一直处于执行中状态。

3、第一个窗口执行:

commit;

第二个窗口立即执行查询操作,结果如下:

怎么在postgresql中使用for update对行级锁进行测试

第二个窗口记得提交commit;。

三、加锁演示(for update nowait)

1、打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行:

begin;

select * from db_user where name='lisi' for update nowait;

输出结果:

怎么在postgresql中使用for update对行级锁进行测试

2、再打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行:

begin;

select * from db_user where name='lisi' for update nowait;

输出结果:

怎么在postgresql中使用for update对行级锁进行测试

不会进行资源等待,返回错误信息。

3、第一个窗口执行:

commit;

提交成功,资源锁释放。

总结:

for update nowait和 for update 都会对所查询到得结果集进行加锁,所不同的是,如果另外一个线程正在修改结果集中的数据,for update nowait 不会进行资源等待,只要发现结果集中有些数据被加锁,立刻返回 “55P03错误,内容是无法在记录上获得锁.

命令说明:

begin;--开启事务

begin transaction;--开启事务

commit;--提交

rollback;--回滚

set lock_timeout=5000;--设置超时时间

注意:

连表查询加锁时,不支持单边连接形式,例如:

select u.*,r.* from db_user u left join db_role r on u.roleid=r.id for update;

支持以下形式,并锁住了两个表中关联的数据:

select u.*,r.* from db_user u, db_role r where u.roleid=r.id for update;

补充:PostgreSQL select for update指定列(兼容oracle)

我们可以使用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字段

上述就是小编为大家分享的怎么在postgresql中使用for update对行级锁进行测试了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI