CODEWITHSUNDEEP
sqlsql-serveracid
Lu4
Lu4

Reputation: 15032

When updating two columns of one record, is such change is atomic?

The question itself is mostly defined in the subject, I'm trying to update two columns of a record with similar statement:

UPDATE SomeTableName
SET Field1 = 1,
    Field2 = 2
WHERE ID = 123;

Is that change is atomic or not in SQL Server? In simple words, if power outage occurs during middle of update operation (or any other catastrophic event), does it mean that I can end up with only one field updated?

Upvotes: 1

Views: 120

Answers (1)

Gary Walker
Gary Walker

Reputation: 9134

In theory ALL transactions are atomic -- can't guarantee no possible bug in sql server could break this.

If you don't speficy an explicit transaction, each statement is its own transaction.

Power failures, etc. don't cause a problem because the transaction log is applied on restart.

ADDED

Re: comment about prior question 21468742

Sorry, I don't think so -- a lot to read there, but I saw nothing violating atomicity there, it appeared to be a confusion of atomicity and isolation. And I see that Martin Smith came to the same conclusion. Think of it this way, when you update stuff like this you are updating a disk block by rewriting the whole block (or database base page). With a log and commit architecture the whole block is written and committed, or none of it is. In case of P/F the last good write is known, and if a failed write happens and it not marked complete it is not applied to the database from the tranlog on restart.

Upvotes: 2

Related Questions