{Urgent} what could be the probelms with unrepeatable read , Second lost updates problem ,Phantom read

 unrepeatable read ---- I understood it means A transaction reads a row twice and reads different state each time. For example, another transaction may have written to the row, and committed, between the two reads. ---- but why it is a problem , ofcourse  always the updated data is best to work with.

  Second lost updates problem ---two concurrent transactions both read a row, one writes to it and commits, and then the second writes to it and commits. The changes made by the first writer are lost  .... but why does it matter the changes made by first writer , anyway those will be lost after some point of time.

  Phantom read-A transaction executes a query twice, and the second result set includes rows that weren't visible in the first result set ----- same question as unrepeatable read.

  Please brief me with the use cases ,if any.
sukh54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
>> lost updates
This needs careful sequencing of your programs.

>> why does it matter the changes made by first writer , anyway those will be lost after some point of time.
"Changes are not always lost at some point of time."

Lets see this scenario, you have a commission column. You have Two programs(transactions) updating the commission value. One program updates commission based on the existing commission values. And another updates all commission values.

Program 1 rule is - if existing commission is > 1000 then add 500 to the commission else add 10 to commission.
Program 2 rule is - Add 100 to all the commission values.

I have one record with commision value 950.

Say you are allowing phantom reads Any one of the following cases can happen.

Case 1
transaction 1 checkes commission value, 950 is not >= 1000 so adds 10 - commission becomes 960
transaction 2 adds 100 to commission - final commission is 1060

Case 2
transaction 2 adds 100 commission becomes 1050
transaction 1 sees that the commission value is >= 1000 so it adds 500 - final commission value becomes 1550


You can see that the changes are not lost, but in fact it resulted in totally inconsistant results.

Now lets see the above scenario with the cocurrency control.
program1 and program 2 are running concurrently, both are seeing the value of commission as 950.
Program 1 updates to 960 (Within the transaction, haven't committed. Other transaction is not seeing it yet)
Program 2 updates to 1050 (Within the transaction, haven't committed. Other transaction is not seeing it yet)
Depending on which program commits second that will become the value of the commission in the table. Again in consistant results!!!!

The problem lies with the sequencing of the application logic. Database doesnt know what the application does. It just follows the commands you are instructing it to do.

If you allow phantom read, the result could be anything, you cannot predict.
On the contrary if you have concurrency control(read consistant data), you can sequence your programs accordingly. So that the end results are always consistant.
0
sukh54Author Commented:
I think I got confused ...... but I believe that these are the problems that arise from the schedules which are not serializable. So .... is it that the given schdules are not serializable .... ( I think serializable means ,  results of the transactions when executed in interleaved should be equal to "some" schedule that had the transaction in order.
0
schwertnerCommented:
unrepeatable read> is a problem, because the application will get wrong data and the decission made will be wrong,.
In Oracle it is easy to avoid this changing the status of the Reader to Writer:
SELECT  .... FOR UPDATE;
If other application has also read the rows to change them, the first application will get message that they are locked.


  Second lost updates problem > also very bad thing! Available tickets are 140. The data will be inconsistent. Process A sells 20 thickets, the remain thickets are 120, process B sells 50 tickets, the remain tickets are 90. 90 is recorded, but this is wrong. In fact they should be 70 ... The same cure as above ---> locking the rows!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sukh54Author Commented:
schwertner ,
  In the Second lost updates problem , is it that  the lock ,which is used to avoid this problem , is being held by A till A is committed so that B will not access it and B should be able to access 120 and not 140.
So the Lock should be on READ also , right ?
0
schwertnerCommented:
B should explicitly say that it reads the data for update. In this case it will get message that the row is locked.
In Oracle writers do not disturb readers, but prevent other writers from accessing the rows that will be or are updated until the rows are comited.
0
nag9s2007Commented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.