Does READ COMMITED commited blocks any rows?

Question

I know that there are four diffrent types of isolation levels in databases. I've read many articles about that but still, I don't get the idea of Read Commited level. Does this level lock the rows which we are trying to get?

For example:

Transaction 1 - READ COMMITED

begin;

select * from table <- Is this query locking anything?

update table set id = 2 where id =3;  

Is this query locking anything?

If after this query will be another query from transaction REPETABLE READ will we be able to execute select from second transation?

Answer 1

Locks is a way to implement isolation levels, exactly what gets locked and not is implementation specific. So it may be better to think in terms of what the isolation level guarantees. READ COMMIT guarantees that there are no dirty reads. I.e. we won't see uncommitted data that is later on rolled back. However, both Nonrepeatable reads and Phantom reads are possible. Non-repeatable means that if we do:

BEGIN T
select * from tbl -- s1
select * from tbl -- s2
END T

s1 and s2 is not guaranteed to return the same result, i.e. it is valid for another transaction to delete or update from tbl so that s1 and s2 differs. Phantom read typically means that another transaction inserted a row between s1 and s2.

Depending on the implementation, some rows may be looked during REPEATABLE READ, but probably not all.

Db2 as an example (not considering CURRENTLY COMMITED here), locks a row that matches the predicate with a Shared-lock[1] (in the example there is none so all row matches) until it finds a new row that matches the predicate. This may be different than how it is implemented in other DBMS, so you should check out what mechanism the DBMS you have in mind is using.

[1] Shared means that other readers can read the row, but writers must wait