Oracle SQL Fundamentals/Transactions
Appearance
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]- Read Wikipedia: Database transaction.
- Read Wikipedia: Commit (data management).
- Read Wikipedia: Rollback (data management).
- Read Wikipedia: Savepoint.
- Read Oracle: Transaction Management.
Multimedia
[edit | edit source]- YouTube: PL SQL Tutorial DML and TCL statements (Theory)
- YouTube: PL SQL Tutorial DML and TCL statements (Hands On)
Activities
[edit | edit source]Schema Diagrams
[edit | edit source]- Review HR and OE schema diagrams.
- Review the Oracle: HR and OE schema diagrams.
HR TCL Statements
[edit | edit source]- 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.
- Insert the region Antarctica using the following query:
- 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.
- Insert the region Antarctica using the following query:
- 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.
- Update regions using the following queries:
- 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.
- Select the region Antarctica for update using the following query:
OE TCL Statements
[edit | edit source]- Control transactions using ROLLBACK.
- Control transactions using COMMIT.
- Control transactions using SAVEPOINT.
- 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]- Flashcards: Quizlet: Oracle 1Z0-051 Exam - Transactions
See Also
[edit | edit source]References
[edit | edit source]- ↑ Oracle: Database 11g: SQL Fundamentals I Exam Topics
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Database transaction
- ↑ Wikipedia: Database transaction
- ↑ Oracle: Transaction Management
- ↑ Wikipedia: Commit (data management)
- ↑ Wikipedia: Commit (data management)
- ↑ Wikipedia: Savepoint
- ↑ Oracle: SELECT
- ↑ Wikipedia: Commit (data management)
- ↑ Wikipedia: Savepoint
- ↑ Wikipedia: Savepoint
- ↑ Oracle: SELECT