DatabaseError:当前事务中止,命令被忽略,直到事务块结束?

问题:DatabaseError:当前事务中止,命令被忽略,直到事务块结束?

消息有很多错误:

"DatabaseError: current transaction is aborted, commands ignored until end of transaction block"

从python-psycopg更改为python-psycopg2作为Django项目的数据库引擎。

代码保持不变,只是不知道这些错误来自何处。

I got a lot of errors with the message :

"DatabaseError: current transaction is aborted, commands ignored until end of transaction block"

after changed from python-psycopg to python-psycopg2 as Django project’s database engine.

The code remains the same, just don’t know where those errors are from.


回答 0

当查询产生错误并且您尝试运行另一个查询而不先回滚事务时,这就是postgres所做的。(您可能会认为这是一项安全功能,可以防止您破坏数据。)

要解决此问题,您将需要弄清楚错误查询在代码中的何处执行。在您的PostgreSQL服务器中使用log_statementlog_min_error_statement选项可能会有所帮助。

This is what postgres does when a query produces an error and you try to run another query without first rolling back the transaction. (You might think of it as a safety feature, to keep you from corrupting your data.)

To fix this, you’ll want to figure out where in the code that bad query is being executed. It might be helpful to use the log_statement and log_min_error_statement options in your postgresql server.


回答 1

要消除错误,请在修复代码后回滚上一个(错误的)事务

from django.db import transaction
transaction.rollback()

您可以使用try-except来防止发生错误:

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    transaction.rollback()

参考:Django文档

To get rid of the error, roll back the last (erroneous) transaction after you’ve fixed your code:

from django.db import transaction
transaction.rollback()

You can use try-except to prevent the error from occurring:

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    transaction.rollback()

Refer : Django documentation


回答 2

因此,我遇到了同样的问题。我在这里遇到的问题是我的数据库未正确同步。简单的问题似乎总是引起最大的忧虑。

要在终端的应用程序目录中同步django db,请输入:

$ python manage.py syncdb

编辑:请注意,如果您使用的是django-south,则运行’$ python manage.py migration’命令也可以解决此问题。

祝您编码愉快!

So, I ran into this same issue. The problem I was having here was that my database wasn’t properly synced. Simple problems always seem to cause the most angst…

To sync your django db, from within your app directory, within terminal, type:

$ python manage.py syncdb

Edit: Note that if you are using django-south, running the ‘$ python manage.py migrate’ command may also resolve this issue.

Happy coding!


回答 3

在Flask中,您只需要编写:

curs = conn.cursor()
curs.execute("ROLLBACK")
conn.commit()

PS文档在这里https://www.postgresql.org/docs/9.4/static/sql-rollback.html

In Flask you just need to write:

curs = conn.cursor()
curs.execute("ROLLBACK")
conn.commit()

P.S. Documentation goes here https://www.postgresql.org/docs/9.4/static/sql-rollback.html


回答 4

以我的经验,这些错误是通过以下方式发生的:

try:
    code_that_executes_bad_query()
    # transaction on DB is now bad
except:
    pass

# transaction on db is still bad
code_that_executes_working_query() # raises transaction error

第二个查询没有任何问题,但是由于发现了真正的错误,因此第二个查询引发了错误(信息少得多)。

编辑:仅当except子句捕获IntegrityError(或任何其他低级数据库异常)时,才会发生这种情况;如果捕获到DoesNotExist此类错误,则不会出现此错误,因为DoesNotExist这不会破坏事务。

这里的类是不要尝试/exceptions/通过。

In my experience, these errors happen this way:

try:
    code_that_executes_bad_query()
    # transaction on DB is now bad
except:
    pass

# transaction on db is still bad
code_that_executes_working_query() # raises transaction error

There nothing wrong with the second query, but since the real error was caught, the second query is the one that raises the (much less informative) error.

edit: this only happens if the except clause catches IntegrityError (or any other low level database exception), If you catch something like DoesNotExist this error will not come up, because DoesNotExist does not corrupt the transaction.

The lesson here is don’t do try/except/pass.


回答 5

我认为使用PostgreSQL时,priestc提到的模式更可能是此问题的常见原因。

但是,我认为该模式有有效的用途,我不认为这个问题应该成为始终避免它的原因。例如:

try:
    profile = user.get_profile()
except ObjectDoesNotExist:
    profile = make_default_profile_for_user(user)

do_something_with_profile(profile)

如果您确实对这种模式感到满意,但是想避免到处都是显式的事务处理代码,那么您可能希望考虑启用自动提交模式(PostgreSQL 8.2+):https ://docs.djangoproject.com/en/ dev / ref / databases /#autocommit-mode

DATABASES['default'] = {
    #.. you usual options...
    'OPTIONS': {
        'autocommit': True,
    }
}

我不确定是否有重要的性能考虑因素(或任何其他类型的考虑因素)。

I think the pattern priestc mentions is more likely to be the usual cause of this issue when using PostgreSQL.

However I feel there are valid uses for the pattern and I don’t think this issue should be a reason to always avoid it. For example:

try:
    profile = user.get_profile()
except ObjectDoesNotExist:
    profile = make_default_profile_for_user(user)

do_something_with_profile(profile)

If you do feel OK with this pattern, but want to avoid explicit transaction handling code all over the place then you might want to look into turning on autocommit mode (PostgreSQL 8.2+): https://docs.djangoproject.com/en/dev/ref/databases/#autocommit-mode

DATABASES['default'] = {
    #.. you usual options...
    'OPTIONS': {
        'autocommit': True,
    }
}

I am unsure if there are important performance considerations (or of any other type).


回答 6

如果您在交互式外壳程序中得到此消息并需要快速修复,请执行以下操作:

from django.db import connection
connection._rollback()

最初在此答案中看到

If you get this while in interactive shell and need a quick fix, do this:

from django.db import connection
connection._rollback()

originally seen in this answer


回答 7

postgres终端上运行有故障的事务时,我遇到了类似的行为。此后什么都没有发生,因为database处于的状态error。但是,如果可以避免的话,只是快速解决方法rollback transaction。以下对我有用:

COMMIT;

I encountered a similar behavior while running a malfunctioned transaction on the postgres terminal. Nothing went through after this, as the database is in a state of error. However, just as a quick fix, if you can afford to avoid rollback transaction. Following did the trick for me:

COMMIT;


回答 8

我有silimar问题。解决的办法是迁移数据库(manage.py syncdb或者manage.py schemamigration --auto <table name>如果您使用南方)。

I’ve got the silimar problem. The solution was to migrate db (manage.py syncdb or manage.py schemamigration --auto <table name> if you use south).


回答 9

只是使用回滚

范例程式码

try:
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")
except:
    cur.execute("rollback")
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")

just use rollback

Example code

try:
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")
except:
    cur.execute("rollback")
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")

回答 10

我也刚遇到这个错误,但是它掩盖了另一个更相关的错误消息,其中代码试图将125个字符的字符串存储在100个字符的列中:

DatabaseError: value too long for type character varying(100)

我必须调试代码才能显示以上消息,否则它将显示

DatabaseError: current transaction is aborted

I just had this error too but it was masking another more relevant error message where the code was trying to store a 125 characters string in a 100 characters column:

DatabaseError: value too long for type character varying(100)

I had to debug through the code for the above message to show up, otherwise it displays

DatabaseError: current transaction is aborted

回答 11

作为对@priestc和@Sebastian的回应,如果您做这样的事情怎么办?

try:
    conn.commit()
except:
    pass

cursor.execute( sql )
try: 
    return cursor.fetchall()
except: 
    conn.commit()
    return None

我只是尝试了这段代码,它似乎可以正常工作,无需关心任何可能的错误就可以静默失败,并且在查询良好时可以正常工作。

In response to @priestc and @Sebastian, what if you do something like this?

try:
    conn.commit()
except:
    pass

cursor.execute( sql )
try: 
    return cursor.fetchall()
except: 
    conn.commit()
    return None

I just tried this code and it seems to work, failing silently without having to care about any possible errors, and working when the query is good.


回答 12

我相信@AnujGupta的答案是正确的。但是,回滚本身会引发异常,您应该捕获并处理该异常:

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    try:
        transaction.rollback()
    except transaction.TransactionManagementError:
        # Log or handle otherwise

如果发现要在各个save()位置重写此代码,则可以提取方法:

import traceback
def try_rolling_back():
    try:
        transaction.rollback()
        log.warning('rolled back')  # example handling
    except transaction.TransactionManagementError:
        log.exception(traceback.format_exc())  # example handling

最后,您可以使用装饰器装饰它,该装饰器保护使用的方法save()

from functools import wraps
def try_rolling_back_on_exception(fn):
    @wraps(fn)
    def wrapped(*args, **kwargs):
        try:
            return fn(*args, **kwargs)
        except:
            traceback.print_exc()
            try_rolling_back()
    return wrapped

@try_rolling_back_on_exception
def some_saving_method():
    # ...
    model.save()
    # ...

即使您实现了上面的装饰器,try_rolling_back()在需要进行特殊处理而通用装饰器处理还不够的情况下,如果需要手动使用提取方法,将其保留为提取方法仍然很方便。

I believe @AnujGupta’s answer is correct. However the rollback can itself raise an exception which you should catch and handle:

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    try:
        transaction.rollback()
    except transaction.TransactionManagementError:
        # Log or handle otherwise

If you find you’re rewriting this code in various save() locations, you can extract-method:

import traceback
def try_rolling_back():
    try:
        transaction.rollback()
        log.warning('rolled back')  # example handling
    except transaction.TransactionManagementError:
        log.exception(traceback.format_exc())  # example handling

Finally, you can prettify it using a decorator that protects methods which use save():

from functools import wraps
def try_rolling_back_on_exception(fn):
    @wraps(fn)
    def wrapped(*args, **kwargs):
        try:
            return fn(*args, **kwargs)
        except:
            traceback.print_exc()
            try_rolling_back()
    return wrapped

@try_rolling_back_on_exception
def some_saving_method():
    # ...
    model.save()
    # ...

Even if you implement the decorator above, it’s still convenient to keep try_rolling_back() as an extracted method in case you need to use it manually for cases where specific handling is required, and the generic decorator handling isn’t enough.


回答 13

这对我来说是非常奇怪的行为。我很惊讶没有人想到保存点。在我的代码中,查询失败是预期的行为:

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
    return skipped

我已经以这种方式更改代码以使用保存点:

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    sid = transaction.savepoint()
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
            transaction.savepoint_rollback(sid)
        else:
            transaction.savepoint_commit(sid)
    return skipped

This is very strange behavior for me. I’m surprised that no one thought of savepoints. In my code failing query was expected behavior:

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
    return skipped

I have changed code this way to use savepoints:

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    sid = transaction.savepoint()
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
            transaction.savepoint_rollback(sid)
        else:
            transaction.savepoint_commit(sid)
    return skipped

回答 14

在Flask Shell中,我需要做的只是session.rollback()克服这个问题。

In Flask shell, all I needed to do was a session.rollback() to get past this.


回答 15

我已经遇到了这个问题,由于错误交易尚未正确结束,因此出现错误,我在这里找到了postgresql_transactionsTransaction Control命令

交易控制

以下命令用于控制交易

BEGIN TRANSACTION  To start a transaction.

COMMIT  To save the changes, alternatively you can use END TRANSACTION command.

ROLLBACK  To rollback the changes.

所以我使用END TRANSACTION来结束错误TRANSACTION,如下代码:

    for key_of_attribute, command in sql_command.items():
        cursor = connection.cursor()
        g_logger.info("execute command :%s" % (command))
        try:
            cursor.execute(command)
            rows = cursor.fetchall()
            g_logger.info("the command:%s result is :%s" % (command, rows))
            result_list[key_of_attribute] = rows
            g_logger.info("result_list is :%s" % (result_list))
        except Exception as e:
            cursor.execute('END TRANSACTION;')
            g_logger.info("error command :%s and error is :%s" % (command, e))
    return result_list

I have met this issue , the error comes out since the error transactions hasn’t been ended rightly, I found the postgresql_transactions of Transaction Control command here

Transaction Control

The following commands are used to control transactions

BEGIN TRANSACTION − To start a transaction.

COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

ROLLBACK − To rollback the changes.

so i use the END TRANSACTION to end the error TRANSACTION, code like this:

    for key_of_attribute, command in sql_command.items():
        cursor = connection.cursor()
        g_logger.info("execute command :%s" % (command))
        try:
            cursor.execute(command)
            rows = cursor.fetchall()
            g_logger.info("the command:%s result is :%s" % (command, rows))
            result_list[key_of_attribute] = rows
            g_logger.info("result_list is :%s" % (result_list))
        except Exception as e:
            cursor.execute('END TRANSACTION;')
            g_logger.info("error command :%s and error is :%s" % (command, e))
    return result_list

回答 16

您可以通过“ set_isolation_level(0)”禁用交易

you could disable transaction via “set_isolation_level(0)”