I'm considering applying delayed durability in a system I work on and want to make sure I have the correct mental model in order to avoid data loss. My understanding is it basically means
COMMIT turns into a sort-of "Schrodinger's cat" between
ROLLBACK, right? In other words, some transactions that are committed might actually end up rolled back ("the commit is a lie").
Hence, we should not actually expect to see any data loss in scenarios like the following:
- A service broker activation procedure that
RECEIVEs messages, processes them inside a transaction, and
COMMITs. If "the commit is a lie" and the transaction doesn't actually commit, it will be a rollback instead and service broker will automatically rerun it.
- A periodic ETL-type job which bulk imports files into a staging table (this part would be fully durable). Afterwards, inside a transaction, it processes the staged data and marks the staged data as processed. If "the commit is a lie" and the transaction doesn't actually commit, it will be a rollback instead and the staged data will not be marked as processed, so we'll do it again next time.
In these scenarios we would not expect actual data loss because the transactions get rerun automatically. In this case it's no more risky than anything else that could interrupt a transaction (e.g. a deadlock or an unplanned shutdown that interrupts the process).
In contrast, a scenario like the following would not be a good candidate for delayed durability and would risk data loss:
- An external process (e.g. SSIS), for each file in a directory, begins a transaction, bulk imports the data from the file, and commits the transaction. Once the transaction is committed, it deletes the file. Now, if "the commit is a lie" and the transaction doesn't actually commit, we will have lost the file data for good because we deleted the file but the import was rolled back.
So in sum, delayed durability can lose data only if some external party is relying on the
COMMIT. For example, an online checkout enters a sale and reasonably assumes that when the data has been committed, the sale is finished, etc., and informs the user as such. But if it's some processing that can already automatically tolerate and resume from interruptions/rollbacks, there should be no increased risk.
Does that sound right?