|
|
| Author |
Message |
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 9
Location: delhi
|
|
|
|
| can anybody clarify this? |
|
| Back to top |
|
 |
References
|
 Posted: Mon Oct 22, 2007 12:02 pm Post subject: Re: we should not commit every time . why? |
|
|
|
|
| Moved: Mon Oct 22, 2007 12:31 pm by mcmillan From CICS to Interview Questions |
vasanthkumarhb
Senior Member
Joined: 06 Sep 2007
Posts: 300
Location: Bang,iflex
|
|
|
|
Hi Uma,
In DB2 there are isolation levels such as RS(Read stability) CS(cursor stability) RR(Repeable Read) UR(un commited read).
In these isolation levels there will be different levels of locks, for example: if you are using Isolation level of cursor stability in your application for retrieving rows or updating the rows, then lock will be retained on the last row retrieved during reading the rows and the row lock will be acquired only on the row which is being updated.
So if any other application accessing the same table and doing updation on any other row and at the same time you are attempting to commit the work then changes maded by your aplication and by other application also saved, if you want to avoid this u can go for other isolatioin level and commit the operatiion.
Regard's
Vasanth.......  |
|
| Back to top |
|
|
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 9
Location: delhi
|
|
|
|
| hi vasanth! thanks for ur response. but i think it is not accurate answer to my question. and i had askd about that why don't we use commit everytime? |
|
| Back to top |
|
|
enrico-sorichetti
Global Moderator
Joined: 14 Mar 2007
Posts: 2325
Location: italy
|
|
|
|
| Can You clarify, please, what You/the_interviewer means by "every time" |
|
| Back to top |
|
|
vasanthkumarhb
Senior Member
Joined: 06 Sep 2007
Posts: 300
Location: Bang,iflex
|
|
|
|
Hi,
Please specify your queestion in post rather more in subject line....
clarify your question once again.
Regard's
Vasanth....... |
|
| Back to top |
|
|
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 9
Location: delhi
|
|
|
|
we should not commit every time why ? and what we have to do?
for example you opened cursor and do some operations, and then you have one statement like if sqlcode=00 then commit else roll back.Then you will do further operations.
in these type of situations what you will do? |
|
| Back to top |
|
|
inamadugu
Warnings : 1
New User
Joined: 23 Aug 2007
Posts: 20
Location: Delhi
|
|
|
|
I think the question would be " why shouldn't we use COMMIT more often?".
Is it, Uma? |
|
| Back to top |
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 6756
Location: 221 B Baker St
|
|
|
|
Hello,
Typically a commit is issued at the end of a Logical Unit of Work (LUW).
The application process defines what a LUW is.
Commits at the end of an LUW are to provide a stable point from which a restart might be done.
| Quote: |
| I think the question would be " why shouldn't we use COMMIT more often?". |
More often than what?
| Quote: |
for example you opened cursor and do some operations, and then you have one statement like if sqlcode=00 then commit else roll back.Then you will do further operations.
in these type of situations what you will do? |
Please elaborate on what you want to learn. In your example, the code "keeps" the update(s) when the sql was successful and "backs them out" when the sql is not successful. Usually commit/rollback this is not coded after every sql statement. |
|
| Back to top |
|
|
uma rao
Currently Banned
New User
Joined: 20 Oct 2007
Posts: 9
Location: delhi
|
|
|
|
| Thank you d.sch. May be your way of explanation helps me to elaborate my view! |
|
| Back to top |
|
|
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 178
Location: Chennai
|
|
|
|
Uma,
This is what my DBA had as the answer
| Quote: |
This is because.....
Whenever a commit is done the data in the bufferpool is written to the disk.........
If frequent commits are made then it will be additional overhead to the system...........
bufferpool is like RAM in our PC...... |
|
|
| Back to top |
|
|
enrico-sorichetti
Global Moderator
Joined: 14 Mar 2007
Posts: 2325
Location: italy
|
|
|
|
| Quote: |
| Whenever a commit is done the data in the bufferpool is written to the disk......... |
I would say that Your DBA got things wrong..
Data Buffer flushing depends only on buffer management not on commits
DB2 will write log buffers, but, that' s all
DB2 is smart enough to apply changes ,
restarting after a failure, for committed data only..
and discard in flight updates |
|
| Back to top |
|
|
|
|
