0

Let's say I have the following situation:

T1:

Update Student Set GPA=(1.1)*GPA Where sizeHS > 2500

T2:

Set Transaction isolation Level Read Committed;

Select Avg(GPA) From Student;

Select Max(GPA) From Student;

If T1 runs in between the two select statements in T2, then I can clearly see that the first select statement will take the T1 updates into account, while the second select statement will not.

But I'm wondering if T1 can interlace itself entirely within the first select statement? I.E the first select statement will read half of the students in the pursuit of calculating Avg(GPA), acquiring and releasing row locks for every row as it goes along. Then T1 comes in, takes out the necessary write locks, updates what it wants, and commits. Then the first Select statement comes back and reads the other half of the students, now with the new updates. So potentially, we can't even calculate the Avg(GPA) properly even though it's a single statement, because half of the students we read have received their updated mark while the other half has not. It looks like this inconsistency is allowed to happen under Read Committed, but I just want to verify.

On a related note I was reading this page: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16, specifically a line under READ_COMMITTED_SNAPSHOT that says:

Page locks are released when the next page is read, and table locks are released when the statement finishes.

I'm wondering what it means by table locks? Of course I gather that it doesn't just stop all modifications on the table, but does it do something else like stopping inserts and deletions for the duration of the select statement?

1 Answer 1

2

All read committed guarantees is that you only read committed data.

It doesn't guarantee anything else.

With read committed locking isolation even if no GPA is updated at all it would still be possible for a scan to return inconsistent results (rows missed or read twice as they were moved around in the index during the scan).

Similarly it would certainly be possible for rows to be processed by the UPDATE in an ordering which meant that the scan sees some rows "pre-update" (before the update gets to them) and some rows "post-update" (after the update has committed).

You can avoid these issues by using the snapshot isolation implementation of read committed - this uses row versions to get a transactionally consistent snapshot of the data for each statement as at the time that statement started executing.

Regarding your question title...

This is not about "Atomicity" - the "A" in ACID. Atomicity just guarantees that a transaction either succeeds or is rolled back entirely. It is about "Isolation".

1
  • "Atomicity just guarantees that a transaction either succeeds or is rolled back entirely." - clearly what you mean by "transaction" is "a set of changes caused by one or more statements in the scope of the current transaction". That's much narrower than how most people understand the word "atomic", and it might be worth being explicit about that subtlety.
    – Steve
    Commented Dec 26, 2023 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.