命令行验证MySQL的隔离级别
https://www.bilibili.com/video/BV1eJ411c7rf?p=40
准备
- Windows 下 cmd 进入命令行：
C:\Users\Administrator>mysql -uroot -p123456
- 创建一个新用户登录MySQL Server：用户名：tom 密码：abc123
mysql> create user tom identified by 'abc123'; Query OK, 0 rows affected (0.03 sec)
- 登录tom：
C:\Users\Administrator>mysql -utom -pabc123
- 查看 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)
- 查看 tom 用户可以操作的数据库：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
- 为 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)
- 验证 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 用户的事务结束（commit 或 rollback）之前对 root 用户来说就一直是多少，不会变。
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 用户提交更新的数据。
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)