mysql> SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES like 'transaction%';
+------------------------------+-----------------+
| Variable_name                | Value           |
+------------------------------+-----------------+
| transaction_alloc_block_size | 8192            |
| transaction_allow_batching   | OFF             |
| transaction_isolation        | REPEATABLE-READ |
| transaction_prealloc_size    | 4096            |
| transaction_read_only        | OFF             |
+------------------------------+-----------------+
5 rows in set (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

-- グローバルな設定
SET GLOBAL autocommit = 0;

-- セッション単位
SET autocommit = 0;

mysql> desc information_schema.innodb_trx;
+----------------------------+-----------------+------+-----+---------+-------+
| Field                      | Type            | Null | Key | Default | Extra |
+----------------------------+-----------------+------+-----+---------+-------+
| trx_id                     | bigint unsigned | NO   |     |         |       |
| trx_state                  | varchar(13)     | NO   |     |         |       |
| trx_started                | datetime        | NO   |     |         |       |
| trx_requested_lock_id      | varchar(126)    | YES  |     |         |       |
| trx_wait_started           | datetime        | YES  |     |         |       |
| trx_weight                 | bigint unsigned | NO   |     |         |       |
| trx_mysql_thread_id        | bigint unsigned | NO   |     |         |       |
| trx_query                  | varchar(1024)   | YES  |     |         |       |
| trx_operation_state        | varchar(64)     | YES  |     |         |       |
| trx_tables_in_use          | bigint unsigned | NO   |     |         |       |
| trx_tables_locked          | bigint unsigned | NO   |     |         |       |
| trx_lock_structs           | bigint unsigned | NO   |     |         |       |
| trx_lock_memory_bytes      | bigint unsigned | NO   |     |         |       |
| trx_rows_locked            | bigint unsigned | NO   |     |         |       |
| trx_rows_modified          | bigint unsigned | NO   |     |         |       |
| trx_concurrency_tickets    | bigint unsigned | NO   |     |         |       |
| trx_isolation_level        | varchar(16)     | NO   |     |         |       |
| trx_unique_checks          | int             | NO   |     |         |       |
| trx_foreign_key_checks     | int             | NO   |     |         |       |
| trx_last_foreign_key_error | varchar(256)    | YES  |     |         |       |
| trx_adaptive_hash_latched  | int             | NO   |     |         |       |
| trx_adaptive_hash_timeout  | bigint unsigned | NO   |     |         |       |
| trx_is_read_only           | int             | NO   |     |         |       |
| trx_autocommit_non_locking | int             | NO   |     |         |       |
| trx_schedule_weight        | bigint unsigned | YES  |     |         |       |
+----------------------------+-----------------+------+-----+---------+-------+
25 rows in set (0.00 sec)

-- ------------------------------------------------------------
-- 稼働中のトランザクションのトランザクション分離レベルを確認する
-- ------------------------------------------------------------
-- Transaction A> : REPEATABLE READ
-- ---------------------------------------------------------------
SET TRANSACTION isolation level repeatable READ;

START TRANSACTION;

INSERT INTO
	tx_test (id, name)
VALUES
	(4, 'Oracle from Tx A');

-- Transaction B> : READ COMMITTED
-- ---------------------------------------------------------------
SET TRANSACTION isolation level READ committed;

START TRANSACTION;

INSERT INTO
	tx_test (id, name)
VALUES
	(5, 'JavaDB from Tx B');

-- 現在稼働中のトランザクションのトランザクション分離レベルを確認
-- trx_mysql_thread_id は CONNECTION_ID() と同一
-- +--------+---------------------+---------------+
-- | trx_id | trx_isolation_level | connection_id |
-- +--------+---------------------+---------------+
-- |   4655 | READ COMMITTED      |            12 |
-- |   4654 | REPEATABLE READ     |            11 |
-- +--------+---------------------+---------------+
SELECT
	trx_id,
	trx_isolation_level,
	trx_mysql_thread_id AS connection_id
FROM
	information_schema.innodb_trx;

-- Transaction A>
ROLLBACK;

-- Transaction B>
ROLLBACK;

-- ------------------------------------------------------------
-- トランザクション分離レベルを変えて見る
-- Transaction A> : REPEATABLE READ
-- Transaction B> : READ COMMITTED
-- Transaction C> : REPEATABLE READ（この例ではあまり関係ないけど ）
-- ------------------------------------------------------------

-- Transaction A> : REPEATABLE READ
-- ---------------------------------------------------------------
-- SET TRANSACTION ISOLATION LEVEL <level>:
-- 注意：
-- - 次に開始するトランザクションの特性を変える文なのでセッション変数は上書きされない。
-- - なので SHOW VARIABLES like 'transaction_isolation'; は変わらない。
SET TRANSACTION isolation level repeatable READ;

START TRANSACTION;

-- Transaction B> : READ COMMITTED
-- ---------------------------------------------------------------
SET TRANSACTION isolation level READ committed;

START TRANSACTION;

-- Transaction C> : REPEATABLE READ
-- ---------------------------------------------------------------
SHOW VARIABLES like 'transaction_isolation';
-- MySQL のデフォルトのトランザクション分離レベルは REPEATABLE READ

-- Transaction C: レコードの更新。Tx A, B からはどう見えるか？
-- (id, name) = (1, 'MySQL') -> (5, 'PostgreSQL') に更新
START TRANSACTION;

UPDATE tx_test
SET
	id = 5,
	name = 'PostgreSQL'
WHERE
	id = 1;

-- Transaction A> : REPEATABLE READ
-- 更新は見えない
SELECT
	*
FROM
	tx_test;

-- Transaction B> : READ COMMITTED
-- 更新は見えない
SELECT
	*
FROM
	tx_test;

-- Transaction C
COMMIT;

-- Transaction A> : REPEATABLE READ
-- 更新は見えない
SELECT
	*
FROM
	tx_test;

-- Transaction B> : READ COMMITTED
-- トランザクション中だが、更新が見えるようになる
-- Fuzzy Read (Non Repeatable Read) が起きている
SELECT
	*
FROM
	tx_test;
-- +-----+--------------+
-- | id  | name         |
-- +-----+--------------+
-- |   2 | MySQL        |
-- |   3 | PostgreSQL   |
-- |   5 | PostgreSQL   |
-- | 999 | Aurora MySQL |
-- +-----+--------------+

-- Transaction A>
ROLLBACK;
-- トランザクションを抜けたので、Transaction A でも更新が見えるようになる
SELECT
	*
FROM
	tx_test;

-- Transaction B>
ROLLBACK;

-- Transaction C>
ROLLBACK;