Parallel Execution Causing Duplicates [SQL Server] There are multiple procedures on our database, handling a "inventory". Let's say each user can store items, and then request those items to be moved to another user's inventory, or to be stacked with other items. 

For example, you have 5 wood, here, and 25 here, you can combine them to have 30 wood. Each individual user can send post requests to your c# WebApi, which then runs some checks, and finally executes the necessary procedures.

Everything is checked, quantity, ids, slots, everything is double checked. But here is the problem:

If a user, sends hundreds of parallel requests, with a web debugging proxy, or an automated posting client, since the api manages each request individually, and in parallel, this causes bugs where duplicates are caused. In other words, botting the api, with parallel requests, causes many checks in the procedure to pass, since they happen in parallel. eg: is there enough of this item? check, is there enough space for the item in the target container? check. etc etc...

I tried limiting the requests per X miliseconds on iis, but then again, we use cloudflare, which make all requests look like they come from the CF ip's as we resolve the cf ip in the application level. What about only allowing 1 execution at a time per user in the webapi?

What should I do to prevent this from happenning?
Fix the application. There is no magic setting in the database that will prevent a badly written application from inserting wrong data in the database.

Fixing the application means using the appropriate isolation levels and keeping transactions as short as possible.

Here is an example of how you check and decrease quantity in the same query:

IF OBJECT_ID('tempdb..#stock') IS NOT NULL DROP TABLE #stock;
IF OBJECT_ID('tempdb..#basket') IS NOT NULL DROP TABLE #basket;
GO

SET NOCOUNT ON;


CREATE TABLE #stock (
    product varchar(50) PRIMARY KEY,
    quantity int
);

INSERT INTO #stock 
VALUES 
     ('pants', 30),
     ('shirt', 18),
     ('shoes', 4);


CREATE TABLE #basket (
    product varchar(50),
    quantity int
);


-- At this point the stock covers the basket
INSERT INTO #basket 
VALUES 
    ('pants', 2),
    ('shoes', 4);

-- Uncomment this statement to make it fail
INSERT INTO #basket 
VALUES 
    ('shoes', 1);




DECLARE @invalid_stock TABLE (
    product varchar(50) PRIMARY KEY,
    quantity int
);


BEGIN TRAN

-- Here you decrease the stock of the quantity required by your basket
-- If you have stock that was turned to negative by the operation, it
-- will be inserted in the @invalid_stock table.
INSERT INTO @invalid_stock
SELECT * 
FROM (
    UPDATE s
    SET quantity = s.quantity - b.quantity
    OUTPUT inserted.*
    FROM #stock AS s
    INNER JOIN (
        SELECT product, SUM(quantity) AS quantity
        FROM #basket 
        GROUP BY product
    ) AS b
        ON s.product = b.product
) AS newstock
WHERE quantity &lt; 0;


IF EXISTS (SELECT * FROM @invalid_stock)
BEGIN
    ROLLBACK;
    THROW 50001, 'Not enough stock to complete this order!', 1;
END
ELSE
BEGIN 
    COMMIT;
    PRINT 'Transaction complete!'
END