Hello,
I have multiple processes that can potentially insert duplicate rows into
the database. These inserts do not happen very frequently (a few times
every hour) so it is not performance critical.
I've tried an exist check before doing the insert, like so:
#Assume we're inserting a camera object, that's a valid SQLAlchemy ORM
object that inherits from declarative_base...
try:
stmt = exists().where(Camera.id == camera_id)
exists_result =
session.query(Camera).with_lockmode("update").filter(stmt).first()
if exists_result is None:
session.add(Camera(...)) #Lots of parameters, just assume it works
session.commit()
except IntegrityError as e:
session.rollback()
The problem I'm running into is that the `exist()` check doesn't lock the
table, and so there is a chance that multiple processes could attempt to
insert the same object at the same time. In such a scenario, one process
succeeds with the insert and the others fail with an IntegrityError
exception. While this works, it doesn't feel "clean" to me as I end up with
"gaps" in the primary key ID as failed inserts still increment the primary
key counter.
I would really like some way of locking the Camera table before doing the
`exists()` check.
Thanks,
Phil
p.s. I've also posted this on Stackoverflow.
http://stackoverflow.com/questions/14520340/sqlalchemy-and-explicit-locking
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
