Commit and rollback commands in Oracle Database

Source: Internet
Author: User
Tags savepoint

Here I will only briefly introduce these two commands, but they are used in the process of use. So I will mention them here. If you are interested in this, you can search for more introductions. Please share them ~~

A Database Transaction starts with an SQL statement and ends with one of the following four events:
COMMIT or ROLLBACK statement
DDL/DCL implicit commit
Automatically submitted upon exit
Cancel the transaction when the system is forced to close
After the transaction ends, the next SQL statement starts a new transaction.

Statement Function
COMMIT ends the current transaction and submits all data changes to the database for effectiveness.
SAVEPOINT name creates a storage point in the current transaction.
ROLLBACK [to savepoint name] the ROLLBACK command ends the current transaction and cancels all data changes. Rollback to savepoint name discard the save point and cancel all data changes after the save point

Implicit transaction commit:
A transaction will be automatically submitted in the following circumstances
DDL statement
DCL statement
The user exits the database normally, but does not have an explicit COMMIT or ROLLBACK.
A transaction will be automatically rolled back in the following circumstances
The user unexpectedly exits the database.
Forced system shutdown
Data status before COMMIT/ROLLBACK:
All data changes in the transaction will be temporarily stored in the database buffer pool until the transaction is committed or rolled back. Therefore, the previous state in the transaction can be recovered.
You can use the SELECT statement to query the data changes that you initiate, even if the transaction is not committed. However, you cannot see data that has not been committed when another person initiates a transaction.
The change data in the transaction initiated by the user will be locked by the database system. Other users cannot change the locked data until the locked data is submitted or rolled back.
Status of data after COMMIT:
Data changes take effect in the database
Data cannot be restored
All users can see the change data
The data lock will be released, and the locked data can be modified by all users again.
All save points are cleared.
Status of data after ROLLBACK:
All data changes are canceled, and the data status is restored before the start of the transaction.
The data lock will be released, and the locked data can be modified by all users again.
Roll back to a save point:
Use the SAVEPOINT statement to create a save point. If the save point name already exists, the new save point overwrites the old one.
Use the rollback to savepoint statement TO roll back TO the existing storage point status.

Summary:
1. Data not submitted (commit) cannot be rolled back after deletion
2. You can use rollback to restore the committed data after it is deleted.
3. After data is deleted and submitted (commit), rollback cannot be used for restoration.

The above is only used in the process. I just searched and shared it with you. I will share more information later. By the way, there is another point: no commit after the delete command is used, insert or other operations in the program will remain in the running state, but it will not appear after commit ~

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.