0

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 COMMIT and 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:

  1. 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.
  2. 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:

  1. 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?

Improve this question

2 Answers 2

Reset to default
1

That is basically correct.

If a transaction is reading from the database and writing to the database, and you have some mechanism to retry the transaction if it is rolled back, you will have no data loss with delayed durability.

But in both the scenarios you describe, I doubt delayed durability would be useful. Delayed durability can improve throughput where a session is committing a large number of small transactions by eliminating the log flush on commit. In the Service Broker sample, the transaction should be scoped around a batch of messages to reduce the commit frequency. And in the ETL example you have a large transaction, so eliminating the single log flush on commit at the end is unlikely to be a material improvement.

If a client is writing new data to the database with delayed durability, there may be data loss.

Improve this answer
6
  • Thanks David, that gives some valuable food for thought. You're probably right that the commits themselves are a negligible portion of the activity. But we did see some transaction-related locks and latches (there are a decent number of transactions going on, as there are a couple of feeders to a couple of queues and a couple of simultaneous activation procedures running against them), so it seemed like a fairly simple thing that might be worth trying (as long as it's safe!)
    – Mark Sowul
    Feb 6, 2021 at 0:04
  • I would first batch commits in the activation proc. So only commit after every 10 RECEIVEs, or when the queue is drained. But you'll need to RECEIVE in a loop with no WAITFOR.
    – David Browne - Microsoft
    Feb 6, 2021 at 18:38
  • Thanks David. Unfortunately I've found that if I don't use a WAITFOR, I sometimes end up with messages in the queue yet no activation procedure running. I assume it's some subtle race condition with service broker activation
    – Mark Sowul
    Feb 8, 2021 at 17:02
  • e.g. assume we're in the RECEIVE loop and we just finished processing a batch and begin the next iteration of the loop: we begin a transaction, a RECEIVE happens (with no WAITFOR), but there's nothing there, and so we COMMIT and end the loop and end the procedure. Meanwhile another connection SENDs some messages, sometime after the RECEIVE but before the procedure has fully exited. Hence service broker doesn't start another copy of the procedure, but we're past the point of the RECEIVE so the messages stay dormant until something else wakes up the activation proc again.
    – Mark Sowul
    Feb 8, 2021 at 17:03
  • 1
    You can RECEIVE with a WAITFOR after COMMIT. So an outer loop with WAITFOR and in inner loop without. You definitely should make your activation proc linger with a short WAITFOR before exiting and waiting for activation again.
    – David Browne - Microsoft
    Feb 8, 2021 at 17:06
3

I'm afraid you misunderstand how transactions with delayed durability work. There won't be a magic rollback after a reportedly successful commit -- the client application will not know if the changes it thinks have been committed are never actually persisted. As described in the documentation, in the delayed durability mode the server reports a successful commit immediately, but delays writing the log buffer to the disk until some time later. If the server crashes or shuts down before then, these changes will be lost and the client will not know about that.

Improve this answer
3
  • Thanks, but I'm not sure how this differs from what I laid out: what does it mean that "the changes will be lost" if not "the transaction will be rolled back, instead of being committed"? I'm not saying that it will be a "magic rollback", but rather that a commit may not be truly committed...and thus, from the perspective of the outside world (e.g. a client app), it may appear that the commit became a rollback [if the server crashes or shuts down before the commit is truly made durable].
    – Mark Sowul
    Feb 5, 2021 at 23:54
  • There is no rollback; " from the perspective of the outside world" the transaction has been committed, though it has not.
    – mustaccio
    Feb 6, 2021 at 2:08
  • Can you explain what you mean that there is no rollback? What happens in the following scenarios? 1. a transaction updates a value from 1 to 2 and commits with delayed durability, but a "data loss event" occurs before it's durable. 2. a transaction deletes some rows and commits with delayed durability, but a "data loss event" occurs before it's durable. 3. a transaction inserts some rows and commits with delayed durability, but a "data loss event" occurs before it's durable. What does the data look like if there is no rollback?
    – Mark Sowul
    Feb 8, 2021 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.