“命令行验证MySQL的隔离级别”的版本间的差异

来自姬鸿昌的知识库
跳到导航 跳到搜索
 
（未显示同一用户的14个中间版本）
第64行： 第64行：
  
  
−
=== 验证 ===
+
=== 验证事务隔离级别为 REPEATABLE-READ 时的情况 ===
  
 
==== 查看初始数据 ====
 
==== 查看初始数据 ====
第170行： 第170行：
 
+------+----------+---------+
 
+------+----------+---------+
 
1 row in set (0.00 sec)
 
1 row in set (0.00 sec)
−
</syntaxhighlight>balance 的值仍然是 1000，因为 默认事务的隔离级别是  REPEATABLE-READ（可重复读），所以 root 用户的事务开始时 balance 的值是多少，直到 root 用户的事务结束（commit 或 rollback）之前对 root 用户来说就一直是多少，不会变。
+
</syntaxhighlight>balance 的值仍然是 1000，因为 默认事务的隔离级别是  REPEATABLE-READ（可重复读），
 +
 
 +
所以用户 root 在 <u>用户 tom 更新 user='AA' 的用户 balance 为 3000 之后且提交事务之前</u> 读取的 balance 值为 1000；（屏蔽了'''<big>脏读</big>'''的情况，不会读取到用户 tom 未提交事务变更的数据）
 +
 
 +
所以用户 root 在 <u>用户 tom 更新 user='AA' 的用户 balance 为 3000 且提交事务之后</u>，在用户 root 自己的当前事务中读取到的 balance 值仍为 1000；（屏蔽了'''<big>不可重复读</big>'''的情况，在用户 root 自己的当前事务 commit 或 rollback 之前可以重复读取同一条数据，得到的值始终是一致的）。
  
  
第187行： 第191行：
 
</syntaxhighlight>现在看到了 tom 用户提交更新的数据。
 
</syntaxhighlight>现在看到了 tom 用户提交更新的数据。
  
 +
 +
 +
=== 验证事务隔离级别为 READ-COMMITTED 时的情况 ===
  
 
==== root 用户修改全局事务隔离级别 ====
 
==== root 用户修改全局事务隔离级别 ====
第250行： 第257行：
 
1 row in set, 1 warning (0.00 sec)
 
1 row in set, 1 warning (0.00 sec)
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 +
 +
==== 重新设置 root 和 tom 禁用自动提交 ====
 +
因为断开重连 MySQL Server，所以需要再次设置禁用自动提交<syntaxhighlight lang="powershell">
 +
mysql> show variables like 'autocommit';
 +
+---------------+-------+
 +
| Variable_name | Value |
 +
+---------------+-------+
 +
| autocommit    | ON    |
 +
+---------------+-------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 +
mysql> set autocommit=false;
 +
Query OK, 0 rows affected (0.00 sec)
 +
 +
mysql> show variables like 'autocommit';
 +
+---------------+-------+
 +
| Variable_name | Value |
 +
+---------------+-------+
 +
| autocommit    | OFF  |
 +
+---------------+-------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== 在 tom 的事务中更新数据 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> update user_table set balance = 4000 where user = 'AA';
 +
Query OK, 1 row affected (0.00 sec)
 +
Rows matched: 1  Changed: 1  Warnings: 0
 +
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    4000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== 在 root 的事务中查询数据 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    3000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== 提交 tom 的事务 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> commit;
 +
Query OK, 0 rows affected (0.01 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== 再在 root 的事务中查询数据 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    4000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>因为当前 root 的事务隔离级别是 READ-COMMITTED，
 +
 +
所以在 root 的事务 commit 或 rollback 之前读取不到 tom 的事务中更新了、但未执行事务提交的数据；（屏蔽了'''<big>脏读</big>'''）
 +
 +
但在 tom 的事务提交之后，在 root 的事务 commit 或 rollback 之前，在 root 的事务中就读取到了 tom 的事务中更新且进行事务提交了的数据。（未屏蔽'''<big>不可重复读</big>'''的问题，在 root 的同一个事务中两次读取到的 user='AA' 的  balance 值不一致）
 +
 +
 +
 +
 +
=== 验证事务隔离级别为 READ-UNCOMMITTED 时的情况 ===
 +
 +
==== root 设置全局事务隔离级别为 READ-UNCOMMITTED ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> set global transaction isolation level read uncommitted;
 +
Query OK, 0 rows affected (0.00 sec)
 +
 +
mysql> exit
 +
Bye
 +
 +
C:\Users\Administrator>mysql -uroot -p123456
 +
mysql: [Warning] Using a password on the command line interface can be insecure.
 +
Welcome to the MySQL monitor.  Commands end with ; or \g.
 +
Your MySQL connection id is 116
 +
Server version: 5.7.35-log MySQL Community Server (GPL)
 +
 +
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 +
 +
Oracle is a registered trademark of Oracle Corporation and/or its
 +
affiliates. Other names may be trademarks of their respective
 +
owners.
 +
 +
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 +
 +
mysql> select @@tx_isolation;
 +
+------------------+
 +
| @@tx_isolation  |
 +
+------------------+
 +
| READ-UNCOMMITTED |
 +
+------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== tom 重新登录确认当前事务的隔离级别 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> exit
 +
Bye
 +
 +
C:\Users\Administrator>mysql -utom -pabc123
 +
mysql: [Warning] Using a password on the command line interface can be insecure.
 +
Welcome to the MySQL monitor.  Commands end with ; or \g.
 +
Your MySQL connection id is 117
 +
Server version: 5.7.35-log MySQL Community Server (GPL)
 +
 +
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 +
 +
Oracle is a registered trademark of Oracle Corporation and/or its
 +
affiliates. Other names may be trademarks of their respective
 +
owners.
 +
 +
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 +
 +
mysql> select @@tx_isolation;
 +
+------------------+
 +
| @@tx_isolation  |
 +
+------------------+
 +
| READ-UNCOMMITTED |
 +
+------------------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== tom、root 配置禁用事务自动提交 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> show variables like 'autocommit';
 +
+---------------+-------+
 +
| Variable_name | Value |
 +
+---------------+-------+
 +
| autocommit    | ON    |
 +
+---------------+-------+
 +
1 row in set, 1 warning (0.00 sec)
 +
 +
mysql> set autocommit = false;
 +
Query OK, 0 rows affected (0.00 sec)
 +
 +
mysql> show variables like 'autocommit';
 +
+---------------+-------+
 +
| Variable_name | Value |
 +
+---------------+-------+
 +
| autocommit    | OFF  |
 +
+---------------+-------+
 +
1 row in set, 1 warning (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== root 更新数据但不执行事务提交 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> update user_table set balance = 5000 where user = 'AA';
 +
Query OK, 1 row affected (0.00 sec)
 +
Rows matched: 1  Changed: 1  Warnings: 0
 +
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    5000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== tom 查询数据 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> use jdbc_test;
 +
Database changed
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    5000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>因为当前 tom 的事务隔离级别是 READ-UNCOMMITTED，所以读取到了 root 事务中未提交的数据更新。（未屏蔽'''<big>脏读</big>'''）
 +
 +
 +
==== root 回滚事务 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> rollback;
 +
Query OK, 0 rows affected (0.01 sec)
 +
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    4000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>
 +
 +
 +
==== tom 再查询数据 ====
 +
<syntaxhighlight lang="powershell">
 +
mysql> select * from user_table where user = 'AA';
 +
+------+----------+---------+
 +
| user | password | balance |
 +
+------+----------+---------+
 +
| AA  | 123456  |    4000 |
 +
+------+----------+---------+
 +
1 row in set (0.00 sec)
 +
</syntaxhighlight>脏读，
 +
 +
tom 在自己的事务中如果以 root 中未提交的事务影响的数据（且之后进行了回滚）作为基础进行其他操作就会对数据的正确性产生影响。

2023年1月7日 (六) 05:23的最新版本

https://www.bilibili.com/video/BV1eJ411c7rf?p=40

准备

  1. Windows 下 cmd 进入命令行：
    C:\Users\Administrator>mysql -uroot -p123456
  2. 创建一个新用户登录MySQL Server：
    mysql> create user tom identified by 'abc123';
Query OK, 0 rows affected (0.03 sec)
    用户名：tom 密码：abc123
  3. 登录tom：
    C:\Users\Administrator>mysql -utom -pabc123
  4. 查看 root 用户可以操作的数据库：
    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2019             |
| guns               |
| jdbc_test          |
| ke                 |
| m3u8_download      |
| mysql              |
| nacos_config       |
| performance_schema |
| sakila             |
| seata              |
| seata_account      |
| seata_order        |
| seata_storage      |
| sql                |
| sys                |
| world              |
+--------------------+
17 rows in set (0.00 sec)
  5. 查看 tom 用户可以操作的数据库：
    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
  6. 为 tom 用户授权：
    mysql> grant select,insert,update,delete on jdbc_test.* to tom@localhost identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
  7. 验证 tom 用户得到的授权：
    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jdbc_test          |
+--------------------+
2 rows in set (0.00 sec)



验证事务隔离级别为 REPEATABLE-READ 时的情况

查看初始数据

mysql> select * from user_table;
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    1000 |
| BB   | 654321   |    1000 |
+------+----------+---------+
2 rows in set (0.00 sec)


设置事务不自动提交

tom 和 root 都设置

mysql> set autocommit =false;
Query OK, 0 rows affected (0.00 sec)


查看 tom 用户当前的事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)


root 用户查看当前数据

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    1000 |
+------+----------+---------+
1 row in set (0.00 sec)

tom 用户查看当前数据

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    1000 |
+------+----------+---------+
1 row in set (0.00 sec)


tom 用户修改数据

mysql> update user_table set balance = 3000 where user = 'AA';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


tom 用户查看修改后的数据

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    3000 |
+------+----------+---------+
1 row in set (0.00 sec)


root 用户查看数据

mysql>  select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    1000 |
+------+----------+---------+
1 row in set (0.00 sec)


tom 用户提交事务

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


root 用户再次查看数据

mysql>  select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    1000 |
+------+----------+---------+
1 row in set (0.00 sec)

balance 的值仍然是 1000，因为 默认事务的隔离级别是 REPEATABLE-READ（可重复读），

所以用户 root 在 用户 tom 更新 user='AA' 的用户 balance 为 3000 之后且提交事务之前 读取的 balance 值为 1000；（屏蔽了脏读的情况，不会读取到用户 tom 未提交事务变更的数据）

所以用户 root 在 用户 tom 更新 user='AA' 的用户 balance 为 3000 且提交事务之后，在用户 root 自己的当前事务中读取到的 balance 值仍为 1000；（屏蔽了不可重复读的情况，在用户 root 自己的当前事务 commit 或 rollback 之前可以重复读取同一条数据，得到的值始终是一致的）。


root 用户提交事务后查看数据

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

mysql>  select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    3000 |
+------+----------+---------+
1 row in set (0.00 sec)

现在看到了 tom 用户提交更新的数据。


验证事务隔离级别为 READ-COMMITTED 时的情况

root 用户修改全局事务隔离级别

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

注意：现在修改完全局的事务隔离级别之后查询事务隔离级别仍然显示为“REPEATABLE-READ”，这是因为需要把当前连接断开重连之后才会生效。

mysql> exit
Bye

C:\Users\Administrator>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 114
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

其他连接也是，也需要断开连接重连后，才能应用新的全局事务隔离级别

mysql> exit
Bye

C:\Users\Administrator>mysql -utom -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 115
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)


重新设置 root 和 tom 禁用自动提交

因为断开重连 MySQL Server，所以需要再次设置禁用自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set autocommit=false;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)


在 tom 的事务中更新数据

mysql> update user_table set balance = 4000 where user = 'AA';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    4000 |
+------+----------+---------+
1 row in set (0.00 sec)


在 root 的事务中查询数据

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    3000 |
+------+----------+---------+
1 row in set (0.00 sec)


提交 tom 的事务

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


再在 root 的事务中查询数据

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    4000 |
+------+----------+---------+
1 row in set (0.00 sec)

因为当前 root 的事务隔离级别是 READ-COMMITTED，

所以在 root 的事务 commit 或 rollback 之前读取不到 tom 的事务中更新了、但未执行事务提交的数据；（屏蔽了脏读）

但在 tom 的事务提交之后，在 root 的事务 commit 或 rollback 之前，在 root 的事务中就读取到了 tom 的事务中更新且进行事务提交了的数据。（未屏蔽不可重复读的问题，在 root 的同一个事务中两次读取到的 user='AA' 的 balance 值不一致）



验证事务隔离级别为 READ-UNCOMMITTED 时的情况

root 设置全局事务隔离级别为 READ-UNCOMMITTED

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Users\Administrator>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)


tom 重新登录确认当前事务的隔离级别

mysql> exit
Bye

C:\Users\Administrator>mysql -utom -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)


tom、root 配置禁用事务自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set autocommit = false;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)


root 更新数据但不执行事务提交

mysql> update user_table set balance = 5000 where user = 'AA';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    5000 |
+------+----------+---------+
1 row in set (0.00 sec)


tom 查询数据

mysql> use jdbc_test;
Database changed
mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    5000 |
+------+----------+---------+
1 row in set (0.00 sec)

因为当前 tom 的事务隔离级别是 READ-UNCOMMITTED，所以读取到了 root 事务中未提交的数据更新。（未屏蔽脏读）


root 回滚事务

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    4000 |
+------+----------+---------+
1 row in set (0.00 sec)


tom 再查询数据

mysql> select * from user_table where user = 'AA';
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |    4000 |
+------+----------+---------+
1 row in set (0.00 sec)

脏读，

tom 在自己的事务中如果以 root 中未提交的事务影响的数据（且之后进行了回滚）作为基础进行其他操作就会对数据的正确性产生影响。

取自“https://www.jihongchang.top/index.php?title=命令行验证MySQL的隔离级别&oldid=4404”