How to Start a MySQL Transaction and Commit Changes

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.

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

Previous MySQL Exercise: Transactions and Concurrency Exercises Home
Next MySQL Exercise: Rollback a Transaction on Error.