关于MySQL的锁以及数据脏读,重复读,幻读的笔记。

这几天花了有10天再MySQL上学习,一个小脚本全部用的MySQL,本来想用多线程操作MySQL代替队列了。

但中间碰到了一些问题,查了些资料,随便把MySQL里面的一些锁的信息记录下。

 

MySQL5.5之前mysql 默认使用 MyISAM引擎,MyISAM引擎与INNODB的最主要的区别是一个支持事务,一个不支持事务。

还有MyISAM使用的是表锁,INNODB使用的是行所。

 

我这里主要记录一些,数据的脏读,重复读,以及幻读。

数据库事务具有ACID这4个特性:

  • A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
  • C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
  • I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
  • D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。

出现脏读,重复读,以及幻读都是再一致性上面出了问题。

 

首先,查看一下我的数据版本以及表格格式

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.27    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE xman;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xman  | CREATE TABLE `xman` (
  `name` varchar(100) NOT NULL,
  `addr` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `hobby` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name` (`name`,`addr`),
  KEY `hobby` (`hobby`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

 

一下对于脏读,不可重复读,以及幻读,来至本人有限的理解。

首先脏读

mysql> SELECT * FROM xman;
+------+----------+------+-------+
| name | addr     | age  | hobby |
+------+----------+------+-------+
| 888  | hangzhou |   99 | sing  |
+------+----------+------+-------+
1 row in set (0.00 sec)

mysql> 

 数据库里面有这么一条数据.

这个是干扰操作,在事务中执行了修改,然后又进行了

mysql> SELECT * FROM xman;
+------+----------+------+-------+
| name | addr     | age  | hobby |
+------+----------+------+-------+
| 888  | hangzhou |   99 | sing  |
+------+----------+------+-------+
1 row in set (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

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

mysql> UPDATE xman SET name=‘999‘ WHERE age=99;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

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

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


mysql> INSERT INTO xman VALUES(‘lala‘,‘shanghai‘, 55, ‘play‘);
Query OK, 1 row affected (0.00 sec)

mysql> 

 这是另外一个窗口读取到的数据:

mysql> SELECT * FROM xman;
+------+----------+------+-------+
| name | addr     | age  | hobby |
+------+----------+------+-------+
| 888  | hangzhou |   99 | sing  |
+------+----------+------+-------+
1 row in set (0.00 sec)

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


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

mysql> SELECT * FROM xman;
+------+----------+------+-------+
| name | addr     | age  | hobby |
+------+----------+------+-------+
| 888  | hangzhou |   99 | sing  |
+------+----------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM xman;
+------+----------+------+-------+
| name | addr     | age  | hobby |
+------+----------+------+-------+
| 888  | hangzhou |   99 | sing  |
| lala | shanghai |   55 | play  |
+------+----------+------+-------+
2 rows in set (0.00 sec)

 从结果可以看出,干扰操作,在没有COMMMIT事务的情况下,另外一个窗口可以读到干扰操作执行的任务,但这些数据读取到的数据极有可能是不对的。

因为干扰操作可能会进行ROLLBACK回滚操作,所以读取数据的时候,读到了不对的数据,也就是脏读。

 

不可重复读(Non Repeatable Read)

其实这个比脏读确实要好一点,在读取数据的时候,如果干扰操作在事务中对数据进行了增删改,读操作不能该收到变化,但干扰操作COMMIT了以后,另一个窗口执行的获取数据就会发生变化,也就是同一个事务中读取到了不同的数据,这就是我认为的不可重复读取

 

不可重复读

干扰操作又对数据进行了插入,但这次,再插入后,另一个窗口的事务再执行查寻窗口的时候,数据没有发生变化

但当干扰操作COMMIT了以后,另一个窗口的事务执行查寻是,数据发生了变化。

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

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


mysql> INSERT INTO xman VALUES(‘wangba‘,‘beijing‘,19, ‘ball‘);
Query OK, 1 row affected (0.00 sec)

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

mysql> 

 

幻读,我网上了解了一下,幻读,正常情况下,是你读不出这个数据的,就算干扰操作提交了事务,但你可以对该行数据进行进行UPDATE后就能看到数据。

或者当设置又字段唯一的时候,你无法插去数据,但你看不到重复的行,能不能删,我到时候测试下(能删的)。

干扰操作

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO xman VALUES(‘hong4‘,‘bj‘,22,‘dance‘);
Query OK, 1 row affected (0.00 sec)

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

mysql> 

 另一个窗口

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * FROM xman;
+-------+------+------+-------+
| name  | addr | age  | hobby |
+-------+------+------+-------+
| hong  | bj   |   22 | dance |
| hong2 | bj   |   22 | dance |
| hong3 | bj   |   22 | dance |
+-------+------+------+-------+
3 rows in set (0.00 sec)

mysql> select * FROM xman;
+-------+------+------+-------+
| name  | addr | age  | hobby |
+-------+------+------+-------+
| hong  | bj   |   22 | dance |
| hong2 | bj   |   22 | dance |
| hong3 | bj   |   22 | dance |
+-------+------+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM xman WHERE name=‘hong4‘;
Empty set (0.00 sec)

mysql> INSERT INTO xman VALUES(‘hong4‘,‘bj‘,22,‘dance‘);
ERROR 1062 (23000): Duplicate entry ‘hong4‘ for key ‘PRIMARY‘
mysql> SELECT * FROM xman WHERE name=‘hong4‘;
Empty set (0.01 sec)

mysql> DELETE FROM xman WHERE name=‘hong4‘;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO xman VALUES(‘hong4‘,‘bj‘,22,‘dance‘);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xman;
+-------+------+------+-------+
| name  | addr | age  | hobby |
+-------+------+------+-------+
| hong  | bj   |   22 | dance |
| hong2 | bj   |   22 | dance |
| hong3 | bj   |   22 | dance |
| hong4 | bj   |   22 | dance |
+-------+------+------+-------+
4 rows in set (0.01 sec)

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

mysql> 

 可以看出来,幻读是读不出那条数据的,说明数据读取的时候,一致性能实现的,避免了可重复读的错误。

 

关于MySQL的锁以及数据脏读,重复读,幻读的笔记。

上一篇:【Vue】-- 数据双向绑定的原理 --Object.defineProperty()


下一篇:MongoDB 数组操作符与运算操作符