Understanding SQL Transactions: A Beginner’s Guide.

5 min readApr 7, 2023

In SQL, a transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions are used to ensure that database operations are performed in a consistent and reliable manner, and to maintain the integrity of the data stored in the database.

What are Transactions?

Let’s say we want to Insert, Update, or even Delete data from one or more database tables, the Transaction function can help us group together all of these operations as a single unit of work.

Transactions can be thought of as a wrapper around a set of SQL statements that ensures that either all the statements in the transaction are executed successfully, or none of them are executed at all.

The purpose of a transaction is to ensure that all the database operations within that transaction are treated as a single, atomic operation. This means that either all the operations within the transaction are completed successfully, or none of them are. If an error occurs during the transaction, all the changes made up to that point will be rolled back, meaning that the database will be restored to its previous state.

To use transactions in SQL, you need to use a few SQL statements. The most common ones are:

BEGIN TRANSACTION: This statement begins a new transaction. Any SQL statements that follow this statement are treated as part of the transaction until the transaction is either committed or rolled back.
COMMIT TRANSACTION: This statement saves the changes made during the transaction to the database. If the transaction completes successfully, the changes are permanent.
ROLLBACK TRANSACTION: This statement cancels the changes made during the transaction and restores the database to its previous state.

Enough of the talk, let’s delve into a few examples……

Example 1

In this example, we are using a transaction to update the status of an order and decrement the quantity of a product in the inventory table simultaneously.

BEGIN TRANSACTION;

-- update the orders table
UPDATE orders 
SET status = 'shipped' 
WHERE order_id = 123;

--update the inventory table
UPDATE inventory 
SET quantity = quantity - 1 
WHERE product_id = 456;

COMMIT TRANSACTION;

If both statements complete successfully, the transaction is committed and the changes are made permanent in the database.

Example 2.

Suppose we have a table called “employees” with columns “employee_id”, “first_name”, and “last_name”. We want to update the last name of employee with ID 123 to “Doe” and employee with ID 456 to “Smith”. Here’s how we might do this using a transaction:

BEGIN TRANSACTION;

UPDATE employees
SET last_name = 'Doe'
WHERE employee_id = 123;

UPDATE employees
SET last_name = 'Smith'
WHERE employee_id = 456;

COMMIT TRANSACTION;

In this example, we start a new transaction using BEGIN TRANSACTION. We then update the last name of employee with ID 123 to “Doe” and employee with ID 456 to “Smith”. Once both updates are complete, we commit the transaction to make the changes permanent.

Example 3

Let’s say we have a database with a table called “customers”. We want to update the phone number of a customer with the ID of 12345 and insert a new customer into the table with an ID of 67890.

Here’s how we might use a transaction to ensure that both of these operations are treated as a single atomic unit:

BEGIN TRANSACTION;

UPDATE customers
SET phone_number = '555-1234'
WHERE customer_id = 12345;

INSERT INTO customers (customer_id, name, phone_number)
VALUES (67890, 'John Doe', '555-6789');

COMMIT TRANSACTION;

In this example, we use the BEGIN TRANSACTION statement to start a new transaction. We then update the phone number of the customer with ID 12345 and insert a new customer with an ID of 67890. Finally, we use the COMMIT TRANSACTION statement to commit the changes made during the transaction to the database.

Now, let us take a look at a complex example to illustrate how transactions work.

Example 4.

Imagine we have a database with two tables: one for customer orders and one for inventory levels. When a customer places an order, we need to update both tables to reflect the new order and reduce the inventory level for the relevant product.

Here’s an example of how we might accomplish this using transactions in SQL:

BEGIN TRANSACTION;

-- insert new order into orders table
INSERT INTO orders (customer_id, product_id, quantity, status)
VALUES (1, 2, 3, 'Pending');

-- update inventory level for relevant product
UPDATE inventory
SET quantity = quantity - 3
WHERE product_id = 2;

-- check if inventory level is now negative
IF EXISTS (SELECT * FROM inventory WHERE product_id = 2 AND quantity < 0)
BEGIN
  -- if inventory level is negative, rollback transaction
  ROLLBACK TRANSACTION;
  PRINT 'Error: inventory level is negative';
END
ELSE
BEGIN
  -- if inventory level is not negative, commit transaction
  COMMIT TRANSACTION;
  PRINT 'Order successfully placed';
END

Note in the example above, we begin by starting a transaction using the BEGIN TRANSACTION statement. We then insert a new order into the orders table, and update the inventory level for the relevant product using an UPDATE statement. We then check if the inventory level is now negative using an IF statement. If it is, we roll back the transaction and print an error message. If it’s not, we commit the transaction and print a success message.

However, if the inventory level is negative and we rollback the transaction, both the new order and the inventory update will be undone, ensuring that the database remains in a consistent state.

Example 5

Finally, imagine we have a database with two tables: one for employee information and one for employee salaries. When we update an employee’s salary, we need to update both tables to ensure consistency.

Here’s an example of how we might accomplish this using transactions in SQL:

BEGIN TRANSACTION;

-- update employee salary in salaries table
UPDATE salaries
SET salary = 75000
WHERE employee_id = 123;

-- update employee information in employees table
UPDATE employees
SET last_name = 'Doe'
WHERE employee_id = 123;

-- check if both updates were successful
IF @@ROWCOUNT = 1
BEGIN
  COMMIT TRANSACTION;
  PRINT 'Employee salary and information updated successfully';
END
ELSE
BEGIN
  ROLLBACK TRANSACTION;
  PRINT 'Error: failed to update employee salary and information';
END

In this example, we begin by starting a transaction using the BEGIN TRANSACTION statement. We then update the employee’s salary in the salaries table and their last name in the employees table. We then check if both updates were successful using @@ROWCOUNT, which returns the number of rows affected by the previous statement. If both updates were successful, we commit the transaction and print a success message. If not, we rollback the transaction and print an error message.

Conclusion

Transactions are an important concept in SQL, as they help ensure data consistency and reliability. By grouping together multiple operations as a single atomic unit, you can avoid errors and ensure that your database remains in a consistent state.

I hope you find this article helpful, remember to keep practicing this concept.

Thanks for reading. 🙏🙏🙏