开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL Oracle 兼容性之 - select for update of column_name (change to table_name or table_alias)

简介:
+关注继续查看

标签

PostgreSQL , Oracle , select for update of column


背景

当一个SELECT中包含多张表时(比如JOIN),FOR UPDATE可以指定要LOCK哪张表的匹配到的ROWS。

例如

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702

The following statement locks only those rows in the employees table with purchasing clerks located in Oxford. No rows are locked in the departments table:

SELECT e.employee_id, e.salary, e.commission_pct  
   FROM employees e JOIN departments d  
   USING (department_id)  
   WHERE job_id = 'SA_REP'  
   AND location_id = 2500  
   FOR UPDATE OF e.salary  
   ORDER BY e.employee_id;  

PostgreSQL 对应语法

create table employees(department_id int, job_id text, employee_id int, salary float, commission_pct float);  
create table departments(department_id int, location_id int);  
  
insert into employees values (1, 'SA_REP', 1, 100, 1);  
insert into employees values (1, 'SA_REP123', 1, 100, 1);  
insert into departments values (1, 2500);  

语法如下,只是将列名改成表名或ALIAS,另外,order by需要放到前面。

postgres=# explain SELECT e.employee_id, e.salary, e.commission_pct  
   FROM employees e JOIN departments d  
   USING (department_id)  
   WHERE job_id = 'SA_REP'  
   AND location_id = 2500  
   ORDER BY e.employee_id   
   FOR UPDATE OF e;  -- order by 放前面  
  
                                      QUERY PLAN                                        
--------------------------------------------------------------------------------------  
 LockRows  (cost=182.79..182.84 rows=4 width=32)  
   ->  Sort  (cost=182.79..182.80 rows=4 width=32)  
         Sort Key: e.employee_id  
         ->  Hash Join  (cost=58.86..182.75 rows=4 width=32)  
               Hash Cond: (d.department_id = e.department_id)  
               ->  Seq Scan on departments d  (cost=0.00..123.62 rows=45 width=10)  
                     Filter: (location_id = 2500)  
               ->  Hash  (cost=58.62..58.62 rows=19 width=30)  
                     ->  Seq Scan on employees e  (cost=0.00..58.62 rows=19 width=30)  
                           Filter: (job_id = 'SA_REP'::text)  
(10 rows)  

我们还可以观察被LOCK的行

1、创建行锁观测插件

https://www.postgresql.org/docs/10/static/pgrowlocks.html

postgres=# create extension pgrowlocks ;  
CREATE EXTENSION  

2、在事务中执行以上LOCK的SQL,在其他会话中观测被锁的行

可以看到只有employees被LOCK

postgres=# select * from pgrowlocks('employees');  
 locked_row | locker  | multi |   xids    |     modes      |  pids     
------------+---------+-------+-----------+----------------+---------  
 (0,1)      | 1001814 | f     | {1001814} | {"For Update"} | {26460}  
(1 row)  

departments没有被锁

postgres=# select * from pgrowlocks('departments');  
 locked_row | locker | multi | xids | modes | pids   
------------+--------+-------+------+-------+------  
(0 rows)  

使用关联查询,可以看到被锁的行的记录

postgres=# SELECT * FROM employees AS a, pgrowlocks('employees') AS p  
  WHERE p.locked_row = a.ctid;  
 department_id | job_id | employee_id | salary | commission_pct | locked_row | locker  | multi |   xids    |     modes      |  pids     
---------------+--------+-------------+--------+----------------+------------+---------+-------+-----------+----------------+---------  
             1 | SA_REP |           1 |    100 |              1 | (0,1)      | 1001814 | f     | {1001814} | {"For Update"} | {26460}  
(1 row)  

3、如果不使用for update of ,则会锁住所有JOIN表的行。

postgres=# begin;  
BEGIN  
postgres=# SELECT e.employee_id, e.salary, e.commission_pct  
postgres-#    FROM employees e JOIN departments d  
postgres-#    USING (department_id)  
postgres-#    WHERE job_id = 'SA_REP'  
postgres-#    AND location_id = 2500  
postgres-#    ORDER BY e.employee_id   
postgres-#    FOR UPDATE  
postgres-# ;  
 employee_id | salary | commission_pct   
-------------+--------+----------------  
           1 |    100 |              1  
(1 row)  

观测

postgres=# select * from pgrowlocks('employees');  
 locked_row | locker  | multi |   xids    |     modes      |  pids     
------------+---------+-------+-----------+----------------+---------  
 (0,1)      | 1001826 | f     | {1001826} | {"For Update"} | {26460}  
(1 row)  
  
postgres=# select * from pgrowlocks('departments');  
 locked_row | locker  | multi |   xids    |     modes      |  pids     
------------+---------+-------+-----------+----------------+---------  
 (0,1)      | 1001826 | f     | {1001826} | {"For Update"} | {26460}  
(1 row)  
  

PostgreSQL for update更细致的用法

在对主外键表的主表加LOCK时,可以指定是否需要LOCK referenced的COLUMN。

FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE are locking clauses;   
  
they affect how SELECT locks rows as they are obtained from the table.  
  
The locking clause has the general form  
  
FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]  
where lock_strength can be one of  
  
UPDATE  -- 当前事务可以改所有字段  
NO KEY UPDATE  -- 当前事务可以改除referenced KEY以外的字段  
SHARE    -- 其他事务不能改所有字段  
KEY SHARE  -- 其他事务不能改referenced KEY字段  

参考

https://www.postgresql.org/docs/10/static/pgrowlocks.html

《并发事务, 共享行锁管理 - pg_multixact manager for shared-row-lock implementation》

《PostgreSQL add 2 DML LOCK TUPLE MODE to 4》

《PostgreSQL How to deal TUPLE LOCK : 2 - "one|more transactions waiting one|more transactions release tuple lock"》

《PostgreSQL How to deal TUPLE LOCK : 1 - "One transaction lock single or multiple tuples | rows"》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
OSS重磅推出OSS Select——使用SQL选取文件的内容
OSS重磅推出OSS Select功能,可以直接使用简单的SQL语句,从OSS的文件中选取所需要的内容
26396 0
Error: Cannot find a valid baseurl for repo: base
解决方法如下(修改dns配置) vi /etc/resolv.conf 在此文件最后加入:nameserver 8.8.8.8 如果没有vi编辑器可用: echo "nameserver 8.8.8.8" >>/etc/resolv.conf
774 0
MYSQL: Cannot delete or update a parent row: a foreign key constraint fails
这可能是MySQL在InnoDB中设置了foreign key关联,造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。 SET FOREIGN_KEY_CHECKS = 0; 删除完成后设置 SET FOREIGN_KEY_CHECKS = 1; 其他: 关...
945 0
【MySQL】select for update 的Row Lock 与Table Lock
select for update 对表施加的锁模式分两种情况: 只有当where 条件中明确地使用指定主键时,MySQL 才会对表执行Row lock (只锁住被选取的数据) , 否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
586 0
API Plug-in for the S60 3rd Edition SDK for Symbian OS, for C++, MR
Package URL: Extensions plug-in package for S60 3rd Edition SDK for Symbian OS, for C++, MRCurrent version: 5.
839 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载