LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ihjar

トランザクション内でUPDATEした後にSELECTすると行が抽出されることがある

Posted at

Reaptable Read,MySQL8.0にて
トランザクション内でUPDATEをした後、別トランザクションでINSERTされた行が抽出されることがある。

結論：

  • REPEATABLE READ では、トランザクション開始時点で存在しなかった行は通常の SELECT では見えません。
  • しかし、UPDATE が条件に一致する新しい行を変更すると、その行が「トランザクション内で変更されたデータ」として認識され、以降の SELECT で見えるようになります。
トランザクション１
--tran1
begin;--2番目に実行する

select * from phantom_test; --3番目に実行する

select * from phantom_test; --5番目に実行する

select * from phantom_test;--7番目に実行する

update phantom_test set value = 'updated_tran1' where id = 1; --8番目に実行する

select * from phantom_test; --9番目に実行する

update phantom_test set value = 'NEW'; -- 10番目に実行する

select * from phantom_test; --11番目に実行する

commit; -- 12番目に実行する

select * from phantom_test; --13番目に実行する
トランザクション２
tran2
begin; --1番目に実行する

insert into phantom_test(value) values ('NEW'); --4番目に実行する

commit; --6番目に実行する

実行結果

ケース１ Insertされた行が更新された場合

update後にSELECTするとcommit前だが抽出される

ケース１
--トランザクション1
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.40    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from phantom_test;
+----+-----------+
| id | value     |
+----+-----------+
|  1 | updated_2 |
|  2 | updated_2 |
|  8 | updated_2 |
|  9 | updated_2 |
| 10 | updated_2 |
| 11 | updated_2 |
| 12 | updated_2 |
| 13 | term2_4   |
+----+-----------+
8 rows in set (0.03 sec)

mysql> select * from phantom_test;
+----+-----------+
| id | value     |
+----+-----------+
|  1 | updated_2 |
|  2 | updated_2 |
|  8 | updated_2 |
|  9 | updated_2 |
| 10 | updated_2 |
| 11 | updated_2 |
| 12 | updated_2 |
| 13 | term2_4   |
+----+-----------+
8 rows in set (0.02 sec)

mysql> select * from phantom_test;
+----+-----------+
| id | value     |
+----+-----------+
|  1 | updated_2 |
|  2 | updated_2 |
|  8 | updated_2 |
|  9 | updated_2 |
| 10 | updated_2 |
| 11 | updated_2 |
| 12 | updated_2 |
| 13 | term2_4   |
+----+-----------+
8 rows in set (0.02 sec)

mysql> update phantom_test set value = 'updated_tran1' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from phantom_test;
+----+---------------+
| id | value         |
+----+---------------+
|  1 | updated_tran1 |
|  2 | updated_2     |
|  8 | updated_2     |
|  9 | updated_2     |
| 10 | updated_2     |
| 11 | updated_2     |
| 12 | updated_2     |
| 13 | term2_4       |
+----+---------------+
8 rows in set (0.01 sec)

mysql> update phantom_test set value = 'updated_tran1_2';
Query OK, 9 rows affected (0.02 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> select * from phantom_test;
+----+-----------------+
| id | value           |
+----+-----------------+
|  1 | updated_tran1_2 |
|  2 | updated_tran1_2 |
|  8 | updated_tran1_2 |
|  9 | updated_tran1_2 |
| 10 | updated_tran1_2 |
| 11 | updated_tran1_2 |
| 12 | updated_tran1_2 |
| 13 | updated_tran1_2 |
| 14 | updated_tran1_2 |
+----+-----------------+
9 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from phantom_test;
+----+-----------------+
| id | value           |
+----+-----------------+
|  1 | updated_tran1_2 |
|  2 | updated_tran1_2 |
|  8 | updated_tran1_2 |
|  9 | updated_tran1_2 |
| 10 | updated_tran1_2 |
| 11 | updated_tran1_2 |
| 12 | updated_tran1_2 |
| 13 | updated_tran1_2 |
| 14 | updated_tran1_2 |
+----+-----------------+
9 rows in set (0.02 sec)
トランザクション2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into phantom_test(value) values ('NEW');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)
ケース２ Insertされた行が更新後の値が同じため更新されない場合

この時はupdate後にSELECTしてもcommit前は抽出されない

トランザクション1
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from phantom_test;
+----+-----------------+
| id | value           |
+----+-----------------+
|  1 | updated_tran1_2 |
|  2 | updated_tran1_2 |
|  8 | updated_tran1_2 |
|  9 | updated_tran1_2 |
| 10 | updated_tran1_2 |
| 11 | updated_tran1_2 |
| 12 | updated_tran1_2 |
| 13 | updated_tran1_2 |
| 14 | updated_tran1_2 |
+----+-----------------+
9 rows in set (0.02 sec)

mysql> select * from phantom_test;
+----+-----------------+
| id | value           |
+----+-----------------+
|  1 | updated_tran1_2 |
|  2 | updated_tran1_2 |
|  8 | updated_tran1_2 |
|  9 | updated_tran1_2 |
| 10 | updated_tran1_2 |
| 11 | updated_tran1_2 |
| 12 | updated_tran1_2 |
| 13 | updated_tran1_2 |
| 14 | updated_tran1_2 |
+----+-----------------+
9 rows in set (0.02 sec)

mysql> select * from phantom_test;
+----+-----------------+
| id | value           |
+----+-----------------+
|  1 | updated_tran1_2 |
|  2 | updated_tran1_2 |
|  8 | updated_tran1_2 |
|  9 | updated_tran1_2 |
| 10 | updated_tran1_2 |
| 11 | updated_tran1_2 |
| 12 | updated_tran1_2 |
| 13 | updated_tran1_2 |
| 14 | updated_tran1_2 |
+----+-----------------+
9 rows in set (0.01 sec)

mysql> update phantom_test set value = 'updated_tran1' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from phantom_test;
+----+-----------------+
| id | value           |
+----+-----------------+
|  1 | updated_tran1   |
|  2 | updated_tran1_2 |
|  8 | updated_tran1_2 |
|  9 | updated_tran1_2 |
| 10 | updated_tran1_2 |
| 11 | updated_tran1_2 |
| 12 | updated_tran1_2 |
| 13 | updated_tran1_2 |
| 14 | updated_tran1_2 |
+----+-----------------+
9 rows in set (0.01 sec)

mysql> update phantom_test set value = 'NEW';
Query OK, 9 rows affected (0.01 sec)
Rows matched: 10  Changed: 9  Warnings: 0

mysql> select * from phantom_test;
+----+-------+
| id | value |
+----+-------+
|  1 | NEW   |
|  2 | NEW   |
|  8 | NEW   |
|  9 | NEW   |
| 10 | NEW   |
| 11 | NEW   |
| 12 | NEW   |
| 13 | NEW   |
| 14 | NEW   |
+----+-------+
9 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from phantom_test;
+----+-------+
| id | value |
+----+-------+
|  1 | NEW   |
|  2 | NEW   |
|  8 | NEW   |
|  9 | NEW   |
| 10 | NEW   |
| 11 | NEW   |
| 12 | NEW   |
| 13 | NEW   |
| 14 | NEW   |
| 15 | NEW   |
+----+-------+
10 rows in set (0.01 sec)
トランザクション2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> insert into phantom_test(value) values ('NEW');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?