To make permanent all changes made to a table, the user needs to commit these ch

Question

To make permanent all changes made to a table, the user needs to commit these changes. The instruction that allows a user to record these changes permanently into the database is the COMMIT statement. The basic syntax of this statement is this:

COMMIT [WORK];

Notice that the keyword WORK is optional.

You should be aware that prior to the execution of a COMMIT command, all changes made to the rows of a table are stored in a database buffer or working storage area in main memory. If, for some reason, the user quits the database before committing the changes, no data will be written to the database files and the changes will be lost. If the user making changes to a table is working in a multi-user environment and this table is shared by other users, no changes made to the table will be accessible to the other user unless the person making the changes issues a COMMIT command. This happens because whenever a user modifies the rows of a table, he or she has exclusive access to these rows until the changes have been committed. By "exclusive access of a row," we mean that no other user can view the current contents of the row that have been changed. The affected rows are said to be locked. At this time, any other user accessing the same table will not notice that the table has changed. When the user commits the changes, the modified rows are written to the database files and the locks on all affected rows are released. Users whose transactions started after the data was committed can view the modified rows with their new content.

Given these facts, answer the following questions:

1. If work is committed, but is incorrect, can it be changed? What are the implications for users and for system developers?

Explanation / Answer

Generally in ideal flow it’s not possible to undo a commit. But if database is using a backup mechanism then database state previous to commit can be restored from these back up files. Also there are some DBA tools which can replay data modifying queries from the logs and revert the changes back into the database.

            Whenever commit is executed, all the changes are made permanent and all the locks will be released which being held by the current user. Since it is a multiuser environment another issuer might access the incorrect commit data that makes system vulnerable. Before commit all the changes are exclusive because the resources must have been locked and changes must have been made in a non-shareable environment. But after commit all the incorrect data will be visible to all the users. This state is unsafe and system administrator must deploy recovery of the data.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.