Oracle programming Art Study Notes (16)-transaction persistence

Generally, after a transaction is committed, its changes are persistent. Even if the database crashes after submission, the changes are permanently stored in the database.
However, there are two exceptions:
1) Use the WRITE extension added by the COMMIT statement
New Features of version 10gR2 and later, including the following two options
· COMMIT [WORK] write wait is equivalent to COMMIT. You need to WAIT for the submission to complete (the physical WRITE operation to the REDO log file), that is, synchronous COMMIT.
· COMMIT [WORK] write nowait is returned without waiting for the submission to complete. Physical WRITE operations are performed in the background, that is, asynchronous COMMIT. Therefore, durability cannot be guaranteed.
In synchronous submission, Oracle will request LGWR to write the REDO logs in the buffer zone to the online REDO log file. This is a physical write operation, which takes a lot of time, it even takes more time than the DML statement in the transaction.
If it is an asynchronous COMMIT, you do not need to wait for the physical write to complete, which can greatly improve the performance, especially when there are a large number of COMMIT.
Asynchronous submission is only applicable to applications that can be automatically restarted when a fault occurs, even if the committed application is not permanently stored in the database. For example, batch processing applications.

2) execute COMMIT in non-distributed PL/SQL code blocks
The PL/SQL code block uses asynchronous commit transparently. The COMMIT statement in the PL/SQL code block does not wait for the physical I/O to complete, but waits for the physical write to the REDO log file to be completed before the PL/SQL returns to the customer's application, that is, no matter how many COMMIT statements exist in the PL/SQL code block, PL/SQL only waits once.
After 11 GB, you can call commit write wait to WAIT for the submission to complete.

 

 

Author: NowOrNever