How to Start, Commit and Rollback Transactions in SQL

Learn how to start, commit and rollback transactions in SQL with this practical guide. You will also learn how to handle errors, use savepoints, set isolation levels, and optimize performance.

1. What are Transactions and Why are They Important?

A transaction is a logical unit of work that consists of one or more SQL statements that are executed as a single unit. A transaction ensures that the database remains in a consistent state after the execution of the statements. A transaction has four main properties, also known as ACID:

• Atomicity: A transaction is either completed in its entirety or not at all. If any statement in the transaction fails, the entire transaction is rolled back and the database is restored to its original state.
• Consistency: A transaction preserves the integrity and validity of the database. A transaction follows the rules and constraints defined by the database schema and does not violate them.
• Isolation: A transaction is isolated from other concurrent transactions. The changes made by one transaction are not visible to other transactions until the transaction is committed.
• Durability: A transaction is permanent and persistent. Once a transaction is committed, the changes made by the transaction are not lost even in the event of a system failure or power outage.

Transactions are important because they allow you to perform complex operations on the database without compromising its consistency and reliability. Transactions also help you to handle errors and exceptions that may occur during the execution of the statements. For example, suppose you want to transfer money from one account to another. This operation involves two steps: deducting the amount from the source account and adding the amount to the destination account. If you execute these steps as separate statements, there is a possibility that only one of them succeeds and the other fails, leaving the database in an inconsistent state. However, if you execute these steps as a single transaction, you can ensure that both of them succeed or both of them fail, maintaining the consistency of the database.

In this tutorial, you will learn how to start, commit and rollback transactions in SQL using the START TRANSACTION, COMMIT and ROLLBACK commands. You will also learn how to handle errors and exceptions in transactions, how to use savepoints in transactions, how to set transaction isolation levels in SQL, and how to optimize transaction performance in SQL.

Are you ready to learn how to manage transactions in SQL? Let’s get started!

2. How to Start a Transaction in SQL

To start a transaction in SQL, you need to use the START TRANSACTION command. This command initiates a new transaction and sets the transaction mode to autocommit off. This means that the changes made by the SQL statements in the transaction are not applied to the database until you explicitly commit them.

The syntax of the START TRANSACTION command is as follows:

START TRANSACTION [transaction_characteristics];

The transaction_characteristics are optional parameters that specify the behavior of the transaction, such as the isolation level, the access mode, and the consistency level. You can learn more about these parameters in section 7 of this tutorial.

Here is an example of how to start a transaction in SQL:

-- Start a new transaction
START TRANSACTION;

-- Perform some SQL statements
SELECT * FROM customers;
UPDATE customers SET balance = balance - 100 WHERE id = 1;
UPDATE customers SET balance = balance + 100 WHERE id = 2;

-- The changes are not visible to other transactions until committed

As you can see, the START TRANSACTION command begins a new transaction and executes some SQL statements. However, the changes made by the UPDATE statements are not applied to the database until the transaction is committed. This means that other transactions cannot see the updated balances of the customers until then.

Starting a transaction in SQL allows you to group a set of SQL statements into a single logical unit of work. This way, you can ensure that the database remains consistent and reliable after the execution of the statements. You can also control when the changes are applied to the database and when they are rolled back in case of an error or exception.

How do you commit or rollback a transaction in SQL? You will learn how to do that in the next sections of this tutorial.

3. How to Commit a Transaction in SQL

To commit a transaction in SQL, you need to use the COMMIT command. This command applies the changes made by the SQL statements in the transaction to the database and ends the transaction. Once a transaction is committed, the changes are permanent and visible to other transactions.

The syntax of the COMMIT command is as follows:

COMMIT [WORK] [AND [NO] CHAIN] [NO RELEASE];

The WORK keyword is optional and has no effect on the command. The AND [NO] CHAIN and NO RELEASE options are specific to some database systems and control the behavior of the next transaction. You can learn more about these options in section 8 of this tutorial.

Here is an example of how to commit a transaction in SQL:

-- Start a new transaction
START TRANSACTION;

-- Perform some SQL statements
SELECT * FROM customers;
UPDATE customers SET balance = balance - 100 WHERE id = 1;
UPDATE customers SET balance = balance + 100 WHERE id = 2;

-- Commit the transaction
COMMIT;

-- The changes are now applied to the database and visible to other transactions

As you can see, the COMMIT command ends the transaction and applies the changes made by the UPDATE statements to the database. This means that other transactions can now see the updated balances of the customers.

Committing a transaction in SQL allows you to confirm the changes made by the SQL statements in the transaction and make them permanent and persistent. This way, you can ensure that the database reflects the desired state of the data after the execution of the statements. You can also prevent the changes from being rolled back in case of an error or exception.

How do you rollback a transaction in SQL? You will learn how to do that in the next section of this tutorial.

4. How to Rollback a Transaction in SQL

To rollback a transaction in SQL, you need to use the ROLLBACK command. This command discards the changes made by the SQL statements in the transaction and restores the database to its original state before the transaction started. Once a transaction is rolled back, the changes are lost and not visible to other transactions.

The syntax of the ROLLBACK command is as follows:

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name] [AND [NO] CHAIN] [NO RELEASE];

The WORK keyword is optional and has no effect on the command. The TO [SAVEPOINT] savepoint_name option allows you to rollback the transaction to a specific savepoint that you have created within the transaction. You can learn more about savepoints in section 6 of this tutorial. The AND [NO] CHAIN and NO RELEASE options are specific to some database systems and control the behavior of the next transaction. You can learn more about these options in section 8 of this tutorial.

Here is an example of how to rollback a transaction in SQL:

-- Start a new transaction
START TRANSACTION;

-- Perform some SQL statements
SELECT * FROM customers;
UPDATE customers SET balance = balance - 100 WHERE id = 1;
UPDATE customers SET balance = balance + 100 WHERE id = 2;

-- Rollback the transaction
ROLLBACK;

-- The changes are now discarded and the database is restored to its original state

As you can see, the ROLLBACK command cancels the transaction and discards the changes made by the UPDATE statements. This means that the database returns to its original state before the transaction started and the balances of the customers are unchanged.

Rolling back a transaction in SQL allows you to undo the changes made by the SQL statements in the transaction and revert the database to its previous state. This way, you can prevent the database from being corrupted or inconsistent in case of an error or exception. You can also abort the transaction if you decide not to apply the changes for any reason.

How do you handle errors and exceptions in transactions? You will learn how to do that in the next section of this tutorial.

5. How to Handle Errors and Exceptions in Transactions

Errors and exceptions are inevitable in any database operation. They can occur due to various reasons, such as syntax errors, logical errors, constraint violations, network failures, system crashes, etc. When an error or exception occurs in a transaction, you need to handle it properly to avoid data corruption or inconsistency.

There are two main ways to handle errors and exceptions in transactions: implicit rollback and explicit rollback.

• Implicit rollback: This is the default behavior of most database systems. When an error or exception occurs in a transaction, the database system automatically rolls back the transaction and discards the changes made by the SQL statements in the transaction. This way, the database is restored to its original state before the transaction started and the error or exception is reported to the user.
• Explicit rollback: This is the alternative behavior that you can implement using the ROLLBACK command and the TRY…CATCH block. When an error or exception occurs in a transaction, you can catch it using the TRY…CATCH block and execute the ROLLBACK command to cancel the transaction and discard the changes made by the SQL statements in the transaction. This way, you have more control over the error handling and the rollback process.

The syntax of the TRY…CATCH block is as follows:

BEGIN TRY
  -- Start a new transaction
  START TRANSACTION;
  
  -- Perform some SQL statements
  SELECT * FROM customers;
  UPDATE customers SET balance = balance - 100 WHERE id = 1;
  UPDATE customers SET balance = balance + 100 WHERE id = 2;
  
  -- Commit the transaction
  COMMIT;
END TRY
BEGIN CATCH
  -- Rollback the transaction
  ROLLBACK;
  
  -- Handle the error or exception
  SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH

As you can see, the TRY…CATCH block consists of two parts: the TRY part and the CATCH part. The TRY part contains the SQL statements that you want to execute as a transaction. The CATCH part contains the ROLLBACK command and the error handling code. If no error or exception occurs in the TRY part, the transaction is committed and the CATCH part is skipped. If an error or exception occurs in the TRY part, the transaction is rolled back and the CATCH part is executed.

Handling errors and exceptions in transactions allows you to prevent the database from being corrupted or inconsistent in case of an error or exception. You can also customize the error handling and the rollback process according to your needs and preferences.

How do you use savepoints in transactions? You will learn how to do that in the next section of this tutorial.

6. How to Use Savepoints in Transactions

A savepoint is a named point in a transaction that you can use to rollback the transaction to that point without affecting the entire transaction. Savepoints are useful when you want to undo some changes in a transaction without discarding all the changes. You can create multiple savepoints in a transaction and rollback to any of them as needed.

To create a savepoint in a transaction, you need to use the SAVEPOINT command. This command assigns a name to the current point in the transaction and records the state of the database at that point. You can use any valid identifier as the savepoint name.

The syntax of the SAVEPOINT command is as follows:

SAVEPOINT savepoint_name;

To rollback to a savepoint in a transaction, you need to use the ROLLBACK TO command. This command discards the changes made by the SQL statements after the savepoint and restores the database to the state it was at the savepoint. The savepoint remains valid after the rollback and can be used again.

The syntax of the ROLLBACK TO command is as follows:

ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name;

The WORK and SAVEPOINT keywords are optional and have no effect on the command.

Here is an example of how to use savepoints in a transaction:

-- Start a new transaction
START TRANSACTION;

-- Perform some SQL statements
SELECT * FROM customers;
UPDATE customers SET balance = balance - 100 WHERE id = 1;
UPDATE customers SET balance = balance + 100 WHERE id = 2;

-- Create a savepoint named SP1
SAVEPOINT SP1;

-- Perform some more SQL statements
UPDATE customers SET balance = balance - 50 WHERE id = 3;
UPDATE customers SET balance = balance + 50 WHERE id = 4;

-- Rollback to the savepoint SP1
ROLLBACK TO SP1;

-- The changes made by the last two UPDATE statements are discarded
-- The changes made by the first two UPDATE statements are preserved
-- The savepoint SP1 is still valid and can be used again

As you can see, the SAVEPOINT command creates a savepoint named SP1 after the first two UPDATE statements. The ROLLBACK TO command rolls back the transaction to the savepoint SP1 and discards the changes made by the last two UPDATE statements. The changes made by the first two UPDATE statements are still intact and the savepoint SP1 is still valid and can be used again.

Using savepoints in transactions allows you to have more flexibility and control over the rollback process. You can undo some changes in a transaction without affecting the entire transaction. You can also reuse the savepoints as many times as you want.

How do you set transaction isolation levels in SQL? You will learn how to do that in the next section of this tutorial.

7. How to Set Transaction Isolation Levels in SQL

A transaction isolation level is a setting that determines how much a transaction is isolated from other concurrent transactions. Transaction isolation levels affect the visibility and consistency of the data accessed by the transactions. Different transaction isolation levels provide different trade-offs between performance and data integrity.

There are four standard transaction isolation levels defined by the SQL standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each isolation level prevents or allows certain types of anomalies or phenomena that can occur when multiple transactions access the same data concurrently. These anomalies or phenomena are: dirty reads, non-repeatable reads, phantom reads, and serialization anomalies. You can learn more about these terms in section 8 of this tutorial.

The table below summarizes the transaction isolation levels and the anomalies or phenomena they prevent or allow:

To set the transaction isolation level in SQL, you need to use the SET TRANSACTION command. This command sets the isolation level for the next transaction that you start. You can also use the START TRANSACTION command with the ISOLATION LEVEL option to set the isolation level for the current transaction.

The syntax of the SET TRANSACTION command is as follows:

SET TRANSACTION [transaction_characteristics];

The transaction_characteristics are optional parameters that specify the behavior of the transaction, such as the isolation level, the access mode, and the consistency level. You can learn more about these parameters in section 8 of this tutorial.

The syntax of the START TRANSACTION command with the ISOLATION LEVEL option is as follows:

START TRANSACTION [ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}];

Here is an example of how to set the transaction isolation level in SQL:

-- Set the transaction isolation level to READ COMMITTED for the next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Start a new transaction with the isolation level set to READ COMMITTED
START TRANSACTION;

-- Perform some SQL statements
SELECT * FROM customers;
UPDATE customers SET balance = balance - 100 WHERE id = 1;
UPDATE customers SET balance = balance + 100 WHERE id = 2;

-- Commit the transaction
COMMIT;

-- Start another transaction with the isolation level set to SERIALIZABLE
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Perform some more SQL statements
SELECT * FROM products;
INSERT INTO products VALUES (5, 'Laptop', 500);
DELETE FROM products WHERE id = 3;

-- Commit the transaction
COMMIT;

As you can see, the SET TRANSACTION command sets the transaction isolation level to READ COMMITTED for the next transaction. The START TRANSACTION command starts a new transaction with the isolation level set to READ COMMITTED. The START TRANSACTION command with the ISOLATION LEVEL option starts another transaction with the isolation level set to SERIALIZABLE.

Setting the transaction isolation level in SQL allows you to control how much a transaction is isolated from other concurrent transactions. You can choose the isolation level that suits your needs and preferences, depending on the performance and data integrity requirements of your application.

How do you optimize transaction performance in SQL? You will learn how to do that in the next section of this tutorial.

8. How to Optimize Transaction Performance in SQL

Transaction performance is a measure of how fast and efficiently a transaction executes and completes. Transaction performance is affected by various factors, such as the transaction isolation level, the transaction size, the transaction frequency, the database design, the network latency, the system resources, etc. Optimizing transaction performance is important for improving the overall performance and scalability of your database application.

There are several ways to optimize transaction performance in SQL, such as:

• Choosing the appropriate transaction isolation level: As you learned in section 7 of this tutorial, different transaction isolation levels provide different trade-offs between performance and data integrity. Choosing a lower isolation level, such as READ UNCOMMITTED or READ COMMITTED, can improve the performance of your transactions by reducing the locking and blocking of the data. However, this may also increase the risk of data anomalies or phenomena, such as dirty reads, non-repeatable reads, phantom reads, and serialization anomalies. Choosing a higher isolation level, such as REPEATABLE READ or SERIALIZABLE, can improve the data integrity of your transactions by preventing the data anomalies or phenomena. However, this may also decrease the performance of your transactions by increasing the locking and blocking of the data. Therefore, you need to choose the isolation level that suits your needs and preferences, depending on the performance and data integrity requirements of your application.
• Reducing the transaction size: The transaction size is the number of SQL statements that are executed as a single unit of work in a transaction. Reducing the transaction size can improve the performance of your transactions by reducing the amount of data that needs to be locked, logged, and transferred. However, this may also increase the number of transactions that need to be executed and committed, which may increase the overhead and latency of the database system. Therefore, you need to balance the transaction size and the transaction frequency, depending on the nature and complexity of your application.
• Using the AND CHAIN and NO RELEASE options: These are specific options that you can use with the COMMIT and ROLLBACK commands to control the behavior of the next transaction. The AND CHAIN option starts a new transaction immediately after the current transaction ends, using the same isolation level and access mode as the current transaction. The NO RELEASE option keeps the current connection open after the transaction ends, instead of releasing it and opening a new one for the next transaction. These options can improve the performance of your transactions by reducing the overhead and latency of starting and ending transactions and connections. However, these options are not supported by all database systems and may have some limitations and restrictions. Therefore, you need to check the documentation of your database system before using these options.

Optimizing transaction performance in SQL allows you to improve the speed and efficiency of your transactions and enhance the overall performance and scalability of your database application. You can use various techniques and strategies to optimize transaction performance, depending on the factors and requirements of your application.

What are the benefits and challenges of using transactions in SQL? You will learn about that in the final section of this tutorial.

9. Conclusion

In this tutorial, you have learned how to start, commit and rollback transactions in SQL. You have also learned how to handle errors and exceptions in transactions, how to use savepoints in transactions, how to set transaction isolation levels in SQL, and how to optimize transaction performance in SQL.

Transactions are a powerful feature of SQL that allow you to perform complex operations on the database without compromising its consistency and reliability. Transactions also help you to handle errors and exceptions that may occur during the execution of the statements. By using transactions, you can ensure that the database reflects the desired state of the data after the execution of the statements.

Some of the key points that you have learned in this tutorial are:

• A transaction is a logical unit of work that consists of one or more SQL statements that are executed as a single unit.
• A transaction has four main properties, also known as ACID: atomicity, consistency, isolation, and durability.
• To start a transaction in SQL, you need to use the START TRANSACTION command.
• To commit a transaction in SQL, you need to use the COMMIT command.
• To rollback a transaction in SQL, you need to use the ROLLBACK command.
• To handle errors and exceptions in transactions, you can use implicit rollback or explicit rollback using the ROLLBACK command and the TRY…CATCH block.
• To use savepoints in transactions, you need to use the SAVEPOINT command and the ROLLBACK TO command.
• To set the transaction isolation level in SQL, you need to use the SET TRANSACTION command or the START TRANSACTION command with the ISOLATION LEVEL option.
• To optimize transaction performance in SQL, you can choose the appropriate transaction isolation level, reduce the transaction size, and use the AND CHAIN and NO RELEASE options.

We hope that you have enjoyed this tutorial and learned something useful. If you have any questions or feedback, please feel free to leave a comment below. Thank you for reading!