插件式存储引擎是MySQL数据库最重要的特性之一,用户可以根据需要选择如何使用存储和索引数据等。MySQL支持多种存储引擎,本文主要介绍三种存储引擎InnoDB,MyISAM以及MEMORY,然后再介绍MySQL的事务处理相关内容。
1、MySQL存储引擎
MySQL默认的存储引擎是InnoDB,可以根据不同需求进行更改。
1.1 存储引擎设置
查看MYSQL支持的存储引擎命令:show engines;
创建表时设置存储引擎:create table table_name() engine = innodb;
修改存储引擎:alter table table_name engine = myisam;
修改配置文件来达到全部表的存储引擎修改:Windows 下my.ini文件 ,Linux下my.cnf文件
[mysqld]
default-storage-engine=INNODB
1.2 存储引擎特点
InnoDB
该存储引擎提供具有提交,回滚、崩溃恢复能力事务安全机制、支持外键、自动增长列等功能,索引采用的是聚集索引,索引和数据存放在同一个文件中,所以INNODB存储引擎的数据存在磁盘上就两个文件。文件名和表名是相同的,扩展名分别为:.frm(存储表的定义) .ibd(存储的是数据和索引)
MyISAM
该存储引擎不支持事务、也不支持外键、索引采用非聚集索引,对数据完整性没有要求或者以select、insert为主的应用基本上采用MYISAM存储引擎,索引和数据分开存储,MYISAM在磁盘上存储的数据有三个文件,文件名和表名是相同的,扩展名分别为:.frm(存储表的定义) .MYD(MYData 存储数据) .MYI(MYIndex 存储索引)
MEMORY
该存储引擎使用内存来存储数据,每个memory表实际存在一个文件,格式.frm(存储表的定义),memory类型存储引擎表的访问是很快的,因为数据都存放在内存中,默认的索引hash索引(不支持区间查找),假如服务一旦关闭,表中的数据就不存在了。
1.3 三种存储引擎的比较
锁机制:在事务并发操作时,并发操作粒度;
B-树索引 | hash索引:提高访问速度;
外键:子表的字段依赖于父表的键,设置两张表的依赖关系;
事务:多个SQL语句执行,要么全部成功,要么全部失败,不存在部分成功;
索引缓存 | 数据缓存:在查询时,对源数据未做修改,重复查询是否可以减少磁盘IO;
2、事务处理
2.1 什么是事务?
事务就是由一个或多个SQL语句组成的不可分割的单元,只有所有的操作都正常完成,事务会提交给数据库,若部分操作未完成,事务就会退回初始状态。因此,事务要么成功,要么失败。
2.2 事务处理相关命令
操作 | 对应的命令 |
查看存储引擎 | SHOW CREATE TABLE 表名; |
更改引擎 | ALTER TABLE 表名 ENGINE=新引擎名; |
回滚 | ROLLBACK; |
声明事务开始 | BEGIN; |
事务提交 | COMMIT; |
查询自动提交功 能状态 | SELECT @@AUTOCOMMIT; |
设置自动提交功能 | SET AUTOCOMMIT=0或1;(0为手动) |
设置分离水平 | SET SESSION TRANSACTION ISOLATION LEVEL 分离水平; |
使用保留点 SAVEPOINT
savepoint 是在数据库事务处理中实现“子事务”(subtransaction),也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT,执行多条操作时,回滚到想要的那条语句之前。
使用 SAVEPOINT
SAVEPOINT savepoint_name; // 声明一个 savepoint
ROLLBACK TO savepoint_name; // 回滚到savepoint
删除 SAVEPOINT
保留点再事务处理完成(执行一条 ROLLBACK 或 COMMIT)后自动释放。
MySQL5 以来,可以用:
RELEASE SAVEPOINT savepoint_name; // 删除指定保留点
2.3 事务的ACID特性
一般来说,事务必须具备四个特性,简称为ACID特性:
原子性(Atomic)
事务是一个不可分割的整体,事务必须具有原子性。即事务包含的所有操作要么全部成功,要么全部失败回滚。
一致性(Consistency)
事务执行前后,数据库的数据状态必须保持一致性。 数据库的一致性状态需要用户来负责,由并发控制机制实现。举例来说,网上购物来说,将商品加入购物车,商品的库存介绍、到商品加入购物车是一个完整性的事务。
由于并发操作带来的数据不一致性问题包括数据脏读,不可重复读,幻读,相关内容将在下一节进行介绍。
隔离性(Isolation)
当两个事务或多个事务并发操作时,为了保证数据的安全性,将一个事务的内部操作和其他事务隔离起来,不被其他正在执行的事务看到。举例说明,假如事务t1和事务t2并发操作时,对t1而言,要么是在t2执行完成之后执行,那么是可以看到t2执行的结果,要是在t2之前执行完成,那么t2能够看到t1执行的结果。
持久性(Durability)
事务完成之后,数据库中的数据永久性的保存下来,即使数据库出现故障,也能恢复数据。
2.4 事务未隔离问题
当多个线程都开启事务操作数据库中数据时,数据库系统要能进行隔离操作,以保证各个线程获取数据的准确性。如果没有隔离,会发生的几种问题:
脏读(Dirty Read)
一个事务读取了另一个事务未提交的数据。举例来说,事务A和事务B并发执行时,事务A更新后,事务B查询到事务A尚未提交的数据,此时,事务A回滚,则事务B读取到的数据就是脏数据(事务B读取事务A未移交的数据)
不可重复读(NonRepeatable Read)
一个事务的操作导致另一个事务前后两次读取的数据不同。举例来说,事务A和事务B并发执行,当事务B查询读取数据后,事务A更新了事务读物的数据,此时事务B再次读取数据是,发现前后两次的数据不一样(事务B读取了事务A已提交的事务)。
幻读(虚读)(Phantom Read)
一个事务的操作导致另一个事务前后两次查询结果的数据数量不同。举例来说,事务A和事务B并发执行,当事务B查询读取数据后,此时事务A增加了数据或者是删除了数据。事务B查询,发现查询到前一次不存在的数据,或者发现前一次查询的记录不见了(事务B读取了事务A新增加的或者读取到事务A删除的数据)。
2.5 事务的隔离级别
在JDBC编程中,Condition对象定义了5种事务隔离级别:
1、Transation_None:表示不支持事务
2、Transation_Read_Uncommited:未提交读。
说明在提交前一个事务可以看到另一个事务的变化。这样:脏读、不可重复读,幻读都是存在的。
3、Transation_Read_Commited:已提交读。
读取未提交的数据是不被允许的。这个级别任然允许不可重复读和幻读。
4、Transation_Repeatable_Read:可重复读。
事务保证能够读取相同的数据而不会失败。允许幻读
5、Transation_Serializable:可序列化/串行化。
最高的事务级别,防止脏读、可重复读、幻读
注意:事务的隔离级别越高,为避免冲突所环花费的性能也就越高
以下为不同隔离级别,对于三种问题的处理:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
未提交读 | 可以 | 可以 | 可以 |
已提交读 | 不可以 | 可以 | 可以 |
可重复读 | 不可以 | 不可以 | 可以 |
串行化 | 不可以 | 不可以 | 不可以 |
设置隔离级别
查看当前事务的隔离级别:select @@tx_isolation;
修改事务的隔离级别
>set [global | session] transaction isolation level 隔离级别名称;
>set tx_isolation=’隔离级别名称;’
隔离级别:Serializable | Repeatable read | Read committed |
Read uncommitted
注意:
设置默认级别是指当前session的下一个事务
设置session级别是指当前session以后的所有事务
设置global级别是指对之后的所有session,不包括当前session
2.6 事务控制语句
-
BEGIN 或 START TRANSACTION 显式地开启一个事务;
-
COMMIT 也可以使用 COMMIT WORK,不过二者是等价的。COMMIT 会提交事务,并使已对数据库进行的所有修改成为永久性的;
-
ROLLBACK 也可以使用 ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
-
SAVEPOINT identifier,SAVEPOINT 允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT;
-
RELEASE SAVEPOINT identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
-
ROLLBACK TO identifier 把事务回滚到标记点;
-
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ U NCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
2.7 MYSQL 事务处理主要有两种方法
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
2.8 MySQL中的锁机制
数据库为了维护ACID,尤其是一致性和隔离性,一般使用加锁的方式。同时由于数据库是个高并发的应用,同一时间有大量的并发访问,如果加锁过度,会极大的降低并发处理的能力。所以对于加锁的处理,是数据库对于事务处理的精髓所在
锁方案
一次封锁
因为有大量的并发访问,为了预防死锁,一般应用中采用的是一次封锁的方案:就是在方法的开始阶段,已经预先知道需要用到那些数据,然后全部锁住,在方法执行之后,再全部解锁。
这种方案可以有效避免死锁发生,当时由于数据库操作在事务开始阶段并不知道具体会用到哪些数据,所以该方案不合适在数据库中使用。
两段锁
两段锁协议将事务分成两个阶段:加锁阶段和解锁阶段
- 加锁阶段:在该阶段可以进行加锁操作。读数据前需要申请获取S锁(共享锁:其他事务可以继续加共享锁,但不能加排他锁);写数据前需要申请获取X锁(排他锁:其他事务不能获取任何锁)。加锁不成功,则事务进入等待状态,直到加锁成功才继续执行。
-
解锁阶段:当事务释放了一个封锁以后,事务进入解锁阶段,在该阶段只能进行解锁操作不能进行加锁操作。
备注:这个方案无法避免死锁,但是可以保证事务调度的串行化(串行化在数据库恢复和备份时候很重要)。
MySQL的锁类型
表锁
对一整张表加锁,并发能力低下(即使有分读锁、写锁),一般在DDL处理时使用
行锁
只锁住特定行的数据,并发能力强,MySQL一般都是用行锁来处理并发事务。
如果用到无索引的字段,那么MySQL会在存储引擎层面将所有的记录加锁,然后由MySQL Server过滤,如果不满足会调用unlock_row把不满足条件的记录释放锁(这里违背了二段锁协议)。
这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候,如果无法使用相应的索引,MySQL Server过滤数据的的时候特别慢,就会出现虽然没有修改某些行的数据,但是它们还是被锁住了的现象。
GAP锁(间隙锁)
是MySQL使用索引对行锁两边的区间进行加锁,避免其他事务在这两个区间insert的一种锁。
如图所示:数据库中存在值5,30。那么数据库会将数据段切分以下几个区间:
(negative infinity, 5],
(5,30],
(30,positive infinity)
当对值为30这一行加行锁的时候,会同时对(5,30]和(30,positive infinity)加GAP锁。这样其他事务如果想在这两个区间进行insert操作的时候,需要等待本次事务完成。
如果对不存在的数据进行更新,比如更新20(不存在)对应数据行,那么数据库也会对其存在的区间(5,30]加GAP锁。这样,如果有其他事务想插入值为10的数据,需要等待20这个事务完成。
如果使用的是没有索引的字段,那么会给全表加入GAP锁。
Next-Key锁
Next-Key锁是行锁和GAP锁的合并(MySQL使用它来避免幻读)
MVVC(多版本并发控制)
Innodb中的乐观锁实现。通过它提高MySQL的读取操作的性能。并能解决MySQL的重复读问题。
MVVC在每一行记录的后面加两个隐含列(记录创建版本号和删除版本号)。这里的版本号指的是事务的版本号(每个事务启动的时候,都有一个递增的版本号)。
- 比如插入一条记录(事务id为1)
id | name | create_version | delete_version |
---|---|---|---|
1 | bird | 1 | - |
- 如果把这条记录name更新为dog
id | name | create_version | delete_version |
---|---|---|---|
1 | dog | 2 | - |
1 | bird | 1 | 2 |
在更新操作的时候,采用的是先标记旧的那行记录为已删除,并且删除版本号是事务版本号,然后插入一行新的记录的方式。
- 删除这条记录时
id | name | create_version | delete_version |
---|---|---|---|
1 | dog | 2 | 3 |
1 | bird | 1 | 2 |
删除操作的时候,就把事务版本号作为删除版本号
- 执行查询操作需要符合如下规则才能被查出来
- 删除版本号 大于 当前事务版本号,就是说删除操作是在当前事务启动之后做的。
- 创建版本号 小于或者等于 当前事务版本号 ,就是说记录创建是在事务中(等于的情况)或者事务启动之前。
在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,是不及时的数据,不是数据库当前的数据!这在一些对于数据的时效特别敏感的业务中,就很可能出问题。
对于这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库当前版本数据的方式,叫当前读 (current read)。很显然,在MVCC中:
快照读:就是select>select * from table ....;
当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。>select * from table where ? lock in share mode;
>select * from table where ? for update;
>insert;
>update ;
>delete;
Innodb中事务隔离级别和锁的关系
Innodb通过使用不同的锁来实现事务隔离
避免脏读
通过对数据加行锁或则表锁,使对同一数据进行操作的事务处于等待状态,来避免同时操作
事务A | 事务B |
---|---|
begin; | begin; |
update class_teacher set class_name='初三二班' where teacher_id=1; | update class_teacher set class_name='初三三班' where teacher_id=1; |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction | |
commit; |
避免不可重复读
通过MVVC实现事务的可重复读
避免幻读
通过Next-Key锁避免产生幻读现象
MySQL在RC和RR中都避免了幻读现象。
参考
https://www.jianshu.com/p/bcc614524024
https://blog.csdn.net/moxigandashu/article/details/62046030
http://www.runoob.com/mysql/mysql-transaction.html
四种隔离事务总结: http://blog.chinaunix.net/uid-14010457-id-3956842.html