我希望有人能启发我:-)
根据文档：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应用程序执行的.原则上我正在考虑创建一个锁定表来满足我的需要.有任何想法吗？
非常感谢！
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.
