While writing a query other day a thought came to me and have stuck in my mind.
What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise unique constraint error? Will it even matter?
Edit: As suggested below in answer that this issue depends on database I am adding tag postgresql.
Let the DB raise an error.
Testing first isn't safe for concurrency because you'll get a collision eventually because 2 threads may pass the "NOT EXIST" and both will try to write. This applies to both "READ COMMITTED" and MVCC/Snapshot lock strategies.
You can use lock hints to force isolation, but you reduce performance.
I call this the JFDI pattern (SO link). For "update if exists" then see this here: Need Help Troubleshooting Sql Server 2005 Deadlock Scenario. These are SQL Server. MySQL has INSERT IGNORE which handles this gracefully. Not sure about the rest
I do not think your question is really database agnostic. The right answer could depend on implementation details, which may vary from vendor to vendor and change with the next version. I would test under concurrency before choosing any approach on any RDBMS.
Right now, on SQL Server 2008 R2, I am using the following:
Low concurrency and low amount of modifications. To save a single row I serialize using sp_getapplock and use MERGE. I stress test under high concurrency to verify that it works.
Higher concurrency and/or volume. To avoid concurrency and boost performance, I do not save one row at a time. I accumulate changes on my app server, and use TVPs to save batches. Still, to avoid concurrency related issues, I serialize using sp_getapplock before the MERGE. Again, I stress test under high concurrency to verify that it works.
As a result, we have good performance and zero concurrency related issues in production: no deadlocks, no PK/unique constraints violations etc.
