基于postgresql行级锁for update测试_PostgreSQL_脚本之家 (jb51.net)

postgresql行级锁for update测试_朔语的博客-CSDN博客_postgresql 行锁

基于postgresql行级锁for update测试

 更新时间：2020年12月30日 15:54:12   作者：朔语  

这篇文章主要介绍了基于postgresql行级锁for update测试，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

创建表：

1

2

3

4

5

6

7

8

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编辑器窗口，执行：

1

2

begin;

select * from db_user where name='lisi';

输出结果：

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

1

2

begin;

select * from db_user where name='lisi';

输出结果：

二、加锁演示（for update）

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

1

2

begin;

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

输出结果：

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

1

2

begin;

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

输出结果：

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

3、第一个窗口执行：

commit;

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

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

三、加锁演示（for update nowait）

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

1

2

begin;

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

输出结果：

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

1

2

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;--设置超时时间

注意：

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

1

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

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

1

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例子：

建表：

1

2

3

4

5

6

7

8

9

10

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列。

1

2

3

4

5

6

7

8

9

10

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兼容方法：

建表：

1

2

3

4

5

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语法放到前面，并且将列名换成表名。

1

2

3

4

5

6

7

8

9

10

11

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插件

1

2

bill=# create extension pgrowlocks;

CREATE EXTENSION

2、观察

t1表被锁：

1

2

3

4

5

bill=# select * from pgrowlocks('t1');

 locked_row | locker | multi | xids |  modes  | pids

------------+--------+-------+--------+----------------+--------

 (0,1)  | 1037 | f  | {1037} | {"For Update"} | {2022}

(1 row)

t2表没有被锁：

1

2

3

4

bill=# select * from pgrowlocks('t2');

 locked_row | locker | multi | xids | modes | pids

------------+--------+-------+------+-------+------

(0 rows)

我们还可以再看看t1表中具体被锁住的数据：

1

2

3

4

5

6

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 # 数据库 # database
Logo
技术共进，成长同行——讯飞AI开发者社区

技术共进，成长同行——讯飞AI开发者社区

更多推荐

cover

联发科 Dimensity 9500 芯片组深度分析报告 ——AI、能效与游戏性能的全面升级

avatar 讯飞AI开发者社区

读人形机器人21全球经济格局

1.7.1. 随着机器人接管常规任务，社会对技能的需求转向与自动化互补的技能，包括编程、维护、人工智能开发以及需要创造力、情感智能和复杂人际互动的工作。4.7.1. 为机器人技术和AI制定明确的规章制度，能够确保安全、增进信任，并促进其应用，包括产品安全、数据保护、有道德和负责任地使用AI等方面的标准。初创公司和老牌公司都在投资开发医疗机器人，以提升医疗服务的可及性和质量。2.1.2. 随着对机器

avatar 讯飞AI开发者社区
cover

零基础人工智能入门学习笔记（33）

avatar 讯飞AI开发者社区