Bug in SQLAlchemy Rollback after DB Exception? : <p>My SQLAlchemy application (running on top of MariaDB) includes two models <code>MyModelA</code> and <code>MyModelB</code> where the latter is a child-record of the former:</p>

<pre><code>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)
</code></pre>

<p>These are the instances of <code>MyModelA</code> and <code>MyModelB</code> that I create:</p>

<pre><code>&gt;&gt;&gt; my_a = MyModelA(my_field1="A1")
&gt;&gt;&gt; my_a.aid
1
&gt;&gt;&gt; MyModelB(a_id=my_a.aid, my_field2="B1")
</code></pre>

<p>I have the following code that deletes the instance of <code>MyModelA</code> where <code>a_id==1</code>:</p>

<pre><code>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()
</code></pre>

<p>When I run this code look at the unexpected results I get:</p>

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

<p>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!!!</p>

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