In my experience, the root cause of these symptoms is most commonly a race condition in the application and/or T-SQL code rather than data corruption due to hardware, network, or malicious activity. Race conditions are insidious because the undesired outcome occurs by happenstance, difficult to reproduce, and may rarely occur.
One must use transactions, along with the appropriate isolation level (or locking hints), to ensure aggregated values like Remaining Qty in the items table reflects the related Purchase and Sales transactions.
EDIT
Testing concurrency problems is a challenge because multiple sessions are required and often many iterations too. Below is an example technique using multiple SSMS query windows I use to reproduce production issues as well as proactively identify suspect code before release. Be aware that race conditions are a matter of timing so even due diligence with testing may not catch rare occurrences.
--example setup script with race condition vulnerability
USE YourDatabase;
DROP TABLE dbo.Transactions;
DROP TABLE dbo.Item;
CREATE TABLE dbo.Item(
ItemNumber int NOT NULL
CONSTRAINT PK_Item PRIMARY KEY
, RemainingQTY int NOT NULL
);
CREATE TABLE dbo.Transactions(
TransactionID int NOT NULL IDENTITY
CONSTRAINT PK_Traansactions PRIMARY KEY
, ItemNumber int NOT NULL
CONSTRAINT FK_Traansactions_Item FOREIGN KEY REFERENCES dbo.Item(ItemNumber)
INDEX idx_Transactions_ItemNumber
, TransactionType varchar(10) NOT NULL
CONSTRAINT CK_Transactions_TransactionType CHECK (TransactionType IN('Purchase', 'Sale'))
, QTY int
);
--load 5000 items
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Item(ItemNumber, RemainingQTY)
SELECT num, 1000
FROM t10k
WHERE num <= 5000;
--load 5000 purchase transactions
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Transactions(ItemNumber, TransactionType, QTY)
SELECT ItemNumber, 'Purchase', 1000
FROM dbo.Item;
GO
CREATE OR ALTER PROC dbo.usp_InsertTransaction
@ItemNumber int
, @TransactionType varchar(10)
, @QTY int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @RemainingQTY int;
BEGIN TRY
BEGIN TRAN;
SELECT @RemainingQTY = RemainingQTY
FROM dbo.Item
WHERE ItemNumber = @ItemNumber;
IF @TransactionType = 'Purchase'
SET @RemainingQTY = @RemainingQTY + @QTY
ELSE
SET @RemainingQTY = @RemainingQTY - @QTY;
UPDATE dbo.Item
SET RemainingQTY = @RemainingQTY
WHERE ItemNumber = @ItemNumber;
INSERT INTO dbo.Transactions VALUES
(@ItemNumber, @TransactionType, @QTY);
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
GO
Open 3 SSMS query windows and run these scripts as instructed:
--Step 1: Run script in SSMS query window 1 to acquire exclusive lock to sync start of test executions
DECLARE @return_code int;
EXEC @return_code = sp_getapplock
@Resource = 'concurrency_test'
,@LockMode = 'exclusive'
,@LockOwner = 'session';
RAISERROR('sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO
--Step 2: Run this script in SSMS query windows 2 and 3 to acquire a shared lock on same resource as session 1.
--These will block until session 1 lock is released to start test.
DECLARE @return_code int;
EXEC @return_code = sp_getapplock
@Resource = 'concurrency_test'
,@LockMode = 'shared'
,@LockOwner = 'session';
RAISERROR('sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO
--execute queries to insert transactions and update RemainingQTY 100 times
DECLARE @IterationCount int = 0;
WHILE @IterationCount < 100
BEGIN
EXEC dbo.usp_InsertTransaction
@ItemNumber = 1000
, @TransactionType = 'Sale'
, @QTY = 1;
SET @IterationCount += 1;
END;
GO
--release lock after test completes
DECLARE @return_code int;
EXEC @return_code = sp_releaseapplock
@Resource = 'concurrency_test'
,@LockOwner = 'session';
RAISERROR('sp_releaseapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO
--Step 3: Run script in SSMS query window 1 to release lock to start test
DECLARE @return_code int;
EXEC @return_code = sp_releaseapplock
@Resource = 'concurrency_test'
,@LockOwner = 'session';
RAISERROR('sp_releaseapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;
GO
--Step 4: Run this script to validate RemainingQTY after test completes
WITH transaction_summary AS (
SELECT
t.ItemNumber
, SUM(CASE WHEN t.TransactionType = 'Purchase' THEN t.QTY END) AS Purchase
, SUM(CASE WHEN t.TransactionType = 'Sale' THEN t.QTY END) AS Sale
FROM dbo.Transactions AS t
WHERE t.ItemNumber = 1000
GROUP BY t.ItemNumber
)
SELECT
i.ItemNumber
, i.RemainingQTY AS ItemRemainingQTY
, t.Purchase
, t.Sale
, t.Purchase - t.Sale AS ActualTramsactionsRemainingQTY
FROM dbo.Item AS i
JOIN transaction_summary AS t
ON t.ItemNumber = i.ItemNumber
WHERE i.RemainingQTY <> t.Purchase - t.Sale;
GO
Below is the output of the step 4 validation query after a test on my box that shows item RemainingQTY is invalid. This happened 31 times of 200 total proc calls on my test machine. The cause is different sessions both executed the SELECT query at about the same time and read the same RemainingQTY value. One session reduced the RemainingQTY as desired but the value was subsequently overwritten by the other session based on stale data. This test ran in a tight loop so it's more likely to occur than in a common prod workload.
| ItemNumber |
ItemRemainingQTY |
Purchase |
Sale |
ActualTramsactionsRemainingQTY |
| 1000 |
831 |
1000 |
200 |
800 |
One way to fix this code bug is to refactor the proc to avoid the local variable. This will serialize updates to the item row and perform slightly better too by eliminating the SELECT query:
CREATE OR ALTER PROC dbo.usp_InsertTransaction
@ItemNumber int
, @TransactionType varchar(10)
, @QTY int
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @RemainingQTY int;
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.Item
SET RemainingQTY = RemainingQTY +
CASE @TransactionType WHEN 'Purchase' THEN @RemainingQTY ELSE @RemainingQTY * -1 END
WHERE ItemNumber = @ItemNumber;
INSERT INTO dbo.Transactions VALUES
(@ItemNumber, @TransactionType, @QTY);
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
GO
