法人番号公表サイトについてのメモ

法人番号公表サイトとは

  • 国税庁が公表している法人固有の番号(マイナンバー的な)を検索できるサイト
  • APIで取得するパターンとCSV(or XML)をダウンロードするパターンがある
    • ダウンロードの場合は差分データを公開してくれているので更新も簡単!

www.nta.go.jp

含まれている情報は

  • 色々あるけど使いそうなところで言うとこんなところか
    • 法人番号
    • 商号又は名称
    • 国内所在地
    • フリガナ

EDINETで公開されている財務情報には法人番号が含まれているので紐付けることができる。 少なくとも上場している企業であれば概ね自動的に最新情報に更新していく機能は組めそう

オープンデータが進むと色々楽しいコードが書けそうだなー

EDINETから財務情報を取得するメモ

EDINETってなに

www.fsa.go.jp

EDINETはAPIを公開しているので画面をポチポチしなくても情報を取ってくることができる

流れ

  • 提出された書類を把握するためのAPI(書類一覧API)から提出された書類情報を取得
  • 提供された書類を取得するためのAPI(書類取得API)から提出された書類を取得

この辺は、EDINETの操作ガイドにある仕様書に記載があるので詳細は譲る

書類一覧API

  • 単一日付を指定してその日に提出された書類一覧を取得する
  • 基本的には提出書類一覧及びメタデータを取得するので type=2になるはず

用途にもよるがある程度の企業情報を必要とする場合は日付分APIを呼び出す必要がある あまり負荷がかからないように気をつけよう こちらを参考にしつつ、必要な期間の日付を生成 【Python】指定した期間の連続した日付をだす - Qiita

import os
import requests
import datetime
from datetime import timedelta

import json
import time

def daterange(_start, _end):
    for n in range((_end - _start).days):
        yield _start + timedelta(n)

base_url = 'https://disclosure.edinet-fsa.go.jp/api/v1/documents.json' # v1は変わる可能性があるらしい
base_filename = 'documents_'

def download(self, date, type=2):
    param_date = str(date)
    params = {
        "date": param_date,
        "type": type
    }
    filename = self.base_filename + param_date + ".json"
    path = "./documents/" + filename # パスは適当
    
    if not os.path.exists(path): # 再取得を避ける
        url = self.base_url
        response = requests.get(url, params=params)
        json = response.content
        with open(path, 'wb') as file:
            file.write(json)
        time.sleep(2) # 適当な時間待たせる
    return path

for target_date in daterange(start_date, end_date):
    print(target_date)
    path = download(target_date)

取得したJSONに法人番号も含まれているので紐付けておくと吉 書類管理番号(docID)があるので必要な書類のIDを控えておく

書類取得API

  • 種類一覧APIで取得した書類管理番号(docID)を使って書類を取得する
  • 基本的にはXBRLという形式のはずなので定義書を確認するなり、CSVへの変換ツールが提供されているのでそちらを使うなりする

あくまで一例(個人的には、書類管理番号(docID)の配列を作って↓を回すのが効率的な気がする)

import os
import requests

import time
import glob
import zipfile
import shutil

doc_id = xxxxx # 書類一覧APIから取得した書類管理番号(docID)
url = "https://disclosure.edinet-fsa.go.jp/api/v1/documents/" + doc_id # v1は変わる可能性があるらしい
params = {"type": 1}
filename = "./edinet/" + doc_id + ".zip" # パスは適当
if not os.path.exists(filename):  # 再取得を避ける
    res = requests.get(url, params=params ,stream=True)

    if res.status_code == 200:
        with open(filename, 'wb') as file:
            for chunk in res.iter_content(chunk_size=1024):
                file.write(chunk)
    time.sleep(3) # 適当な時間待たせる

# ダウンロードするのがzipファイルなので解凍までしておく
zip_filepath = "./edinet/" + doc_id
zip_file = zipfile.ZipFile(filename)
zip_file.extractall(zip_filepath)

# 必要なものがわかっている場合はそれだけ残してあとは消す
files = glob.glob(zip_filepath + "/XBRL/PublicDoc/*.xbrl")
for file in files:
    shutil.move(file, "./edinet/xbrl/" + doc_id + ".xbrl")
    print(file)
    dir = "/".join(file.split("/")[:3])
    if os.path.exists(dir):
        shutil.rmtree(dir)

あとは、XRBLの仕様書とにらめっこしながら必要な情報を抽出する 目検のときはPDF版と照らし合わせると確認しやすい

XRBLのパースの一例

import xmltodict
with open(file) as f:
    doc = xmltodict.parse(f.read())
    company_name = doc["xbrli:xbrl"]["jpcrp_cor:CompanyNameCoverPage"]["#text"]

ざっくり始めるクエリパフォーマンスチューニング

お断り

  • 基本的にはMySQL(InnoDB)を前提にしています
  • ものによりMySQL5.6と5.7の情報が混在しているかも
  • 一部個人的な見解が含まれています

クエリの実行順序

大きい括りでの基本戦略

  • あくまで個人的な意見
  • DBサーバにはDBサーバにしかできないことをやらせる戦略
    • アプリサーバでできるなら任せる
      • 例えば、データのソート、絞り込み
    • 転送量との相談にはなるがざっくり投げてもネットワークに負荷がかからない、アプリサーバのメモリに乗り切る場合はアプリサーバにぶん投げてアプリサーバ上でやる
    • DBサーバのリソースは貴重です
    • DBのキャッシュを有効にしているとキャッシュヒット率が改善されるかもしれません
      • MySQL8.0からキャッシュが無効になったので過度なキャッシュ利用は避けましょう
      • キャッシュは麻薬
    • DBのキャッシュ機能をオフっていてもOSキャッシュが効く可能性がある
    • DBサーバのリソースはDBサーバにしかできないことに使いましょう
      • 基本的には、更新系クエリ優先
      • 読み取りは、レプリケーションを使えばある程度はスケールアウトが可能
      • 更新系の処理で負荷に耐えれない場合は一般的にはスケールアップで対応する
      • スケールアップにも限界がある
      • マルチマスタという手もあるが一般的に運用(障害発生時など)が大変なので避けたほうが良いというスタンス
      • 物理的にDBを分ける垂直分割という手もあるがトランザクションやデータの整合性を担保するのにアプリ側の対応が必要になる場合がある
  • これが絶対正しいわけではない

実行計画のもととなるデータ

実行計画の改善の基本戦略

  • インデックスを活用する
    • インデックス特性を理解して取得する
    • カバリングインデックスが使えて効果的な場合は使う
    • カーディナリティが低い場合は、あえてインデックスを設定しない選択もする
    • 基本的にMySQL(InnoDB)は一つのテーブルにつきインデックスは一つしか使われない
      • ケースにより使われる(sort_union)
  • 不要なデータは取得しない
    • ただしアプリに処理を委ねるときはその限りではない
  • 駆動表は可能な限り小さく
  • オンメモリで完結させる
    • Using temporaryはメモリ上では対応できなくなったという意味で外部装置を使っている
    • いくらSSDが速くなったとはいえメモリに勝てるほどではない
    • 読み込み速度を意識する
    • あくまで目安。実際はSSDはもっと速いものもあるが
名前 読込速度 読込速度(MB/s)
メモリ 10GB/s 10000MB/s
有線ネットワーク 1GB/s 1000MB/s
SSD(シーケンシャルアクセス) 100MB/s 100MB/s
HDD(シーケンシャルアクセス) 100MB/s 100MB/s
SSD(ランダムアクセス) 10MB/s 10MB/s
HDD(ランダムアクセス) 1MB/s 1MB/s

カーディナリティ

  • 多様性のこと
  • 都道府県やフラグ、モードなどデータの種類が限られていること
  • データの種類が限られていることをカーディナリティが低いという
  • 一般的にはカーディナリティが低いとインデックスの効果が薄いと言われる
    • Bツリーでの絞り込み効果が低いため
  • ただし、カバリングインデックスを効かせるためにあえて複合インデックスの一部として設定することもある
    • クラスタインデックス側を参照しなくて良いのでパフォーマンスは向上する

駆動表

インデックス

出力フォーマットの変更

  • MySQL5.6から実行計画はテーブル形式の他にJSON形式でも出力できる
    • explain format=json select ~
  • テーブル形式より細かい粒度の情報が出力される
    • 特にコストが表示されるので実際に何にコストがかかっているかを分解してみることができる
  • それぞれのコストとかの説明とかあると良いよね

実行計画の改善

実行計画の出力イメージ

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
SELECT 識別子。 SELECT 型 出力行のテーブル 一致するパーティション 結合型 選択可能なインデックス 実際に選択されたインデックス 選択されたキーの長さ インデックスと比較されるカラム 調査される行の見積もり テーブル条件によってフィルタ処理される行の割合 追加情報

type

  • レコードへのアクセスタイプ。上のほうが良い
type名 説明 意訳
system systemテーブルの話
const 一致するレコードが1つしかない 1件しかないので高速
eq_ref プライマリーキーもしくはユニークなキーでの結合 1対1でマッチするから高速
ref 上記以外の等価結合 NULLの可能性もあるが1対1でマッチするから高速
fulltext FULLTEXTインデックスが使われる よく知らん
ref_or_null IS NULLを使ったアクセス NULLアクセスはインデックス効かないけど最適化されているので若干マシ
index_merge 2つのキーをマージする 単体のインデックスアクセスよりは高速
unique_subquery サブクエリの最適化パターン。一意キーを選択した場合これになる サブクエリなので一般的な最適化されたアクセスタイプよりは遅い
index_subquery サブクエリの最適化パターン。一意でないキーを選択した場合これになる ユニークではない分、unique_subqueryより遅い
range 範囲指定をした場合でのアクセスタイプ 範囲指定している分アクセス対象が多いので遅め
index インデックスをフルスキャン インデックスをあまり上手く使えてないよ
ALL フルテーブルスキャン 基本的には避ける。ただしデータが小さいテーブルはこれになることもあるしそれが最適なこともある

extra

  • 自分も知らないしかいつまんで
  • 特にこの2つはメモリに乗り切らない可能性があり外部装置に吐き出された瞬間パフォーマンスが悪化する要因になる
  • 特に目くじらを立てて一掃する必要はないが、そもそも不要な処理をさせている可能性があるので使っていることを理解し問題ない判断をした上で使うことをおすすめする
    • ORマッパが吐き出すクエリを基本的には信用しない
extra 説明 意訳
Using filesort ソート処理でクイックソートが実行されている ソートは本当にDB上でする必要がありますか?
Using temporary 一時的にテーブルを作成している 結果が多すぎませんか?もっと小さくできませんか?

その他

  • 実行されたクエリ自体はGeneral Logに出力されるので本番環境とかでも確認は可能
  • ただし、実行時間までは出ない
  • 可能ならslow query logに出力させることで実行時間を知ることができる
    • slow_query_log = 1, long_query_time = 0 と設定すると実行されるクエリがすべて出力される(かしこー)
    • ただし再起動しないと反映されないので注意
  • https://nishinatoshiharu.com/mysql-slow-query-log/

MySQLの権限を絞ってみた

権限設定でカラムレベルというものが存在するらしいのでどういうものか確かめてみた

root権限

mysql> create table if not exists grant_columns(
    ->     id int unsigned not null AUTO_INCREMENT,
    ->     name varchar(255) NOT NULL,
    ->     granted_name varchar(255) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into grant_columns (name, granted_name) values ('aaa', 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> create user test_user@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+-----------------------------------------------+
| Grants for test_user@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' |
+-----------------------------------------------+
1 row in set (0.01 sec)

mysql> GRANT SHOW DATABASES ON test.* TO 'test_user'@'localhost';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT select(`granted_name`) ON test.grant_columns TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+----------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                   |
+----------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost'                           |
| GRANT SELECT (granted_name) ON `test`.`grant_columns` TO 'test_user'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

test_user

mysql> use test;
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'test'
mysql> SHOW GRANTS FOR test_user;
ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host '%'
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| grant_columns  |
+----------------+
1 row in set (0.00 sec)

mysql> desc grant_columns;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| granted_name | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from grant_columns;
ERROR 1143 (42000): SELECT command denied to user 'test_user'@'localhost' for column 'id' in table 'grant_columns'
mysql> select granted_name from grant_columns;
+--------------+
| granted_name |
+--------------+
| bbb     |
+--------------+
1 row in set (0.00 sec)

mysql> 

descでは他にカラム定義がないように見えるのに*を使うと怒られる(´・ω・`) 「見せる」設定はできるけど「見せない」という設定はできないように見える。

MySQLについていろいろ書いてみる(2020年)

重複しているものもあるけど一旦書く

ストレージエンジン

ストレージエンジンとは?

  • MySQLはいくつかのコンポーネントを組み合わせてできている
  • その中で実際にデータを格納してデータを取得するところはストレージと呼ばれていて選択することが可能
  • 他にも全文検索用のMroongaやオンメモリのMemoryストレージなどがある
  • それまではMyISAMがデフォルトだった

    InnoDB

  • MySQL5.5からデフォルトになったストレージエンジン
  • トランザクションをサポートしている
  • パフォーマンスの懸念があったがどっかのタイミングでMyISAMを抜いた
  • 一般的にMySQLの文脈でストレージエンジンに関することを言っていたら大体InnoDBのことを言っていると言っても良い
  • この説明も基本的にInnoDBのことについて書く

    MyISAM

  • MySQL5.4まではデフォルトだったストレージエンジン
  • なんとトランザクションをサポートしていなかった
  • パフォーマンスは悪くなく敢えてMyISAMを選択することもあったとか
  • パフォーマンスでInnoDBに抜かれたことで存在価値がなくなった
  • お疲れ様でした

    インデックス

    B+ツリーとは?

  • Bツリーの改良版
  • ツリーなのでパフォーマンスはO(log b n)
  • ノード連結リストでつながっていることが多いので範囲指定がやりやすい(range検索に有効)
  • https://ja.wikipedia.org/wiki/B%2B%E6%9C%A8

    クラスタインデックス

  • 主キーが設定されていると主キーのインデックスにデータが格納されている
  • MySQL(InnoDB)で主キー検索が速いのはこの構造による

    セカンダリインデックス

  • 主キー以外のインデックスのこと
  • 主キー以外なので、インデックスにはインデキシングされた値と主キーが格納されている
  • セカンダリインデックスの場合は、2回インデックスの走査が行われる
  • ちなみにOracleはインデックスとデータが2つに別れているのでそもそも2回走る

    カバリングインデックス

  • MySQLに関わらず、クエリで参照される値がインデックスだけで十分な場合、インデックスの値のみで完結するので速い
  • パフォーマンスチューニングでよく利用されるテクニックの一つ

    インデックスを設定するときの注意点

  • インデックスにはコストがかかる
    • 単純にインデックスを格納するストレージ
    • インデックスを作成するイニシャルコスト(CPU、メモリ、ストレージ)
      • 最近(MySQL5.6以降)はオンラインDDLで実行できるのでそこまで気にしていないかも?
    • インデックスを維持するコスト
      • 特に更新系のクエリを実行するとインデックスの再構築を行う
  • インデックスを設定するときは本当に使うのか?本当に必要か?を考えて設定する

    ロック

  • ここからは特に断りがなければInnoDBかつREPEATABLE READを前提にします

    テーブルロック

  • ロックの範囲のこと
  • テーブル自体をロックする
  • これが起こるとパフォーマンスが低下する場合が多くなるべく避ける

    行ロック

  • ロックの範囲のこと
  • 最小のロック単位のためこれになるように工夫する
  • 行自体をロックする
  • 特定のカラムだけロック、とかはない

    ギャップロック

  • 範囲指定してロックをかけたときにその間も一緒にかけるロックのこと
  • 例えば、実データがID1、4、5とあったとき、ID1〜5でロックを書けるとID1、5はもちろんID4もロックをかけ、ID2、3もINSERTできないようになる
  • ギャップロックは存在しないデータに対してもロックをかける
  • 空振るとテーブルロック相当のロックをかける
  • ギャップロックはインデックスをベースにしてロックをかけているのでインデックスないところをロックしようとしてもテーブルロックになるので注意

    ネクスキーロック

  • 結構難しい
  • 行ロックとギャップロックを組み合わせたもの
  • これのおかげでInnoDBではファントムが発生しない

    ロックの確認

  • information_schema.INNODB_TRX

    ロッキングリードとノンロッキングリード

  • ロッキングリードはselect ~ for updateselect ~ for shareなどロックをかけて読み取ること
  • ノンロッキングリードは通常の読み取り
  • MVCCで更新中のトランザクションがある場合に2つのリードの結果が異なることがある
  • ノンロッキングリードで読み取られるのは古いバージョンのデータ
  • なので、最新のデータが何であろうと、ロックされていようがいまいが構わない
  • つまり、最新の情報がほしいときはロッキングリードを用いる必要がある

    MVCC

  • MultiVersion Concurrency Control
  • 日本語では多重バージョン並行処理制御などと呼ばれる
  • ロック中に読み取り性能を損なわないために更新前のスナップショットを返す仕組み
  • MySQLに限らず、OraclePostgreSQLや他の製品にもある
  • InnoDBではこれがあるため、ファントムリードが起きないことになっている
  • これがあることで別の問題が発生することもある
  • https://ja.wikipedia.org/wiki/MultiVersion_Concurrency_Control

    トランザクション分離レベル

    REPEATABLE READ

  • MySQL(InnoDB)のREPEATABLE READでは、ネクスキーロックがあるので事実上、ファントムリードを防ぐのでSERIALIZABLE相当の実装になっている。
  • ただし、ロストアップデートをストレージエンジンが防いでくれないので気をつけないといけない(PostgreSQLとかは防いでくれるらしい)

    結合の種類

    NL

  • Nested Loop
  • InnoDBにはこれしかない
  • 駆動表が大きくなると不利なのでなるべく小さくしましょう
  • 要はループの入れ子
  • なので一番外のループ回数が少ないと性能は上がる

    Hash

  • ハッシュテーブルを作成する
  • メモリを食う
  • インデックスがなくてフルスキャンが必要なときにはパフォーマンスを発揮するらしい

    ソートマージ

  • 結合する方、される方どちらもソートされている必要がある
  • 完全外部結合などをするときに高いパフォーマンスを発揮するらしい
  • あんま使われているところを見たことない
  • こちらもメモリを食う

    実行計画

    統計情報

  • 実行計画を作成するときに参照される情報
  • インデックス使うべきか、とかデータ量からテンポラリファイルを作成するべきか、とかを計画するのに使う
  • 基本的にはサンプリングされた情報が入る
  • 更新頻度によっては実態と乖離することもある
  • これを手動で更新して痛い目をみたことがある(Oracle)
  • SELECT * FROM mysql.innodb_table_stats で確認できる
  • https://dev.mysql.com/doc/refman/5.6/ja/innodb-persistent-stats.html

    カーディナリティ

  • データの多様性
  • 限られた種類ならインデックス効いてなくても良いかな
  • カバリングインデックスを狙ってインデックス設定するのはあり

    ロックの性質

  • 行ロックでも書き込みが頻発するなら待ちが発生しやすい

    ヒント句

  • MySQLにはインデックスヒントを指定することができる
  • ただしオススメしない
  • 最近のMySQLオプティマイザはバカじゃないので基本的に任せたほうが良い
    • 統計情報が更新されると適切な計画に自動で変更してくれる
  • 特にデータが増えたときに適切なインデックスを選択できなくなるリスクがあるので基本的にはおまかせする
  • 自分がオプティマイザより賢いという自負があるなら設定すると良い

    テーブル設計

    正規化

    実際的な設計のポイント

  • 正しく行ロックをかけていたとしても更新が集中するレコードがある場合は期待値通りのパフォーマンスが出ないことがある
  • 例えばカウンターとか。単純にインクリメントしていくやつ
  • そういう場合は、レコード追加で定期的に集計とかで代用できないか考える

    敢えて正規化を崩す

  • JOINのコストを減らしたいということだと思われる
  • データを冗長に持つことを許容する?
  • 多分その延長線上にNoSQLがある気がする
  • 少なくとも普通のWebサービスでそこまでのトレードオフを迫られることはないのでは?

    サンプル

    準備

    docker-compose.yml

version: '3'

services:
    mysql:
        image: mysql:5.7
        command: --default-authentication-plugin=mysql_native_password
        environment:
          MYSQL_ROOT_PASSWORD: root
          MYSQL_DATABASE: test
          MYSQL_USER: user
          MYSQL_PASSWORD: password
          TZ: 'Asia/Tokyo'
        ports:
            - 3306:3306
        volumes:
            - ./my.cnf:/etc/mysql/conf.d/my.cnf

my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4
$ docker-compose exec mysql bash

データ準備

create table if not exists user(
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

insert into user (name) values ("user1"), ("user2"), ("user3"), ("user4"), ("user5");

-- ID=4 を削除
delete from user
where id = 4;

パターン1:trx1でインデックスを指定してUPDATE、trx2ではINSERTできる

NO trx1 trx2
1 begin;
2 -- データを確認
select * from user;
3 update user
set name = 'user5-1'
where id = 5;
4 -- データを確認
select * from user
5 begin;
6 -- データを確認(id=5のnameはname5)
select * from user;
7 -- 成功
insert into user (id, name) values(4, "name4");
8 -- 成功
insert into user (name) values("name6");
9 -- データを確認(id=5のnameはname5)
select * from user;
10 -- データを確認(id=4,6は見えない)
select * from user;

最終的なロックの状態

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1872
                 trx_state: RUNNING
               trx_started: 2020-03-15 12:10:17
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 7
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 2
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1871
                 trx_state: RUNNING
               trx_started: 2020-03-15 12:10:16
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> 

もちろんこの状態でtrx1がID=4へのINSERTをしてもロックされている

パターン2:trx1でインデックスを設定していないカラムを指定してUPDATE、trx2でINSERTできない→テーブルロックがかかる

NO trx1 trx2
1 begin;
2 -- データを確認
select * from user;
3 update user
set name = 'user5-1'
where name = 'name5';
4 -- データを確認
select * from user
5 begin;
6 -- データを確認(id=5のnameはname5)
select * from user;
7 -- 待ち
insert into user (id, name) values(4, "name4");
8 -- 待ち
insert into user (name) values("name6");
9 -- データを確認(id=5のnameはname5)
select * from user;

最終的なロックの状態

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1883
                 trx_state: LOCK WAIT
               trx_started: 2020-03-15 16:10:49
     trx_requested_lock_id: 1883:26:3:10
          trx_wait_started: 2020-03-15 16:10:54
                trx_weight: 2
       trx_mysql_thread_id: 7
                 trx_query: insert into user (id, name) values(4, "name4")
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1882
                 trx_state: RUNNING
               trx_started: 2020-03-15 16:10:39
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 5
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> 

ここでtrx_rows_lockedが5なのはテーブルロックがかかったため実データの4件+ギャップロックの1件(削除したID=4)のデータと思われる

パターン3:trx1でインデックスを範囲指定してUPDATE、trx2ではINSERTできるものとできないものがある

NO trx1 trx2
1 begin;
2 -- データを確認
select * from user;
3 update user
set name = concat(name, '-1')
where id > 1 and id < 5;
4 -- データを確認
select * from user;
5 begin;
6 -- データを確認
select * from user;
7 -- 待ち
insert into user (id, name) values(4, "name4");
8 -- 成功
insert into user (name) values("name6");
9 -- データを確認
select * from user;

最終的なロックの状態

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1887
                 trx_state: RUNNING
               trx_started: 2020-03-15 16:19:55
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 7
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1886
                 trx_state: RUNNING
               trx_started: 2020-03-15 16:19:24
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 2
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> 
  • これはギャップロックによるもの
  • trx_id=1886のtrx_rows_locked が3になっているのはID=2,3,4のこと
    • ID=4は存在しないがギャップロックによりロック対象になる
  • whereで指定した範囲は2〜4だったので最終行にINSERTできた

パターン4:trx1でインデックスを範囲指定してUPDATE、trx2ではINSERTできない

NO trx1 trx2
1 begin;
2 -- データを確認
select * from user;
3 update user
set name = concat(name, '-1')
where id > 1 and id <= 5;
4 -- データを確認
select * from user;
5 begin;
6 -- データを確認
select * from user;
7 -- 待ち
insert into user (id, name) values(4, "name4");
8 -- 待ち
insert into user (name) values("name6");
9 -- データを確認
select * from user;

最終的なロックの状態

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1890
                 trx_state: RUNNING
               trx_started: 2020-03-15 16:25:20
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 1
       trx_mysql_thread_id: 7
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1889
                 trx_state: RUNNING
               trx_started: 2020-03-15 16:24:52
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 5
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 4
         trx_rows_modified: 3
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> 
  • 範囲指定に最終行のID=5が含まれているのでネクスキーロックが効いて末尾へのINSERTができなくなった

ロッキングリードとノンロッキングリード

NO trx1 trx2
1 mysql> begin;
Query OK, 0 rows affected (0.01 sec)
2 mysql> select * from user\G
1. row
id: 1
name: user1
2. row
id: 2
name: user2
3. row
id: 3
name: user3
4. row
id: 5
name: user5
4 rows in set (0.01 sec)
3 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
4 mysql> select * from user\G
1. row
id: 1
name: user1
2. row
id: 2
name: user2
3. row
id: 3
name: user3
4. row
id: 5
name: user5
4 rows in set (0.01 sec)
5 mysql> update user
-> set name = concat(name, '-1')
-> where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
6 mysql> select * from user\G
1. row
id: 1
name: user1-1
2. row
id: 2
name: user2
3. row
id: 3
name: user3
4. row
id: 5
name: user5
4 rows in set (0.01 sec)
7 mysql> commit;
Query OK, 0 rows affected (0.01 sec)
8 mysql> select * from user\G
1. row
id: 1
name: user1
2. row
id: 2
name: user2
3. row
id: 3
name: user3
4. row
id: 5
name: user5
4 rows in set (0.01 sec)
9 mysql> select * from user for update\G
1. row
id: 1
name: user1-1
2. row
id: 2
name: user2
3. row
id: 3
name: user3
4. row
id: 5
name: user5
4 rows in set (0.00 sec)
  • ロッキングリードとノンロッキングリードだと結果が異なる
  • 本当にずれると困る値などはトランザクション中はロッキングリードでロックしておくとtrx2では更新待ちになって不整合が起きにくい
  • ちなみにこのまま更新処理を行うとこうなる
  • 多分想定していない結果になっているはずで変数として一度プログラムに読み込んで計算した結果をUPDATEとかすると期待値とずれることがあるだろう(ロストアップロード)
NO trx1 trx2
10 mysql> update user
-> set name = concat(name, '-1')
-> where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
11 mysql> select * from user\G
1. row
id: 1
name: user1-1-1
2. row
id: 2
name: user2
3. row
id: 3
name: user3
4. row
id: 5
name: user5
4 rows in set (0.00 sec)