Transaction control transaction control includes a plurality of synchronization access to the same data. When a user changed the data being used by another user, Oracle uses transaction to control any data. A basic unit of the business represents work is a series of SQL statements that are successful or unsuccessful as a unit. There are many statements in SQL and PL / SQL to allow programmers to control transactions. Programmers can: 1. Explicitly start a thing, select the sentence level consistency or transaction-level consistency 2, set the revocation rollback point, roll back to the rollback point 3, complete the transaction to change the data or give up the modification. Transaction control statement
Statement Use Commit Commit transaction, data modification successfully and opens the ROLLBACK revocation of other users, revoke all operations of Rollback to SavePoint Removal of the Roll Rolleout point after setting the SET Transaction response transaction or statement consistency; especially for transaction segment
example:
BEGINUPDATE checkingSET balance = balance-5000WHERE account = 'Kieesha'; INSERT INTO checking_log (action_date, action, amount) VALUES (SYSDATE, 'Transfer to brokerage', - 5000); UPDATE brokerageSET cash_balance = cash_balance 5000WHERE account = 'Kiesha'; INSERT INTO BROKERAGE_LOG (Action_Date, Action, Amount) Values (sysdate, 'Tracfer from Checking', 5000) CommitExceptionwhen OthersrollBackend
SavePoint and partial rollback (Partial Rollback) SavePoint in SQL and PL / SQL is an intermediate flag in a transaction range. It is often used to divide a long transaction into small parts. Reserve any point in the point SavePoint flag-long transaction, allows you to return to the operation after the point. SavePoint frequently in the application; for example, a process contains several functions, and a reserved point can be created before each function. If the function fails, it is easy to return to the start of each function. After rolling back to a SavePoint, the data blocked after the SavePoint is released. In order to achieve partial rollback, you can roll back the transaction back to the specified location with the ROLLBACK statement with the To SavePoint clause. example
BEGIN INSERT INTO ATM_LOG (who, when, what, where) VALUES ( 'Kiesha', SYSDATE, 'Withdrawal of $ 100', 'ATM54') SAVEPOINT ATM_LOGGED; UPDATE checkingSET balance = balance-100RETURN balance INTO new_balance; IF new_balance <0THENROLLBACK TO ATM_Logged; Commitraise Insufficient_funda; end ifend
Keyword SavePoint is optional, so the following two statements are equivalent:
Rollback to atm_logged; rollback to savepoint atm_logged;
Consistency and transaction consistency are key attractions to control. Mastering Oracle's consistency model can make you better, more appropriate use of transaction control. Oracle guarantees data by consistency only after the transaction is complete, it can be seen and used by the user. This technology has a huge role in multi-user databases. Oracle often uses state-level consistency, ensuring that data is visible between the life of the statement but cannot be changed. The transaction consists of multiple statements. When using transactions, transaction-level consistency ensures that data is visible to all statements throughout the transaction period. Oracle consistently consistent via SFEM CHANGE NUMBER. An SCN is a time-oriented database internal key. The SCN will only increase, and the SCN indicates a point on time, and each data block has an SCN, and the operation is performed by comparing this point implementation. One function of transaction-level consistency Set Transaction is to ensure that the transaction-level or a statement level is implemented. Oracle uses these terms: Isolation Level Read Commit Represents the uniform ISOLELSLALALALALALALALALAALIZABLE in the statement. Example: Set Transaction Isolation Level Read Commit; Set Transaction Isolation Level Read Commits
The following statement can also ensure that the transaction is consistent:
Set Transcation Read Only
Any an attempt to modify the data in a read only transaction will throw an exception. However, the Read Only transaction can only be used in the following statements:
SELECT (no for Update clause) Lock TableSet RolealTer SystemalTer Alarm
Even if you don't change any data, the read only transaction still must use a commit or rollback to end the entire transaction. Another application of SET Transction is directly using the Rollback Segment. The rollback segment is a special data object in Oracle, and the header of the roll band contains information that is using the returning segment transaction. When the user rolls back the transaction (ROLLBACK), Oracle will use the previous image in the rollover segment to restore the modified data to the original value. Oracle uses Round-Robin to randomly assign a rollback segment. A big transaction can allocate any rollback segments, which may result in a large size of the rollback segment. Therefore, we must avoid randomly allocate back segments. The transaction begins with SET Transaction, like this:
Set Transaction Use Rollback Segment RB_LARGE;
RB_LARGE is a name of a large rollback segment, and now a large returning segment is assigned a big transaction, and other small returns will not be managed by dynamic space, which is more efficient. Let's take an example. We have a split table space size of 2G, which requires 10 rebounds in the peak period to meet the needs of users, these peak online users have only small transactions. One week we have run four big matters, these transactions need to delete and load data, each withdraws 1G, and the size of the roll band is as follows:
rb_large (initial 100M minextenta 2) rb1 (initial 1M next minextents 5) rb2 (initial 1M next minextents 5) rb3 (initial 1M next minextents 5) rb4 (initial 1M next minextents 5) rb5 (initial 1M next minextents 5) rb6 (initial 1M Next MineXTents 5) RB7 (Initial 1M Next Mineltents 5) RB9 (Initial 1M Next Mineltents 5) RB10 (Initial 1M Next Minexts 5) All of the very appropriate arrangements in 2G tablespace If our default Round-Robin gives the transaction back to the segment, 4 big transactions will have four independent returns, each returning segment will be 1G, if so, our 2G table space is not enough. And the database administrator has to work at 2 o'clock at night, and each transaction begins with the following statement:
Set Transaction Use Rollback Segment RB_LARGE
Now 4 transactions reuse the same table space, keeping 4 returns to the tablespace within 2g. Database administrators can sleep to dawn.
