3

Is the following statement an atomic operation?

UPDATE [table1]
SET column1=@val1, column2.WRITE(@val2, NULL, 0) WHERE table1_id=@id

or should it be placed inside a transaction to ensure that none or both columns are updated in case of error situations?

Columns are defined as:

column1 int not null, 
column2 varbinary(max) not null
criticalfix
  • 2,870
  • 1
  • 19
  • 32
kuniklo
  • 31
  • 1
  • Yes, it's atomic. It doesn't matter whether it's 1 row or 1 million, 1 column or 20. INSERT, UPDATE, DELETE are all atomic operations. – StrayCatDBA Apr 16 '13 at 07:20
  • A single update statement is itself atomic, no need to put it in a transaction. – Furqan Hameedi Apr 16 '13 at 07:21
  • @Furqan: if you want to be able to roll it back, then putting it into an explicit transaction does make sense. –  Apr 16 '13 at 07:28
  • Someone should answer this question, and say where the docs say that an UPDATE is atomic. I would upvote. – John Saunders Apr 16 '13 at 07:46
  • @a_horse_with_no_name , I explicitly mentioned that `a single statement` , if you need to execute a single update and roll it back, then what would be the use of it except for testing? – Furqan Hameedi Apr 16 '13 at 07:50
  • @Furqan: exactly that: testing and verifying that everything was changed correctly. What if you missed a condition in the `WHERE` clause? It might not be that important for an UPDATE statement, but for a DELETE statement this very important I think. –  Apr 16 '13 at 07:52
  • 1
    Thanks, so using .WRITE clause does not change the atomic nature of an UPDATE statement then. – kuniklo Apr 16 '13 at 07:59

2 Answers2

2

It is one statement. In SQL Server, each statement carries an implied transaction. Here is another answer if you'd like more details.

What does a transaction around a single statement do?

Community
  • 1
  • 1
tommy_o
  • 3,640
  • 3
  • 29
  • 33
1

Here is an MSDN reference on Autocommit Transactions, which states that:

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.

Modifying Large-Value (max) Data in ADO.NET using UPDATE .WRITE is not documented to affect the UPDATE statement transaction.

criticalfix
  • 2,870
  • 1
  • 19
  • 32