PL/SQL - Transactions: A Beginner's Guide

Hello there, future database wizards! Today, we're going to embark on an exciting journey into the world of PL/SQL transactions. Don't worry if you're new to programming; I'll be your friendly guide, explaining everything step by step. So, let's dive in!

PL/SQL - Transactions

What Are Transactions?

Before we start, let's understand what transactions are. Imagine you're transferring money from your savings account to your checking account. This operation involves two steps:

Deducting money from savings
Adding money to checking

These two steps together form a transaction. It's crucial that either both steps happen, or neither happens. We don't want money disappearing or appearing out of thin air!

Starting and Ending a Transaction

In PL/SQL, transactions begin automatically when you execute your first DML (Data Manipulation Language) statement. These include INSERT, UPDATE, and DELETE operations. Let's look at an example:

BEGIN
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (1001, 'John', 'Doe');

  UPDATE employees
  SET salary = 50000
  WHERE employee_id = 1001;
END;
/

In this example, as soon as the INSERT statement is executed, a transaction begins. The transaction continues with the UPDATE statement.

Committing a Transaction

Now, you might be wondering, "Okay, we started a transaction, but how do we finish it?" Great question! We use the COMMIT statement to end a transaction and make all changes permanent. Let's modify our previous example:

BEGIN
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (1001, 'John', 'Doe');

  UPDATE employees
  SET salary = 50000
  WHERE employee_id = 1001;

  COMMIT;
END;
/

By adding the COMMIT statement at the end, we're telling the database, "Yes, I'm sure about these changes. Please make them permanent."

Why Commit is Important

Imagine you're baking a cake. You've mixed all the ingredients, but until you put it in the oven, it's not really a cake yet. Similarly, until you COMMIT your transaction, the changes aren't permanent in the database.

Rolling Back Transactions

Sometimes, things don't go as planned. Maybe you realized you made a mistake in your transaction. Don't panic! PL/SQL gives us a safety net called ROLLBACK. It's like having an "undo" button for your database operations.

BEGIN
  UPDATE employees
  SET salary = salary * 2;  -- Oops! We didn't mean to double everyone's salary!

  ROLLBACK;  -- Phew! Crisis averted.
END;
/

In this example, we accidentally doubled everyone's salary (wouldn't that be nice?). But thanks to ROLLBACK, we can undo this change before it becomes permanent.

Automatic Transaction Control

PL/SQL also provides automatic transaction control in certain situations. Let's look at a table of these scenarios:

Scenario	Action
Normal end of a PL/SQL block	Automatic COMMIT
Runtime error during a transaction	Automatic ROLLBACK
SQL*Plus EXIT command (without COMMIT)	Automatic ROLLBACK
System crash	Automatic ROLLBACK

Example of Automatic Rollback

BEGIN
  UPDATE employees
  SET salary = salary / 0;  -- Oops! Division by zero error
END;
/

In this case, PL/SQL will automatically roll back the transaction due to the runtime error.

Savepoints: Creating Checkpoints in Your Transaction

Sometimes, you might want to roll back part of a transaction, but not all of it. That's where savepoints come in handy. They're like checkpoints in a video game - you can return to them if things go wrong.

BEGIN
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (1002, 'Jane', 'Smith');

  SAVEPOINT after_insert;

  UPDATE employees
  SET salary = 60000
  WHERE employee_id = 1002;

  -- Oops, we changed our mind about the salary
  ROLLBACK TO after_insert;

  COMMIT;
END;
/

In this example, we insert a new employee and create a savepoint. Then we update the salary, but decide to roll back to the savepoint, undoing the salary update but keeping the insert.

Best Practices for Using Transactions

Keep transactions short: Long-running transactions can lock resources and affect system performance.
Use explicit COMMIT statements: While PL/SQL can automatically commit in some cases, it's better to be explicit about when you want to commit your changes.
Handle exceptions properly: Make sure to include proper exception handling to avoid unintended commits or rollbacks.
Use savepoints for complex transactions: If you have a long transaction, consider using savepoints to allow partial rollbacks.

Conclusion

And there you have it, folks! We've journeyed through the land of PL/SQL transactions, from starting and ending them, to committing and rolling back, and even creating savepoints. Remember, transactions are like the safety harness of database operations - they ensure that your data remains consistent and reliable.

Practice these concepts, play around with different scenarios, and soon you'll be managing transactions like a pro. Happy coding, and may your transactions always be ACID (that's a database joke for another day)!

Credits: Image by storyset