SQL Server : does an CTE update work like a transaction?

Question

I use a query like this to update some columns of a table:

WITH MyTable AS
(
    SELECT TOP 1 
        *, 
        ROW_NUMBER() OVER (ORDER BY T.dtDate ASC) AS RowNum
    FROM 
        important.Table T
    WHERE 
        bWorking = 1
)
UPDATE MyTable
SET iIDfkToOtherTable = 6
WHERE RowNum = 1

There are two queries in this statement (SELECT and UPDATE), so I'm not sure if another user will be able to change the "important.Table" values while I am between the SELECT and the UPDATE. Can someone give me a clue? Thanks!

Could you provide some sample data and exepct result? – D-Shih Aug 27 '18 at 21:24 — D-Shih, Aug 27 '18 at 21:24
There is only one query here. – Ivan Starostin Aug 28 '18 at 04:55 — Ivan Starostin, Aug 28 '18 at 04:55

score 4 · Accepted Answer · answered Aug 27 '18 at 21:34

4

It is essentially a single query for the sake of atomicity. It would be essentially the same as UPDATE ... FROM in those terms:

https://stackoverflow.com/a/2334741/84206

In fact here's a similar but different question that suggests such a CTE for accomplishing atomicity in lieu of a two step select/update:

https://stackoverflow.com/a/42470136/84206

answered Aug 27 '18 at 21:34

AaronLS

37,329
20
143
202

Thanks. I'm dealing with this because I use two bots saving and retrieving data in the same table and they are always deathlocking. I'll keep cheking it. – DSantiagoBC Aug 28 '18 at 21:24
@DavidSantiagoBermdezCaldern I don't want to get into extended offtopic discussion, but usually if you had deadlocking due to a "select then update" type of problem, what you see is two queries with a shared lock trying to be escalate to an exclusive lock for the update. This article is old but a good starting point, there's still reports and different ways to view transaction lock types(exclusive/shared): https://support.microsoft.com/en-us/help/224453 – AaronLS Aug 28 '18 at 21:29

score 0 · Answer 2 · answered Aug 28 '18 at 00:14

0

Don't think of it as two queries. That's procedural thinking. SQL Server is set based, and in this case you are defining an end state "This set of data will have this transformation applied".

As a single definition it will always be atomic.

answered Aug 28 '18 at 00:14

TomC

2,759
1
7
16

SQL Server : does an CTE update work like a transaction?

2 Answers2