Solved

what is Non Repeatable Read ?

Posted on 2010-08-22
22
866 Views
Last Modified: 2013-12-22
This is from wikipedia :  Transaction Isolation

Read Committed (Non-repeatable reads)

Also known as Inconsistent Analysis, non-repeatable reads may occur in a lock-based concurrency control method when read locks are not acquired when performing a SELECT. Under multiversion concurrency control, non-repeatable reads may occur when the requirement that a transaction affected by a commit conflict must roll back is relaxed.


Transaction 1                                                         Transaction 2
/* Query 1 */
SELECT * FROM users WHERE id = 1;
                                                                                  /* Query 2 */
                                                                                  UPDATE users SET age = 21 WHERE id = 1;
                                                                                  COMMIT; /* in multiversion concurrency
                                                                                  control, or lock-based READ COMMITTED */
/* Query 1 */
SELECT * FROM users WHERE id = 1;
COMMIT; /* lock-based REPEATABLE READ */



My question is : why Non-repeatable read  is called  inconsistent  ?  what is inconsistency here ?  Transaction 1's   second select query  shows the updated data  because Transaction 2 has updated the record . What is inconsistency here ? why would we call this inconsistent ?
0
Comment
Question by:cofactor
  • 9
  • 8
  • 5
22 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 167 total points
ID: 33495685
cofactor,

The data result set between the two iterations of query #1 are inconsistent with each other as the second is impacted by the update statement that occurs in between. When you use REPEATABLE READ then the lock would have been placed on all rows from the first query and not released until the COMMIT after the second making the result sets consistent with each other. The UPDATE would then execute after the lock is released.

So the consistency or lack of is speaking to the queries of the same data within same transaction.

Kevin
0
 

Author Comment

by:cofactor
ID: 33495814
I still have doubt . I'm not asking about lock or possible resolution . In fact my question was broad level .
I'm asking about  GENERAL definition  of "inconsistency" .


To be more clear,  Lets talk about TWO cases .Do we call these TWO CASES of  inconsistencies ?

CASE 1:
-----------------
Suppose ,
 first iteration of  select query #1   in   Transaction-A   return record  '25'  
 second iteration of select   query #1   in Transaction-A   return record  '21'  

Do we call this inconsistence ? I guess 'Yes'  ..because same Transaction A should give same results in EACH iteration but  here its not happening , we get different records and  so this is inconsistence.

Am  I right ?


CASE 2:
------------
Now, lets talk about a different case,

Suppose,
first iteration of  select query #1   in   Transaction A  return record  '22'  
second iteration of select  query #1   in Transaction A  return records  '22'  , '29'

Is it also inconsistence ?  Please note , second iteration has brought the earlier record '22' and it has also added one EXTRA record '29'  .....but my hunch is,  can we also call this 'inconsistent' ?  


please comment.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33495826
Case 1: correct!
Case 2: yes, because your result set has incremented by an additional row. In your example this is not that big a deal if looking at the '22' record as it has not changed, but imagine if we were getting:

first iteration of  select top 1 query #1   in   Transaction A  return record  '22'  
second iteration of select top 1 query #1   in Transaction A  return record '29'

Kevin
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33495830
Then case 2 becomes similar to case 1.
0
 

Author Comment

by:cofactor
ID: 33496106
agreed.

in fact ,I designed CASE 2 thinking in mind the PHANTOM read :)  ....in PHANTOM READ additional row comes because of INSERTS and then also there is a inconsistency .....anyway so far everything looks good.


I have read sometimes inconsistencies are also desired !  Could you please tell where we could use Non-repeatable reads ?  Do we use it in Theatre Ticket reservation system ?  I guess not .....but somewhere this inconsistencies are also desired . Thats why we don't resolve it . We put isolation level low. I'm wondering where this Non-repeatable reads are useful ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33496185
One scenario would be if you were say prepping a new table or just needed the table structure. You may read the table once with disregard for the actual data, but in hopes of getting the meta data to setup the remainder of code in the transaction and then at the end actually select the data. In such a case, you would want the most up-to-date info so you wouldn't want to lock out other DML transactions.
0
 

Author Comment

by:cofactor
ID: 33497840
I'm not clear.

>>You may read the table once with disregard for the actual data, but in hopes of getting the meta data to
how does the select query look like here then ?  this will be our query#1  ...right ?

>>setup the remainder of code in the transaction and then at the end actually select the data.
not clear what you meant here.

Could you please clarify bit more . I'm confused.


0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33498079
This could be something like this:

select pk, col1, col2, col3, col4, col5
into a_new_table_name
from your_table_name
where rownum = 1;

delete from a_new_table_name;

-- do some other operations

insert into a_new_table_name
select * from your_table_name;

Better ?
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 333 total points
ID: 33498556
Hi,

Remember that it is about 'isolation' level. So an 'inconsistent' read means that your transaction is not isolated from the others. For example in a booking system, if you have one record per booking, you do the following:
1. select to count the number of current bookings
2. select to get the maximum capacity. Subtraction gives the remaining free places
3. show 'there is remaining places to book. Do you want to book one ?'
4. the user says yes then insert a new booking

Between 1. and 4. you must be sure that there is no concurrent bookings from other users that reached the capacity, or you will do overbooking.

But there is more than that. Imagine that while you are at step 1. another user cancels a booking. And another one adds a new booking. With repetable reads, the count(*) will not change. But without repeatable reads, some RDBMS can give a result that can be +/- 1 because the query has read half of the rows before the concurrent change and the other half after the change. So the resultset is not consistent itself.

>> I have read sometimes inconsistencies are also desired !
Where did you read that ? They are not desired. If it is the case, then that means that you must not design that in only one transaction. But sometimes, you don't need repetable reads, and then you don't want to put unneeded locks.

A good explanation about that:
http://www.oracle.com/technetwork/oramag/2005/05-nov/o65asktom-082389.html

Regards,
Franck.
0
 

Author Comment

by:cofactor
ID: 33503503
>>>Where did you read that ? They are not desired.

Yes. They are sometimes.

see the details from Book "Bea Weblogic Server 8.1 unleashed"

CASE 1:

A dirty read occurs when a transaction is allowed to read data affected by another transaction before the other transaction commits. For example, assume you have a reservation system that shows all seats available in an amphitheater. Mr. X reserves four seats and pays with his credit card. Mr. Y wants to buy these same four seats, but they show up as being taken. Meanwhile, the credit card verification rejects the transaction and the seats are freed. This situation is acceptable, so dirty reads are allowed in this system.

CASE 2:

A nonrepeatable read occurs when a transaction attempts to select the same row twice, but a second transaction has modified or deleted the row in the interim. This situation might lead to unpredictable or incorrect results in the first transaction. In an auction Web site, a buyer sees that the last bid was $5. Another buyer posts a bid for $ 10 and commits. The first buyer posts a bid for $6. but this bid is rejected right away because there's already a higher bid in the system. During the second transaction, we checked the last bid and got the new value, although the transaction wasn't committed yet. There were two queries, yielding two different results, in the same transaction. This situation could cause trouble in other systems, but is required in the case of a bidding system.


well, I liked these examples .

but I am not happy with the naming "Dirty read"

"Dirty Read" also can be termed as non-repeatable read  in my opinion . why ?  because Dirty Read also returns different results in same select query  in same transaction as similar to non repeatable read.



0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 333 total points
ID: 33508686
Hi,

How could they say that CASE 1 is acceptable ?
They disallow Mr. Y to have seats that are free ! Imagine that you tell to Mr. Y you can't book those seats. And then Mr. Z comes just 1 second after, when Mr. X transaction has been rolled back, and is able to book those seats. Imagine Mr. Y seeing that ... furious !

The right, consistent way to do that is the repeatable read. Mr. X locks the rows. And Mr. Y either waits to see if Mr. X transaction is commited or not, or receives a message telling 'those seats are currently being reserved, try again later'

Same issue in CASE 2. The right way is to lock rows. Either locked as soon as the first buyer reads $5 (pessimistic locking - then second byer will wait) or when he updates $5 to $6 (optimistic locking - then he sees that value is not $5 anymore and fails).

Dirty read is 'dirty'  because it reads unconsistent data: data that never exist, data that only partially exist, data where integrity constraints have not yet been verified,...
And it is never required: each kind of buisness is running on an Oracle databases, and Oracle do not provide dirty reads. You just can't ask for unconsistent resultset.

>> "Dirty Read" also can be termed as non-repeatable read  in my opinion
No, 'uncommited read' is worse than non-repeatable read. The 'read commited' isolation level is a non-repeatable read but shows only commited data.

Regards,
Franck.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:cofactor
ID: 33517829
>And it is never required: each kind of buisness is running on an Oracle databases, and Oracle do not >provide dirty reads. You just can't ask for unconsistent resultset.

Ok...How this dirty read problem is resolved ?  Is it the programmer's responsibility or responsibility of the oracle database ?

I have also read further that  if we set TRANSACTION_SERIALIZABLE  as isolation level , then this problem could be resolved.

Question is :  
How its done ? is it  done by the java code   OR  its done by the Oracle Database settings ? I'm confused here .
0
 

Author Comment

by:cofactor
ID: 33517841
please comment on the above query.

Also

>>The right way is to lock rows

how it is done in code ? I'm using java - jdbc. I would love to see that .
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33517869
Franck is definitely the Oracle authority between us two, but hopefully this will help clear some of your remaining doubts and help support Franck's prior statement that Oracle doesn't allow "Dirty" reads.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10005.htm

You will note the syntax for set transaction isolation level and that there is only 'serializable' and 'read committed'; therefore, you can can't 'read uncommitted' which would be the way to get "Dirty" reads as explained earlier.

From Java you can set the isolation level as so:
http://download.oracle.com/javase/1.5.0/docs/api/java/sql/Connection.html

The API shows a constant for READ_UNCOMMITTED, but given the 10g documentation doesn't show that isolation level and Franck as an Oracle Expert indicates Oracle has no such animal I would tend to trust that and consider the inclusion of this constant just due diligence so this is consistent to other SQL connection implementations as well as in case it is added to Oracle at a later time. But even if it is, the point is set a more appropriate isolation level like serializable.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33517876
cofactor, think we have satisfied the original question, haven't you?
related threads are great for asking follow-up type questions by the way...

Glad we could help you.

Best regards and happy coding,
Kevin
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 33518563
Hi cofactor,

>> How this dirty read problem is resolved ?  Is it the programmer's responsibility or responsibility of the oracle database ?

The goal of dirty reads, as defined by SQL standard, is to provide a way to read data without locking (that is a performance issue, and that can lead to deadlocks), when consistency is not an issue. But Oracle provides consistent results even without locking (it uses multiversioning - ability to get commited data as of a single point in time) with a very lsmall performance overhead.

>> how it is done in code ? I'm using java - jdbc. I would love to see that.
The java code set the required isolation level, and it can do a 'select for update' to explicitely lock rows if it is needed. In your examples (case 1 and case 2) you need to lock rows to prevent concurrent updates befor you end your transaction. Using dirty reads for that is do not provide consistency and integrity that is usually needed for those kind of applications.

Regards,
Franck.


0
 

Author Comment

by:cofactor
ID: 33524226
I have read your comments. I'm in mixed understanding.

Here I'm posting  my understanding . please tell me if I miss anything

(i) oracle database is a smart database. you will never face "Dirty Read"  problem in your application if you are using oracle database as persistent storage. In other database you might face "Dirty read" problem but not in oracle.

 If your system need "dirty read" , then don't use oracle database, because oracle always provides committed and consistent data.

(ii)However, there is a settings in oracle  set_transaction::=  .  .....using this settings you can instruct oracle to stop phantom read and non repeatable read inconsistency if you want. how to set this can be found in the oracle  manual.

(iii) Well, if you don't want to do the settings from oracle as in (ii) , there is also a alternative way to set .
you can use java . java has a Connection interface . using this interface you can stop phantom/dirt read /non repeatable read etc inconsistencies.

but you should follow only one approach .



0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33524361
Do you need "Dirty" reads ?
I would not worry about it unless you need to use it then you can research it further with your particular Oracle system as the specifics may change by version.

http:#a33517869

It is up to you whether you want to do all your SQL code in Oracle and simply call procedures with the transactions in them OR whether you set the transaction isolation and code from Java. Agreed you want to pick the one that works best for you and stick with it to ease maintenance.
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 33526128
Hi,

>> If your system need "dirty read" , then don't use oracle database, because oracle always provides committed and consistent data.

No, your system never need dirty reads. When you use a database, you want consistency, isolation. You never need inconsistent, wrong results.

Isolation level can be set by sql statement (alter session). Jdbc has an api to do that, but it is the same.

Regards,
Franck.
0
 

Author Comment

by:cofactor
ID: 33528011
>>>>No, your system never need dirty reads. When you use a database, you want consistency, isolation. You never need inconsistent, wrong results.

OK . fine...yea true.....definitely I need the consistent result for my system.

I know the highest isolation level is  TRANSACTION_SERIALIZATION .  this can give  isolated consistent results . why do we need other isolation level then if we are really interested in isolated consistent results ? there are many isolation levels right ?

>>>Isolation level can be set by sql statement (alter session). Jdbc has an api to do that, but it is the same.

true. I did research further

in  ORACLE way:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED; (session level)
..........................................
.........................................

Interestingly , is not Oracle get these default settings when it is installed ?  


in  JAVA  way:
Connection.TRANSACTION_READ_COMMITTED
Connection.TRANSACTION_REPEATABLE_READ
..............................................
...............................................



Experts Points increased to 500 for further discussion.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528188
>>why do we need other isolation level then if we are really interested in isolated consistent results ? there are many isolation levels right ?

Because it depends on your specific application needs.  In some instances you may not care if other transactions are occurring to update or insert new rows as long as they are not touching the row you are working on so you may choose to do row level locking.

Similarly, there are instances where you may not want to do serialization transaction as you may be okay with using a lower transaction isolation in order to gain the benefits of multiple read/write.

Kevin
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 33528694
>> why do we need other isolation level

Because a high isolation level such as serialize may have drawbacks (locks on whole table - depending on the RDBMS) and because it is not always possible (error 'can't serialize access' in Oracle)


>> Interestingly , is not Oracle get these default settings when it is installed ?  
Yes, Oracle default is READ COMMITED because it provides consistent results without those drawbacks. And you can use 'select for update' to acheive repeatble reads even in this isolation level. Cases where you need SERIALIZE are very rare.

isolation level is always for a transaction, but the 'alter session' statement ca set it for all further transactions in the current session.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Via a live example, show how to take different types of Oracle backups using RMAN.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now