Oracle SQL Fundamentals/Transactions

This lesson introduces transactions.

Objectives and Skills
Objectives and skills for the transactions portion of Oracle SQL Fundamentals I certification include:
 * Manipulating Data
 * Control transactions

Readings

 * 1) Read  Database transaction.
 * 2) Read  Commit (data management).
 * 3) Read  Rollback (data management).
 * 4) Read  Savepoint.
 * 5) Read Oracle: Transaction Management.

Multimedia

 * 1) YouTube: PL SQL Tutorial DML and TCL statements (Theory)
 * 2) YouTube: PL SQL Tutorial DML and TCL statements (Hands On)

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

HR TCL Statements

 * 1) Control transactions using ROLLBACK.
 * 2) Insert the region Antarctica using the following query:
 * 3) Select all records from regions to verify the results.
 * 4) Roll back the current transaction using the following query:
 * 5) Select all records from regions to verify the results.
 * 6) Control transactions using COMMIT.
 * 7) Insert the region Antarctica using the following query:
 * 8) Select all records from regions to verify the results.
 * 9) Commit the current transaction using the following query:
 * 10) Select all records from regions to verify the results.
 * 11) Control transactions using SAVEPOINT.
 * 12) Update regions using the following queries:
 * 13) Select all records from regions to verify the results.
 * 14) Control transactions using FOR UPDATE.
 * 15) Select the region Antarctica for update using the following query:
 * 16) Start a second connection to the HR database using SQL Developer or SQL*PLUS.
 * 17) In the second session, attempt to update the region Antarctica using the following query:
 * 18) In the first session, delete the region Antarctica using the following query:
 * 19) In the first session, commit the transaction using the following query:
 * 20) Observe the results in the second session.

OE TCL Statements

 * 1) Control transactions using ROLLBACK.
 * 2) Control transactions using COMMIT.
 * 3) Control transactions using SAVEPOINT.
 * 4) Control transactions using FOR UPDATE.

Lesson Summary

 * A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions.
 * Transactions in a database environment have two main purposes:
 * To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure.
 * To provide isolation between programs accessing a database concurrently.
 * A database transaction, by definition, must be atomic, consistent, isolated and durable (ACID).
 * In Oracle databases, a transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
 * A COMMIT statement ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users.
 * A ROLLBACK statement undoes all work performed since the transaction (or optional savepoint) began.
 * A savepoint is a way of implementing subtransactions (also known as nested transactions) within a relational database management system by indicating a point within a transaction that can be "rolled back to" without affecting any work done in the transaction before the savepoint was created. Multiple savepoints can exist within a single transaction.
 * The SELECT statement FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction.
 * COMMIT syntax: COMMIT;
 * ROLLBACK syntax: ROLLBACK [TO SAVEPOINT ];
 * SAVEPOINT syntax: SAVEPOINT ;
 * FOR UPDATE syntax: SELECT <column(s) FROM <table(s) [WHERE condition] FOR UPDATE;

Assessments

 * Flashcards: Quizlet: Oracle 1Z0-051 Exam - Transactions