SQL Server stored proc locking, serialization

Question

Stored proc in SQL Server INSERT INTO table1 value sets A , B

Value set A has as one of its parameters: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)

Value set B has as one of its parameters: (SELECT TOP 1 cashbalance + 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)

When run as INSERT . . . A , B then if the userid is the same in both value sets SQL Server reuses the same select. Hence a cashbalance field that say started at 0 records -1 into the first inserted row and +1 (i.e. not back to 0-1+1=0 as should be done) into the second inserted row.

When run as separate sequential INSERT . . A then INSERT . . B statements the cashbalance field computes correctly but the createdUTC field has a tiny gap -- which thus infers a very tiny concern about another update/read slipping in between.

Obviously related to locks and/or serialization -- which I'm researching and will post findings from).

Ideally I am trying to implement double entry bookkeeping (i.e. every transaction has a row debiting A and another row crediting B the same amount simultaneously) with immediately sequential id's and the same createdUTC.

---

Since most research I've done starts with "use a set based approach for SQL instead", I'll explain a bit more about my use case -- and either help someone following in the future OR maybe become better educated myself.

Three tables: BuyOrders, SellOrders, ContractTransactions

A new BuyOrder is submitted say for 100 contracts. (SQL?) Code is needed to match that BuyOrder to the best available SellOrder(s) on FIFO basis -- say first three in order SellOrders are for 50, 40 and 30 contracts. Hence the 50 and 40 should get matched plus 10 of the 30 (which I do later fwiw).

I use a SELECT . . . SUM(contractsavailable) OVER (ORDER BY createdutc) to populate a cursor (or temp table) with the first two (50 and 40) orders. A set based approach to INSERT both new entries into ContractTransactions table leads to the complication in my original question.

Hence RBAR is (seems??) the only way to achieve desired results -- and though a different WHILE loop could avoid a temptable/cursor it would be at the cost of a new SELECT each time. Feedback is welcomed.

Answer 1

Are you saying you're concerned with reading the balance twice because it can change between the reads?

yes that is my concern.

For the use case you presented, you can actually get away with just reading the cashbalance once, doing your manipulations and saving them to local variables, and then using those as the parameters to the procedure call like so:

DECLARE @CashBalance DECIMAL(16,2) =
(
    SELECT TOP 1 cashbalance
    FROM table1 
    WHERE userid = __ 
    ORDER BY createdUTC, ID
);

DECLARE @A DECIMAL(16,2) = @CashBalance - 1;
DECLARE @B DECIMAL(16,2) = @CashBalance + 1;

EXEC dbo.YourProcedure @A, @B;

By only reading from the table once, first, and then doing your manipulations, you have an atomic read of the cashbalance value that is constant for all of your manipulations. This also improves the runtime of your query and reduces the locking time of the table being read from. All good things.

Also that the read value is locked until update is written.

This just depends on your transaction isolation level and how you want your system to function. By default, with READ COMMITTED isolation level, writers block readers (as you're aware of). The idea is if a write began before the read occurs, then a change is occurring that should be finished first before the read obtains the value, to be correct.

If you want reads to be able to access the previous version of the row, when it is currently locked by a write, then you can change your isolation level to SNAPSHOT or READ COMMITED SNAPSHOT ISOLATION aka RCSI. Then the read won't wait for the write to finish. Here's some further information on enabling those isolation levels.