在Django中，如何实现事务的可重复读取? [英] In Django, how to achieve repeatable reads for a transaction?

问题描述

我有一个函数，可以对同一数据集执行多个查询，并且我想确保所有查询都可以看到完全相同的数据.

I have a function, that does multiple queries on the same dataset and I want to ensure all the queries would see exactly the same data.

就SQL而言，这意味着支持它的数据库的REPEATABLE READ隔离级别.我不介意如果数据库不具备更高级别的权限，甚至没有完全锁定的权限.

In terms of SQL, this means REPEATABLE READ isolation level for the databases that support it. I don't mind having higher level or even a complete lockdown if the database isn't capable.

据我所知，事实并非如此. IE.如果我在一个Python Shell中运行类似这样的代码:

As far as I see, this isn't the case. I.e. if I run something like this code in one Python shell:

with transaction.atomic():
    for t in range(0, 60):
        print("{0}: {1}".format(t, MyModel.objects.count()))
        time.sleep(1)

在另一个地方执行MyModel.objects.create(...)时，正在运行的循环看到的值立即增加.这正是我要避免的.进一步的测试表明，该行为与READ COMMITTED级别相匹配，这对于我的口味来说太松懈了.

As soon as I do MyModel.objects.create(...) in another, the value seen by the running loop increase immediately. Which is exactly what I want to avoid. Further tests shows the behavior matches READ COMMITTED level, which is too lax for my tastes.

我也想强调一点，我只想对单个功能而不是整个项目使用更严格的隔离级别.

I'd also want to stress the point, I want stricter isolation level only for a single function, not for the whole project.

实现此目标的最佳选择是什么?

What are my best options to achieve this?

在我的特定情况下，我关心的唯一数据库是PostgreSQL 9.3+，但是我还希望与SQLite3兼容，在这种情况下，即使完全锁定整个数据库也可以.但是，显然，解决方案越通用，它就越受欢迎.

In my particular case, the only database I care of is PostgreSQL 9.3+, but I also want some compatibility with SQLite3 in which case even completely locking the whole database is okay with me. Yet, obviously, the more general the solution is, the more preferred it is.

推荐答案

您是正确的，postgres中的默认事务隔离级别为READ COMMITTED. 您可以在设置中轻松更改它，以测试它是否适合您的需求: https://docs.djangoproject.com/zh-CN/1.8/ref/数据库/#隔离级别

You're right, default transaction isolation level in postgres is READ COMMITTED. You can easily change it in settings to test whether it would fit your needs: https://docs.djangoproject.com/en/1.8/ref/databases/#isolation-level

我也怀疑您会遇到一些性能问题，因为postgres在处理事务时会非常高效地运行.即使在SERIALIZABLE模式下. mysql也具有REPEATABLE READ默认的隔离级别，并且正如我们所见，它也不会损害性能.

Also I doubt you will face some performance issues because postgres operates very efficiently while working with transactions. Even in SERIALIZABLE mode. Also mysql has REPEATABLE READ default isolation level and as we see it doesn't hurt performance too.

无论如何，您都可以在需要时手动设置隔离模式，如下所示: http://initd.org/psycopg/docs/extensions.html#isolation -level-constants

Anyway you can set isolation mode manually whenever you need like this: http://initd.org/psycopg/docs/extensions.html#isolation-level-constants

要设置自定义事务隔离级别，您可以尝试以下方式:

To set custom transaction isolation level you can try smth like:

from django.db import connection

with transaction.atomic():
    cursor = connection.cursor()
    cursor.execute('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ')
    # logic

我也建议您先更改设置中的默认模式(如果可以的话). 然后，如果适合您的需要，您可以将其删除并在特殊位置修改代码.

Also I would suggest you to change default mode in settings first (if you can). Then if will fit your needs you can remove it and modify code in special places.

这篇关于在Django中，如何实现事务的可重复读取?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！