Bug in SQLAlchemy Rollback after DB Exception?

Question

My SQLAlchemy application (running on top of MariaDB) includes two models MyModelA and MyModelB where the latter is a child-record of the former:

class MyModelA(db.Model):
    a_id   = db.Column(db.Integer, nullable=False, primary_key=True)
    my_field1 = db.Column(db.String(1024), nullable=True)

class MyModelB(db.Model):
    b_id   = db.Column(db.Integer, nullable=False, primary_key=True)
    a_id = db.Column(db.Integer, db.ForeignKey(MyModelA.a_id), nullable=False)
    my_field2 = db.Column(db.String(1024), nullable=True)

These are the instances of MyModelA and MyModelB that I create:

>>> my_a = MyModelA(my_field1="A1")
>>> my_a.aid
1
>>> MyModelB(a_id=my_a.aid, my_field2="B1")

I have the following code that deletes the instance of MyModelA where a_id==1:

db.session.commit()
try:
    my_a = MyModelA.query.get(a_id=1)
    assert my_a is not None
    print "#1) Number of MyModelAs: %s\n" % MyModelA.query.count()
    db.session.delete(my_a)
    db.session.commit()
except IntegrityError:
    print "#2) Cannot delete instance of MyModelA because it has child record(s)!"
    db.session.rollback()
    print "#3) Number of MyModelAs: %s\n" % MyModelA.query.count()

When I run this code look at the unexpected results I get:

#1) Number of MyModelAs: 1
#2) Cannot delete instance of MyModelA because it has child record(s)!
#3) Number of MyModelAs: 0

The delete supposedly fails and the DB throws an exception which causes a rollback. However even after the rollback, the number of rows in the table indicates that the row -- which supposedly wasn't deleted -- is actually gone!!!

Why is this happening? How can I fix this? It seems like a bug in SQLAlchemy.

same idea: you say that you use MariaDB. Which kind of engine in MariaDB ? MyISAM does not support transactions, so it is always in "autocommit" mode — Stephane Martin, Nov 21 '16 at 23:21
What are the queries generated by sqlalchemy? Do the `SELECTs` have `FOR UPDATE`? — Rick James, Nov 23 '16 at 00:20

score 1 · Answer 1 · answered Nov 27 '16 at 10:01

TL;DR Your problem might be related to the lack of explicit relationship declaration.

For example, here there is a sample of objects' relationship. In addition to the usage of a ForeignKey field, the class explicitly uses the relationship directive to define that connection. In the session API documentation, the following text appears:

object references should be constructed at the object level, not at the foreign key level

Which might imply to the way of SQLAlchemy to manage relations. I am not deeply familiar with the underlying mechanisms, but it is possible that this is what happens. Your session only includes the MyModelA object. Since you did not use the relationship() directive in the definition of MyModelB, objects of MyModelA type are not aware to the fact that some other object might refer them through a ForeignKey. Hence, when the session is about to commit, it is not aware to the fact that deleting the object affects some other MyModelB object, and its transaction mechanism does not take that into account. I suggest that adding the relationship explicitly might prevent that behavior.

Bug in SQLAlchemy Rollback after DB Exception?

1 Answers1