在PostgreSQL中混合隔离级别 [英] Mixing isolation levels in PostgreSQL
问题描述
如果任何其他会话使用例如自动提交还是READ COMMITED隔离级别？
Does it matter for a SERIALIZABLE transaction if any other session uses e.g. autocommit or the READ COMMITED isolation level?
换句话说，当从多个进程/线程（或访问）访问数据库时，混合隔离级别（&自动提交）是否存在任何危险？还有其他需要注意的地方吗？
In other words is there any danger in mixing isolation levels (& autocommit) when accessing a database from multiple processes/threads (or anything else to watch out for)?
请注意，我知道普通问题，例如SERIALIZABLE事务要求重试等。我要求的是任何显而易见的事情
Note that I'm aware of the "ordinary" issues, like SERIALIZABLE transactions asking for a retry etc. I'm asking for anything non-obvious that can happen when one is mixing different isolation levels.
编辑：
来自 http://www.postgresql.org/docs/9.4/static/transaction-iso.html ：
一致使用可序列化事务可以简化开发。保证任何一组并发可序列化交易将具有与一次运行相同的效果，这意味着如果您可以证明单个书面交易将在正确的时间做正确的事情它本身运行，您可以放心，即使没有任何有关其他事务可能做的信息，它也可以在任何可序列化事务混合中做正确的事情。
Consistent use of Serializable transactions can simplify development. The guarantee that any set of concurrent serializable transactions will have the same effect as if they were run one at a time means that if you can demonstrate that a single transaction, as written, will do the right thing when run by itself, you can have confidence that it will do the right thing in any mix of serializable transactions, even without any information about what those other transactions might do.
这可能表明混合隔离级别不是一个好主意。另一方面，它仅表示持续使用SERIALIZABLE级别是好的，而不是混合隔离级别是不好的。
That could indicate that mixing isolation levels is not a good idea. On the other hand it merely says that consistent use of the SERIALIZABLE level is good, and not that mixing isolation levels is bad.
推荐答案
当前事务的所有语句只能看到在此执行第一个查询或数据修改语句之前提交的行交易。如果在并行可序列化事务中进行读写操作模式会导致这种事务的任何串行（一次）执行都不可能发生的情况，则其中之一将被滚动
All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error.
这意味着， SERIALIZABLE 事务仅起作用不同的是，当针对另一个 SERIALIZABLE 事务运行时。如果它们针对非 SERIALIZABLE 事务而运行，则它们应按 REPEATABLE READ 事务进行操作。这表明，混合使用这些事务隔离级别是完全安全的。
That means, a SERIALIZABLE transaction only act differently, when running against another SERIALIZABLE transaction(s). If they run against non-SERIALIZABLE transactions, they should act as they were REPEATABLE READ transactions. That suggests, it's completely safe to mix these transaction isolation levels.
这篇关于在PostgreSQL中混合隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
