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?
