Timeline for SQL Server stored proc locking, serialization
Current License: CC BY-SA 4.0
11 events
when toggle format | what | by | license | comment | |
---|---|---|---|---|---|
Aug 19, 2023 at 17:37 | history | edited | Paul White♦ | CC BY-SA 4.0 |
appended answer 330439 as supplemental
|
Aug 18, 2023 at 21:56 | history | edited | M S | CC BY-SA 4.0 |
deleted 348 characters in body
|
Aug 16, 2023 at 19:46 | vote | accept | M S | ||
Aug 16, 2023 at 12:48 | answer | added | J.D. | timeline score: 1 | |
Aug 16, 2023 at 5:50 | comment | added | M S | @J.D. yes that is my concern. Also that the read value is locked until update is written. | |
Aug 16, 2023 at 1:26 | comment | added | 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?
|
|
Aug 16, 2023 at 0:39 | comment | added | 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. | |
Aug 16, 2023 at 0:22 | comment | added | 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 ?
|
|
Aug 16, 2023 at 0:14 | comment | added | 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 | |
S Aug 15, 2023 at 23:06 | review | First questions | |||
Aug 16, 2023 at 9:25 | |||||
S Aug 15, 2023 at 23:06 | history | asked | M S | CC BY-SA 4.0 |