CODEWITHSUNDEEP
sqlsql-servertransactions
Nick Nick
Nick Nick

Reputation: 177

How is consistency maintained in transactional systems in the event of a power outage?

How is consistency maintained in transactional systems in the event of a power outage? For example, consider the operation below that are performed within a transaction.

UPDATE table SET someColumn = someColumn + 1 WHERE <whatever>

In the event of a sudden power outage, how can we ensure that the operation is completed or not?

From SQL server docs:

SQL Server uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction. ... The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk.

As far as I understand, using the example of the SQL increment operation above, the following operations occur:

  1. Write a record to the transaction log.
  2. Flush changes to disk.

When a sudden shutdown occurs, how does the system know if the incremental operation has completed? How does it understand from when to rollback? (a shutdown can occur after adding a log to transaction log, but before flushing changes to disk, is it?)

And I have another (second) question, it is similar to this one. How can we guarantee atomicity? For example,

UPDATE table SET someColumn = someColumn + 1 AND otherColumn = otherColumn + 2 WHERE <whatever>

How can we ensure that in the event of a sudden power off, the field otherColumn will also be updated, or no fields will be updated?

Upvotes: 2

Views: 164

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89256

Write-Ahead Logging (WAL). See eg Transaction Log Architecture

a shutdown can occur after adding a log to transaction log, but before flushing changes to disk, is it?

Log records are added to the transaction log in memory, but when a transaction is committed SQL Server waits for confirmation that all the log records have been written to durable media. And on restart the transaction will be rolled back if all its log records haven't been flushed to disk.

And SQL Server requires the storage system to flush changes to disk, disabling any non-persistent write caching.

Upvotes: 1

Related Questions