PostgreSQL performance speedup by avoid lock references tuple when add or update(new) Foreign Key’s value

背景

PostgreSQL 将针对foreign key场景的一致性约束手段进行优化, 目前依赖对references表的对应行加RowShareLock锁保障一致性. 有几大的锁冲突弊端.

优化后将降低锁冲突, 除关联字段, 其他字段都不会产生冲突.

补丁如下 :

https://commitfest.postgresql.org/action/patch_view?id=987

foreign key在很多场景中都有应用, 例如以下场景 :

课程表

create table course (  
id int primary key,  
cname name  
);  

学生信息表

create table student (  
id int primary key,  
sname name  
);  

选修表

create table selective (  
sid int references student(id),  
cid int references course(id),  
info text  
);  

当新增1条选修记录时, 需要扫描课程表和学生信息表中是否有关联的id记录, 有则可以插入, 同时需要锁住课程表以及学生信息表的对应记录.

当更新选修表的sid或cid为一个新值是, 同样需要扫描课程表或学生信息表中是否有关联的id记录, 有则可以更新, 同时需要锁住课程表以及学生信息表的对应记录.

创建测试表 :

  CREATE TABLE A (  
        AID integer not null,  
        Col1 integer,  
        PRIMARY KEY (AID)  
  );  
  
  CREATE TABLE B (  
        BID integer not null,  
        AID integer not null,  
        Col2 integer,  
        PRIMARY KEY (BID),  
        FOREIGN KEY (AID) REFERENCES A(AID)  
  );  

插入测试数据 :

  INSERT INTO A (AID) VALUES (1),(2);  
  INSERT INTO B (BID,AID) VALUES (2,1);  

创建pgrowlocks extension, 观察行锁.

digoal=# create extension pgrowlocks;  
CREATE EXTENSION  

测试 :

模拟B表新增记录, A表的对应行将加锁.

SESSION A:

digoal=# begin;  
BEGIN  
digoal=# insert into b (aid,bid) values (1,1);  
INSERT 0 1  

SESSION B:

digoal=# select * from pgrowlocks('b');  
  
 locked_row | lock_type | locker | multi | xids | pids   
------------+-----------+--------+-------+------+------  
(0 rows)  
digoal=# select * from pgrowlocks('a');  
 locked_row | lock_type | locker | multi |  xids  |  pids    
------------+-----------+--------+-------+--------+--------  
 (0,3)      | Shared    |   1755 | f     | {1755} | {9126}  
(1 row)  
digoal=# select * from a where ctid='(0,3)';  
 aid | col1   
-----+------  
   1 |    2  
(1 row)  

此时无法修改A表对应的该行, 因为锁冲突.

digoal=# update a set col1=22 where aid=1;  
  
等待锁...  
-- 在Oracle 10G中测试并提交不需要等待. 说明Oracle已经处理了这个锁机制.  

模拟B表修改FK的内容为一个新值, A表的对应行将加锁.

SESSION A:

digoal=# begin;  
BEGIN  
digoal=# update b set aid=2 where aid<>2;  
UPDATE 3  

SESSION B:

digoal=# select * from pgrowlocks('a');  
 locked_row | lock_type | locker | multi |  xids  |  pids    
------------+-----------+--------+-------+--------+--------  
 (0,4)      | Shared    |   1757 | f     | {1757} | {9126}  
(1 row)  
digoal=# select * from a where ctid='(0,4)';  
 aid | col1   
-----+------  
   2 |    2  
(1 row)  

此时无法修改A表对应的该行, 因为锁冲突.

digoal=# update a set col1=22 where aid=2;  
等待锁...  
-- 在Oracle 10G中测试并提交不需要等待. 说明Oracle已经处理了这个锁机制.  

模拟B表修改FK的内容, 但是值不变, A表的对应行不会加锁.

SESSION A:

digoal=# begin;  
BEGIN  
digoal=# update b set aid=2 where aid=2;  
UPDATE 3  
  
SESSION B:  
digoal=# select * from pgrowlocks('a');  
 locked_row | lock_type | locker | multi | xids | pids   
------------+-----------+--------+-------+------+------  
(0 rows)  

小结

1. 目前Foreign Key的约束关系是通过对references table的相关tuple追加RowShareLock来实现的, 因此在新增B表记录或者更新B表的关联字段为新值时, A表的对应行也会被追加RowShareLock锁. 这个显然增加了A表的锁等待的几率, 降低了并行处理能力.

2. 因此对锁A表的整行改成不允许修改对应的相关字段值, 可以允许大部分A表的操作. 例如上面的例子A表修改col1的值应该是允许的。

这样可以大大提高并行处理能力.

参考

1. http://database-explorer.blogspot.hk/2013/01/reducing-contention-with-foreign-key.html

2. src/test/isolation/specs/fk-deadlock2.spec

3. src/backend/storage/lmgr/lmgr.c

4. src/include/storage/lock.h

5. http://blog.163.com/digoal@126/blog/static/163877040201210134586363/

6. http://www.postgresql.org/docs/9.2/static/explicit-locking.html

7. https://commitfest.postgresql.org/action/patch_view?id=987

digoal’s 大量PostgreSQL文章入口