Does MySQLdb rollback on control-C? Maybe not.

Discussion in 'Python' started by John Nagle, Sep 3, 2010.

  1. John Nagle

    John Nagle Guest

    I would expect MySQLdb to rollback on a control-C, but it doesn't
    seem to have done so. I have a program which does a thousand or
    so INSERT operations in one transaction on an InnoDB table.

    I kill it with a control-C on Windows, and it aborts. But
    it looks like some of the INSERT operations took place. Here's
    the abort, from the middle of an INSERT operation.

    Filing index item already in database under another date:
    edgar/data/1455650/0000950123-10-062814.txt
    Traceback (most recent call last):
    ....
    File "C:\projects\sitetruth\edgar\edgarfilingindex.py", line 93, in
    dbinsert
    cursor.execute(sql, values) # do the insert
    File "C:\python26\lib\site-packages\MySQLdb\cursors.py", line 166, in
    execute
    self.errorhandler(self, exc, value)
    File "C:\python26\lib\site-packages\MySQLdb\connections.py", line 35,
    in defaulterrorhandler
    raise errorclass, errorvalue
    KeyboardInterrupt
    Terminate batch job (Y/N)? y

    Checking with the manual MySQL client, looking at the table
    before and after the run, the table did change when the program ran.
    Not good.

    The INSERT looks like this:

    INSERT INTO edgarfilingindex
    (cik, company_name, form_type,
    date_filed, file_name, date_indexed, confidence)
    VALUES (%s,%s,%s,%s,%s,%s,%s)

    SHOW CREATE TABLE returns this:

    CREATE TABLE `edgarfilingindex` (
    ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    so it's definitely an InnoDB table.

    I have print statements at all commit calls, and there don't seem to
    be any unwanted commits happening. I'm not doing anything that
    forces a commit, like RENAME or ALTER or CREATE; it's just
    simple INSERT operations. I'm not doing an explicit START
    TRANSACTION, but I shouldn't have to.

    I put a SHOW VARIABLES LIKE "autocommit" in the program, and
    it comes back with ("autocommit", "OFF"). So that's not the problem.

    I even did an explicit START TRANSACTION at the beginning of the
    update, and it doesn't help.

    Something is broken.

    Python 2.6, MySQLdb 1.2.2-py2.6, MySQL 5.1.47-com, Windows 7.

    John Nagle
     
    John Nagle, Sep 3, 2010
    #1
    1. Advertising

  2. John Nagle

    Mike Kent Guest

    On Sep 3, 12:22 am, John Nagle <> wrote:
    >     I would expect MySQLdb to rollback on a control-C, but it doesn't
    > seem to have done so.  


    > Something is broken.


    I wouldn't expect it to, I'd expect to roll back on an exception, or
    commit if not. Perhaps this will help you. I use it in production
    code.

    ##
    # This is a transaction context manager. It will ensure that the code
    in
    # the context block will be executed inside a transaction. If any
    exception
    # occurs, the transaction will be rolled back, and the exception
    reraised.
    # If no exception occurs, the transaction will be committed.
    # db is a database connection object.

    from contextlib import contextmanager

    @contextmanager
    def transaction(db):
    db.begin()
    try:
    yield None
    except:
    db.rollback()
    raise
    else:
    db.commit()
     
    Mike Kent, Sep 3, 2010
    #2
    1. Advertising

  3. John Nagle

    John Nagle Guest

    On 9/3/2010 12:41 PM, Mike Kent wrote:
    > On Sep 3, 12:22 am, John Nagle<> wrote:
    >> I would expect MySQLdb to rollback on a control-C, but it doesn't
    >> seem to have done so.

    >
    >> Something is broken.

    >
    > I wouldn't expect it to, I'd expect to roll back on an exception, or
    > commit if not.


    MySQL does rollback properly on connection failure or program
    exit. It turns out I had a commit on that database handle in logging
    code.

    John Nagle
     
    John Nagle, Sep 3, 2010
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SmltIEhlYXZleQ==?=

    Oracle - cannot ROLLBACK in a distributed tran

    =?Utf-8?B?SmltIEhlYXZleQ==?=, Feb 3, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    975
    =?Utf-8?B?SmltIEhlYXZleQ==?=
    Feb 3, 2005
  2. Guest
    Replies:
    5
    Views:
    644
  3. Ville Vainio
    Replies:
    11
    Views:
    639
    Hamish Lawson
    Aug 10, 2004
  4. Replies:
    2
    Views:
    809
    Magnus Lycka
    Dec 5, 2005
  5. Replies:
    6
    Views:
    1,360
Loading...

Share This Page