Are Transactions Always Atomic?

I'm trying to better un开发者_如何学JAVAderstand a nuance of SQL Server transactions.

Say I have a query that updates 1,000 existing rows, updating one of the columns to have the values 1 through 1,000. It's possible to execute this query and, when completed, those rows would not be numbered sequentially. This is because it's possible for another query to modify one of those rows before my query finishes.

On the other hand, if I wrap those updates in a transaction, that guarantees that if any one update fails, I can fail all updates. But does it also mean that those rows would be guaranteed to be sequential when I'm done?

In other words, are transactions always atomic?

But does it also mean that those rows would be guaranteed to be sequential when I'm done?

No. This has nothing to do with transactions, because what you're asking for simply doesn't exists: relational tables have no order an asking for 'sequential rows' is the wrong question to ask. You can rephrase the question as 'will the 1000 updated rows contain the entire sequence from 1 to 1000, w/o gaps' ? Most likely yes, but the truth of the matter is that there could be gaps depending on the way you do the updates. Those gaps would not appear because updated rows are modified after the update before commit, but because the update will be a no-op (will not update any row) which is a common problem of read-modify-write back type of updates ( the row 'vanishes' between the read and the write-back due to concurrent operations).

To answer your question more precisely whether your code is correct or not you have to post the exact code you're doing the update with, as well as the exact table structure, including all indexes.

Atomic means the operation(s) within the transaction with either occur, or they don't.

If one of the 1,000 statements fails, none of the operations within the transaction will commit. The smaller the sample of statements within a transaction -- say 100 -- means that the blocks of 100 leading up to the error (say at the 501st) can be committed (the first 400; the 500 block won't, and the 600+ blocks will).

But does it also mean that those rows would be guaranteed to be sequential when I'm done?

You'll have to provide more context about what you're doing in a transaction to be "sequential".

The 2 points are unrelated

Sequential

If you insert values 1 to 1000, it will be sequential with an WHERE and ORDER BY to limit you to these 1000 rows in some column. Unless there are duplicates, so you'd need a unique constraint

If you rely on an IDENTITY, it isn't guaranteed: Do Inserted Records Always Receive Contiguous Identity Values.

Atomicity

All transactions are atomic:

• Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?
• SQL Server and connection loss in the middle of a transaction
• Does it delete partially if execute a delete statement without transaction?

SQL transactions, like transactions on all database platforms, put the data in isolation to cover the entire ACID acronym (atomic, consistent, isolated and durable). So the answer is yes.

A transaction guarantees atomicity. That is the point.

You problem is that after you do the insert, they are only "Sequential" until the next thing comes along and touches one of the new records.

If another step in you process requires them to still be sequential then that step, too, needs to be within your original transaction.