INSERTの結果
READ UNCOMMITTED
Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.00 sec)
innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31837196
Purge done for trx's n:o < 31836179 undo n:o < 0 state: running but idle
History list length 524
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31837195, ACTIVE 18 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x11b4, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31837195 lock mode IX
-
TABLE LOCK ... lock mode IX: テーブルロック(IX)を取得済み
READ COMMITTED
Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.00 sec)
innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31837708
Purge done for trx's n:o < 31837203 undo n:o < 0 state: running but idle
History list length 527
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31837707, ACTIVE 3 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x13b8, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31837707 lock mode IX
-
TABLE LOCK ... lock mode IX: テーブルロック(IX)を取得済み
REPEATABLE READ
Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.05 sec)
innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31838220
Purge done for trx's n:o < 31837715 undo n:o < 0 state: running but idle
History list length 530
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31838219, ACTIVE 23 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x129c, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31838219 lock mode IX
-
TABLE LOCK ... lock mode IX: テーブルロック(IX)を取得済み
SERIALIZABLE
Tx1
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| tx_isolation | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (1, 10);
Query OK, 1 row affected (0.01 sec)
innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31838732
Purge done for trx's n:o < 31838227 undo n:o < 0 state: running but idle
History list length 533
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31838731, ACTIVE 6 sec
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x12c0, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31838731 lock mode IX
-
TABLE LOCK ... lock mode IX: テーブルロック(IX)を取得済み
行ロックを取得していない？
どの分離レベルにおいても、テーブルロック(IX)のみで、行ロックを取得していない。予測外の結果だ！
でもロックしないわけがない。もしかして、ロックのタイミングの問題なのでは？と考え、以下の作業を追加した。
-
Tx1で書き込みSQLを実行後、 - 新しいウィンドウを開きトランザクション
Tx2を開始 -
Tx2でロック(S)付き読み取りを試みる -
innodb_lock_monitorを確認
Tx2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;
Tx2のロック(S)読み取りは待機状態になり、その間に取得したinnodb_lock_monitorの情報が以下のようになった。
innodb_lock_monitor（TRANSACTIONSのみ抜粋）
Trx id counter 31837709
Purge done for trx's n:o < 31837203 undo n:o < 0 state: running but idle
History list length 527
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 31837708, ACTIVE 79 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x5f0, query id 16 localhost ::1 root statistics
SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000001e5ce0b; asc ;;
2: len 7; hex 8b000001750110; asc u ;;
3: len 4; hex 8000000a; asc ;;
------------------
TABLE LOCK table `til`.`test` trx id 31837708 lock mode IS
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000001e5ce0b; asc ;;
2: len 7; hex 8b000001750110; asc u ;;
3: len 4; hex 8000000a; asc ;;
---TRANSACTION 31837707, ACTIVE 203 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x13b8, query id 9 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31837707 lock mode IX
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837707 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000001e5ce0b; asc ;;
2: len 7; hex 8b000001750110; asc u ;;
3: len 4; hex 8000000a; asc ;;
（REPEATABLE READの試行の中で行った結果なので、Tx1のトランザクションIDが31837707、Tx2のトランザクションIDが31837708）
各トランザクションの情報の中で、ロック解放待ちの情報が先に表示され、その後ロックの情報が表示される。
まずTx2（トランザクションID 31837708）のロック解放待ち情報。
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000001e5ce0b; asc ;;
2: len 7; hex 8b000001750110; asc u ;;
3: len 4; hex 8000000a; asc ;;
-
RECORD LOCKS ... lock mode S locks rec but not gap waiting: 行ロックで、レコードロック(S)を待機中-
0: len 4; hex 80000001; asc ;;:id = 1のレコード
-
続いて、同じくTx2のロック情報。
------------------
TABLE LOCK table `til`.`test` trx id 31837708 lock mode IS
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837708 lock mode S locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000001e5ce0b; asc ;;
2: len 7; hex 8b000001750110; asc u ;;
3: len 4; hex 8000000a; asc ;;
-
TABLE LOCK ... lock mode IS: テーブルロック(IS)を取得済み -
RECORD LOCKS ... lock mode S locks rec but not gap waiting: 行ロックで、レコードロック(S)を待機中-
0: len 4; hex 80000001; asc ;;:id = 1のレコード
-
次に、Tx1（トランザクションID 31837707）のロック情報。
TABLE LOCK table `til`.`test` trx id 31837707 lock mode IX
RECORD LOCKS space id 42762 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31837707 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000001e5ce0b; asc ;;
2: len 7; hex 8b000001750110; asc u ;;
3: len 4; hex 8000000a; asc ;;
-
TABLE LOCK ... lock mode IX: テーブルロック(IX)を取得済み -
RECORD LOCKS ... lock_mode X locks rec but not gap: 行ロックで、レコードロック(X)を取得済み-
0: len 4; hex 80000001; asc ;;:id = 1のレコード
-
Tx1がいつの間にかレコードロック(X)を取得している。
INSERTのまとめ
-
INSERT自体では、テーブルロック(IX)を取得する。 - その後、他のトランザクションによって該当レコードのロックが試みられたときには、すでに挿入した行のレコードロック(X)を取得している。
- 実質的には【予測①-INSERT】の通りといえる？