close
The Wayback Machine - https://web.archive.org/web/20250130132633/https://www.geeksforgeeks.org/sql-transactions/
Open In App

SQL TRANSACTIONS

Last Updated : 13 Jan, 2025
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

SQL transactions are essential for ensuring data integrity and consistency in relational databases. Transactions allow for a group of SQL operations to be executed as a single unit, ensuring that either all the operations succeed or none of them do. This capability, together with the ACID (Atomicity, Consistency, Isolation, and Durability) properties, is fundamental for maintaining the integrity of our data.

What is an SQL Transaction?

A transaction in SQL is a sequence of one or more SQL statements executed as a single unit of work. These statements could be performing operations like INSERT, UPDATE, or DELETE. The main idea behind a transaction is that all changes made within it should either be completely successful or fully undone in case of failure. In the case of an error, a rollback ensures that no partial changes are saved.

The SQL transaction must adhere to four key properties known as ACID:

  • Atomicity: The outcome of a transaction can either be completely successful or completely unsuccessful. The whole transaction must be rolled back if one part of it fails.
  • Consistency: Transactions maintain integrity restrictions by moving the database from one valid state to another.
  • Isolation: Concurrent transactions are isolated from one another, assuring the accuracy of the data.
  • Durability: Once a transaction is committed, its modifications remain in effect even in the event of a system failure.

The SQL Transaction Process

Transactions are controlled using specific SQL commands. The most common commands used for managing transactions are:

  1. BEGIN TRANSACTION: Starts a transaction.
  2. COMMIT: Commits the transaction, saving all changes made.
  3. ROLLBACK: Rolls back the transaction, undoing all changes made since the BEGIN TRANSACTION.
  4. SAVEPOINT: Marks a point within a transaction to which you can later roll back.

Example of SQL Transaction with a Bank Transfer Scenario

Let’s look at an example of a bank transfer between two accounts. This example demonstrates the usage of multiple queries in a single transaction.

BEGIN TRANSACTION;

-- Deduct $150 from Account A
UPDATE Accounts
SET Balance = Balance - 150
WHERE AccountID = 'A';

-- Add $150 to Account B
UPDATE Accounts
SET Balance = Balance + 150
WHERE AccountID = 'B';

-- Commit the transaction if both operations succeed
COMMIT;

If any error occurs, such as an issue with the UPDATE query, you can use ROLLBACK to undo all changes made during the transaction:

ROLLBACK;

This ensures that the system doesn’t end up in an inconsistent state, such as deducting money from one account without adding it to another.

SQL Transaction Control Commands

In SQL, transaction control commands manage the execution of SQL operations, ensuring the integrity and reliability of database transactions. These commands help manage the start, commit, and rollback of changes made to the database. Below are the key transaction control commands in SQL, explained with syntax and examples for each.

1. BEGIN TRANSACTION Command

The BEGIN TRANSACTION command marks the beginning of a new transaction. All SQL statements that follow this command will be part of the same transaction until a COMMIT or ROLLBACK is encountered. This command doesn’t make any changes to the database, it just starts the transaction.

Syntax:

BEGIN TRANSACTION transaction_name ;

Example

BEGIN TRANSACTION TransferFunds;

2. COMMIT Command

The COMMIT command is used to save all changes made during the current transaction to the database. Once a transaction is committed, the changes are permanent. 

Syntax:

COMMIT;

Here is the sample Student table that will be used to perform the operations in this example. This table contains basic student details such as ID, name, age, and other relevant information that will be manipulated using various transaction control commands.

output

Student Table

Example

Following is an example which would delete those records from the table which have age = 20 and then COMMIT the changes in the database. 

DELETE FROM Student WHERE AGE = 20;
COMMIT;

Output

output

output

3. ROLLBACK Command

The ROLLBACK command is used to undo all changes made in the current transaction. It is used when an error occurs or when the desired changes cannot be completed. The database will revert to the state it was in before the BEGIN TRANSACTION was executed.

Syntax:

ROLLBACK;

Example

Delete those records from the table which have age = 20 and then ROLLBACK the changes in the database. In this case, the DELETE operation is undone, and the changes to the database are not saved.

DELETE FROM Student WHERE AGE = 20;
ROLLBACK;

Output

output

output

4. SAVEPOINT Command

A SAVEPOINT is used to create a checkpoint within a transaction. We can roll back to a specific SAVEPOINT instead of rolling back the entire transaction. This allows us to undo part of the transaction rather than the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example

SAVEPOINT SP1;
//Savepoint created.
DELETE FROM Student WHERE AGE = 20;
//deleted
SAVEPOINT SP2;
//Savepoint created.

Output

output

output

Explanation:

From the above example Sample table1, Delete those records from the table which have age = 20 and then ROLLBACK the changes in the database by keeping Savepoints. Here SP1 is first SAVEPOINT created before deletion. In this example one deletion have taken place. After deletion again SAVEPOINT SP2 is created. 

5. ROLLBACK TO SAVEPOINT

The ROLLBACK TO SAVEPOINT command allows us to roll back the transaction to a specific savepoint, effectively undoing changes made after that point.

Syntax:

ROLLBACK TO SAVEPOINT SAVEPOINT_NAME;

Example

Deletion have been taken place, let us assume that we have changed our mind and decided to ROLLBACK to the SAVEPOINT that we identified as SP1 which is before deletion. So, In this case the DELETE operation is undone, and the transaction is returned to the state it was in at the SP1 savepoint.

ROLLBACK TO SP1;
//Rollback completed

Output

output

output

6. RELEASE SAVEPOINT Command

This command is used to remove a SAVEPOINT that we have created. Once a SAVEPOINT has been released, we can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT. It is used to initiate a database transaction and used to specify characteristics of the transaction that follows. 

Syntax: 

RELEASE SAVEPOINT SAVEPOINT_NAME

Example

Once the savepoint SP2 is released, we can no longer roll back to it.

RELEASE SAVEPOINT SP2; -- Release the second savepoint.

Summary of Transaction Control Commands

Command Purpose Example
BEGIN TRANSACTION Starts a new transaction. BEGIN TRANSACTION TransferFunds;
COMMIT Saves all changes made during the transaction. COMMIT;
ROLLBACK Undoes changes made during the transaction. ROLLBACK;
SAVEPOINT Creates a point in the transaction to which you can roll back. SAVEPOINT SP1;
ROLLBACK TO SAVEPOINT Rolls back the transaction to a specific savepoint. ROLLBACK TO SP1;
RELEASE SAVEPOINT Removes a savepoint from the transaction. RELEASE SAVEPOINT SP2;

Types of SQL Transactions

There are different types of transactions based on their nature and the specific operations they perform:

  • Read Transactions: Used to only read the data, typically with SELECT queries.
  • Write Transactions: These involve modifying the data in the database with INSERT, UPDATE, or DELETE operations.
  • Distributed Transactions: These transactions span multiple databases and ensure consistency across them.
  • Implicit Transactions: Automatically started by SQL Server for certain operations.
  • Explicit Transactions: Manually controlled transactions where the user begins and ends the transaction using BEGIN TRANSACTION, COMMIT, and ROLLBACK.

How to Monitor and Optimize SQL Transactions

Optimizing SQL transactions is critical for performance. Here are some best practices:

  • Keep Transactions Short: Avoid long-running transactions as they can lock tables and reduce concurrency.
  • Use Proper Indexing: Proper indexing on tables involved in the transaction can greatly improve performance.
  • Avoid Transaction Blocking: Ensure transactions don’t block others unnecessarily, causing delays.
  • Commit Early: Try to commit as soon as the transaction is complete to release resources.

Conclusion

SQL transactions are a fundamental part of database management, providing a way to ensure data consistency, integrity, and isolation. Understanding how to use BEGIN TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT is essential for writing reliable and efficient SQL queries. With proper transaction control, we can ensure that our database operations are robust, consistent, and optimized for performance. By following best practices like keeping transactions short and committing early, we can also improve the scalability and concurrency of our database.

FAQs

What is the SQL transaction?

An SQL transaction is a sequence of SQL statements executed as a single unit of work. It ensures that either all operations are successfully completed or none of them are, maintaining the database’s integrity using properties like ACID (Atomicity, Consistency, Isolation, Durability).

What is Transact SQL with an example?

Transact-SQL (T-SQL) is an extension of SQL used in Microsoft SQL Server. It provides additional features such as procedural programming, error handling, and control-of-flow constructs. Example:
BEGIN TRANSACTION;
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;
COMMIT;

What is the difference between a query and a transaction in SQL?

A query is a single SQL statement used to retrieve or modify data in the database, while a transaction is a series of queries that are executed together to ensure data consistency, with support for commit or rollback in case of errors.



Next Article
Article Tags :

Similar Reads

three90RightbarBannerImg