4

I'm trying to better understand 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?

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

5 Answers5

5

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • When I'm talking about will the rows be in order, I'm referring to the sequential values I'm writing, and not the physical order of the rows. In other words, if no one else is accessing the database, then I am guaranteed to have sequential values. The concern is if someone else comes along to modify one or more of the rows during my update. – Jonathan Wood Jul 11 '11 at 20:30
  • Your update is not instantaneous. The 1000 updates will be 'spread' in a timeline, no matter how you do it. Before any write (insert, delete, update) SQL Server will lock the row being modified with an exclusive lock. This exclusive lock is always held until the transaction commits (or rolls back) and prevents any other transaction from modifying that row. But, as I said, this does not necessarily guarantee that the result will be 1000 sequential values. – Remus Rusanu Jul 11 '11 at 20:34
  • 1
    There are some detail about the locking: locks could be escalated to page/partition/table exclusive lock, the lock may apply to the key of the row not to the row itself and other finer points. We should not enter into these details now though, my explanation above should basically cover your case. – Remus Rusanu Jul 11 '11 at 20:36
4

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".

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I suspect he is asking, if his update commits is he guaranteed the rows will be sequential (i.e. they were locked from other processes until they all completed) – JNK Jul 11 '11 at 20:20
  • Well, maybe atomic is the wrong word then. I'm trying to find out if I'm guaranteed no one else will modify any of the affected rows while I'm committing my transaction. As far as *how* I make them sequential, that's not really important. If it help, one approach would be to loop through 1,000 UPDATE queries. – Jonathan Wood Jul 11 '11 at 20:24
  • @JNK: Yes, that's exactly what I'm asking. – Jonathan Wood Jul 11 '11 at 20:24
  • @Jonathan Wood: Your desired functionality is called "locking". You can do that either pessimistically using table/etc locking, or optimistically using a column to allow the first update to occur. – OMG Ponies Jul 11 '11 at 20:26
  • @OMG Ponies: Yes, I'm familiar with locking. I'm asking about a specific point related to transactions. I guess, put yet another way, does a transaction lock all related rows when it's committed? – Jonathan Wood Jul 11 '11 at 20:28
  • @jonathan - the answer is "maybe" - depends on your server-level settings. – JNK Jul 11 '11 at 20:28
  • if you really need a sequential numbering you should look at adding a field that you can control the numbering of...if you are using an auto-incrementing field there really is NO way to guarantee sequential numbering – Leslie Jul 11 '11 at 21:29
  • 1
    @Leslie: No, rolling your own autoincrement is not as safe as using the database alternative (thankfully, sequences are ANSI now & SQL Server supports them in the next version) – OMG Ponies Jul 11 '11 at 23:05
  • @OMG - I wasn't suggesting replacing the auto-incrementing with a user-controlled ID, I was suggesting adding another field to the table in addition to the existing auto-incrementing that the user could muck up all he wanted in trying to keep a sequential numbering... – Leslie Jul 12 '11 at 19:25
  • @Leslie: I don't know why I'm having so much difficulty conveying what I'm doing here. Yes, I have my own field. That's why I said `updating one of the columns to have the values 1 through 1,000`. – Jonathan Wood Jul 15 '11 at 13:00
2

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.

Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32
2

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:

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • You may be right but your first point seems unrelated to what I'm talking about. Let's say, instead of storing sequential values, I'm instead going to write the same value to a column in 1,000 rows. When I'm done, do those rows contain the same value in the updated column? Well, not if another user was writing to one of those rows during the middle of my operations. So what if I write the same value to 1,000 rows as part of a transaction? – Jonathan Wood Jul 12 '11 at 02:29
  • All 1000 rows are exclusively locked for your write: no-one else can change them until you are complete or rolled back. This is the Isolation in ACID. In your example, it depends who gets there first. One of you will overwrite the other, but your separates updates are both atomic and exclusive. For a table with >1000 rows, then different processes can update different rows (unless lock granularity or isolation level is changed to prevent this eg table lock: out of scope though here because this would *not* be default behaviour) – gbn Jul 12 '11 at 02:46
  • Just to be clear, I'm not explicitly creating a lock. You mean as a result of the transaction, correct? If so, looks like the answers to my question include "yes", "no, and "maybe". :-) – Jonathan Wood Jul 12 '11 at 03:16
  • The txn implied by the update, yes. All dml uses locks (selects can be changed to have dirty reads (out of scope here) – gbn Jul 12 '11 at 03:21
0

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.

Mark
  • 1,058
  • 6
  • 13
  • I'm asking about them being sequential at the very moment the transaction has been committed. Yes, obviously someone could come along and change them later. – Jonathan Wood Jul 11 '11 at 20:25