It seems better to have a Unique constraint on the database side when guaranteeing the uniqueness of data

f:id:R-Hack:20200501153516p:plain

Hello. I am Toyonaga from Rakuma. I continue to hit against the wall with competitive programming. When do you make a Unique constraint on a database? I had a conversation like this with my senior in the past.

This is the scene where I was receiving a review of the table design that would be newly added during development.

Toyonaga: "Do we need a Unique constraint?"

Senior: "If necessary."

Toyonaga: "..."

Databases include MySQL and PostgreSQL. This time, I would like to think about "when is the situation where Unique constraint is applied in MySQL?"

What is a Unique constraint

Guaranteeing that there is only one column of data in a table. (Uniqueness) -[MySQL :: MySQL 5.6 Reference Manual :: 1.7.3.1 PRIMARY KEY and UNIQUE index constraints https://dev.mysql.com/doc/refman/5.6/ja/constraint-primary-key.html)

Consider the case of a web application

When you want to guarantee data uniqueness in Web application (Rails) 1. Verify before saving data on the Web application side (server side) 2. Add Unique constraint on MySQL side I think these will achieve it. Well, here is the answer to the question: "when is the situation where Unique constraint is applied in MySQL?" 1. Verify it on the web application side (server side) before saving data. If it is done, 2. Add Unique constraint on MySQL side. is this unnecessary? It is necessary. Let's look at the cases where you do need it.

Confirmation environment

$ rails --version
Rails 5.2.3

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using  EditLine wrapper

Verification

Preparation

CREATE TABLE `hoges` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uniq_test1` int(11) DEFAULT NULL,
  `uniq_test2` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_test2` (`uniq_test2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO hoges VALUES (null, 11111, 22222, NOW(), NOW());

app/models/hoge.rb

class Hoge < ApplicationRecord
  validates :uniq_test1, uniqueness: true
  validates :uniq_test2, uniqueness: true

  validate :hoge

  def hoge
    sleep(5)
  end
end

On the Web application side (server side), both uniq_test1 and uniq_test2 are verified before saving data.

This time, create a model in the rails console.

$ rails c
Running via Spring preloader in process 12528
Loading development environment (Rails 5.2.3)
irb(main):002:0> ActiveRecord::Base.transaction do
irb(main):003:1* Hoge.create(uniq_test1: 5, uniq_test2: nil)
irb(main):004:1> end
   (1.9ms)  BEGIN
  Hoge Exists (6.3ms)  SELECT  1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1
  Hoge Exists (9.9ms)  SELECT  1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1
  Hoge Create (4.0ms)  INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:42:39', '2020-03-09 16:42:39')
   (6.9ms)  COMMIT
=> #<Hoge id: 6, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:42:39", updated_at: "2020-03-09 16:42:39">

Process flow

Start MySQL transaction
Data uniqueness verification
MySQL transaction end (data is created here)

The problem occurs when they are processed almost at the same time.

MySQL has no unique key constraint (bad pattern)

Console 1

$ rails c
Running via Spring preloader in process 12742
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: 5, uniq_test2: nil)
irb(main):003:1> end
   (2.2ms)  SET NAMES utf8mb4,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
   (1.1ms)  BEGIN
  Hoge Exists (3.2ms)  SELECT  1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1
  Hoge Exists (1.4ms)  SELECT  1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1
  Hoge Create (4.3ms)  INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:55:12', '2020-03-09 16:55:12')
   (7.9ms)  COMMIT
=> #<Hoge id: 7, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:55:12", updated_at: "2020-03-09 16:55:12">

Console 2

$ rails c
Running via Spring preloader in process 12757
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: 5, uniq_test2: nil)
irb(main):003:1> end
   (1.8ms)  SET NAMES utf8mb4,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
   (1.5ms)  BEGIN
  Hoge Exists (2.0ms)  SELECT  1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1
  Hoge Exists (5.1ms)  SELECT  1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1
  Hoge Create (5.1ms)  INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:55:13', '2020-03-09 16:55:13')
   (49.3ms)  COMMIT
=> #<Hoge id: 8, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:55:13", updated_at: "2020-03-09 16:55:13">

Slipped through the web application side (server side)! !! !!

MySQL has unique key constraint (good pattern)