Multiple inserts fail with IntegrityError due to failed constraint

Question

With Python (SQLAlchemy) and MariaDB, I am trying to perform three inserts, whereby a constraint for the third insert requires the first two to have been successful. However, currently the third insert fails unless the first two inserts have been committed. I would like to perform all three inserts within one transaction and rollback if an error occurs.

The code looks like this:

try:
    ulcdb.add_person(uid, lang)
    ulcdb.add_group(gid, gid_number)
    ulcdb.add_membership(uid, gid, 'owner')
    ulcdb.commit()
except Exception as e:
    ulcdb.rollback()
    logger.error(str(e))
    raise

whereby ulcdb is an instance of the following class:

class UserLifecycleDatabase:

    def __init__(self):

        engine = sqlalchemy.create_engine(url)
        Session = sqlalchemy.orm.sessionmaker(bind=engine)
        self.session = Session()

    def commit(self):

        self.session.commit()

    def rollback(self):

        self.session.rollback()

    def add_person(self, uid, lang):

        self.session.add(Person(uid=uid, lang=lang))

    ...

If I commit the first two inserts, the third is also successful. What am I doing wrong?

Answer 1

The solution was to run session.flush() rather than session.commit() after the inserts. This way the information needed to fulfil the constraint is available to the session and the third insert succeeds. The whole transaction can then be committed. If, however, an exception occurs, any inserts which were successful can be rolled back.