7

When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable). These are pretty much the definition of a transaction.

Wikipedia states that there are some databases that insure a transaction remains isolated by locking the rows and not unlocking them until the transaction has committed.

My question is: How can a database that solely relies on locking guarantee consistency? If a power outage occurs mid-transaction, there may be data partially written to the row.

Even for databases like SQL Server that use a Temporary DB to perform all the transactions, what happens if a power outage occurs as the database is committing the transactions to disk?

Does the data become corrupted or does the database handle that correctly?

1 Answer 1

13

Bear with me, this is a complicated question to clarify and we may go through a few rounds of edit and commenting to plug the gaps. From the way your question is phrased I'm guessing you're not differentiating the atomicity, isolation, consistency and durability elements of ACID.

When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable). These are pretty much the definition of a transaction.

The isolation part of ACID is widely misunderstood. There is a degree to which transactions are isolated from each other, as determined by the transaction isolation level. The other elements of ACID are absolute.

Wikipedia states that there are some databases that insure a transaction remains isolated by locking the rows and not unlocking them until the transaction has committed.

This relates to the isolation part of ACID, it doesn't have any impact on your main question.

My question is: How can a database that solely relies on locking guarantee consistency? If a power outage occurs mid-transaction, there may be data partially written to the row.

Your example is not concerned with consistency, it's durability and atomicity. These are guarenteed by write ahead logging (WAL). With WAL, all changes are written to the undo/redo log before they are applied to the data.

In the event of a power failure, a recovery process is run which will read the log to identify a) "mid-flight" transactions that did not commit and b) transaction that did commit but which were not applied to the data. The changes from a) transactions are undone (rolled back), returning the data to its pre-transaction state. The changes from b) are redone (rolled forward), ensuring the data is in the expected post-transaction state.

Even for databases like SQL Server that use a Temporary DB to perform all the transactions, what happens if a power outage occurs as the database is committing the transactions to disk?

TempDB (assuming that's what you're referring to) has absolutely nothing to do with SQL Servers execution of transactions. Are you confusing the role of TempDB in snapshot isolation levels?

3
  • +1 Excellent. The write-ahead logging link fills in a lot of gaps and makes a lot of sense. You're right about me confusing TempDB and isolation levels. I'll do more research in that area. Many thanks!
    – Richard
    Aug 12, 2011 at 12:11
  • and what if there is another power outage when the database is running the undo/redo log?
    – Ike
    Jun 1, 2015 at 15:49
  • @Ike The same happens again. Jun 2, 2015 at 6:00

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.