Some clarifications on different Isolation level in database transaction?

5
votes

Below is the statement written from Wikipedia's Isolation article about REPEATABLE READS

In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so the phantom reads phenomenon can occur (see below).

My question here is when does the the transaction begin and end respectively.

If we take the example of Non-repeatable reads with REPEATABLE READS Isolation level at the same link , as per my understanding trnsaction 1 begin when first query is fired i.e SELECT * FROM users WHERE id = 1. DBMS will keep the lock on the users table until and unless transaction gets end. here By end I mean is when connection gets rolledback or commited not on the completion of SELECT * FROM users WHERE id = 1. Till that time Transaction 2 will wait Right?

Question 2 :- Now if we consider the isolation level and thier behaviour as given below (at the same link)

Isolation level     Dirty reads   Non-repeatable   Phantoms
Read Uncommitted    may occur     may occur        may occur
Read Committed      -             may occur        may occur
Repeatable Read     -             may occur        -
Serializable        -             -                -

As per my understanding Most reliable is Serializable then Repeatable Read and then Read Committed but still i have seen aplications using Read Committed. Is that because of performance of Serializable and Repeatable Read is bad in comparison to Read Committed because in serializable it will be sequential and in case of transaction has to wait for release of lock by another transaction. Right? So to get best of all three we can use isolation level as Read Committed with SELECT FOR UPDATE (to achieve repeatable read).Not sure how we can achieve phantom read if we want , in case of read commited isolation level?

1
sqloracleconcurrencytransactionsisolation-level
See stackoverflow.com/questions/10935850/… for a discussion of SELECT ... FOR UPDATE – Gili
So to get best of all three we can use isolation level as Read Committed with SELECT FOR UPDATE - this is the approach of JDO persistence layers like Datanucleus. They provide a mechanism to control "SELECT FOR UPDATE" on a per transaction basis. I believe this approach will give the benefits of Serializable transaction lock mechanism when using "lower" transaction types. – marcolopes
Are you sure that "Repeatable Read" may occur in a transaction with "Non-repeatable" isolation level? In this article the table is different - oracle.com/technetwork/issue-archive/2010/10-jan/… – naXa

1 Answers

6
votes

Oracle does not support the REPEATABLE READ isolation level. However, SQL Server does - and it does place locks on all rows selected by the transaction until it ends (ie: it's committed or rolled back). So you are correct, this will indeed make other transactions wait (if they are updating the locked data) and can be detrimental to concurrency.

As for question 2: Yes, the higher the isolation level, the worse your concurrent transactions will perform because they have to wait for more locks to be released. I am not sure what you mean by "getting the best of all three" by using SELECT FOR UPDATE because SELECT FOR UPDATE will place row locks on all selected rows.

And finally, here's a quote from Oracle's manual on phantom reads:

[phantom reads occur when] a transaction reruns a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new hire. More data satisfies the query criteria than before, but unlike in a fuzzy read the previously read data is unchanged.

Reference: