在 PostgreSQL 上为 Django 配置 REPEATABLE READ 或 SERIALIZED 事务隔离
我有一个基于 Django 的 Web 应用程序，它通过 psycopg2 使用 PostgreSQL 作为后端。目前我们使用的是 postgres 8.4.x，但将来会迁移到 9.1.x。我们正在使用 Django (1.3.1) 的当前稳定版本。
我想启用更严格的事务隔离（根据 postgres 文档）可重复读取或可串行化级别。从 psycopg2 源代码来看，可用的有：
"""Isolation level values."""
ISOLATION_LEVEL_AUTOCOMMIT = 0
ISOLATION_LEVEL_READ_UNCOMMITTED = 1
ISOLATION_LEVEL_READ_COMMITTED = 2
ISOLATION_LEVEL_REPEATABLE_READ = 3
ISOLATION_LEVEL_SERIALIZABLE = 4
不幸的是，Django 1.3.1 的 psycopg2 后端实现 _set_isolation_level() 包括 assert level in (0, 1) 这似乎排除了使用此方法将隔离级别设置为我现在想要的那么高。
在对 Django 主干的最近提交中，很高兴看到此限制已放宽以允许级别然而，最多 4 个，似乎仍然没有通过正常 Django 设置的选项来实际向后端表明您想要比 ISOLATION_LEVEL_READ_COMMITTED 更高的隔离级别。换句话说，虽然 _set_isolation_level() 现在将接受高达 4 的级别，但没有基于配置的方法来实际导致以 4 作为参数来调用它。
问题：
假设我愿意开始使用 Django 的开发版本来获得此更改，那么手动调用
_set_isolation_level()的正确/推荐方法是什么，因为没有配置选项可以导致以我想要的隔离级别调用它？假设我不愿意使用 Django 的 devel 版本，那么使用 Django 1.3.1 将所需的隔离级别传递到 psycopg2 的推荐方法是什么？
当前和未来版本的 Django 是否都使得指定这些更高的隔离级别变得困难，因为它们在实践中会导致巨大的问题？ （即我应该这样做吗？）请记住，我们的应用程序的数据库吞吐量相对较低，事务量大且不频繁，并且对一致性的要求极高。
预先感谢您的任何建议。
I have a Django-based webapp that is using PostgreSQL as the backend via psycopg2. Right now we're on postgres 8.4.x but will move to 9.1.x in the future. We're using the current stable release of Django (1.3.1).
I want to enable stricter transaction isolation (as per the postgres docs) at either the REPEATABLE READ or SERIALIZABLE levels. From the psycopg2 source, the ones available are:
"""Isolation level values."""
ISOLATION_LEVEL_AUTOCOMMIT = 0
ISOLATION_LEVEL_READ_UNCOMMITTED = 1
ISOLATION_LEVEL_READ_COMMITTED = 2
ISOLATION_LEVEL_REPEATABLE_READ = 3
ISOLATION_LEVEL_SERIALIZABLE = 4
Unfortunately, Django 1.3.1's psycopg2 backend implementation of _set_isolation_level() includes assert level in (0, 1) which seems to preclude using this method to set the isolation level as high as I want right now.
In a recent commit to Django's trunk, it's nice to see that this restriction has been relaxed to allow levels up to 4, however, there still does not appear to be an option via the normal Django settings to actually indicate to the backend that you want a higher isolation level than ISOLATION_LEVEL_READ_COMMITTED. In other words, whilst _set_isolation_level() will now accept a level up to 4, there is no config-based way to actually cause it to be called with 4 as the argument.
Questions:
Assuming I was willing to start using Django's devel version to get this change, what would be the correct / recommended way to call
_set_isolation_level()manually given that there is no config option that will result in it being called with the isolation level I want?Assuming I'm not willing to use Django's devel version, what is the recommended way to pass the desired isolation level through to psycopg2 using Django 1.3.1?
Do both current and future versions of Django make it hard to specify these higher isolation levels because they cause huge problems in practice? (i.e. should I be doing this at all?) Keep in mind, our app has relatively low DB throughput with large, infrequent transactions with an extremely high desire for consistency.
Thanks in advance for any suggestions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最简单且最兼容的方法是编写您自己的数据库适配器，该适配器继承自 django 的默认值并覆盖方法，以便您设置自己的隔离级别。
The easiest and most compatible way of doing this would be to write your own DB adapter that inherits from django's default and overrides the methods so that you set your own isolation level.
这看起来很奇怪。 AUTOCOMMIT 不是事务隔离级别。 READ COMMITTED 是 PostgreSQL 的默认设置，您将通过 1 或 2 获得 READ COMMITTED 行为。
我认为您正在寻找 set_session([isolation_level,] [只读,] [可延迟,] [自动提交])。
据我所知，这些问题更多地与 MySQL 和向后兼容性有关，而不是与 PostgreSQL 有关。 MySQL默认为REPEATABLE READ；一些 Django 开发人员认为 READ COMMITTED 行为没有得到应有的彻底测试。
https://code.djangoproject.com/ticket/13906
That seems odd. AUTOCOMMIT isn't a transaction isolation level. READ COMMITTED is PostgreSQL's default, and you'd get READ COMMITTED behavior with either 1 or 2.
I think you're looking for set_session([isolation_level,] [readonly,] [deferrable,] [autocommit]).
As far as I can tell, the issues have more to do with MySQL and backward compatibility than with PostgreSQL. MySQL defaults to REPEATABLE READ; some Django developers think that READ COMMITTED behavior is not as thoroughly tested as it should be.
https://code.djangoproject.com/ticket/13906