• 售前

  • 售后

热门帖子
入门百科

基于postgresql行级锁for update测试

[复制链接]
蓝毛凤凰都bv 显示全部楼层 发表于 2021-10-26 13:39:28 |阅读模式 打印 上一主题 下一主题
创建表:
  1. CREATE TABLE db_user
  2. (
  3. id character varying(50) NOT NULL,
  4. age integer,
  5. name character varying(100),
  6. roleid character varying,
  7. CONSTRAINT db_user_pkey PRIMARY KEY (id)
  8. )
复制代码
任意插入几条数据即可。
一、不加锁演示

1、打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,实行:
  1. begin;
  2. select * from db_user where name='lisi';
复制代码
输出结果:

2、再打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,实行:
  1. begin;
  2. select * from db_user where name='lisi';
复制代码
输出结果:

二、加锁演示(for update)

1、打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,实行:
  1. begin;
  2. select * from db_user where name='lisi' for update;
复制代码
输出结果:

2、再打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,实行:
  1. begin;
  2. select * from db_user where name='lisi' for update;
复制代码
输出结果:

查询不停处于实行中状态。
3、第一个窗口实行:
  1. commit;
复制代码
第二个窗口立即实行查询操纵,结果如下:

第二个窗口记得提交commit;。
三、加锁演示(for update nowait)

1、打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,实行:
  1. begin;
  2. select * from db_user where name='lisi' for update nowait;
复制代码
输出结果:

2、再打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,实行:
  1. begin;
  2. select * from db_user where name='lisi' for update nowait;
复制代码
输出结果:

不会进行资源等待,返回错误信息。
3、第一个窗口实行:
  1. 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. SQL> create table t1(id int, c2 varchar(20), c3 int, c4 float, c5 float);
  2. Table created.
  3. SQL> create table t2(id int, c6 int);
  4. Table created.
  5. SQL> insert into t1 values (1, 'SA_REP', 1, 100, 1);
  6. 1 row created.
  7. SQL> insert into t1 values (1, 'SA_REP123', 1, 100, 1);
  8. 1 row created.
  9. SQL> insert into t2 values (1, 2500);
  10. 1 row created.
复制代码
查询:
我们利用下列查询用来只锁住c4列。
  1. SQL> SELECT e.c3, e.c4, e.c5
  2. 2  FROM t1 e JOIN t2 d
  3. USING (id)
  4. WHERE c2 = 'SA_REP'
  5. AND c6 = 2500
  6. 3 4 5 6  FOR UPDATE OF e.c4
  7. 7  ORDER BY e.c3;
  8.   C3   C4   C5
  9. ---------- ---------- ----------
  10.    1  100   1
复制代码
PostgreSQL兼容方法:

建表:
  1. create table t1(id int, c2 text, c3 int, c4 float, c5 float);
  2. create table t2(id int, c6 int);
  3. insert into t1 values (1, 'SA_REP', 1, 100, 1);
  4. insert into t1 values (1, 'SA_REP123', 1, 100, 1);
  5. insert into t2 values (1, 2500);
复制代码
pg中利用方法和oracle雷同,只是必要将order by语法放到前面,而且将列名换成表名。
  1. bill=# SELECT e.c3, e.c4, e.c5
  2. bill-# FROM t1 e JOIN t2 d
  3. bill-# USING (id)
  4. bill-# WHERE c2 = 'SA_REP'
  5. bill-# AND c6 = 2500
  6. bill-# ORDER BY e.c3
  7. bill-# FOR UPDATE OF e ;
  8. c3 | c4 | c5
  9. ----+-----+----
  10. 1 | 100 | 1
  11. (1 row)
复制代码
验证:
我们可以验证下pg中是否只锁定了指定的行。
1、安装pgrowlocks插件
  1. bill=# create extension pgrowlocks;
  2. CREATE EXTENSION
复制代码
2、观察
t1表被锁:
  1. bill=# select * from pgrowlocks('t1');
  2. locked_row | locker | multi | xids |  modes  | pids
  3. ------------+--------+-------+--------+----------------+--------
  4. (0,1)  | 1037 | f  | {1037} | {"For Update"} | {2022}
  5. (1 row)
复制代码
t2表没有被锁:
  1. bill=# select * from pgrowlocks('t2');
  2. locked_row | locker | multi | xids | modes | pids
  3. ------------+--------+-------+------+-------+------
  4. (0 rows)
复制代码
我们还可以再看看t1表中具体被锁住的数据:
  1. bill=# SELECT * FROM t1 AS a, pgrowlocks('t1') AS p
  2. bill-# WHERE p.locked_row = a.ctid;
  3. id | c2 | c3 | c4 | c5 | locked_row | locker | multi | xids |  modes  | pids
  4. ----+--------+----+-----+----+------------+--------+-------+--------+----------------+--------
  5. 1 | SA_REP | 1 | 100 | 1 | (0,1)  | 1037 | f  | {1037} | {"For Update"} | {2022}
  6. (1 row)
复制代码
除此之外,pg中for update子句另有其它的选项:
UPDATE – 当前事务可以改全部字段
NO KEY UPDATE – 当前事务可以改除referenced KEY以外的字段
SHARE – 其他事务不能改全部字段
KEY SHARE – 其他事务不能改referenced KEY字段
以上为个人经验,盼望能给各人一个参考,也盼望各人多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作