Read Committed vs Read Uncommitted if both transactions do not rollback

Question

I am trying to understand read committed and read uncommitted isolation levels. I know that theoreticay read uncommitted allows dirty reads and read committed doesn't, but I still can't really understand.

Considering Figure above, if none of the transactions were aborted then the final result is the same for both read committed and read uncommitted?

Answer 1

Please find the link https://www.postgresql.org/docs/9.5/transaction-iso.html

I am re-writing

13.2.1. Read Committed Isolation Level

Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.

Answer 2

READ UNCOMMITTED allows you to read the dirty data that has not been committed by other transactions. SQL Server engine ignores any lock under the table being read and reads the data directly from memory.

READ COMMITTED will read the data that has already been COMMITTED but will wait if the data is being affected by other transaction.

So, in the example provided the system is not only reading but also trying to DELETE a row that has still not been COMMITTED, so, both will wait until the other transaction finishes so, the example is a typical example for DEADLOCK.

To ilustrate the differences between COMMITTED vs UNCOMMITTED I will show you a simple and clear example that we will run twice, in the two modes.

-- Query Window 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Prepare for first Run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Prepare for second Run

BEGIN TRANSACTION                                   -- Step 1
INSERT INTO Audit (ProductID, PrevValue, NewValue, ChangedBy)   
    VALUES (1, 'AAA', 'aaa', SYSTEM_USER);          -- Step 3
COMMIT TRANSACTION                                  -- Step 5

-- Query Window 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Prepare for first Run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Prepare for second Run

BEGIN TRANSACTION                           -- Step 2
SELECT * FROM Audit WHERE PrevValue = 'AAA' -- Step 4
COMMIT TRANSACTION                          -- Step 6

We have to run first the line for UNCOMMITTED LEVEL in both queries and then, go to first one, run Step 1, go to the second, step 2 and so on. In the UNCOMMITTED when we run Step 4 we will see the results inmediately as we are doing a dirty read (from memory). For second run, we will remove first the line test with:

DELETE FROM Audit WHERE PrevValue LIKE 'AAA';   

Then, will run the line for COMMITTED LEVEL in both query windows and will run the same sequence. We will observe that now, when we run Step 4 the system remains with no response. Just in the moment that we run Step 5 to commit the insert the window will show the results.

I hope that the question now is clearer.

Answer 3

Your example has nothing to do with Isolation Levels. This is because they affect readers behaviour, not writers, and in your example there are only writers.

You should refer this BOL article: Understanding Isolation Levels that says

Choosing a transaction isolation level doesn't affect the locks that are acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

In your example none of transactions read, they both modify. The first transaction will acquire X on interested RID or key (depends on table structure, if it is heap or clustered table) -- I'll call it res_1 in future -- for inserting and will hold it for all the duration of the transaction (it will also have IX on corresponding page and object), and the same is for the first statement of the second transaction: it will acquire X on res_2 when inserting.

On the DELETE attempt the second transaction will be blocked as it cannot obtain X (or U in case there is no index on where condition), this is because there is already X on the same resource (res_1) hold by the first transaction. And there will be no second INSERT in the second transaction because previous DELETE is blocked.

Finally, when the first transaction attempts its DELETE, it needs X or U (depending on index existence) on res_2, but it's already blocked with X by tran2, so it's also blocked and there is no exit from this situation, every session waits for another session to finish and no session can complete, at this point a deadlock occur and server will resolve it by rolling back one of the transactions.

Answer 4

If you work with read committed isolation level, T2 needs to wait on step 4 for T1 to finish and commit its work. furthermore T1 in step 6 cannot find Nome with Maria% thus, deletes 0 rows.

but on read uncommitted isolation level, both read/write operations can be done simultaneously.

Result For read committed isolation level,

Pessoas (Jaoa Silva, 96.....)
Pessoas (Maria Fon..., 9199...)
Pessoas (Joao Manuel Silva, 9699...)

whereas for read uncommitted isolation level

Pessoas (Joao Manuel Silva, 9699...)