基于postgresql行级锁for update测试

输出结果：

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

begin;
select * from db_user where name= lisi

输出结果：

二、加锁演示（for update）

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

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

输出结果：

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

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

输出结果：

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

3、第一个窗口执行：

commit;

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

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

三、加锁演示（for update nowait）

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

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

输出结果：

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

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

输出结果：

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

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字段

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 基于postgresql行级锁for update测试