# 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

* Read Wikipedia: Database transaction.
* Read Wikipedia: Commit (data management).
* Read Wikipedia: Rollback (data management).
* Read Wikipedia: Savepoint.
* Read Oracle: Transaction Management.

## Multimedia

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

## Activities
### Schema Diagrams

* Review HR and OE schema diagrams.
** Review the Oracle: HR and OE schema diagrams.

### HR TCL Statements

* Control transactions using ROLLBACK.
** Insert the region Antarctica using the following query: INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
** Select all records from regions to verify the results.
** Roll back the current transaction using the following query: ROLLBACK;
** Select all records from regions to verify the results.
* Control transactions using COMMIT.
** Insert the region Antarctica using the following query: INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
** Select all records from regions to verify the results.
** Commit the current transaction using the following query: COMMIT;
** Select all records from regions to verify the results.
* Control transactions using SAVEPOINT.
** Update regions using the following queries: SAVEPOINT STEP_1; UPDATE REGIONS SET REGION_NAME = 'Antarctica' WHERE REGION_ID = 5; SAVEPOINT STEP_2; DELETE FROM REGIONS WHERE REGION_ID = 5; ROLLBACK TO SAVEPOINT STEP_2; COMMIT;
** Select all records from regions to verify the results.
* Control transactions using FOR UPDATE.
** Select the region Antarctica for update using the following query: SELECT * FROM REGIONS WHERE REGION_ID = 5 FOR UPDATE;
** Start a second connection to the HR database using SQL Developer or SQL*PLUS.
** In the second session, attempt to update the region Antarctica using the following query: UPDATE REGIONS SET REGION_NAME = 'Antarctica' WHERE REGION_ID = 5;
** In the first session, delete the region Antarctica using the following query: DELETE FROM REGIONS WHERE REGION_ID = 5;
** In the first session, commit the transaction using the following query: COMMIT;
** Observe the results in the second session.

### OE TCL Statements

* Control transactions using ROLLBACK.
* Control transactions using COMMIT.
* Control transactions using SAVEPOINT.
* 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 <name>];
* SAVEPOINT syntax: SAVEPOINT <name>;
* FOR UPDATE syntax: SELECT <column(s) FROM <table(s) [WHERE condition] FOR UPDATE;

## Assessments

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

## See Also

* Oracle: Database 2 Day Developers Guide - DML Statements and Transactions