Django删除的行存在吗？

Question

我面临一个非常奇怪的问题，即使我删除了一些行，我也可以在同一个事务中再次取回它们。我在apache和mod_wsgi下运行这个，数据库是mysql。

编辑:
我创建了一个示例应用程序来测试它，这样我就可以确定我的代码都不是罪魁祸首。

我用以下代码创建了一个 testapp

模型.py

import uuid
from django.db import models

class TestTable(models.Model):
    id = models.CharField(max_length=36, primary_key=True)
    name = models.CharField(max_length=50)

    @classmethod
    def get_row(cls, name):
        return TestTable(id=str(uuid.uuid4()), name=name)

    def __unicode__(self):
        return u"%s[%s]"%(self.name, self.id)

View .py

import traceback
import time
from django.db import transaction
from django.http import HttpResponse

from testapp.models import TestTable

@transaction.commit_manually
def test_view(request):
    time.sleep(1)
    out = []
    try:
        # delete 3 rows
        for row in TestTable.objects.all()[:3]:
            ID=row.id
            out.append("deleting %s"%row)
            row.delete()
            # check fi really deleted
            try:
                TestTable.objects.get(id=ID)
                out.append("row not deleted?")
            except TestTable.DoesNotExist,e:
                out.append("row deleted.")

        # create 5 rows
        for i in range(5):
            row = TestTable.get_row("row %s"%i)
            row.save()

    except Exception,e:
        out.append("Error:%s"%traceback.format_exc())
        transaction.rollback()
    else:
        transaction.commit()

    return HttpResponse('\n'.join(out), 'text/text')

urls.py

from django.conf.urls.defaults import *

urlpatterns = patterns('testapp.views', (r'^test_bug$', 'test_view')

测试脚本

import urllib2
from multiprocessing import Process

def get_data():
    r = urllib2.urlopen("http://localhost:81/timeapp/test/test_bug")
    print "---------"
    print r.read()

if __name__ == "__main__":
    for i in range(2):
        p = Process(target=get_data)
        p.start()

输出:

$ python test.py 
---------
deleting row 1[3ad3a82e-830f-4540-8148-88479175ed5e]
row deleted.
deleting row 0[544462d1-8588-4a8c-a809-16a060054479]
row deleted.
deleting row 3[55d422f3-6c39-4c26-943a-1b4db498bf25]
row deleted.
---------
deleting row 1[3ad3a82e-830f-4540-8148-88479175ed5e]
row not deleted?
deleting row 0[544462d1-8588-4a8c-a809-16a060054479]
row not deleted?
deleting row 3[55d422f3-6c39-4c26-943a-1b4db498bf25]
row not deleted?

所以我的问题是如何通过 TestTable.objects.get 再次检索已删除的行，即使我在第二次调用中睡得更多，以便第一次调用可以提交代码，我仍然会在第二次调用中得到删除的行。

Answer 1

你的问题让我着迷，所以我花了很多时间调查它，我能得出的唯一结论是它是 python-MySQL 或 MySQL 本身的一个真正的错误。这是我尝试过的:

我应该注意，我稍微更改了代码，而不是:

try:
    TestTable.objects.get(id=ID)
    out.append("row not deleted?")
except TestTable.DoesNotExist,e:
    out.append("row deleted.")

我有:

c = TestTable.objects.filter(id=ID).count()
if c:
    out.append("row not deleted?")
else:
    out.append("row deleted.")

这使得调试稍微容易一些，并且不影响问题的表现。

首先，Django 的缓存不应该归咎于此。从 MySQL 日志中可以看出，发出了获取计数的查询(1 和 2 表示建立的两个单独的并发连接):

1 Query SET NAMES utf8
2 Query SET NAMES utf8
2 Query set autocommit=0
1 Query set autocommit=0
1 Query SELECT `testapp_testtable`.`id`, `testapp_testtable`.`name` FROM `testapp_testtable` LIMIT 3
2 Query SELECT `testapp_testtable`.`id`, `testapp_testtable`.`name` FROM `testapp_testtable` LIMIT 3
2 Query DELETE FROM `testapp_testtable` WHERE `id` IN ('32f027ff-c798-410b-8621-c2d47e2cfa7c')
1 Query DELETE FROM `testapp_testtable` WHERE `id` IN ('32f027ff-c798-410b-8621-c2d47e2cfa7c')
2 Query SELECT COUNT(*) FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '32f027ff-c798-410b-8621-c2d47e2cfa7c'
2 Query DELETE FROM `testapp_testtable` WHERE `id` IN ('3f693297-9993-4162-98c4-a9ca68232c75')
2 Query SELECT COUNT(*) FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '3f693297-9993-4162-98c4-a9ca68232c75'
2 Query DELETE FROM `testapp_testtable` WHERE `id` IN ('96f9a1f7-c818-4528-858f-4e85a93de5c3')
2 Query SELECT COUNT(*) FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '96f9a1f7-c818-4528-858f-4e85a93de5c3'
2 Query SELECT (1) AS `a` FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '035c90ba-82a6-4bdc-afe1-318382563017'  LIMIT 1
2 Query INSERT INTO `testapp_testtable` (`id`, `name`) VALUES ('035c90ba-82a6-4bdc-afe1-318382563017', 'row 0')
2 Query SELECT (1) AS `a` FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '15393978-4200-4b98-98e6-73636c39dd1c'  LIMIT 1
2 Query INSERT INTO `testapp_testtable` (`id`, `name`) VALUES ('15393978-4200-4b98-98e6-73636c39dd1c', 'row 1')
2 Query SELECT (1) AS `a` FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '22459ba2-18d5-4175-ac6b-2377ba63ecc7'  LIMIT 1
2 Query INSERT INTO `testapp_testtable` (`id`, `name`) VALUES ('22459ba2-18d5-4175-ac6b-2377ba63ecc7', 'row 2')
2 Query commit
2 Quit  
1 Query SELECT COUNT(*) FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '32f027ff-c798-410b-8621-c2d47e2cfa7c'
1 Query DELETE FROM `testapp_testtable` WHERE `id` IN ('3f693297-9993-4162-98c4-a9ca68232c75')
1 Query SELECT COUNT(*) FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '3f693297-9993-4162-98c4-a9ca68232c75'
1 Query DELETE FROM `testapp_testtable` WHERE `id` IN ('96f9a1f7-c818-4528-858f-4e85a93de5c3')
1 Query SELECT COUNT(*) FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '96f9a1f7-c818-4528-858f-4e85a93de5c3'
1 Query SELECT (1) AS `a` FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '6dc6e901-bebe-4f3b-98d1-c8c4a90d06df'  LIMIT 1
1 Query INSERT INTO `testapp_testtable` (`id`, `name`) VALUES ('6dc6e901-bebe-4f3b-98d1-c8c4a90d06df', 'row 0')
1 Query SELECT (1) AS `a` FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = 'c335ccad-31c6-4ddd-bccd-578435cd6e7b'  LIMIT 1
1 Query INSERT INTO `testapp_testtable` (`id`, `name`) VALUES ('c335ccad-31c6-4ddd-bccd-578435cd6e7b', 'row 1')
1 Query SELECT (1) AS `a` FROM `testapp_testtable` WHERE `testapp_testtable`.`id` = '2c507629-a87e-48ec-b80d-2f758cd16c44'  LIMIT 1
1 Query INSERT INTO `testapp_testtable` (`id`, `name`) VALUES ('2c507629-a87e-48ec-b80d-2f758cd16c44', 'row 2')
1 Query commit
1 Quit  

当然，在 session 关闭后获取计数的任何后续尝试都表明该行实际上已被删除。此外，在 django.db.models.sql.query 中记录接收到的 SQL 结果显示 SELECT COUNT紧跟在 DELETE 之后的声明上述日志后半部分的语句实际上返回了 1，而不是预期的 0。我对此没有任何解释。

据我所知，只有两个选项可以获得您想要的功能。我已经验证它们都有效:

在你的 Apache conf 中，设置 MaxClients和 ThreadsPerChild到 1(不是一个非常实用的选择)。

使用 PostgreSQL(我会向任何使用 MySQL 的人推荐这个)。