标签
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 : 1 - "One transaction lock single or multiple tuples | rows"》
