Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Transactional Statements

Explore how transactional statements enable control over data changes in MySQL. Understand the ACID properties ensuring database consistency and learn to use START TRANSACTION, COMMIT, and ROLLBACK to verify changes before committing or rolling back for safer data manipulation.

We'll cover the following...

In our running example, we mostly worked with SQL SELECT statements. That is, with queries that read data from the database without modifying it. Though, this is certainly possible with SQL using clauses like INSERT INTO, UPDATE, and DELETE. In our running example of car model parts, we could find ourselves updating the built_at attribute of newly assembled parts:

MySQL
-- Generate a table for sample car models.
DROP TABLE IF EXISTS CarPart;
DROP TABLE IF EXISTS CarModel;
CREATE TABLE CarModel
(
    id           INT auto_increment,
    manufacturer TEXT DEFAULT NULL,
    name         TEXT DEFAULT NULL,
    `power (kW)` INT  DEFAULT NULL,
    PRIMARY KEY (id)
);
-- Populate the generated table with sample car models.
INSERT INTO CarModel (manufacturer, name, `power (kW)`)
VALUES ('Audi', 'A3', 81),
       ('Audi', 'A4', 100),
       ('Audi', 'A5', 110),
       ('Audi', 'A6', 210),
       ('Audi', 'A7', 150),
       ('Audi', 'A8', 210),
       ('Mercedes Benz', 'C63 AMG', 350),
       ('Mercedes Benz', 'S63 AMG', 450),
       ('Volkswagen', 'Golf VII', 92),
       ('Volkswagen', 'Golf VIII', 110);
-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATE DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);
-- Populate the generated table with sample car model parts.
INSERT INTO CarPart (model, name, built_at)
VALUES (1, 'exterior', '2022-03-28'),
       (1, 'interior', NULL),
       (1, 'wheels', '2022-03-28'),
       (2, 'exterior', '2022-03-28'),
       (2, 'interior', '2022-03-28'),
       (2, 'wheels', '2022-03-28'),
       (3, 'exterior', '2022-03-27'),
       (3, 'interior', '2022-03-28'),
       (3, 'wheels', '2022-03-28'),
       (4, 'exterior', '2022-03-26'),
       (4, 'interior', NULL),
       (4, 'wheels', NULL),
       (5, 'exterior', '2022-03-28'),
       (5, 'interior', '2022-03-28'),
       (5, 'wheels', '2022-03-28'),
       (6, 'exterior', NULL),
       (6, 'interior', NULL),
       (6, 'wheels', NULL),
       (7, 'exterior', '2022-03-27'),
       (7, 'interior', NULL),
       (7, 'wheels', NULL),
       (8, 'exterior', NULL),
       (8, 'interior', NULL),
       (8, 'wheels', NULL),
       (9, 'exterior', '2022-03-26'),
       (9, 'interior', '2022-03-27'),
       (9, 'wheels', '2022-03-28'),
       (10, 'exterior', '2022-03-24'),
       (10, 'interior', '2022-03-26'),
       (10, 'wheels', '2022-03-28');
-- Update `built_at` for newly assembled parts of the car model with `id = 4`.
UPDATE CarPart
SET built_at = CURRENT_DATE
WHERE model = 4;

While the above SQL statement correctly updates built_at for the previously missing car parts, the ones already assembled have also been updated by accident. Without our memory, we would need to rely on external documentation or backups of the database to recover from this mistake. Although artificial, this example ...