catalogue

Business background

Business background

Generally, some record tables of the business system have some uniqueness constraints. For example, duplicate names are not allowed under the same user; You can usually create a unique index on the specified column, for example:

CREATE TABLE `novel`
(
    `id`                      bigint(20) NOT NULL AUTO_INCREMENT,
    `novel_id`                bigint(20)                                                   DEFAULT NULL,
    `user_id`                     varchar(32)                                                  DEFAULT NULL,
    `name`                    varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `novel_name_unique_idx` (`user_id`,  `name`) COMMENT 'Name uniqueness index'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 ;

Database uniqueness constraints can be used to ensure no duplication. However, the deletion operation of online system is usually logical deletion, not physical deletion. The logical deletion will have a deleted field to mark whether to delete, for example:

CREATE TABLE `novel`
(
    `id`                      bigint(20) NOT NULL AUTO_INCREMENT,
    `novel_id`                bigint(20)                                                   DEFAULT NULL,
    `deleted`                 int(11)                                                      DEFAULT '0',
    `user_id`                     varchar(32)                                                  DEFAULT NULL,
    `name`                    varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `novel_name_unique_idx` (`user_id`, `deleted`, `name`, `deleted_version`) COMMENT 'Name uniqueness index'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 ;

There will be problems in this scenario: Novel with the same name can only be deleted once, and deleting twice will lead to the failure of uniqueness constraint verification; If logical code verification is used instead of database index (one account allows multiple users to log in at the same time to create Novel), for example:

public void save(Novel novel){

    //1: Business logic condition verification
    //2: Name repeat check
    if(novel.getName not in database ){
    //3: save novel
    }else{
        return "Name conflict";
    }
}

The concurrency problem will cause two threads to execute if # novel at the same time The getname not in database check results in both passing and repeating. To solve this problem, the options are as follows:

Distributed lock (create serialization)

Create according to User_id obtains distributed locks. Only one logged in user is allowed to create them at a time, and the distributed locks that are not obtained are waiting.

Disadvantages: for high concurrency services, QPS cannot go up and affect concurrency.

Distributed lock (lock User_id and name)

When creating Novel, reserve the name for the user in advance, and the same user will fail to reserve the name again.

Disadvantages: the creation of the first reserved name failed, resulting in the login user of the second reservation not using the name and poor experience.

Expanded database uniqueness index (recommended scheme)

CREATE TABLE `novel`
(
    `id`                      bigint(20) NOT NULL AUTO_INCREMENT,
    `novel_id`                bigint(20)                                                   DEFAULT NULL,
    `deleted`                 int(11)                                                      DEFAULT '0',
    `user_id`                     varchar(32)                                                  DEFAULT NULL,
    `name`                    varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `deleted_version`         bigint(20)                                                   DEFAULT '0' COMMENT 'Delete the version number. When deleting, the value is ID，New as 0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `novel_name_unique_idx` (`user_id`, `deleted`, `name`, `deleted_version`) COMMENT 'Name uniqueness index'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 ;

Due to the uniqueness of the previous index, only user_id, deleted and name will conflict during the second deletion, so you can add another field to solve this problem, that is, deleted_version field; The default value of this field is 0 when the user creates it for the first time. When the user deletes it, the SQL is as follows:

update novel set deleted_version = id , deleted = true  where novel_id = 1 and user_id = 'zhangsan'

The conflict problem of secondary deletion can be solved.