Oracle data manipulation and Control Language (ii)

oracle| Control | data | Detailed transaction control transaction control includes coordinated access to multiple synchronizations of the same data.　　When a user changes the data that another user is using, Oracle uses transaction control who can manipulate the data. A transaction transaction represents a basic unit of work, a series of SQL statements that are successful or unsuccessful operations as a unit. There are many statements in SQL and pl/sql that allow programmers to control transactions.　　　　Programmers can: 1, explicitly start a thing, select statement-level consistency or transaction-level consistency 2, set undo rollback point, and roll back to rollback point 3, complete transaction change data forever or discard changes. Transaction CONTROL Statement Statement use Commit complete transaction, data modification successful and open to other user Rollback undo transaction, Undo all operations Rollback to savepoint undo operation Set Transaction response transaction or statement after setting rollback point Consistency, especially for transactions using rollback 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 and partial rollback ( Partial Rollback) savepoint in SQL and Pl/sql are intermediate flags within the scope of a transaction. Often used to divide a long transaction into small parts. The reservation point savepoint any point in a long transaction, allowing the action to be rolled back after that point. SavePoint is often used in applications, for example, a process contains several functions, a reservation can be established before each function, and if the function fails, it is easy to return to the beginning of each function. After the rollback to a savepoint, the data blockade obtained after the SavePoint is released. In order toImplement partial rollback The transaction can be rolled 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 $ ', ' ATM54 ') savepoint atm_ logged; UPDATE checkingset Balance=balance-100return balance into new_balance;if new_balance<0thenrollback to ATM_LOGGED; Commitraise Insufficient_funda; The End IfEnd Keyword savepoint is optional, so the following two statements are equivalent: ROLLBACK to atm_logged;　　ROLLBACK to SavePoint atm_logged; Consistency and transactional consistency are key ideas in controlling things. Mastering Oracle's consistency model enables you to better and more appropriately use transaction control. Oracle guarantees that data can be seen and used by users only when the transaction is complete.　　This technology has a huge effect on multi-user databases. Oracle often uses statement-level (State-level) consistency to ensure that data is visible between statements ' lifetimes, but cannot be changed.　　A transaction consists of multiple statements, and when a transaction is used, the object-level (Transaction-level) consistency guarantees that the data is visible to all statements throughout the transaction lifecycle. Oracle enforces consistency through the SCN (syatem change number). A SCN is a time-oriented database internal key.　　The SCN only increases and does not decrease, the SCN represents a point in time, each data block has a SCN, which is implemented by comparing this point. A transaction-level consistency SET TRANSACTION is a function of ensuring that there is one implementation in transaction-level consistency or statement-level consistency.　　Oracle uses these terms: the isolation level READ COMMIT represents the consistent isolation levels SERIALIZABLE the transactional level. Example: SET TRANSACTION isolation level READ COMMIT; SET TRANSACTION Isolation Level READ COMMIT　　The following statement also ensures transactional level consistency: SET transcation Read Only throws an exception for any attempt to modify data in a read only transaction. 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 no data is changed, READ　　Only transactions must still use a commit or rollback to end the entire transaction. Another application of SET transction is to use the rollback segment (ROLLBACK SEGMENT) directly on rollback. The rollback segment is a special data object for Oracle, and the header of the rollback segment contains information that is in use for the rollback segment transaction. When the user rolls back the transaction (ROLLBACK), Oracle will use the data before the rollback segment to restore the modified data to its original value. Oracle uses Round-robin to randomly allocate rollback segments to transactions. A large transaction can be assigned any rollback segment, which may cause the rollback segment to become larger in size.　　Therefore, avoid having large transactions randomly allocate rollback segments.　　The transaction begins with set TRANSACTION, as follows: Set TRANSACTION use ROLLBACK SEGMENT rb_large;　　Rb_large is the name of a large rollback segment that now assigns a large rollback segment to a large transaction, and other small rollback segments are not managed by dynamic space, which is more efficient. Let's take a look at an example. We have a rollback segment with a table space size of 2G, which requires 10 rollback segments at peak times to meet the needs of the user, these peak online users have only small transactions. In a week we ran 4 large transactions that required deletion and loading of data, 1G for each revocation, and the size of the rollback segment 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 minextents 5) Rb8 (INITial 1M Next minextents 5 rb9 (initial 1M next minextents 5) RB10 (initial 1M next minextents 5) all are properly arranged in the 2G tablespace, if we The default Round-robin assigns a rollback segment to a transaction, 4 large transactions will have 4 separate rollback segments, each rollback segment size will be 1G, if so our 2G table space is not enough, and the database administrator will have to work at 2 O ' night, each transaction starts with the following statement: Set TRANSACTION use ROLLBACK SEGMENT rb_large now 4 transactions reuse the same tablespace, preserving the table space of 4 rollback segments within 2G. The database administrator can sleep until dawn.