More than 5 years have passed since last update.

トランザクション分離レベルの勉強記録(2) MySQLのinnodb_lock_monitor（検証）

MySQL

Last updated at 2017-04-28Posted at 2017-04-28

前回の検証はかなり基本的で大雑把だったので、もっと詳しく調べる。

色々と検証していたらとんでもなく長くなり、結論だけ別記事にしたので、この記事は飛ばしていいと思う。

おさらい

トランザクション分離レベルは、同時実行で起こる問題をどれだけ排除できるか＝どれだけの正確性や一貫性が保証できるか、を段階的に定義したもの
現実的には、同時実行で起こる問題と性能の間で妥協点を見つける＝システムの仕様に応じてトランザクション分離レベルを選ぶ必要がある
同時実行で起こる問題をどのように排除するかは複数考えられる
- 操作をエラーにする
- 前のバージョンの値を使う
「どのように排除するか」まで解っていないと、適切なトランザクション分離レベルを選択できないと思う

検証したいこと

前回の検証は現象の起きる・起きないを眺めるだけだったので、実際にMySQLがどのように現象を排除しているかを調べたい。

分離レベルごとに、トランザクション内で任意のSQLを実行し、以下の点を確認
- 読み取り／書き込みの区別
- 読み取りの場合、どのバージョンを参照しているか
- ロックを取得しているか、しているなら種類と範囲はどうか
なんかこの辺をモニタできるコマンドがあるとか聞いたことがある

参考にしたページ

公式のドキュメントを確認する。なんか日本語訳が変なところもあるので適宜英語版も読む。

14.2.2 InnoDB のトランザクションモデルおよびロック
- ロックに関する話
- 読み取りにもロック読み取り（locking read）と非ロック読み取り（non-locking read）がある
14.2.3 InnoDB のロックモード
- 共有 (S) ロックと排他 (X) ロック
- 行ロック、ギャップロック、ネクストキーロック
14.2.5 ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)
- 読み取りでもロックが必要になる場合の具体例
14.2.12 InnoDB マルチバージョン
- MVCCの話
- トランザクションに一意なIDがあり、データの各行にその行を最後に更新したトランザクションをDB_TRX_IDとして保存している
- ロールバックセグメントという場所にUndo ログがあり、Undoに必要な情報を格納している
MySQL 用語集 - 一貫性読み取り
- 読み取りの際に「ある時点のスナップショット」を構築し、そのデータを読み取る（どの時点かは分離レベルや読み取り方法で異なる）
- スナップショット情報の構築にはUndo ログを使う
- 英語では consistent read という
14.2.4 一貫性非ロック読み取り
- 英語ではConsistent Nonlocking Reads
- START TRANSACTION WITH CONSISTENT SNAPSHOTなるものがあるらしい

似たような検証をしているブログ記事など。

MySQL InnoDBのネクストキーロックおさらい - SH2の日記
- SQL文の条件を細かに変えて、3種類のトランザクション分離レベルについて検証している
InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる
- innodb_lock_monitorを使ってロックを確認している
- 今回私が行いたい検証に近い（と思う）
漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点
- Lost Update現象と、Locking Readについて
- 前回「MySQLのREPEATABLE READではPhantom Readは起きない」と書いたけれど、Locking Readだと別らしい！
- Locking ReadとNon-Locking Readで分けて検証する必要がある
MySQL/MariaDBとTransactdのInnoDBロック制御詳細　その1 - BizStationブログ
- すごく詳しいが、カロリーが高すぎて一度に頭に入らない……勉強してから読み返す……

こっちはまだ読みかけ。

A Critique of ANSI SQL Isolation Levels - Microsoft Research
- SQL標準のトランザクション分離レベルの4つの分類に批判的な論文
- まだ section 2 までしか読めていない……
[database] トランザクション分離レベルのメモ - いたわさににほんしゅ
- 上記の論文をsection 4のSnapshot Isolation部分中心に読んで解説している

検証方法の確認

innodb_lock_monitorを使えばロックの種類と範囲が確認できるようだ
読み取り時にどのバージョンが参照されているかもこれでわかりそうな気がする

innodb_lock_monitor を使ってみる

データベースとテーブルは前回のものを再利用した。トランザクション分離レベルはREPEATABLE READで試した。

準備

USE TIL;
DELETE FROM test;
INSERT INTO test (id, a) VALUES (1, 10);
INSERT INTO test (id, a) VALUES (2, 20);
INSERT INTO test (id, a) VALUES (3, 30);
INSERT INTO test (id, a) VALUES (4, 40);

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

上記のコマンドで、エラーログのファイル（私の環境ではmysql_error.log）に情報が書き出されるようになる。15秒に1回のペースで書き出され、かなりの量になるので注意。

まず、MySQLコンソールのウィンドウを2つ開いてログインしただけの状態。

TRANSACTIONSのみ抜粋

Trx id counter 31826442
Purge done for trx's n:o < 31825964 undo n:o < 0 state: running but idle
History list length 431
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x1044, query id 6 localhost ::1 root cleaning up

片方のウィンドウでトランザクションTx1を開始し、読み取りを行う。（START TRANSACTIONだけでは変化がなかった）

Tx1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 2;
+----+----+
| id | a  |
+----+----+
|  2 | 20 |
+----+----+
1 row in set (0.08 sec)

TRANSACTIONSのみ抜粋

Trx id counter 31826444
Purge done for trx's n:o < 31825964 undo n:o < 0 state: running but idle
History list length 431
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x1044, query id 6 localhost ::1 root cleaning up
---TRANSACTION 31826443, ACTIVE 14 sec
MySQL thread id 1, OS thread handle 0x954, query id 8 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31826444, sees < 31826444

TRANSACTION 31826443がTx1のトランザクションID
Trx read view will not see trx with id >= 31826444, sees < 31826444なので、トランザクションIDが31826444以上のデータは読まない

Tx2を開始し、Tx1で読み取った行のデータを変更してみる。REPEATABLE READなので、Tx1で再度読み取りを行っても、Tx2での変更は反映されていない。

Tx2

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 220 WHERE id = 2;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Tx1

mysql> SELECT * FROM test WHERE id = 2;
+----+----+
| id | a  |
+----+----+
|  2 | 20 |
+----+----+
1 row in set (0.00 sec)

TRANSACTIONSのみ抜粋

Trx id counter 31826445
Purge done for trx's n:o < 31825964 undo n:o < 0 state: running but idle
History list length 431
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31826444, ACTIVE 173 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x1044, query id 10 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31826444 lock mode IX
RECORD LOCKS space id 42733 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31826444 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001e5a20c; asc       ;;
 2: len 7; hex 0c00000fd0062f; asc       /;;
 3: len 4; hex 800000dc; asc     ;;

---TRANSACTION 31826443, ACTIVE 570 sec
MySQL thread id 1, OS thread handle 0x954, query id 12 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31826444, sees < 31826444

TRANSACTION 31826444がTx2のトランザクションID
Tx2のトランザクションIDが31826444以上なので、Tx1はそのデータを読まない（REPEATABLE READ）
Tx2でTABLE LOCK tabletil.testtrx id 31826444 lock mode IX、すなわちtestテーブルに対してインテンション排他ロックをしている
- インテンションロックは本当にロックするのではなく、「今後のこのテーブルに対する行操作のロックは排他（もしくは共有）ロックだ」と明示する効果があるらしい（参考）
trx id 31826444 lock_mode X locks rec but not gapなので、行ロックで排他ロック（参考）

Tx2がid = 2の行の排他ロックを取得しているが、Tx1はその行の前のバージョンを読み取るので、SELECT * FROM test WHERE id = 2をしてもロック開放まで待たされない。

新しいTx3を開始してid = 2の行を読み取ろうとするとどうなるか？

Tx3

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test WHERE id = 2;
+----+----+
| id | a  |
+----+----+
|  2 | 20 |
+----+----+
1 row in set (0.00 sec)

TRANSACTIONSのみ抜粋

Trx id counter 31826446
Purge done for trx's n:o < 31825964 undo n:o < 0 state: running but idle
History list length 431
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31826445, ACTIVE 19 sec
MySQL thread id 4, OS thread handle 0x7ac, query id 15 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31826446, sees < 31826443
---TRANSACTION 31826444, ACTIVE 1753 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x1044, query id 10 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31826444 lock mode IX
RECORD LOCKS space id 42733 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31826444 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001e5a20c; asc       ;;
 2: len 7; hex 0c00000fd0062f; asc       /;;
 3: len 4; hex 800000dc; asc     ;;

---TRANSACTION 31826443, ACTIVE 2150 sec
MySQL thread id 1, OS thread handle 0x954, query id 12 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31826444, sees < 31826444

Tx3はTRANSACTION 31826445
Trx read view will not see trx with id >= 31826446, sees < 31826443なので、Tx1やTx2での変更は読まない
自分が開始したときにコミット済みのトランザクションのIDまでしか読まない

Tx3も、Tx2による変更を読まないので、id = 2の行を読み取るときにロックで待たされない。

Tx3でロック読み取りをしてみる。

Tx3

mysql> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE;
/* 待たされる */
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;
+----+----+
| id | a  |
+----+----+
|  1 | 10 |
+----+----+
1 row in set (0.00 sec)

Tx2が排他ロックを取得しているため、id = 2の行の共有ロックは取得できず、エラーになる。id = 1の行は問題なく共有ロックを取得できる。

TRANSACTIONSのみ抜粋

Trx id counter 31826446
Purge done for trx's n:o < 31825964 undo n:o < 0 state: running but idle
History list length 431
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31826445, ACTIVE 627 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7ac, query id 18 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31826446, sees < 31826443
TABLE LOCK table `til`.`test` trx id 31826445 lock mode IS
RECORD LOCKS space id 42733 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31826445 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5a00b; asc       ;;
 2: len 7; hex 8b000001750110; asc     u  ;;
 3: len 4; hex 8000000a; asc     ;;

---TRANSACTION 31826444, ACTIVE 2361 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x1044, query id 10 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31826444 lock mode IX
RECORD LOCKS space id 42733 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31826444 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001e5a20c; asc       ;;
 2: len 7; hex 0c00000fd0062f; asc       /;;
 3: len 4; hex 800000dc; asc     ;;

---TRANSACTION 31826443, ACTIVE 2758 sec
MySQL thread id 1, OS thread handle 0x954, query id 12 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31826444, sees < 31826444

lock mode Sは共有ロックのネクストキーロック

innodb_lock_monitorの読み方ぜんぜんわからん問題

ここまで、意味が拾えるところだけ読んできたが、0: len 4; hex 80000002; asc ;;とかぜんぜんわからん。ドキュメントが見つからないので仕方なくソースコードを読む。今回読んだのはMySQL 5.6.36。

innodb_lock_monitorの出力と、出力している関数の対応は以下のようになっていた。

   +------------ // ------------
   |             // TRANSACTIONS
(1)|             // ------------
   |             // Trx id counter 31826446
   |             // Purge done for trx's n:o < 31825964 undo n:o < 0 state: running but idle
   +------------ // History list length 431
   +------------ // LIST OF TRANSACTIONS FOR EACH SESSION:
   |       +---- // ---TRANSACTION 31826445, ACTIVE 627 sec
   |    (3)|     // 2 lock struct(s), heap size 320, 1 row lock(s)
   |       +-(4) // MySQL thread id 4, OS thread handle 0x7ac, query id 18 localhost ::1 root cleaning up
   |             // Trx read view will not see trx with id >= 31826446, sees < 31826443
   |         (5) // TABLE LOCK table `til`.`test` trx id 31826445 lock mode IS
   |   +-------- // RECORD LOCKS space id 42733 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31826445 lock mode S
   |   |     (7) // Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
   |(6)|   +-(9) //  0: len 4; hex 80000001; asc     ;;
   |   |(8)| (9) //  1: len 6; hex 000001e5a00b; asc       ;;
   |   |   | (9) //  2: len 7; hex 8b000001750110; asc     u  ;;
(2)|   +---+-(9) //  3: len 4; hex 8000000a; asc     ;;
   |             //
   |       +---- // ---TRANSACTION 31826444, ACTIVE 2361 sec
   |    (3)|     // 2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
   |       +-(4) // MySQL thread id 3, OS thread handle 0x1044, query id 10 localhost ::1 root cleaning up
   |         (5) // TABLE LOCK table `til`.`test` trx id 31826444 lock mode IX
   |   +-------- // RECORD LOCKS space id 42733 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31826444 lock_mode X locks rec but not gap
   |   |     (7) // Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
   |(6)|   +-(9) //  0: len 4; hex 80000002; asc     ;;
   |   |(8)| (9) //  1: len 6; hex 000001e5a20c; asc       ;;
   |   |   | (9) //  2: len 7; hex 0c00000fd0062f; asc       /;;
   |   +---+-(9) //  3: len 4; hex 800000dc; asc     ;;
   |             //
   |    (3)+---- // ---TRANSACTION 31826443, ACTIVE 2758 sec
   |       +-(4) // MySQL thread id 1, OS thread handle 0x954, query id 12 localhost ::1 root cleaning up
   +------------ // Trx read view will not see trx with id >= 31826444, sees < 31826444

詳しく読みたいのは(6)のあたりなので、lock_rec_printを読む。

まず、以下のような関数を使って、ロックの情報を表示している。

lock_get_mode：ロックのmodeをenum lock_modeで返す
lock_rec_get_gap：ロックがLOCK_GAPかどうか
lock_rec_get_rec_not_gap：ロックがLOCK_REC_NOT_GAPかどうか
lock_rec_get_insert_intention：ロックがLOCK_INSERT_INTENTIONかどうか
lock_get_wait：ロックが待機中（LOCK_WAIT）かどうか

rec_print_newに入る。ドキュメントコメントにPrints a physical record.とある通り、物理レコードを表示する関数のようだ。

compact formatは行のフォーマットがCOMPACT形式だという意味。
今回使用したテーブルはフィールドが2つしかないはずなのに、n_fields 4と表示されている。ドキュメントを見ると、

クラスタ化されたインデックス内のレコードには、すべてのユーザー定義カラムのフィールドが含まれます。さらに、6 バイトのトランザクション ID フィールドと 7 バイトのロールポインタフィールドも含まれています。

とあるので、多い2つはトランザクションIDとロールポインタのフィールドだろうか？
このあたりの物理構造についてはThe physical structure of records in InnoDB – Jeremy Coleが参考になりそう。info bitsは上記ブログでいうInfo Flagsなのかな……？この辺りは今回の主題から逸れるので深追いしない。

その後、各レコードを出力するrec_print_compおよびut_print_bufに入る。
1行で1フィールドが出力される。どうやら以下のようになっているようだ。

通し番号: len 長さ; hex 16進データ; asc ascii表示; 表示時の省略情報; externalデータ;

よって0: len 4; hex 80000002; asc ;;は以下のように読み取れる。

通し番号 0
長さ 4
値 0x80000002
ascii表記不可
省略表示なし
externalデータなし

1: len 6; hex 000001e5a20c; asc ;;は、長さ6、値0x000001e5a20c。おそらくこれが「6 バイトのトランザクション ID フィールド」だろう。
0x000001e5a20cを10進数表記に直すと31826444であり、これはid = 2の行を最後に変更したTx2のトランザクションIDと一致する。
2: len 7; hex 0c00000fd0062f; asc /;;は、長さ7、値0x0c00000fd0062fで、おそらく「7 バイトのロールポインタフィールド」だ。（このポインタを使ってデータにアクセスする方法はあるのだろうか？）
3: len 4; hex 800000dc; asc ;;は、長さ4、値0x800000dc。

このレコードはid = 2、a = 220のはずなのに、値が0x80000002だったり0x800000dcだったりする（両方とも最上位ビットが立っている）のは何故だ……？MySQLのINT型格納方法を調べないといけない……。

他のテーブルを使ってもっと確認してみる。

プライマリキーが複合キーのテーブル

CREATE TABLE ordmultikey (
  a TINYINT UNSIGNED,
  b TINYINT UNSIGNED,
  c TINYINT UNSIGNED,
  varchar30 VARCHAR(30),
  PRIMARY KEY (b, c, a)
);
INSERT INTO ordmultikey (a, b, c, varchar30) VALUES(1, 2, 3, 'abc');
START TRANSACTION;
SELECT * FROM ordmultikey WHERE b = 2 LOCK IN SHARE MODE;

抜粋

---TRANSACTION 31828513, ACTIVE 13 sec
2 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 2, OS thread handle 0xfc4, query id 20 localhost ::1 root cleaning up
TABLE LOCK table `til`.`ordmultikey` trx id 31828513 lock mode IS
RECORD LOCKS space id 42745 page no 3 n bits 72 index `PRIMARY` of table `til`.`ordmultikey` trx id 31828513 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 1; hex 03; asc  ;;
 2: len 1; hex 01; asc  ;;
 3: len 6; hex 000001e5aa1a; asc       ;;
 4: len 7; hex 96000001550110; asc     U  ;;
 5: len 3; hex 616263; asc abc;;

データの値から見て、出力されるフィールドの順番がb, c, a, トランザクションID, ロールポインタ, varchar30になっている
b, c, aはPRIMARY KEY (b, c, a)の順番

プライマリキーのないテーブル

CREATE TABLE nokey (
  a TINYINT UNSIGNED,
  varchar30 VARCHAR(30),
  INDEX (a)
);
INSERT INTO nokey (a, varchar30) VALUES (1, 'abc');
START TRANSACTION;
SELECT * FROM nokey WHERE a = 1 LOCK IN SHARE MODE;

抜粋

---TRANSACTION 31828551, ACTIVE 0 sec
3 lock struct(s), heap size 320, 3 row lock(s)
MySQL thread id 2, OS thread handle 0xfc4, query id 39 localhost ::1 root cleaning up
TABLE LOCK table `til`.`nokey` trx id 31828551 lock mode IS
RECORD LOCKS space id 42748 page no 4 n bits 72 index `a` of table `til`.`nokey` trx id 31828551 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 01; asc  ;;
 1: len 6; hex 000000253f02; asc    %? ;;

RECORD LOCKS space id 42748 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `til`.`nokey` trx id 31828551 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000253f02; asc    %? ;;
 1: len 6; hex 000001e5aa46; asc      F;;
 2: len 7; hex bc000002e40110; asc        ;;
 3: len 1; hex 01; asc  ;;
 4: len 3; hex 616263; asc abc;;

実際のフィールドは2つなのにn_fields 5になっている！
通し番号1と2はトランザクションIDとロールポインタ
通し番号0は行IDだろう

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.2 クラスタインデックスとセカンダリインデックスより：

テーブルに PRIMARY KEY も適切な UNIQUE インデックスも存在しない場合には、InnoDB の内部で、行 ID 値を含む合成カラム上に非表示のクラスタ化されたインデックスが生成されます。
（中略）
行 ID は、新しい行が挿入されると単調に増加する 6 バイトのフィールドです。

（番外）色々なデータ型

基本的なデータ型を全部含んだテーブル。めちゃくちゃ長い！

CREATE TABLE alltypes (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  utint TINYINT UNSIGNED,
  stint TINYINT,
  usint SMALLINT UNSIGNED,
  ssint SMALLINT,
  umint MEDIUMINT UNSIGNED,
  smint MEDIUMINT,
  uiint INT UNSIGNED,
  siint INT,
  ubint BIGINT UNSIGNED,
  sbint BIGINT,
  ufloat FLOAT UNSIGNED,
  sfloat FLOAT,
  ufloat106 FLOAT(10,6) UNSIGNED,
  sfloat106 FLOAT(10,6),
  udouble DOUBLE UNSIGNED,
  sdouble DOUBLE,
  udouble106 DOUBLE(10,6) UNSIGNED,
  sdouble106 DOUBLE(10,6),
  udec DECIMAL UNSIGNED,
  sdec DECIMAL,
  udec106 DECIMAL(10,6) UNSIGNED,
  sdec106 DECIMAL(10,6),
  ubit BIT,
  ubit8 BIT(8),
  udate DATE,
  utime TIME,
  ftime TIME(6),
  utimestamp TIMESTAMP,
  ftimestamp TIMESTAMP(6),
  udatetime DATETIME,
  fdatetime DATETIME(6),
  year4 YEAR,
  year2 YEAR(2),
  char30 CHAR(30),
  varchar30 VARCHAR(30),
  binary30 BINARY(30),
  varbinary30 VARBINARY(30),
  tblob TINYBLOB,
  fblob BLOB,
  mblob MEDIUMBLOB,
  lblob LONGBLOB,
  ttext TINYTEXT,
  ftext TEXT,
  mtext MEDIUMTEXT,
  ltext LONGTEXT,
  fenum ENUM('i', 'ii', 'iii', 'iv', 'v'),
  fset SET('i', 'ii', 'iii', 'iv', 'v'),
  PRIMARY KEY (id)
);
INSERT INTO alltypes (
  utint, stint, usint, ssint, umint, smint, uiint, siint,
  ubint, sbint, ufloat, sfloat, ufloat106, sfloat106,
  udouble, sdouble, udouble106, sdouble106, udec, sdec,
  udec106, sdec106, ubit, ubit8, udate, utime, ftime,
  utimestamp, ftimestamp, udatetime, fdatetime, year4, year2,
  char30, varchar30, binary30, varbinary30, tblob, fblob,
  mblob, lblob, ttext, ftext, mtext, ltext, fenum, fset
) VALUES (
  255, -128, 65535, -32768, 16777215, -8388608, 4294967295,
  -2147483648, 18446744073709551615, -9223372036854775808,
  1.234, -1.234, 1230.456789, -1230.456789, 11.234, -11.234,
  1234.456789, -1234.456789,
  99999999999999999999999999999999999999999999999999999999999999999,
  -99999999999999999999999999999999999999999999999999999999999999999,
  1235.456789, -1235.456789, b'1', b'00001010', '2017-04-29',
  '838:59:59', '838:59:59.000000', '2038-01-19 03:14:07',
  '2038-01-19 03:14:07.999999', '9999-12-31 23:59:59',
  '9999-12-31 23:59:59.999999', 2155, 2155, 'abcdef', 'ABCDEF',
  'bin abc', 'BIN ABC', 'tblob abc', 'blob abc', 'mblob abc', 'lblob abc',
  'ttext abc', 'text abc', 'mtext abc', 'ltext abc', 'iii', 'iv'
);
START TRANSACTION;
SELECT * FROM alltypes WHERE id = 1 LOCK IN SHARE MODE;

抜粋

---TRANSACTION 31828552, ACTIVE 7 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, OS thread handle 0xfc4, query id 42 localhost ::1 root cleaning up
TABLE LOCK table `til`.`alltypes` trx id 31828552 lock mode IS
RECORD LOCKS space id 42741 page no 3 n bits 72 index `PRIMARY` of table `til`.`alltypes` trx id 31828552 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 50; compact format; info bits 0
 0: len 3; hex 000001; asc    ;;
 1: len 6; hex 000001e5aa06; asc       ;;
 2: len 7; hex 86000001680110; asc     h  ;;
 3: len 1; hex ff; asc  ;;
 4: len 1; hex 00; asc  ;;
 5: len 2; hex ffff; asc   ;;
 6: len 2; hex 0000; asc   ;;
 7: len 3; hex ffffff; asc    ;;
 8: len 3; hex 000000; asc    ;;
 9: len 4; hex ffffffff; asc     ;;
 10: len 4; hex 00000000; asc     ;;
 11: len 8; hex ffffffffffffffff; asc         ;;
 12: len 8; hex 0000000000000000; asc         ;;
 13: len 4; hex b6f39d3f; asc    ?;;
 14: len 4; hex b6f39dbf; asc     ;;
 15: len 4; hex 9ece9944; asc    D;;
 16: len 4; hex 9ece99c4; asc     ;;
 17: len 8; hex 2b8716d9ce772640; asc +    w&@;;
 18: len 8; hex 2b8716d9ce7726c0; asc +    w& ;;
 19: len 8; hex b1e07ec0d3499340; asc   ~  I @;;
 20: len 8; hex b1e07ec0d34993c0; asc   ~  I  ;;
 21: len 5; hex 893b9ac9ff; asc  ;   ;;
 22: len 5; hex 76c4653600; asc v e6 ;;
 23: len 5; hex 84d306f855; asc     U;;
 24: len 5; hex 7b2cf907aa; asc {,   ;;
 25: len 1; hex 01; asc  ;;
 26: len 1; hex 0a; asc  ;;
 27: len 3; hex 8fc29d; asc    ;;
 28: len 3; hex b46efb; asc  n ;;
 29: len 6; hex b46efb000000; asc  n    ;;
 30: len 4; hex 7fff816f; asc    o;;
 31: len 7; hex 7fff816f0f423f; asc    o B?;;
 32: len 5; hex fef3ff7efb; asc    ~ ;;
 33: len 8; hex fef3ff7efb0f423f; asc    ~  B?;;
 34: len 1; hex ff; asc  ;;
 35: len 1; hex ff; asc  ;;
 36: len 30; hex 616263646566202020202020202020202020202020202020202020202020; asc abcdef                        ;;
 37: len 6; hex 414243444546; asc ABCDEF;;
 38: len 30; hex 62696e206162630000000000000000000000000000000000000000000000; asc bin abc                       ;;
 39: len 7; hex 42494e20414243; asc BIN ABC;;
 40: len 9; hex 74626c6f6220616263; asc tblob abc;;
 41: len 8; hex 626c6f6220616263; asc blob abc;;
 42: len 9; hex 6d626c6f6220616263; asc mblob abc;;
 43: len 9; hex 6c626c6f6220616263; asc lblob abc;;
 44: len 9; hex 747465787420616263; asc ttext abc;;
 45: len 8; hex 7465787420616263; asc text abc;;
 46: len 9; hex 6d7465787420616263; asc mtext abc;;
 47: len 9; hex 6c7465787420616263; asc ltext abc;;
 48: len 1; hex 03; asc  ;;
 49: len 1; hex 08; asc  ;;

（番外）INT系のバイナリ表現どうなってるの

CREATE TABLE intbin (
  id TINYINT UNSIGNED AUTO_INCREMENT,
  tint TINYINT,
  sint SMALLINT,
  mint MEDIUMINT,
  iint INT,
  bint BIGINT,
  PRIMARY KEY (id)
);
INSERT INTO intbin (tint, sint, mint, iint, bint) VALUES (0, 0, 0, 0, 0);
INSERT INTO intbin (tint, sint, mint, iint, bint) VALUES (1, 1, 1, 1, 1);
INSERT INTO intbin (tint, sint, mint, iint, bint) VALUES (-1, -1, -1, -1, -1);
INSERT INTO intbin (tint, sint, mint, iint, bint) VALUES (
  127, 32767, 8388607, 2147483647, 9223372036854775807
);
INSERT INTO intbin (tint, sint, mint, iint, bint) VALUES (
  -128, -32768, -8388608, -2147483648, -9223372036854775808
);
START TRANSACTION;
SELECT * FROM intbin WHERE id >= 1 LOCK IN SHARE MODE;

抜粋

Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 1; hex 01; asc  ;;
 1: len 6; hex 000001e5ae32; asc      2;;
 2: len 7; hex a800000fdb0110; asc        ;;
 3: len 1; hex 80; asc  ;;
 4: len 2; hex 8000; asc   ;;
 5: len 3; hex 800000; asc    ;;
 6: len 4; hex 80000000; asc     ;;
 7: len 8; hex 8000000000000000; asc         ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 6; hex 000001e5ae33; asc      3;;
 2: len 7; hex a9000001630110; asc     c  ;;
 3: len 1; hex 81; asc  ;;
 4: len 2; hex 8001; asc   ;;
 5: len 3; hex 800001; asc    ;;
 6: len 4; hex 80000001; asc     ;;
 7: len 8; hex 8000000000000001; asc         ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 1; hex 03; asc  ;;
 1: len 6; hex 000001e5ae37; asc      7;;
 2: len 7; hex ac000008c70110; asc        ;;
 3: len 1; hex 7f; asc  ;;
 4: len 2; hex 7fff; asc   ;;
 5: len 3; hex 7fffff; asc    ;;
 6: len 4; hex 7fffffff; asc     ;;
 7: len 8; hex 7fffffffffffffff; asc         ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 1; hex 04; asc  ;;
 1: len 6; hex 000001e5ae3b; asc      ;;;
 2: len 7; hex af000008f00110; asc        ;;
 3: len 1; hex ff; asc  ;;
 4: len 2; hex ffff; asc   ;;
 5: len 3; hex ffffff; asc    ;;
 6: len 4; hex ffffffff; asc     ;;
 7: len 8; hex ffffffffffffffff; asc         ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 1; hex 05; asc  ;;
 1: len 6; hex 000001e5ae42; asc      B;;
 2: len 7; hex b4000002800110; asc        ;;
 3: len 1; hex 00; asc  ;;
 4: len 2; hex 0000; asc   ;;
 5: len 3; hex 000000; asc    ;;
 6: len 4; hex 00000000; asc     ;;
 7: len 8; hex 0000000000000000; asc         ;;

UNSIGNEDなidはそのまま符号なし整数のバイナリ表現
符号付き整数の格納方式は2の補数表現でも1の補数表現でもないようだ
- 全ビットが1の値が最大値、全ビットが0の値が最小値になるように割り振っている
これはオフセットバイナリ方式とかエクセスN方式というらしい（参考）
- エクセスNのNは「ある数値は、元の値よりNだけ大きい符号無し数値として表現される」ので、要するに最小値 * -1が入る
- Wikipediaに例のある8ビットエクセス127方式だと、8ビット（1バイト）で-127～128の範囲を表せる
- でもMySQLのTINYINTは-128～127なので、8ビットエクセス128方式にしている
- たぶん一般的な2の補数の範囲と合わせたんじゃないかなあ

型	符号付整数値表現方法
TINYINT	8ビットエクセス128
SMALLINT	16ビットエクセス32768
MEDIUMINT	24ビットエクセス8388608
INT, INTEGER	32ビットエクセス2147483648
BIGINT	64ビットエクセス9223372036854775808

まとめ

かなり長くなったのでまとめは別記事にした。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up