这几天花了有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>
可以看出来,幻读是读不出那条数据的,说明数据读取的时候,一致性能实现的,避免了可重复读的错误。