Read Committed vs Read Uncommited if both transaction 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?

Glisson

Hi and welcome to StackOverflow. Nobody is going to re-type your code, so, the image helps to understand the order but please, provide it also in text. – Jennie

there is nothing to be re-written here, the visual aid is fine and understandable and the code is just for explainin the context. ofcourse it will be better if OP includes markdown table. – Lens

Data modification statements acquire locks in both read committed and uncommitted isolation levels. So the results will be the same in your example. – Temperament

Answer 1

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

Miraflores

Answer 2

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.

Feminine

+1 the BOL link is pure gold. For a long time I didn't realize that write locks are always acquired (which confused me because I knew they were needed somehow) and even more important: that write locks are held until the end of the transaction. – Calorie

Answer 3

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.

Jennie

Uncommitted and "in memory" are not at all the same thing. It is entirely possible for uncommitted data to be written to disc at checkpoints or for committed data to be read from memory – Cahier

I haven't said that is the same... I just mean that for the example provided both have the same effect that is a DEADLOCK. Anyway, thanks for the comment, I will clarify my response... – Jennie

Answer 4

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.

Flake