Are transactions always atomic?

Question

I am trying to better understand the nuance of SQL Server transactions.

Let's say I have a query that updates 1000 existing rows, updating one of the columns to have values ​​from 1 to 1000. It is possible to execute this query, and when it is completed, these rows will not be numbered sequentially. This is because another request may change one of these lines before completing my request.

On the other hand, if I transfer these updates to a transaction, it ensures that if any update fails, I can complete all updates. But does this also mean that these lines will be guaranteed to be consistent when I finish?

In other words, are transactions always atomic?

Answer 1

2 points not connected

Sequential

If you insert values ​​from 1 to 1000, it will be sequential with WHERE and ORDER BY to limit you to these 1000 rows in some column. If there are no duplicates, you will need a unique restriction

If you rely on IDENTITY, this is not guaranteed: Insert records always get adjacent identification values .

Atomicity

All transactions are atomic:

• Do I need to encapsulate one merge statement (with insert, delete and update) in a transaction?
• SQL Server and Mid-Transaction Lost Communication
• Is it partially deleted if the delete statement is executed without a transaction?

Answer 2

But does this also mean that these lines will be guaranteed to be consistent when I finish?

No. This has nothing to do with transactions, because what you are asking for simply does not exist: relational tables are out of order when querying “sequential rows” is the wrong question. You can rephrase the question as "will 1000 updated lines contain the entire sequence from 1 to 1000, without spaces"? Most likely, yes, but the truth is that there may be gaps depending on how you update. These spaces will not appear because the updated lines change after the update before committing, but since the update will be no-op (no lines will be updated), this is a common problem such as updating to read-modify-write back (the line "disappears" between reading and writing due to parallel operations).

To more accurately answer your question whether your code is correct or not, you need to place the exact code with which the update is performed, as well as the exact structure of the table, including all indexes.

Answer 3

Atomic means an operation (s) within a transaction, either they occur or are not executed.

If one of the 1000 statements does not work, none of the operations within the transaction will be committed. The smaller the sample of operators inside the transaction - say, 100 - means that blocks of 100 that precede the error (say, at 501st) can be fixed (the first 400, block 500 will not, but 600 + blocks).

But does this also mean that these lines will be guaranteed to be consistent when I finish?

You will need to provide more context about what you are doing in the transaction in order to be "consistent."

Answer 4

SQL operations, such as transactions on all database platforms, put data in isolation to cover the entire reduction in ACID (atomic, sequential, isolated, and long-lived). So yes.

Answer 5

A transaction guarantees atomicity. That's the problem.

The problem is that after you enter the insert, they will only be “Sequential” until the next thing appears and touches one of the new entries.

If another step in the process requires that they are still consistent, then this step should also be in your original transaction.