SQL Transactions

What do you mean by SQL Transactions?

A logical unit of work performed on a database is referred to as a transaction. Transactions are logically organised work units or sequences that can be done either manually or automatically by a database application.
A transaction is the process of propagating one or more changes to the database.
Creating, updating, or deleting a record from a table, for example, is performing a transaction on that table.
It's vital to keep track of these transactions in order to maintain data integrity and handle database concerns.

In practise, you'll combine numerous SQL queries and execute them all at once as part of a transaction.

A transaction is a logical unit of work that consists of a series of operations executed on a database (using one or more SQL statements).
An atomic, consistent, isolated, and durable database transaction is required. These four points were covered afterwards.
Consider a banking database to grasp the concept of a transaction. The statement will be divided into four blocks if a bank customer transfers money from his savings account (S a/c) to his overdraft account (O a/c):

🚀Debit S a/c.
🚀Credit O a/c.
🚀Record in Transaction Journal
🚀 End Transaction

The SQL command to debit the S account is:

UPDATE S_accounts
SET amount = amount - 3000
WHERE account = 236456 ; 

The SQL command to credit the O account is:

UPDATE O_accounts
SET amount = amount + 3000
WHERE account = 534456 ; 

The following is the SQL statement for a record in the transaction journal:

INSERT INTO journal VALUES
(100896, 'Tansaction on Vikek Kumar a/c', '2-SEP-08' 236456, 534456, 3000);

End Transaction is a SQL statement that looks like this:

COMMIT WORKS; 

Properties of Transactions

Transactions contain the four standard qualities listed below, which are frequently abbreviated as ACID.

🚀 Atomicity : It ensures the successful completion of all processes inside the work unit. Otherwise, the transaction will be aborted at the point of failure, and all preceding activities would be reversed.
🚀 Consistency : When a transaction is successfully committed, consistency ensures that the database changes states properly.
🚀 Isolation : It allows transactions to execute independently of one another while remaining transparent to them.
🚀 Durability ; It ensures that a committed transaction's outcome or effect is preserved in the event of a system failure.

Transaction Control

The commands below are used to manage transactions.

🚀 COMMIT : To save your changes, press COMMIT.
🚀 ROLLBACK :TO REVERSE THE CHANGES, USE ROLLBACK.
🚀 ROLLBACK POINTS : They are created by SAVEPOINT inside groups of transactions.
🚀 SET TRANSACTION : It gives a transaction a name.

Transactional Control Commands

DML commands like as INSERT, UPDATE, and DELETE are the only ones that use transactional control commands. They can't be utilised when adding or removing tables because the database commits these activities automatically. The transactional command COMMIT is used to save changes made by a transaction to the database.

Beginning a Transaction

The execution of numerous SQL statements is being started by a transaction. The atomicity of a transaction is guaranteed when it starts. It can be committed to make the changes permanent or rolled back to undo the changes and leave the database unchanged after starting.

The COMMIT Command

The transactional command COMMIT is used to save changes made by a transaction to the database. Since the last COMMIT or ROLLBACK command, the COMMIT command saves all transactions to the database.

The COMMIT command has the following syntax.

Syntax :

COMMIT; 

The ROLLBACK Command

The transactional command ROLLBACK is used to undo transactions that have not yet been recorded to the database. This command can only be used to reverse transactions that have occurred since the last COMMIT or ROLLBACK command.

The ROLLBACK command has the following syntax:

Syntax :

ROLLBACK; 

The SAVEPOINT Command

A SAVEPOINT is a point in a transaction where you can revert to a previous state without reverting the entire transaction.

The SAVEPOINT command has the following syntax.

Syntax :

SAVEPOINT savepoint_name; 

This command is only used to create a SAVEPOINT in the middle of all transactional statements. To undo a collection of transactions, use the ROLLBACK command.

The following is the syntax for rolling back to a SAVEPOINT.

Syntax :

ROLLBACK to savepoint_name; 

The RELEASE SAVEPOINT Command

The RELEASE SAVEPOINT command is used to delete a previously established SAVEPOINT.

The RELEASE SAVEPOINT command has the following syntax.

Syntax :

RELEASE SAVEPOINT savepoint_name;

You can no longer use the ROLLBACK command to undo transactions made since the last SAVEPOINT if a SAVEPOINT has been released.

The SET TRANSACTION Command

A database transaction can be started with the SET TRANSACTION command. This command is used to define the properties of the next transaction.

The SET TRANSACTION has the following syntax.

Syntax :

SET TRANSACTION [ READ WRITE | READ ONLY ];

With this article at Logicmojo, you must have the complete idea of SQL Transactions.