vlambda博客
学习文章列表

-- 登录mysql的root账户，-p待输入密码，-h mysql服务器地址 -P 端口号（注意大写）➜ mysql -u root -p -h localhost -P 3307Enter password: ****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 284Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>-- 查看当前事务的隔离级别mysql> show variables like 'transaction_isolation';+-----------------------+-----------------+| Variable_name | Value |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+
-- 或者如此查看事务的隔离级别mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ |+-------------------------+
-- 可以看到MySQL的默认事务隔离级别是可重复读

mysql> show columns from t_user;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id   | int(11)     | NO   | PRI | NULL    | auto_increment || name | varchar(50) | NO | | NULL | |+-------+-------------+------+-----+---------+----------------+

-- 可以看到mysql默认开启自动提交模式mysql> SHOW VARIABLES LIKE 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+
-- 可以设置是否自动提交-- 值为 0 和值为 OFF：关闭事务自动提交。-- 值为 1 和值为 ON：开启事务自动提交。-- 如下可设置的4种值-- SET autocommit = 0|1|ON|OFF;

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-UNCOMMITTED |+-------------------------+

mysql> select * from t_user;+----+--------+| id | name |+----+--------+| 1 | 达摩 |+----+--------+

-- session1 事务A 开始mysql> begin;-- session2 事务B 开始mysql> begin;-- session2 事务B 修改name = 吕布 （注意这里并未提交）mysql> update t_user set name = '吕布' where id = 1;-- session1 事务A 读取id = 1的数据 （注意，读到了未提交的脏数据 name = 吕布）mysql> select * from t_user where id = 1;+----+--------+| id | name |+----+--------+| 1 | 吕布 |+----+--------+-- session1 事务A 对读到的脏数据进行提交(事务A结束)mysql> commit;-- session2 事务B 对刚才的修改进行回滚（事务B结束）mysql> rollback;
-- 最终数据库中的name并没有改变，但是事务A读到了脏数据，这就是脏读-- 此事务隔离级别（读未提交）也会造成不可重复读和幻读，这里不做演示，看下面的隔离级别中演示

-- 设置事务的隔离级别为读已提交mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.00 sec)-- 查看修改结果，已经为读已提交mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-COMMITTED |+-------------------------+

mysql> select * from t_user;+----+--------+| id | name |+----+--------+| 1 | 达摩 |+----+--------+

-- session1 事务A开始事务mysql> begin;Query OK, 0 rows affected (0.00 sec)-- session1 事务A 读取表中数据（注意此时未提交事务）mysql> select * from t_user where id = 1;+----+--------+| id | name |+----+--------+| 1 | 达摩 |+----+--------+-- session2 事务B直接修改并提交事务mysql> update t_user set name = '曹操' where id = 1;Query OK, 1 row affected (0.03 sec)-- session1 事务A 再次读取表中数据，发现同一个事务中得到了不一样的结果，这就是不可重复读mysql> select * from t_user where id = 1;+----+--------+| id | name |+----+--------+| 1 | 曹操 |+----+--------+-- 只要是事务A一直不提交，其他事务修改提交之后，事务A再次查询都能查到最新修改的结果-- 此事务隔离级别也会造成幻读，但是避免了脏读-- 测试是否避免脏读，事务A此时未提交-- session2 事务B开始mysql> begin;Query OK, 0 rows affected (0.00 sec)-- session2 事务B修改name = 伽罗 （注意此时未提交）mysql> update t_user set name = '伽罗' where id = 1;Query OK, 1 row affected (0.00 sec)-- session1 事务A进行读取，看到并没有读取到脏数据mysql> select * from t_user where id = 1;+----+--------+| id | name |+----+--------+| 1 | 曹操 |+----+--------+-- session2 事务B回滚mysql> rollback;Query OK, 0 rows affected (0.05 sec)-- session1 事务A提交mysql> commit;Query OK, 0 rows affected (0.00 sec)

-- 设置事务的隔离级别为可重复读mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)-- 查看修改结果，已经为可重复读mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ |+-------------------------+

mysql> select * from t_user;+----+--------+| id | name |+----+--------+| 1 | 曹操 |+----+--------+

-- session1 事务A开启mysql> begin;Query OK, 0 rows affected (0.00 sec)-- session1 事务A查询（注意未提交）mysql> select * from t_user;+----+--------+| id | name |+----+--------+| 1 | 曹操 |+----+--------+-- session2 事务B添加数据（提交）mysql> insert into t_user(name) values('马可波罗');Query OK, 1 row affected (0.04 sec)-- session1 事务A再次查询（一个事务中两次查询多出了数据，幻读）mysql> select * from t_user;+----+--------------+| id | name |+----+--------------+| 1 | 曹操 || 2 | 马可波罗 |+----+--------------+-- 测试此事务隔离级别能避免不可重复读现象（此时事务A还是未提交）-- session2 事务B修改id= 1的数据名字为狄仁杰（提交）mysql> update t_user set name = '狄仁杰' where id = 1;Query OK, 1 row affected (0.04 sec)-- 此时session1 事务A再次读取数据（可以看到跟上一次一样，证明避免了不可重复读现象）mysql> select * from t_user;+----+--------------+| id | name |+----+--------------+| 1 | 曹操 || 2 | 马可波罗 |+----+--------------+