Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Transactions

Explore how to use transactions in MySQL to group multiple statements into atomic units. Understand techniques like row-level locking with SELECT FOR UPDATE, session variables, and rollback mechanisms. This lesson helps you safely handle concurrent data modifications and maintain consistency during complex operations.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
/* Define session variables */
SET @product_id := 1;
SET @qty := 2;
/* Start a transaction */
START TRANSACTION;
/* Capture the before value and lock the row */
SELECT Stock INTO @before_stock
FROM   Products
WHERE  ProductID = @product_id
FOR UPDATE;
/* Apply a temporary decrement */
UPDATE Products
SET    Stock = Stock - @qty
WHERE  ProductID = @product_id;
/* Display both before and after values */
SELECT @product_id AS ProductID,
       @before_stock AS BeforeStock,
       (SELECT Stock FROM Products WHERE ProductID = @product_id) AS AfterStock,
       'Rolled Back' AS Status;
/* Revert changes to maintain data consistency */
ROLLBACK;

Explanation

The explanation of the solution code is given below:

  • Lines 2–3: The SET commands define two session variables: @product_id to identify the product and @qty to represent the quantity to be decremented.

  • Line 6: The START TRANSACTION statement begins an explicit transaction, so all subsequent statements execute as a single atomic unit of work.

  • Lines 9–12: The SELECT ... FOR UPDATE statement reads the current Stock value for the specified product and stores it in @before_stock. It also locks the row so that other concurrent transactions cannot modify it until the current transaction ends.

  • Lines 15–17: The UPDATE statement temporarily decrements the stock value by @qty. Because the transaction has not yet been committed, the change ...