1

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?

Lu4
  • 14,873
  • 15
  • 79
  • 132
  • 1
    [SQL Atomic Operation on UPDATE and DELETE](http://www.codeproject.com/Tips/314241/SQL-Atomic-Operation-on-UPDATE-and-DELETE) – PM 77-1 Aug 12 '14 at 00:47

1 Answers1

2

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.

Community
  • 1
  • 1
Gary Walker
  • 8,831
  • 3
  • 19
  • 41
  • Not strictly true, very good discussion here http://stackoverflow.com/questions/21468742/is-a-single-sql-server-statement-atomic-and-consistent – DavidG Aug 12 '14 at 00:47