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 herhere), 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