Select / Insert version of an Upsert: is there a design pattern for high concurrency?

by

You can use LOCKs to make things SERIALIZABLE but this reduces concurrency. Why not try the common condition first (“mostly insert or mostly select”) followed by safe handling of “remedial” action? That is, the “JFDI” pattern…

Mostly INSERTs expected (ball park 70-80%+):

Just try to insert. If it fails, the row has already been created. No need to worry about concurrency because the TRY/CATCH deals with duplicates for you. 

BEGIN TRY
   INSERT Table VALUES (@Value)
   SELECT @id = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
    ELSE -- only error was a dupe insert so must already have a row to select
      SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
END CATCH

Mostly SELECTs:

Similar, but try to get data first. No data = INSERT needed. Again, if 2 concurrent calls try to INSERT because they both found the row missing the TRY/CATCH handles.

BEGIN TRY
   SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
   IF @@ROWCOUNT = 0
   BEGIN
       INSERT Table VALUES (@Value)
       SELECT @id = SCOPE_IDENTITY()
   END
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
    ELSE
      SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
END CATCH

The 2nd one appear to repeat itself, but it’s highly concurrent. Locks would achieve the same but at the expense of concurrency…

Edit:

Why not to use MERGE…

If you use the OUTPUT clause it will only return what is updated. So you need a dummy UPDATE to generate the INSERTED table for the OUTPUT clause. If you have to do dummy updates with many calls (as implied by OP) that is a lot of log writes just to be able to use MERGE.

More Related Contents:

  1. Confused about UPDLOCK, HOLDLOCK
  2. Comma separated results in SQL
  3. How to check if a column exists in a SQL Server table
  4. Understanding PIVOT function in T-SQL
  5. Using T-SQL, return nth delimited element from a string
  6. Get first day of week in SQL Server
  7. Using RegEx in SQL Server
  8. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  9. INSERT INTO vs SELECT INTO
  10. SQL Server 2008 Empty String vs. Space
  11. How do I use the CONCAT function in SQL Server 2008 R2?
  12. How to set a default value for an existing column
  13. TSQL: How to convert local time to UTC? (SQL Server 2008)
  14. SQL Server: combining multiple rows into one row
  15. How do I find a stored procedure containing ?
  16. SQL Recursive CTE: Finding objects linked by property
  17. How to convert number of minutes to hh:mm format in TSQL?
  18. Convert decimal time to hours and minutes
  19. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  20. How do I move a table into a schema in T-SQL
  21. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  22. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  23. How to wait for 2 seconds?
  24. What are row, page and table locks? And when they are acquired?
  25. Generate MD5 hash string with T-SQL
  26. Create a view with ORDER BY clause
  27. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  28. Possible to store value of one select column and use it for the next one?
  29. Use of Begin / End Blocks and the Go keyword in SQL Server?
  30. how to write number to word function in sql server [closed]

Leave a Comment