8

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.

| improve this question | |
3

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:

  1. 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.

  2. 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.

| improve this answer | |
  • so you mean to say that if concurrency problems are handled well then checking first is a good idea? – codecool Feb 9 '12 at 16:52
  • 1
    Yes, usually avoiding errors performs better than throwing and catching exceptions, but you need to benchmark it on your platform. – A-K Feb 9 '12 at 16:56
7

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

| improve this answer | |
  • okk. I did not think about that! In case of concurrency even checking can fail. :) – codecool Feb 9 '12 at 15:58
  • 2
    Lesson 4 here: sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/… – gbn Feb 9 '12 at 15:59
  • I think it is useful to both perform the check (and fail gracefully) but follow the "trust but verify" adage to catch the failure. The reason? Error handling is expensive. I'm working on some tests today, actually, that should reveal whether this comment makes sense. If this comment disappears, you know the result. :-) – Aaron Bertrand♦ Feb 9 '12 at 16:11
  • Also in 2008 there is MERGE which may have resolved Paul's observations but I haven't tested this at high tx rates. Mostly because I have yet to train my brain to understand that syntax. – Aaron Bertrand♦ Feb 9 '12 at 16:13
  • 1
    Mea culpa. I've made some observations in my testing today. Keep in mind these are very isolated tests with no concurrency. For straight inserts, checking first and avoiding the error is about 10x faster than letting the error happen. But in other cases where you are going to do more complex error handling (TRY/CATCH or IF @@ERROR <> 0, with ROLLBACK or THROW, etc.) it's about twice as slow if you check first. Again I'll stress these are just my initial observations and there are many factors that can influence the impact it can have. It may be some time before I can blog about it thoroughly. – Aaron Bertrand♦ Feb 11 '12 at 4:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.