2

So there are 6 things involved when a transaction is made that writes data:

  1. Tlog in memory
  2. Data page in memory
  3. Tlog on disk
  4. Data page on disk
  5. Send the tlog to secondary AG
  6. Inform the client that transaction is committed

For a query that changes data, does the change get written to tlog (memory), then the tlog is hardened to disk, then the changes are made to data buffer (memory) and then client gets a committed response?

And then after a while the checkpoint will flush the data pages (memory) to data page on disk. Is that how it works?

In AG sync mode and async mode, when does primary send the tlog to secondary?

2 Answers 2

2

For a query that changes data, does the change get written to tlog (memory), then the tlog is hardened to disk, then the changes are made to data buffer (memory) and then client gets a committed response?

The high level synopsis is the log blocks are filled in memory from transactions. When required, the log blocks are flushed to disk, when the are attempting to be flushed to disk they are sent to any synchronous secondary replicas. The primary waits for all required replicas (depending on settings), including itself, to harden the log block and report back. Once all requirements have been met, any transactions waiting on that log block to be hardened will be free to move on and will generate a response to the client.

There are various other caches and items in play but don't generally get in the way of the above.

In AG sync mode and async mode, when does primary send the tlog to secondary?

Pedantically depends on various configurations, however the high level is that any synchronous commit replicas will be sent the log when the log block is attempted to be hardened (flushed to disk) on the primary.

Asynchronous secondary replicas have the log sent depending on various items, however in general when the log cache knows it has new data and/or progress messages sent by the secondary have been processed at the primary with new information, though depends on the performance abilities and current workload of both.

2
  • When I, as a user make a transaction (sync ag scenario), then do I have to wait for the sql engine to flush the log buffer to disk, at same time send the log records to secondary, wait for response from secondary, and itself, then tell the user the transaction has committed? That's quite an insight you have given me. Thanks.
    – variable
    Dec 23, 2022 at 12:24
  • Yes, that's how it works, it needs to wait for everyone to harden before it can signal to the user it's safe to continue. Dec 23, 2022 at 14:35
1

When a dirty page (i.e. one updated by a transaction) is written to disk is independent of when the tlog records for that transaction are written.

The buffer manager is free to evict a page whenever it needs, even one dirtied by one or more transactions.

To commit a transaction the system need not persist all the pages it dirtied. Ensuring the tlog records are persisted is sufficient.

Together these are known as a "steal /no force" policy. It has advantages over the alternatives and is widely adopted.

A database-wide checkpoint can occur at any time persisting all dirtied pages to disk. Those from committed transactions and those from open transactions both can be persisted.

Tlog records from all transactions are held in memory in a single buffer. When it is persisted the whole buffer is persisted, including records from open transactions. It is only after a transaction's commit record is acknowledged as having been persisted that the client can be informed.

This all is coreographed by the ARIES protocol. It dictates what metadata is captured, when, and the order in which processes occur. Is has more requirements than I have mentioned here.

As an aside it is possible that the target page of a write operation is never in memory during the life of a transaction. Consider an insert to a table with no constraints. There is no need to compare the new data to existing data to check for conflicts. So why read any pages into memory, even the one that will hold the new row? Persisting the log records and allowing lazy update of the data page would work. I don't believe SQL Server actually works this way but it's interesting to consider.

0

Your Answer

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

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