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.

• Craig: If you want some kind of reference between multiple different records, then you'd be much better off first generating a unique "transaction ID", then have that as a reference field for all of the other records that you are creating. Trying to ensure the "sequential" records inserted into database tables always relate to one another is setting yourself up for disappointment - in spite of any best efforts. So - if you have multiple stored procs inserting different records, your very first step should be to generate a transaction ID, and then pass that transaction ID to all other procs
• J.D.: So stripping away all the details, your goal is to find a transactionally consistent way to to insert a row into a table that holds the new credit and debit values, based on the previous row's balance?
• M S: 1.) I already have a transactionID but hoped to add this "intuitiveness" even if as you suggest counting on it is likely to disappoint. 2.) My main concern is not the credit/debit amount (basically a static input) but rather the accuracy of the cashbalance field. As above multiple values with one INSERT requires I think some sort of serialization (still reading/testing). I'm now leaning to completely separating the INSERTS into two different sp -- but this lessens my aforementioned always "sequential ids" hopes. Non-sequential ids will be an edge case in any event but still.
• J.D.: @MS I still don't follow. Are you saying you're concerned with reading the balance twice because it can change between the reads?
• M S: @J.D. yes that is my concern. Also that the read value is locked until update is written.

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.

• M S: 1.) Thanks for your (expert) analysis. The local variables suggestion is useful but I think I am going to separate the INSERTs into two independent sprocs called sequentially. In almost all cases the userID will NOT be the same so two different reads are required anyway. The only advantage I was aiming for was always sequential IDs.
• M S: 2.) Per READ COMMITTED et al, by default when the SELECT clause in my INSERT READs a cashbalance value that value is blocked from updating by another process until my INSERT completes. The concern though is whether another new row can be written in the meantime -- i.e. my Insert stmt reads table1.ID=1 has a cashbalance=0 and based on that should write a new row adding 1 so that cashbalance=1. However, between that read and write another process adds a new row table1.ID=2 with cashbalance=999. Then when my INSERT writes table1.ID=3 with cashbalance=1 it will be incorrect (should be 1000).
• J.D.: @MS I think what you're looking for is explicit locking or wrapping units of work in explicit transactions. Either will help ensure your data integrity holds in the use cases you describe. If I do more time I'll update my answer discussing this further.
• M S: @JD Thanks again, I sincerely appreciate your time (and yes transactions with rollback etc are applicable). I'll add more detail for the benefit of future readers below.