DML and Transactions: INSERT, UPDATE, DELETE, COMMIT, ROLLBACK
What is a Transaction?
A transaction is a group of changes that succeed or fail together. Think of moving money: subtract from account A and add to account B. Either both happen or neither does.
| ACID | Meaning |
|---|---|
| Atomic | All or nothing |
| Consistent | Rules (constraints) are always respected |
| Isolated | Other sessions do not see half-finished work |
| Durable | Once committed, the change survives a crash |
The Four DML Statements
sql
INSERT INTO products (product_name, price) VALUES ('Webcam', 60);
UPDATE products SET price = 55 WHERE product_name = 'Webcam';
DELETE FROM products WHERE product_name = 'Webcam';
Always use WHERE:
UPDATE or DELETE without WHERE changes every row. Run a SELECT with the same WHERE first to see what you will touch.
COMMIT and ROLLBACK
sql
UPDATE products SET price = price * 1.10; -- oops, all rows
SELECT product_name, price FROM products;
ROLLBACK; -- undo everything since the last commit
SELECT product_name, price FROM products; -- original prices are back
| Command | What it does |
|---|---|
COMMIT | Makes changes permanent and visible to others |
ROLLBACK | Undoes all changes since the last commit |
SAVEPOINT x | Marks a point inside the transaction |
ROLLBACK TO x | Undoes only back to that point |
Important: In Oracle, DDL statements (CREATE, ALTER, DROP, TRUNCATE) commit automatically. You cannot roll them back.
SAVEPOINT Example
sql
INSERT INTO products (product_name, price) VALUES ('Cable', 5);
SAVEPOINT after_cable;
INSERT INTO products (product_name, price) VALUES ('Dock', 90);
ROLLBACK TO after_cable; -- removes Dock, keeps Cable
COMMIT;
MERGE: Update or Insert
sql
MERGE INTO products p
USING (SELECT 'Mouse' AS name, 18 AS price FROM dual) s
ON (p.product_name = s.name)
WHEN MATCHED THEN UPDATE SET p.price = s.price
WHEN NOT MATCHED THEN INSERT (product_name, price) VALUES (s.name, s.price);
What Other Sessions See
Open two SQL*Plus windows to try this:
- Session 1:
UPDATE products SET price = 20 WHERE product_name = 'Mouse';(do not commit) - Session 2:
SELECT price FROM products WHERE product_name = 'Mouse';shows the old price. Oracle never shows uncommitted data. - Session 2: try to update the same row. It waits, because session 1 holds a row lock.
- Session 1:
COMMIT;and session 2 continues.
DBA tip: Oracle readers never block writers and writers never block readers. Only two writers on the same row block each other. We will find and fix those waits in the performance section.
Conclusion
Group related changes into one transaction, test with SELECT first and COMMIT only when you are sure. ROLLBACK is your safety net until you commit.
Quick Summary: COMMIT saves, ROLLBACK undoes, SAVEPOINT marks. DDL commits automatically.

