5

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?

NullUserException
  • 83,810
  • 28
  • 209
  • 234
M Sach
  • 33,416
  • 76
  • 221
  • 314
  • See http://stackoverflow.com/questions/10935850/when-to-use-select-for-update for a discussion of `SELECT ... FOR UPDATE` – Gili Nov 28 '12 at 20:53
  • 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 Jun 10 '14 at 14:25
  • Are you sure that "Repeatable Read" may occur in a transaction with "Non-repeatable" isolation level? In this article the table is different - http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o65asktom-082389.html – naXa stands with Ukraine Dec 29 '15 at 13:48

1 Answers1

6

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:

Community
  • 1
  • 1
NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • " I am not sure what you mean by "getting the best of all three" Basically what i am trying to ask here is if we use read committed isolation level in oracle ,we will still get both non repeatable and phantom read issues.Right? As per my understanding first of all we should not call them as issues but these should be considered as correct behaviour because in between first transaction if second transaction commit then we should get updated data.Right? Continued... – M Sach Sep 15 '11 at 17:38
  • 1
    continued... Second question is that if we want to avoid non repeatable and phantom read issues with read commited on oracle, is there any way? As per me if we use select for update query we can be saved from non repetable reads .Coorect? But not sure how we can avoid phantom read? – M Sach Sep 15 '11 at 17:39