命令行验证MySQL的隔离级别

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)
   

验证

查看初始数据

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 用户的事务开始时 balance 的值是多少，直到 root 用户的事务结束之前对 root 用户来说就一直是多少，不会变。