How does SqlAlchemy handle unique constraint in table definition

Question

I have a table with the following declarative definition:

class Type(Base):
    __tablename__ = 'Type'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique = True)
    def __init__(self, name):
        self.name = name

The column "name" has a unique constraint, but I'm able to do

type1 = Type('name1')
session.add(type1)
type2 = Type(type1.name)
session.add(type2)

So, as can be seen, the unique constraint is not checked at all, since I have added to the session 2 objects with the same name.

When I do session.commit(), I get a mysql error since the constraint is also in the mysql table.

Is it possible that sqlalchemy tells me in advance that I can not make it or identifies it and does not insert 2 entries with the same "name" columm? If not, should I keep in memory all existing names, so I can check if they exist of not, before creating the object?

Answer 1

SQLAlechemy doesn't handle uniquness, because it's not possible to do good way. Even if you keep track of created objects and/or check whether object with such name exists there is a race condition: anybody in other process can insert a new object with the name you just checked. The only solution is to lock whole table before check and release the lock after insertion (some databases support such locking).