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.