I have a distributed system that needs to perform an atomic database operation. In essence, the application needs to perform a find or create.
I can think of two options:
A) Use a distributed lock to ensure that the operation is atomic across processes/systems. Check if the record exists. Get or create the record accordingly.
B) Always attempt to insert the record. Allow the database to raise a unique constraint violation error and catch the exception in the application. If an exception is raised, then it exists so get the record instead.
Option A seems more correct obviously but which is more performant?
Using exception handling for control flow is smelly but is it really that terrible? I feel like option B is more readable and easier to reason about. Plus it doesn't require a lock on the database.
Can you anyone give me a concrete reason not to use option B with concrete evidence?
Thanks!
Locks obviously come at a cost; even if your processes don't block while trying to acquire them, they still take time and resources to manage.
As far as I know, raising an exception has no consequences, provided it doesn't trigger a rollback of some previously-inserted data (bloating your tables and creating work for the autovacuum).
But as of Postgres 9.5, you can do the same thing - without the smell - using an INSERT ... ON CONFLICT DO NOTHING statement. Your client library should return the number of affected rows, which will tell you whether or not the INSERT succeeded.