OCP Knowledge Point: Control affairs

original works, from the "Blue Blog" blog, Deep Blue blog:http://blog.csdn.net/huangyanlong/article/details/47281071 051.9 Operational Data 051.9.5 Control Services I. Database Services

1, the concept of business.

A transaction is part of a relational database instance, consisting of one or more DML statements followed by a rollback or a commit command;

2, the characteristics of the transaction.

ACID: atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability).

3, the principle of the atomic nature of business.

All parts of a transaction must be completed or not completed.

4, the principle of business consistency.

Specify that the results of the query must be consistent with the database at the start of the query.

5, the principle of separation of business.

For other parts, unfinished (uncommitted) transactions must not be visible.

6, the principle of business permanence.

It is not possible for a database to lose this transaction once a transaction has been completed.

ii. Execute SQL statements

1. Execute SELECT statement

(1), the server process, is the data block from the data file to the data buffer process;

(2), DDWN process is the process of writing data block from database buffer to data file;

(3), select execution process

Step <1>: The user process issues a select instruction and transmits it to the server process, at which point the server process queries the database cache for the required select data, if

There is a step <4> read the data to the server process, the result set back to the user process, back to the customer;

Step <2>: If there is no useful data in the database buffer after the query, go to step <2>, and find the required block in the data file;

Step <3>: After step <2> end, you will undergo step <3> copy the data blocks obtained from the data file into the database buffer;

Step <4>: After step <3>, you will undergo step <4> transfer data to the server process;

Step <5>: This step will receive the data, that is, query results, return to the user process;

2. Execute UPDATE statement

Step <1>: Fetch data from cache: After a client issues a command statement, the data buffer is found to see if the required block of data exists, and if no DML operation completes the

The required block of data is copied from the data file to the data block buffer. Unlike the SELECT statement, the DML statement also requires an empty block of data for the undo segment;

Step <2>: Place the Lock: The lock will be placed on all records and related index keys;

Step <3>: Build Redo: At this point, the server process writes the change vector that will be applied to the specified data block in the log buffer. (Redo to be applied to the "table block" variable later

More and "undo block" changes). If you update a column, the ROWID and new values of the column and the old values are written to the log buffer. If this column is an index key

Part, the changes to be applied to the index and the undo block changes that protect the index changes are also written to the log buffer;

Step <4>: Completes the update operation after the step <3> completes: Completes the update operation in the data buffer. The table data block is updated with the changed column, and the unchanged columns are

Writes the undo segment data Block. From this moment to the time the update operation is submitted, all queries in other sessions related to the record of the change are redirected to

Undo data. Only the session that performs the update operation can see the updated row in the table data block. Similarly, all associated index change steps are the same.

3. Execute INSERT and DELETE statements

Step <1>: Get the data from the cache: the same way as the UPDATE statement is managed. The first is to find the required blocks within the database buffer, and if not, copy the data to a number

According to the database buffer;

Step <2>: Build Redo: Same as UPDATE statement management. All change vectors applied to data blocks and undo blocks are first written to the log buffer. For

Insert, the change vector that will be applied to the table block (which may include the index block) is the byte that makes up the new row (possibly including the new key). The direction that will be applied to the undo block

Quantity is the rowid of the new line. For delete, the change vector to write to the undo block is the entire line.

Insert and Delete differences

(1), insert: When inserting a record, the action to generate undo only involves writing a new rowid for the undo block. This is because when the INSERT statement is rolled back, Oracle only needs

ROWID information, use the following statement:

Sql> DELETE from table_name WHERE ROWID = Rowid_of_the_row;

This allows you to undo the original change operation by executing the above statement.

(2), delete: When a record is deleted, because the entire row (possibly several KB) is written to the undo block, you can construct a statement that inserts the full row back into the table.

Roll back the delete operation as needed.

4. Start and end of business

(1), beginning and ending

The start of a transaction begins when the DML command is issued. The transaction continues to execute any number of DML commands until the session issues a commit or rollback to terminate. Only committed changes will become permanent and will be visible to other sessions. It is not possible to nest transactions.

(2), nested transactions: refers to the SQL standard does not allow users to start a transaction, and then start another transaction before terminating the first transaction. You can do this with pl/sql, but this is not the SQL industry standard.

(3), explicit transaction control: Explicit transaction control statements have commit, ROLLBACK, savepoint (can be used to cancel transactions).

(4), implicitly Terminate transaction: Issue DDL or DCL; Exit user tool (Sql*plus etc.); customer session terminated; system crashes.

(5) The possibility that the client session failed.

The ① user process terminates, the ② session is terminated at the operating system level, the network connection to the ③ database server is interrupted, and the computer that is running the client tool crashes.

OCP Real Title Walkthrough 171, transaction, transaction end

171. Which three statements/commands would cause a transaction to-end? (Choose three.) (which three statements/commands can cause a transaction to end.) Select three)

A. COMMIT

B. SELECT

C. CREATE

D. ROLLBACK

E. savepoint

Answer: ACD

173, transaction, transaction completion

173. When does a transaction complete? (Choose all that apply.) (When to complete a transaction)

A. When a deletes statement is executed (after the DELETE statement is executed)

B. When a ROLLBACK command is executed (after the ROLLBACK statement is executed)

C. When a pl/sql anonymous the block is executed (after a Pl/sql anonymous block has been executed)

D. When a data definition language (DDL) statement was executed (when a DLL statement is executed)

E. When a TRUNCATE statement was executed after the pending transaction (execute the TRUNCATE statement after the waiting transaction)

Answer: BDE

Definition: Defining

Pending: Not decided

174. Business

174. Which statement is true regarding transactions? (Choose all that apply.) (about the business which sentence is correct.) Select all the appropriate)

A. A transaction can consist only a set of DML and DDL statements. (A transaction can only consist of a set of DML and DDL statements)

B. A part or A entire transaction can is undone by using ROLLBACK command (partial or entire transaction may be revoked using ROLLBACK commands)

C. A transaction consists of a set of DML or DCL statements. (a transaction consists of a set of DML or DCL statements)

D. A part or a entire transaction can is made permanent with A commit. (some or all of the transaction can be permanently committed using A commit command)

E. A transaction can consist of only a set of queries or DML or DDL statements. (A transaction can only consist of a set of queries or DML or DDL statements)

Answer: BC

D incorrect because a commit commits the entire transaction and cannot commit some of the transactions

Entire: all, whole.

References: "OCP/OCA certification Examination Guide Full book"

******************************************* Blue's growth record series ****************************************************

original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source ( Http://blog.csdn.net/huangyanlong ).

Blue Growth--chasing DBAs (1): Rushing on the road and advancing to Shandong

the growth of blue--chasing DBA (2): Installation. Installation. A long-lost memory that caused me to rethink the DBA

The growth of blue--chasing DBAs (3): Antiques on operation, data import and export became a problem

The growth of blue--Chasing the DBA (4): Recalling the youth, and then the Oracle installation (Linux under 10g, 11g)

the growth of blue--chasing DBAs (5): Not talking about technology and annoying application systems

the growth of the blue--Chasing the DBA (6): Doing things and being a person: small technology, greatly human

The growth of blue--chasing DBAs (7): Basic command, foundation stone

the growth of blue--chasing DBA (8): Re-pick up SP report, recall Oracle's Statspack experiment

Blue Growth--chasing DBAs (9): National Day fades, chasing DBAs, new plans, new departures

The growth of blue--chasing DBAs (10): Flying Knives for self-defense, and not for expertise: tinkering with middleware WebSphere

The growth of blue--chasing DBAs (11): The ease of coming home, the dizzy wake up

the growth of blue--chasing DBAs (12): Seven days seven harvested SQL

Blue Growth-chasing DBAs (13): Coordinating hardware vendors, six stories: what you see is "servers, storage, switches ..."

The growth of blue--chasing DBAs (14): Memorable "cloud" end, the start of Hadoop deployment

the growth of blue--chasing DBA (15): Think FTP is very "simple", who Chengxiang twists

the growth of blue--chasing DBA: The DBA also drank and was 捭阖.

Blue Growth--chasing DBAs (17): sharing, or spending, learning to grow in the post-IoE era

***************************************************************************************************** *************

******************************************** Football and Oracle series *************************************************

original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source ( Http://blog.csdn.net/huangyanlong ).

Football and Oracle Series (1): 32 Road Princes Soldiers of, Oracle32 Process Alliance Group A The Smon process in Brazil

Football and Oracle Series (2): Brazil opener Preview, Oracle architecture

Soccer and Oracle Series (3): Oracle process rankings, the World Cup round is about to be fought.

Soccer and Oracle Series (4): From Brazil fiasco in Germany, think of the difference in the RAC topology contrast.  

The Soccer and Oracle Series (5): Fifa14 game's missing DirectX Library is analogous to Oracle's RPM package.

Football and Oracle Series (6): The Asian Cup with the building of the library--Come on Chinese team

***************************************************************************************************** *************