PostgreSQL 9.2.1 中具有可序列化隔离的谓词锁定

我已经仔细阅读了关于事务隔离的 postgres 文档 http://www.postgresql.org/docs/current/static/transaction-iso.html建议在我的其他问题 https://stackoverflow.com/questions/12824217/isolation-level-serializable-in-spring-jdbc但我还没有设法理解“谓词锁定”的东西。

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

根据文档：与大多数其他数据库系统一样，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, cid, status)         
                                         VALUES (2, 2, 'ok');
commit;
(=ok)                                 
                                         commit;
                                         (=rollback)

据我了解，请求 1 和请求 2 中的插入与之前的读取不冲突，因此不应出现任何错误。为什么我收到“错误：由于事务之间的读/写依赖性而无法序列化访问”。

正如您可以想象的那样，我无法发生上述行为，因为无论其详细信息如何，每个并发请求都会回滚。在我的业务场景中，我希望并发请求仅在为同一单个客户插入数据（根据示例设备）时才回滚。

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

非常感谢！

来自事务隔离 http://www.postgresql.org/docs/current/static/transaction-iso.html page:

在查询执行期间获取的特定锁将取决于查询使用的计划，并且在执行过程中多个细粒度锁（例如元组锁）可以组合成更少的粗粒度锁（例如页锁）。事务以防止用于跟踪锁的内存耗尽。

...

• 顺序扫描始终需要关系级谓词锁。这可能会导致序列化失败率增加。

An EXPLAIN在那SELECT可以告诉您正在采取什么查询计划，但如果表很小（或为空！），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 满足隔离要求的唯一方法就是要求您的应用程序对其自身进行序列化。因此：

重要的是，使用此技术的环境具有处理序列化失败的通用方法（始终返回 SQLSTATE 值“40001”），因为很难准确预测哪些事务可能有助于读/写依赖关系并且需要回滚以防止序列化异常。