What is SQL Transactions ? and when you should use it ?
Hello everyone, if you’re a data analyst, you might not use transactions in your everyday tasks since you access data mostly in read-only mode. However, understanding how they work is crucial because they are a fundamental principle in managing databases. Ensuring data integrity and consistency is essential in this field.
When use transactions :
Transactions are essential in scenarios where multiple users or processes access the same database concurrently. Without proper transaction handling, conflicts might arise, leading to data corruption, inconsistencies, and other undesirable outcomes.
Properties of Transactions
Transactions possess the following four standard properties, commonly referred to by the acronym ACID.
Transactional Control Commands
Transactional commands are only used with the DML (Data Manipulation Language) Commands such as – INSERT, UPDATE, MERGE and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. Following commands are used to control transactions.
It indicates the start point of an explicit or local transaction.
Syntax:
BEGIN TRANSACTION transaction_name ;
Think of the COMMIT command like hitting the “save” button on your work. When you make a bunch of changes in a database during a transaction, nothing is permanently stored until you say so. COMMIT is how you say, “Okay, all these changes are good. Keep them.”
How to Use It
Simply write:
COMMIT;
Example Time
Imagine we’re creating a table called PETS to keep track of pet owners.
CREATE TABLE PETS (
ID INT NOT NULL,
PET_NAME VARCHAR (20) NOT NULL,
PET_AGE INT NOT NULL,
OWNER_NAME VARCHAR (25),
PET_TYPE VARCHAR (10),
PRIMARY KEY (ID)
);
Let’s add some pets into our table:
INSERT INTO PETS VALUES
(1, 'ROCKY', 4, 'John', 'Dog'),
(2, 'TITI', 2, 'Emma', 'Cat'),
(3, 'MIMI', 1, 'Sophia', 'Bird');
Now, if we delete all cats (sorry, Mittens) and then COMMIT:
DELETE FROM PETS WHERE PET_TYPE = 'Cat';
COMMIT;
Checking the table after, Mittens won’t be there anymore.
ROLLBACK is the “undo” button. Made a mistake? No problem. ROLLBACK says, “Forget everything I did since the last save.”
How It Works
Just type:
ROLLBACK;
Let’s Try It
Suppose we have the same PETS table. If we accidentally delete Buddy:
DELETE FROM PETS WHERE PET_NAME = 'Buddy';
And then realize, “Oops, not what I wanted to do,” we can:
ROLLBACK;
Buddy is back. No harm done.
SAVEPOINT is like creating a checkpoint in a video game. You can go back to it if you mess up, without starting over.
Creating a SAVEPOINT
SAVEPOINT SP_NAME;
Using It
You can rollback to a specific point:
ROLLBACK TO SP_NAME;
Playing With PETS
Say we want to safely remove some pets but might change our minds:
SAVEPOINT REMOVE_MITTENS;
DELETE FROM PETS WHERE PET_NAME = 'Mittens';
SAVEPOINT REMOVE_TWEETY;
DELETE FROM PETS WHERE PET_NAME = 'Tweety';
If we want to bring Tweety back but keep Mittens gone, we just rollback to the second savepoint:
ROLLBACK TO REMOVE_TWEETY;Once you’re happy and don’t need a SAVEPOINT anymore, you can get rid of it.
How to Do It
RELEASE SAVEPOINT SAVEPOINT_NAME;
This means you can’t rollback to that point anymore.
Finally, SET TRANSACTION is like setting the mode on your phone to either silent or loud. It tells the database how you want to run your transactions: quietly in the background or making changes as you go.
Set It Up
SET TRANSACTION READ WRITE;
Or for read-only:
SET TRANSACTION READ ONLY;
