6

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!

sammy
  • 61
  • 1
  • 6
  • 1
    Please supply your table structure and example data. Is the set limit constant for all transactions? – Martin Smith Feb 16 '13 at 19:04
  • As a hunch, the problem might be solved if you make the stored procedure run a lot faster. With a 100ms stored procedure collisions will be exceedingly rare. If you post the query and table definition, we could suggest an index that makes the total calculation very fast. – Andomar Feb 16 '13 at 19:18
  • please find the table structure. In this sum(stake) for each betnumber should not exceed 1000. This 1000 limit is stored in another table. [SlipID] [bigint] IDENTITY(1,1) NOT NULL, [TillID] [int] NOT NULL, [Barcode] [varchar](30) NOT NULL, [GamingDate] [date] NOT NULL, [DrawID] [int] NOT NULL, [BetNumber] [tinyint] NOT NULL, [CurrencyID] [int] NOT NULL, [Stake] [decimal](9, 2) NOT NULL, [SlipTime] [datetime] NOT NULL, – sammy Feb 16 '13 at 19:18
  • @Andomar: Actually, the stored procedure is too big. its checking lot of limits. 5 different limits. The issue occurs when there is only few milliseconds difference between the transactions. Sometimes, as low as 23 milliseconds between the transaction and the total sales exceeds the limit.. – sammy Feb 16 '13 at 19:24

1 Answers1

6

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    +1 Clever! Maybe a bit too clever, as it raises the bar for anyone editing the code. Might be a good idea to close the transaction before the procedure returns. – Andomar Feb 16 '13 at 19:27
  • @Andomar - Thanks. Forgot that! – Martin Smith Feb 16 '13 at 19:29
  • Thanks Martin! It helped... However, we went on to create summary tables with total value gets plus or minus for each transaction. Also, the sales tables is huge and everytime querying the table to calculate total sum took time as too many inserts and indexes get fragmented too fast.. so, we decided to have that summary table... So far, its helping... Thanks! – sammy Jul 13 '13 at 16:29