基于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字段
以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。
您可能感兴趣的文章: