数据库之msyql sql事务浅析加锁、解锁、死锁、脏读、不可重复读、幻读
mysql事务介绍
用来保证数据库的完整性,一组 sql 要么都执行成功、要么全部不执行
事务的四大特征
原子性:
一个事务中的操作要么全部完成、要么全部不完成,假如在中间发生错误,则回滚,此时就像这个事务从来没有发生过一样
隔离性:
数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行,导致数据的不一致,隔离性分为 4 种隔离级别,分别为 读未提交、读已提交、可重复读(默认)、串行化
持久性:
事务执行完成之后,永久保存到磁盘中,即使故障也不会丢失
一致性:
上面的三个特质都是为了保证一致性,也就是说在事务开始之前和事务开始之后,数据库的完整性没有被破坏(eg a 向 b 转账,b 不可能没有收到钱)
查看隔离级别及设置隔离级别 (只有 innodb 支持事务)
// 查看
mysql> SELECT @@TRANSACTION_ISOLATION;
+-------------------------+
| @@TRANSACTION_ISOLATION |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
/* 设置隔离级别 */
//设置read uncommitted级别
set session transaction isolation level read uncommitted;
//设置read committed级别
set session transaction isolation level read committed;
//设置repeatable read级别
set session transaction isolation level repeatable read;
//设置serializable级别
set session transaction isolation level serializable;
当事务的隔离级别不同时,高并发时会出现脏读、不可重复读、幻读的现象 ,观察以下的 name 值
read uncommitted 级别下脏读现象
// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
// session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
// session1
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | nn | NULL | NULL |
| 2 | bianca | NULL | NULL |
+------+--------+------+------------+
2 rows in set (0.00 sec)
// session1
mysql> update users set name='alex' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
// session2
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | alex | NULL | NULL |
| 2 | bianca | NULL | NULL |
+------+--------+------+------------+
2 rows in set (0.00 sec)
此时我们可以看到当 session1 更新了数据但是还没有提交的时候,session2 读取到了 session1 的更新还未提交的数据,这个就叫做脏读,read committed 级别将会解决这种问题,使其只能读到提交后的数据。
read committed 级别下的不可重复读
// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | alex | NULL | NULL |
| 2 | bianca | NULL | NULL |
+------+--------+------+------------+
2 rows in set (0.00 sec)
// session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | alex | NULL | NULL |
| 2 | bianca | NULL | NULL |
+------+--------+------+------------+
2 rows in set (0.00 sec)
// session1
mysql> update users set name='bob' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
// session2
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | alex | NULL | NULL |
| 2 | bianca | NULL | NULL |
+------+--------+------+------------+
2 rows in set (0.00 sec)
此时我们可以看到在 session1 未提交之前 session2 读取的还是以前的数据,因此解决了脏读
// session1
mysql> commit;
Query OK, 0 rows affected (0.16 sec)
// session2
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | bob | NULL | NULL |
| 2 | bianca | NULL | NULL |
+------+--------+------+------------+
2 rows in set (0.01 sec)
此时我们发现当 session1 提交了之后,session2 读取到了 session1 提交之后的数据,
在 session2 的事务里面我们读取了两次 users 表的数据,但是两次不一致,这个就叫做不可重复读
read committed 级别下的幻读
// session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
// session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
// session1
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | bob | NULL | NULL |
| 2 | bianca | NULL | NULL |
| 3 | tay | NULL | NULL |
+------+--------+------+------------+
// session2
mysql> insert into users(id,name) values(4,'lay');
Query OK, 1 row affected (0.00 sec)
// session2
mysql> commit;
Query OK, 0 rows affected (0.40 sec)
// session1
mysql> select * from users;
+------+--------+------+------------+
| id | name | age | created_at |
+------+--------+------+------------+
| 1 | bob | NULL | NULL |
| 2 | bianca | NULL | NULL |
| 3 | tay | NULL | NULL |
| 4 | lay | NULL | NULL |
+------+--------+------+------------+
4 rows in set (0.00 sec)
此时我们发现第一次查询和第二次查询的数据总量不一致,这种情况下就叫做幻读
当隔离级别设置为可重复读时就会解决上述问题
我们都知道线程在访问同一共享资源的时候,一般通过加锁防止数据出现不一致的情况,
但是在高并发下,加锁和解锁是一个很耗时的操作,还容易发生死锁的现象,
但是不加锁就会出现上述脏读、不可重复读、幻读的问题,mysql 为了解决上述问题,
在解决的同时保证高并发的性能,就需要mvcc(多版本并发控制)解决方案
相关文章