View previous topic :: View next topic
|
Author |
Message |
sharad_shanu
New User
Joined: 03 Oct 2006 Posts: 40
|
|
|
|
Hi,
I have a small query.
Q. Consider a high transaction density environment i.e. several users simultaneously updating data.
The requirement is to select some data; then update it after its validation.
SELECT Col1
FROM Table
WHERE Col2 = :given;
Perform validation on retrieved data: Col1.
UPDATE Table
SET Col1 = :value
WHERE Col2 = :given;
What could go wrong with the above 2 SQLs executed sequentially in a program?
And how to correct it using a cursor?
HINT: FOR UPDATE OF, WHERE CURRENT OF
That's all I have. Please help me out regarding this.
Thanks,
Sharad Srivastava |
|
Back to top |
|
|
ahalyah
New User
Joined: 13 Dec 2007 Posts: 25 Location: india
|
|
|
|
Hi,
if you use UPDATE OF clause then u can get the exclusive lock over the column and you can update it. By this time no othe person can update it untill you issue the commit.
Please correct me if i am wrong. |
|
Back to top |
|
|
sharad_shanu
New User
Joined: 03 Oct 2006 Posts: 40
|
|
|
|
Yes!...thank you.
But I am more concerned towards the first part of the query.
that is:
What could go wrong with the above 2 SQLs executed sequentially in a program?
I hope you would understand my requirement.
Thanks,
Sharad Srivastava |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
|
|
Hi Sharad,
Is this question from any question bank? If no, what happened when you have executed?
If you tell what type of problem you have faced, forum can provide possible solution. |
|
Back to top |
|
|
sharad_shanu
New User
Joined: 03 Oct 2006 Posts: 40
|
|
|
|
Hi,
This question was asked during a unit test and I was not able to anser.
That's why I am concerned about it.
Particularly the first part. (As written above)
Thanks,
Sharad Srivastava |
|
Back to top |
|
|
sharad_shanu
New User
Joined: 03 Oct 2006 Posts: 40
|
|
|
|
Can anyone answer my query??
Thanks,
Sharad Srivastava |
|
Back to top |
|
|
ahalyah
New User
Joined: 13 Dec 2007 Posts: 25 Location: india
|
|
|
|
Hi Sharad,
if you select the column without UPDATE OF clause then there may be chances to take exclusive access from some other during your select and update time. If somebody have taken the exclusive access then until they release it u cant update the column.
If u use it then nobody can take the access unless your process completes. |
|
Back to top |
|
|
sharad_shanu
New User
Joined: 03 Oct 2006 Posts: 40
|
|
|
|
Ok...it seems good.
Thanks to all for help.
Regards,
Sharad Srivastava |
|
Back to top |
|
|
|