Oracle SQL Fundamentals/Transactions

From Wikiversity
Jump to navigation Jump to search

This lesson introduces transactions.

Objectives and Skills[edit | edit source]

Objectives and skills for the transactions portion of Oracle SQL Fundamentals I certification include:[1]

  • Manipulating Data
    • Control transactions

Readings[edit | edit source]

  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[edit | edit source]

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

Activities[edit | edit source]

Schema Diagrams[edit | edit source]

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

HR TCL Statements[edit | edit source]

  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[edit | edit source]

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

Lesson Summary[edit | edit source]

  • 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.[2]
  • Transactions in a database environment have two main purposes:[3]
    • 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).[4]
  • 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.[5]
  • A COMMIT statement ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users.[6]
  • A ROLLBACK statement undoes all work performed since the transaction (or optional savepoint) began.[7]
  • 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.[8]
  • 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.[9]
  • COMMIT syntax: COMMIT;[10]
  • ROLLBACK syntax: ROLLBACK [TO SAVEPOINT <name>];[11]
  • SAVEPOINT syntax: SAVEPOINT <name>;[12]
  • FOR UPDATE syntax: SELECT <column(s) FROM <table(s) [WHERE condition] FOR UPDATE;[13]

Assessments[edit | edit source]

See Also[edit | edit source]

References[edit | edit source]