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 Wikipedia: Database transaction.
2. Read Wikipedia: Commit (data management).
3. Read Wikipedia: Rollback (data management).
4. Read Wikipedia: 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)

Activities

Schema Diagrams

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

HR TCL Statements

1. Control transactions using ROLLBACK.
   1. Insert the region Antarctica using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
   2. Select all records from regions to verify the results.
   3. Roll back the current transaction using the following query:
      ROLLBACK;
   4. Select all records from regions to verify the results.
2. Control transactions using COMMIT.
   1. Insert the region Antarctica using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
   2. Select all records from regions to verify the results.
   3. Commit the current transaction using the following query:
      COMMIT;
   4. Select all records from regions to verify the results.
3. Control transactions using SAVEPOINT.
   1. 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;
   2. Select all records from regions to verify the results.
4. Control transactions using FOR UPDATE.
   1. Select the region Antarctica for update using the following query:
      SELECT * FROM REGIONS WHERE REGION_ID = 5 FOR UPDATE;
   2. Start a second connection to the HR database using SQL Developer or SQL*PLUS.
   3. In the second session, attempt to update the region Antarctica using the following query:
      UPDATE REGIONS SET REGION_NAME = 'Antarctica' WHERE REGION_ID = 5;
   4. In the first session, delete the region Antarctica using the following query:
      DELETE FROM REGIONS WHERE REGION_ID = 5;
   5. In the first session, commit the transaction using the following query:
      COMMIT;
   6. 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 <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

References

← Manipulating Data

Oracle SQL Fundamentals

Data Definition Language →