• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    基于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';
    

    输出结果:

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

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

    您可能感兴趣的文章:
    • PostgreSQL 实现登录及修改密码操作
    • postgresql表死锁问题的排查方式
    • Postgresql - 查看锁表信息的实现
    • 基于postgresql数据库锁表问题的解决
    • Postgresql锁机制详解(表锁和行锁)
    • postgresql查询锁表以及解除锁表操作
    • PostgreSQL中关闭死锁进程的方法
    • PostgreSQL用户登录失败自动锁定的处理方案
    上一篇:查看postgresql数据库用户系统权限、对象权限的方法
    下一篇:PostgreSQL时间线(timeline)和History File的用法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    基于postgresql行级锁for update测试 基于,postgresql,行级,锁,for,