在PostgreSQL 9.2.1中使用可序列化隔离进行谓词锁定

发布时间：2020-12-13 16:27:43 所属栏目：百科来源：网络整理

导读：我已经在 other of my questions中彻底阅读了 the postgres documentation on transaction isolation,但是我还没有设法理解“谓词锁定”的东西. 我希望有人能启发我:-) 根据文档：PostgreSQL中的谓词锁,像大多数其他数据库系统一样,都是基于事务实际访问的数

我已经在 other of my questions中彻底阅读了 the postgres documentation on transaction isolation,但是我还没有设法理解“谓词锁定”的东西.

我希望有人能启发我:-)

根据文档：PostgreSQL中的谓词锁,像大多数其他数据库系统一样,都是基于事务实际访问的数据

听起来不错,那为什么会发生以下情况呢？

CREATE TABLE mycustomer(cid integer PRIMARY KEY,licenses integer);
CREATE TABLE mydevice(id integer PRIMARY KEY,cid integer REFERENCES 
mycustomer (cid),status varchar(10));

INSERT INTO mycustomer(cid,licenses) VALUES (1,5);
INSERT INTO mycustomer(cid,licenses) VALUES (2,5);

    Request 1                            Request2
BEGIN TRANSACTION ISOLATION 
LEVEL SERIALIZABLE;
                                         BEGIN TRANSACTION ISOLATION 
                                         LEVEL SERIALIZABLE;
SELECT * from mydevice where cid = 1;

                                         SELECT * from mydevice where cid = 2;
INSERT INTO mydevice(id,cid,status) 
VALUES (1,1,'ok');

                                         INSERT INTO mydevice(id,status)         
                                         VALUES (2,2,'ok');
commit;
(=ok)                                 
                                         commit;
                                         (=rollback)

我明白请求1和请求2的插入不会与以前的读取冲突,因此不应该发生任何错误.为什么我得到一个“错误：由于事务之间的读/写依赖关系无法序列化访问”.

你可以想象,我不能有上述的行为发生,因为每个并发请求将被滚动支持,无论其细节.在我的业务场景中,我希望并发请求仅在同一个客户插入数据(根据示例设备)时才支持.

这些操作是从Java应用程序执行的.原则上我正在考虑创建一个锁定表来满足我的需要.有任何想法吗？

非常感谢！

从 Transaction Isolation页：

The particular locks acquired during execution of a query will depend on the plan used by the query,and multiple finer-grained locks (e.g.,tuple locks) may be combined into fewer coarser-grained locks (e.g.,page locks) during the course of the transaction to prevent exhaustion of the memory used to track the locks.

…

A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.

该SELECT的EXPLAIN可以为您提供查询计划正在执行的操作,但是如果表格很小(或空的),PostgreSQL几乎肯定会选择顺序扫描,而不是引用索引.这将导致整个表上的谓词锁定,只要其他事务对表执行任何操作就会导致序列化失败.

在我的系统上

isolation=# EXPLAIN SELECT * from mydevice where cid = 1;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on mydevice  (cost=0.00..23.38 rows=5 width=46)
   Filter: (cid = 1)
(2 rows)

您可以尝试添加索引并强制使用它：

isolation=# CREATE INDEX mydevice_cid_key ON mydevice (cid);
CREATE INDEX
isolation=# SET enable_seqscan = off;
SET
isolation=# EXPLAIN SELECT * from mydevice where cid = 1;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Index Scan using mydevice_cid_key on mydevice  (cost=0.00..8.27 rows=1 width=46)
   Index Cond: (cid = 1)
(2 rows)

但是,这不是正确的解决方案.我们再来一点点

可串行化是为了保证事务具有完全相同的效果,就像它们是一个接一个地运行一样,尽管事实上你实际上同时运行这些事务. PostgreSQL没有无限的资源,所以尽管它将谓词锁放在查询实际访问的数据上,但是“数据”可能意味着超过“行返回”.

PostgreSQL当它认为可能有问题时,选择标记序列化失败,而不是确定. (因此它如何将行锁定义为页锁.)此设计选项会导致误报,例如您的示例中的错误.假阳性不太理想,但它并不影响隔离语义的正确性.

错误信息是：

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot,during commit attempt.
HINT:  The transaction might succeed if retried.

这个提示是关键.您的应用程序需要捕获序列化失败并重试整个操作.只要SERIALIZABLE正在播放,这是真的 – 尽管并发性,它保证了串行的正确性,但是如果没有您的应用程序的帮助,则无法做到这一点.换句话说,如果您实际上正在进行并发修改,PostgreSQL可以满足隔离要求的唯一方法就是要求您的应用程序自动序列化.从而：

It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with a SQLSTATE value of ‘40001’),because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!