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 not been 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.