• Home
  • About Us
  • Contact Us
  • Disclaimer
  • Privacy Policy
  • Terms & Conditions
Flyy Tech
  • Home
  • Apple
  • Applications
    • Computers
    • Laptop
    • Microsoft
  • Security
  • Smartphone
  • Gaming
  • Entertainment
    • Literature
    • Cooking
    • Fitness
    • lifestyle
    • Music
    • Nature
    • Podcasts
    • Travel
    • Vlogs
  • Camera
  • Audio
No Result
View All Result
  • Home
  • Apple
  • Applications
    • Computers
    • Laptop
    • Microsoft
  • Security
  • Smartphone
  • Gaming
  • Entertainment
    • Literature
    • Cooking
    • Fitness
    • lifestyle
    • Music
    • Nature
    • Podcasts
    • Travel
    • Vlogs
  • Camera
  • Audio
No Result
View All Result
Flyy Tech
No Result
View All Result

Non-blocking Database Migrations – DZone Database

flyytech by flyytech
September 20, 2022
Home Applications
Share on FacebookShare on Twitter


Database migrations are a common part of any web application. They are used to update the database schema to match the application’s code. In a traditional web application, the database migrations are run synchronously, which means that the application is blocked until the migration is complete. This is not ideal, as it means that the application is unavailable to users during the migration. Long past the days when stopping the service for maintenance was acceptable; we need to be able to run migrations without blocking the application.

It’s easy to perform database migrations in small databases or if you have no load. But what if you have a large database and a lot of users?

Initial Database Structure

Let’s imagine we have a simple table for storing customer data:

DROP TABLE IF EXISTS customer;
CREATE TABLE
    customer (
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email   VARCHAR(256),
    balance FLOAT        NOT NULL DEFAULT 0,
    UNIQUE INDEX email_idx (email)
);

INSERT
INTO
    customer (email, balance)
VALUES
    ('alice@example.com', 42),
    ('bob@example.com', -42);

My main focus will be on the “balance” field.

It’s a float, which means that it can have a fractional part. We want to change the type of this field to an integer so that we can store the balance in cents. This is a common practice in financial applications.

But perhaps you already have an active application with millions of users. As a developer, your responsibility is to make sure the application is available to users during the migration. Also, you need to make sure the application will work correctly after the migration, and in case it’s not working properly, you have to roll back your changes without many customers noticing it.

So the plan is:

  1. Create a new field “balance_cents” with the integer type.
  2. Deploy the application with the new field. A new application version should be available for a limited amount of users.
  3. Make sure the application is working correctly with the new field.
  4. Deploy the new application version for all users.
  5. Drop the old field “balance”.

The tricky part is to make data consistent between the old and new fields. As these fields are related to each other, we need to make sure changes in the old field are reflected in the new field, and vice versa.

Let’s go step by step and see how we can implement this plan.

Create a New Field

Creating a new field “balance_cents” with the integer type is a simple task:

ALTER TABLE
    customer
    ADD COLUMN
        balance_cents INT NOT NULL DEFAULT 0;

Set Up Synchronization Between the Old and New Fields

At this point, the application does not know anything about the new field, and it is a perfect time to set up synchronization between the old and new fields. And to achieve this, we need to create triggers

First, let’s create a trigger on the insert.

DROP TRIGGER IF EXISTS insert_balances;
CREATE TRIGGER insert_balances
    BEFORE INSERT
    ON customer
    FOR EACH ROW IF new.balance <> 0 THEN -- insert from the old code
    SET
        new.balance_cents = CEIL(new.balance * 100);

ELSEIF new.balance_cents <> 0 THEN -- insert from the new code
    SET
        new.balance = new.balance_cents / 100;

END IF;
$$

DELIMITER ;

Let’s do some experiments to see how this trigger works.

Insert from the old code:

INTO
    customer (email, balance)
VALUES
    ('account_from_old_code@example.com', -1.23);

SELECT
    email,
    balance,
    customer.balance_cents
FROM
    customer
WHERE
    email="account_from_old_code@example.com";

+-----------------------------------+---------+---------------+
| email                             | balance | balance_cents |
+-----------------------------------+---------+---------------+
| account_from_old_code@example.com |   -1.23 |          -123 |
+-----------------------------------+---------+---------------+

Insert from the new code:

INSERT
INTO
    customer (email, customer.balance_cents)
VALUES
    ('account_from_new_code@example.com', 345);

SELECT
    email,
    balance,
    customer.balance_cents
FROM
    customer
WHERE
    email="account_from_new_code@example.com";

+-----------------------------------+---------+---------------+
| email                             | balance | balance_cents |
+-----------------------------------+---------+---------------+
| account_from_new_code@example.com |    3.45 |           345 |
+-----------------------------------+---------+---------------+

So the trigger works as expected. 

Now let’s create a trigger for an update.

DROP TRIGGER IF EXISTS update_balances;
DELIMITER $$
CREATE TRIGGER update_balances
    BEFORE UPDATE
    ON customer
    FOR EACH ROW IF new.balance <> old.balance THEN -- update from the old code
    SET
        new.balance_cents = CEIL(new.balance * 100);

ELSEIF new.balance_cents <> old.balance_cents THEN -- update from new code
    SET
        new.balance = new.balance_cents / 100;

END IF;
$$

DELIMITER ;

And now let’s test it.

Making updates from the old code:

UPDATE customer
SET
    balance = -1.45
WHERE
    email="account_from_new_code@example.com";

SELECT
    email,
    balance,
    balance_cents
FROM
    customer
WHERE
    email="account_from_new_code@example.com";

+-----------------------------------+---------+---------------+
| email                             | balance | balance_cents |
+-----------------------------------+---------+---------------+
| account_from_new_code@example.com |   -1.45 |          -145 |
+-----------------------------------+---------+---------------+

Making updates from the new code:

UPDATE customer
SET
    balance_cents = 567
WHERE
    email="account_from_new_code@example.com";

SELECT
    email,
    balance,
    balance_cents
FROM
    customer
WHERE
    email="account_from_new_code@example.com";

+-----------------------------------+---------+---------------+
| email                             | balance | balance_cents |
+-----------------------------------+---------+---------------+
| account_from_new_code@example.com |    5.67 |           567 |
+-----------------------------------+---------+---------------+

Our triggers work as expected. Now we need to fill the empty “balance_cents” field with data from the “balance” field.

Filling the Empty “balance_cents” Field

The simplest way to fill the empty “balance_cents” field is to use the UPDATE statement:

UPDATE customer
SET
    balance_cents = CEIL(balance * 100);

But that update query will put a lot of pressure on the database. And as our main goal is to avoid downtime, the update process should be performed in small batches.

It is possible to create a migration script inside the application, but as we are playing with SQL, let’s create a stored procedure.

DROP PROCEDURE IF EXISTS batch_update_balance_cents;

DELIMITER $$
CREATE PROCEDURE batch_update_balance_cents(
    start_id INT,
    end_id INT,
    batch_size INT)
BEGIN
    DECLARE batch_start INT DEFAULT start_id;
    DECLARE batch_end INT DEFAULT start_id + batch_size;

    IF end_id < start_id + batch_size THEN
        SET end_id = start_id + batch_size;
    END IF;

    WHILE batch_end <= end_id
        DO
            UPDATE customer
            SET
                balance_cents = CEIL(balance * 100)
            WHERE
                id BETWEEN batch_start AND batch_end;

            SET batch_start = batch_start + batch_size;
            SET batch_end = batch_end + batch_size;
        END WHILE;
END$$
DELIMITER ;

CALL batch_update_balance_cents(1, (SELECT
                                        MAX(id)
                                    FROM
                                        customer), 1000);

And now let’s check the result:

SELECT *
FROM
    customer;

+----+-----------------------------------+---------+---------------+
| id | email                             | balance | balance_cents |
+----+-----------------------------------+---------+---------------+
|  1 | alice@example.com                 |      42 |          4200 |
|  2 | bob@example.com                   |     -42 |         -4200 |
|  3 | account_from_old_code@example.com |   -1.23 |          -123 |
|  4 | account_from_new_code@example.com |    5.68 |           568 |
+----+-----------------------------------+---------+---------------+

All old entries were updated.

Drop Triggers and Stored Procedure

Migration is done. Everyone is happy about our latest changes. New application code is deployed to all customers.

It’s time to drop triggers and stored procedures.

DROP PROCEDURE IF EXISTS batch_update_balance_cents;
DROP TRIGGER IF EXISTS update_balances;
DROP TRIGGER IF EXISTS insert_balances;

Drop the Old Field

And now, no one uses the old field. It’s time to drop it.

ALTER TABLE customer
    DROP COLUMN balance;

Conclusion

In this article, we have shown how to migrate from one field to another without downtime.

We have used triggers and stored procedures to keep the data in sync.

I used a particular example with the balance field, but it’s possible to use the same approach for any other field or set of fields.



Source_link

flyytech

flyytech

Next Post
JBL Quantum TWS Earbud Review – Hardware & Tech

JBL Quantum TWS Earbud Review - Hardware & Tech

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recommended.

The Difference Between Clipping and Limiting

The Difference Between Clipping and Limiting

December 9, 2022
Røde NT1 5th Generation Studio Condenser Microphone Innovates With Dual Connect Output and 32-Bit Float Recording

Røde NT1 5th Generation Studio Condenser Microphone Innovates With Dual Connect Output and 32-Bit Float Recording

February 21, 2023

Trending.

Image Creator now live in select countries for Microsoft Bing and coming soon in Microsoft Edge

Image Creator now live in select countries for Microsoft Bing and coming soon in Microsoft Edge

October 23, 2022
Review: Zoom ZPC-1

Review: Zoom ZPC-1

January 28, 2023
Allen Parr’s false teaching examined. Why you should unfollow him.

Allen Parr’s false teaching examined. Why you should unfollow him.

September 24, 2022
Elden Ring best spells 1.08: Tier lists, sorceries, incantations, and locations

Elden Ring best spells 1.08: Tier lists, sorceries, incantations, and locations

January 14, 2023
How to View Ring Doorbell on a Roku TV

How to View Ring Doorbell on a Roku TV

December 20, 2022

Flyy Tech

Welcome to Flyy Tech The goal of Flyy Tech is to give you the absolute best news sources for any topic! Our topics are carefully curated and constantly updated as we know the web moves fast so we try to as well.

Follow Us

Categories

  • Apple
  • Applications
  • Audio
  • Camera
  • Computers
  • Cooking
  • Entertainment
  • Fitness
  • Gaming
  • Laptop
  • lifestyle
  • Literature
  • Microsoft
  • Music
  • Podcasts
  • Review
  • Security
  • Smartphone
  • Travel
  • Uncategorized
  • Vlogs

Site Links

  • Home
  • About Us
  • Contact Us
  • Disclaimer
  • Privacy Policy
  • Terms & Conditions

Recent News

Asus Confirms Intel DLVR Was Fused Off in Raptor Lake

How to Overclock Your CPU: Get the Most MHz

March 26, 2023
CS:GO smashes player records as Counter-Strike 2 hype mounts

CS:GO smashes player records as Counter-Strike 2 hype mounts

March 26, 2023

Copyright © 2022 Flyytech.com | All Rights Reserved.

No Result
View All Result
  • Home
  • Apple
  • Applications
    • Computers
    • Laptop
    • Microsoft
  • Security
  • Smartphone
  • Gaming
  • Entertainment
    • Literature
    • Cooking
    • Fitness
    • lifestyle
    • Music
    • Nature
    • Podcasts
    • Travel
    • Vlogs

Copyright © 2022 Flyytech.com | All Rights Reserved.

What Are Cookies
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent.
Cookie SettingsAccept All
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT