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

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

2018-06-21 1890

简介：

+关注继续查看

背景

当一个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字段