How can a transaction view uncommitted changes made by another transaction in UNCOMMITTED READ

Question

I know this is probably a silly question, It is my first, It seems so fundamental that I can't find the answer anywhere, because it must be so straight forward no one explained it. I want to thoroughly understand how transactions work and in particular isolation levels and especially UNCOMMITTED READ isolation level and dirty read.

There is some contradictory information, All these links below from official documentation:

I read many online resources, some of these: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

https://medium.com/inspiredbrilliance/what-are-database-locks-1aff9117c290

https://docs.oracle.com/cd/E17277_02/html/TransactionGettingStarted/isolation.html#dirtyreads

say something along the lines of:

A dirty read is when one transaction can view data modified by another transaction that has not yet been committed, But how is this possible & what does this exactly mean ?

But this is contradictory, If I see the PostgreSQL documentation here: https://www.postgresql.org/docs/current/tutorial-transactions.html

It says:

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

And here:

Another important property of transactional databases is closely related to the notion of atomic updates: when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others. For example, if one transaction is busy totalling all the branch balances, it would not do for it to include the debit from Alice's branch but not the credit to Bob's branch, nor vice versa. So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously.

I thought transactions are autonomous & all or nothing, so how can an update statement for example in the middle of a transaction before commit be visible to another ? After all, two or more transactions may even have their own threads/processes in the case of PostgreSQL, so how can they view data before commitment ?

Does this mean when we run individual DML statements inside a transaction they update the rows before commit and then roll back if failed ? Does this depend on how the locks ? Because this is the only way I can think it could work

As far as Dirty reads are concerned, Is a Dirty read the value in the original row that has been selected by another transaction that may modify it or is it the data inside a transaction that has been updated but not committed yet.

I am not geting something very fundamental about this

For example:

Consider this basic example table:

CREATE TABLE TransactionEg (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR,
    x INT,
    y INT,
    CONSTRAINT TransactionEgPk PRIMARY KEY(id)
);

INSERT INTO transactioneg (name, x, y)
VALUES
('Row 1', 5, 11),
('Row 2', 7, 23),
('Row 3', 11, 36);

If transaction 1:

BEGIN;
SELECT * FROM transactioneg T WHERE T.id = 1;
UPDATE transactioneg T SET x = x + 5 WHERE T.id = 1;
COMMIT;

At more or less the same time or just after the SELECT statement in the 1st transaction, transaction 2:

BEGIN;
SELECT * FROM transactioneg T WHERE T.id = 1;
UPDATE transactioneg T SET x = x + 10 WHERE T.id = 1;
COMMIT;

Transaction 1 would update the value of x to 10 from 5, So does transaction 2 see 10 before Commit ? Or does it just get 5 as thats the original value before commit and the 5 becomes the dirty read ?

Any advice would be appreciated, although I expect I might get downvoted for this question, I hope not.

Answer 1

I thought transactions are autonomous & all or nothing, so how can an update statement for example in the middle of a transaction before commit be visible to another ?

Unfortunately, you are noticing the discrepancy between the ideal goals of a transaction control system, which is to facilitate the full isolation of concurrent transactions from one another, and the practical reality of how mainstream SQL database engines and supporting hardware are generally engineered work, and the facilities the engines need both to perform well enough in normal use, to perform adequately under degraded circumstances, and to be supervised adequately.

The READ UNCOMMITTED isolation level is widely regarded as having no legitimate use in the normal operation of a well-designed database application.

It's used for two reasons. One is when dealing with an adaptation to (or ad-hoc query from) an existing application whose existing design precludes the use of the higher isolation levels for performance reasons, and it is considered more important that the new query run at the highest speed possible and with the least prospect of interference in the progression of other transactions, than that the results from the unisolated transaction be theoretically free of defects.

A programmer with a deep understanding of a particular application may be capable of manually reasoning what defects may occur in the circumstances and how frequently, and accepting them as tolerable for the purpose. This might occur when the data informs regular decisions that are individually low-stakes and can sometimes be wrong.

More often, this mode is (mis-)used excessively by incompetent developers to crudely solve performance problems in database applications (typically problems their own work has created), which could usually be solved differently with modest amounts of ordinary design work and standard competencies.

Another reason for using this isolation mode is when some kind of supervisory action is occurring and that supervisor wishes to completely disapply the isolation offered by the transaction manager (for example, this can be to inspect the progress of other concurrent transactions that are themselves running under stronger isolation guarantees).

To properly get to grips with it all, my advice would be to be prepared for a considerable amount of complexity, as the full details do not reduce to simple answers, and details can differ between brands of SQL engine and between different operating modes which those engines provide.

Most practitioners in this area accept that the situation is not good from the perspective of theory or teaching concerns.

Answer 2

A dirty read would be seeing uncommitted data:

-- transaction one
START TRANSACTION;

INSERT INTO tab VALUES ('something');

               -- transaction two
               SELECT * FROM tab;

-- transaction one
COMMIT;

If transaction two sees the uncommitted insert, that would be a dirty read.

Different database management systems implement the SQL standard's isolation levels differently. The SQL standard defines that dirty reads are “possible” on the READ UNCOMMITTED isolation level, but it does not say that you are guaranteed to get dirty reads. PostgreSQL uses that loophole and “upgrades” you to READ COMMITTED when you request READ UNCOMMITTED.

That's not a problem, because nobody really wants to see dirty data. The database management systems that allow you to see dirty data do so because they are using read locks, and you need READ UNCOMMITTED to avoid taking these read locks. In essence, you are sacrificing consistence for concurrency. In PostgreSQL, readers never block writers and vice versa, to there is no need for dirty reads.