Skip to main content
deleted 2 characters in body
Source Link

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

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

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

added 141 characters in body
Source Link

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

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 locks a row that matches the predicate (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.

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

Source Link

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 locks a row that matches the predicate (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.