Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article
But they are advanced for my understanding. I mean both appear to be same i.e. saving data like a "Save" action. If we rollback, we can rollback only to the most recent checkpoint/commit. Am I right in my statements? Can someone explain?
A commit is a user initiated action that tells the database (Oracle in this case) that the transaction is completed and that the changes may be committed and any locks/resources released. Normally the changes are committed to in memory data buffers and to the redo log buffer.
A checkpoint though is a database initiated action that writes all of the data to the actual physical disk file based on the changes recorded in the redo log buffer. Some good articles can be found here and here.
Commit: its an action performed by user to let database instance know that transaction is completed and correct, and it can not be rolled back.
Checkpoint: after performing a commit, instance will perform action to change system change number (SCN) that already committed to store it (data ) in a block(s) and datafile(s), and SCN in control file(s). and it can be used in rollback.
Note: checkpoint may happened anytime
To complete some of the previous answers:
A commit will end the current transaction in the current section. It will guarantee the consistency of the data that was "touched" during the transaction.
A checkpoint writes all commited changes to disk up to some SCN that willl be kept in the control file and datafile headers. It guarantees the consistency of the database.
EDIT 1
As some of the comments point out that the concepts aren't completely clear I will provide some more information here.
We have 3 major structures involved in the commit and checkpoint concepts.
So changes are made to blocks kept in the Database Buffer Cache (DBCache). Once commited, the changes are pushed to the Redo Log Buffer (RLB) which is dumped on a regular basis to the Redo Log Files (RLF) and, eventually to the database storage files (DF).
Also on a regular basis and not completely unrelated to commit, the checkpoint process dumps the dirty blocks from the DBCache to permanent storage DF. During the checkpoint process the SCN associated with the latest DB block written to storage is written on the DF headers and the control file. That will be from that moment on the latest consistent estate of the database.
