How to Start a MySQL Transaction and Commit Changes

Last update on October 16 2025 12:25:14 (UTC/GMT +8 hours)

Start a Transaction and Commit Changes

Write a MySQL query to start a transaction, update a column, and commit the changes.

Solution:

-- Begin a new transaction to ensure atomicity (all operations either complete or rollback together)
START TRANSACTION;

-- Update the salary of all employees in DepartmentID = 2 by increasing it by 10%:
-- The multiplication (Salary * 1.1) increases each salary by 10%.
-- The WHERE clause ensures only employees in DepartmentID = 2 are affected.
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;

-- Commit the transaction to make the salary updates permanent in the database
COMMIT;

Explanation:

Purpose of the Query:

The goal is to update salaries for employees in a specific department within a transaction and ensure the changes are saved permanently.
This demonstrates the use of START TRANSACTION and COMMIT.

Key Components:

START TRANSACTION: Begins a new transaction.
UPDATE Employees: Updates the salary for employees in Department 2.
COMMIT: Saves the changes permanently.

Why use Transactions?:

Transactions ensure that a set of operations are executed atomically, meaning either all changes are applied, or none are.

Real-World Application:

For example, in a payroll system, you might want to ensure that salary updates are applied consistently and permanently.

For more Practice: Solve these Related Problems:

Write a MySQL query to start a transaction, update a column, and commit the changes in a banking system where a transfer is made between accounts.
Write a MySQL query to update multiple columns within a transaction and commit the changes for employees who received a promotion.
Write a MySQL query to insert multiple records within a transaction and commit only if all insertions are successful.
Write a MySQL query to delete records within a transaction and commit if a certain condition is met.

Go to:

PREV : Transactions and Concurrency Exercises Home
NEXT : Rollback a Transaction on Error.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.