cofactor
asked on
what is Non Repeatable Read ?
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Then case 2 becomes similar to case 1.
ASKER
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 ?
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 ?
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.
ASKER
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.
>>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.
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 ?
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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>>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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>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 .
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 .
ASKER
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 .
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 .
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.
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.
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
related threads are great for asking follow-up type questions by the way...
Glad we could help you.
Best regards and happy coding,
Kevin
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.
>> 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.
ASKER
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 .
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 .
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.
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.
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.
>> 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.
ASKER
>>>>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_REA
Connection.TRANSACTION_REP
..........................
..........................
Experts Points increased to 500 for further discussion.
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_REA
Connection.TRANSACTION_REP
..........................
..........................
Experts Points increased to 500 for further discussion.
>>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
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
>> 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.
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.
ASKER
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.