73442

SQL Server - Simultaneous Inserts to the table from multiple clients - Check Limit and Block

Question:

We are recently facing one issue with simultaneous inserts into one of our sal server tables from multiple clients. I hope you guys can help us through.

We are using stored procedure to do the transactions. In that stored procedure, for each transaction, we calculate total sales so far. If the total sales is less than the set limit, then the transaction will be allowed. Otherwise, the transaction will be denied.

it works fine most of times. But, sometimes when multiple clients trying to do the transaction exactly at the same time, the limit check is failing as both the transactions get done.

Can you guys suggest how we can effectively enforce the limit all the time? Is there any better way to do that?

Thanks!

Answer1:

I don't think it is possible to do this declaratively.

If all inserts are guaranteed to go through the stored procedure and the SaleValue is not updated once inserted then the following should work (I made up table and column names as these were not supplied in the initial question)

DECLARE @SumSaleValue MONEY BEGIN TRAN SELECT @SumSaleValue = SUM(SaleValue) FROM dbo.Orders WITH (UPDLOCK, HOLDLOCK) WHERE TransactionId = @TransactionId IF @SumSaleValue > 1000 BEGIN RAISERROR('Cannot do insert as total would exceed order limit',16,1); ROLLBACK; RETURN; END /*Code for INSERT goes here*/ COMMIT

The HOLDLOCK gives serializable semantics and locks the entire range matching the TransactionId and the UPDLOCK prevents two concurrent transactions locking the same range thus reducing the risk of deadlocks.

An index on TransactionId,SaleValue would be best to support this query.

Recommend

  • Why do major compilers use typedef for stdint.h but use #define for stdbool.h?
  • Angular: call function from other component
  • Footer consisting of two right triangles
  • “Read-Only” Entity Framework? I'm trying to use RIA Services, EF, and Silverlight
  • Viewport for ipad portrait [only]
  • Why $.each() is slower than for loop in jquery?
  • How to load PDFs from Documents Directory?
  • how to animate two continuous popup windows with jquery mobile?
  • javascript - Sum values from JSON
  • How to scroll the horizontal scrollbar in an iFrame from the parent frame?
  • How to write a method signature “T that implements Comparable” in Java?
  • JSON.stringify() - Escaping Issue
  • Plotting multiple variables from same data frame in ggplot [duplicate]
  • How to concatenate data.frame inside lists by using names?
  • Git for windows has stopped working
  • Expression.Call GroupBy then Select and Count()?
  • Django self join , How to convert this query to ORM query
  • How to create mirrored image effect with CSS single element
  • How to sort by Lucene.Net field and ignore common stop words such as 'a' and 'the
  • Why am I getting an Argument exception when creating event handler dynamically?
  • I need a PHP mySQL Login script that directs user to specific URL based on username and password
  • ASP.NET GridView throws: The version of SQL Server in use does not support datatype 'date'
  • Bundling python(“.py”)files along with java class files for a web application
  • Defer unused CSS
  • Admob in ListView not clickable
  • Unable to start a WebView from an AsyncTask
  • How to add html image in to velocity template file to send email?
  • playing mp3 from nsbundle
  • C# code can't “see” the methods in my C++ dll
  • Request Access Token in Postman for Azure Function App protected by Azure AD B2C
  • All Event listing on specified date in Google Calender api (V3) in java?
  • time column in sqlite using gorm
  • When to use the tag in the head and body section of a html page? [duplicate]
  • How to run chrome.tabs.insertCSS from the background page on each page?
  • Print Longitude and Latitude in textbox Android
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • Google App Engine backend servlet not responding
  • Make checkout phone field optional for specific countries in WooCommerce
  • Excel VBA : conditional formatting of sheet1 cells from sheet2 values in excel 2007
  • ReferenceError: TextEncoder is not defined