Day 28: Transactions In SQL (Series Finale)

Day 28 Of Learning

Image By Author

Hey Guys,
Welcome To The Series Finale!

Today, we’ll be looking at transactions in SQL. We’ll cover the basics as the basics are what you need to get an entry level data job. So let’s get started.

What Are Transactions?

A transaction in SQL is a sequence of one or more SQL operations that are executed as a single unit of work. It follows the ACID properties to ensure data integrity:

1. Atomicity – The transaction is all or nothing. If one part fails, the entire transaction is rolled back.

2. Consistency – The database remains in a consistent state before and after the transaction.

3. Isolation – Transactions are executed independently, preventing conflicts.

4. Durability – Once a transaction is committed, the changes are permanent.

Types of Transactions in MySQL

MySQL supports transactions using transactional storage engines like InnoDB. It mainly has two types of transactions:

1. Implicit Transactions

   These are executed automatically without requiring explicit BEGIN or COMMIT.

   Example: Each INSERT, UPDATE, or DELETE statement in MySQL (if using MyISAM) is auto-committed.

2. Explicit Transactions

   These require manual control using transaction commands:

• START TRANSACTION or BEGIN – Marks the start of a transaction.

• COMMIT – Saves the changes permanently.

• ROLLBACK – Reverts changes if an error occurs.

• SAVEPOINT – Sets a point to which a transaction can be rolled back partially.

I am not giving any examples or any exercise since the last few posts because these concepts only require theoretical knowledge, you should just know what they do and have an idea about them if the interviewer asks you to write a code related to them. Since you’re going for an entry level job, it wont be that complex but you should have solid understanding of JOINS, HAVING, WHERE, Subqueries, Window Functions, CTEs, Aggregation.

I haven’t covered CTEs and Window Functions in this series but I will be posting about them soon. This series was a blast with you guys, thankyou for believing in the content and hitting that subscribe button.

What Next?

I’ll be starting a 30 Day

Analyst Builder

Series. I’ll be solving the questions on the website and explaining them here.

Alex The Analyst

You have given us a gem and I intend to make it beneficial for my subscribers.

---

I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

1. ajmal7809@gmail.com

2. Linkedin

3. bideveloper_ (Discord)