一、事务支持
存储引擎MyISAM(Indexed Sequential Access Method):默认engine,特点是高性能,但不支持事务。
MyISAM表存储成3个文件:table_name.frm存储表结构,table_name.MYD存储表数据,table_name.MYI存储表索引。
存储引擎InnoDB(InnoBase开发):Supports transactions, row-level locking, and foreign keys。
1 innodb_file_per_table默认关闭,即table_name.frm存储表结构,所有表数据共同存放在ibdata1、ibdata2等。
2 show variables like 'innodb_data%',可以看到ibdata1初始大小为10M,并且可以自动扩展。
innodb_data_file_path:ibdata1:10M:autoextend
innodb_data_home_dir:
show engines,查看engine支持情况。
二、事务最重要特性(通过原子性保障一致性)
原子性(Atomic):事务执行要做到"要么不做,要么全做",就是说不允许事务部分执行。因为故障使事务不能完成,在rollback时要消除对数据库的影响。
三、begin,rollback,commit方式实现事务(不受autocommit影响)
创建表
drop table if exists test;
create table test(id int, type varchar(20), age int) engine=InnoDB;
正常事务
begin;
insert into test values(1, 'a', 10);
insert into test values(2, 'a', 11);
select * from test; //id=1和id=2的数据都在。
commit;
select * from test; //id=1和id=2的数据都在。
异常事务
begin;
insert into test values(3, 'b', 20);
insert into test values(4, 'b', 21);
select * from test; //id=3和id=4的数据都在。
rollback;
select * from test; //id=3和id=4的数据都不在。
其它异常
只要没有调用commit,所有更新操作都会被回滚,如典型的网络异常。
四、autocommit方式(连续执行事务比较方便)
show variables like 'autocommit'; //查看autocommit设置。
set autocommit=1 //开启自动提交,mysql默认是自动提交的,即每提交一个query直接执行。
set autocommit=0 //禁止自动提交,后面所有的SQL都将做为事务处理,直到用commit确认或rollback结束,注意当结束这个事务的同时开启了新的事务。
示例:
set autocommit=0;
insert into test values(3, 'b', 20);
insert into test values(4, 'b', 21);
set autocommit=1; //提交
set autocommit=0;
insert into test values(3, 'b', 20);
insert into test values(4, 'b', 21);
rollback; //回滚
五、如何加读锁
锁类型:
SELECT ... FOR UPDATE,排他读锁,其它事务读和写需要等待;
SELECT ... LOCK IN SHARE MODE,共享读锁,其它事务写需要等待;
锁级别:
表锁,整表被锁住;
行锁,锁直接加在索引记录上面;
区间锁,锁加在不存在的空闲空间,可以是两个索引记录之间,也可以是第一个索引记录之前或最后一个索引之后的空间;
InnoDB锁特性:
默认行级锁,所以明确指定索引时使用行级锁(只锁定被选取的数据),否则做整表锁;
六、快照读和当前读
快照是事务开始由查询操作触发的一个数据快照,通过MVCC实现,Multi-Version Concurrency Control,即多版本并发控制。
1 快照读是读取快照(普通读是快照读)。
2 当前读是读取数据库最新数据(加锁读、数据更新都是当前读)。
3 当前事务的更新操作会影响当前快照,其它事务的更新操作不影响当前快照。
4 可重复读,同一个事务,一条记录第一次读和第二次读结果一样。
为什么要有快照读,因为要保证事务的可重复读,并且又保证了其它事务的可读写。
当前事务的更新操作对当前快照的影响:
1 更新操作成功会更新快照。
2 数据库记录和快照记录主键一致,则快照记录被更新。
3 数据库记录和快照记录索引一致,数据库记录直接被增加到快照记录,不做索引重复检查。
七、事务隔离级别(不同级别对应不同的锁)
read uncommited:脏读,一个事务可以读取另一个事务未提交的数据。
serializable:串行模式,事务被强制为依次执行,这是SQL标准建议的默认行为。
read commited:
1 一致读,可以读取另外一个事务提交过的数据,其它事务的更新影响当前事务的读。
2 读锁维持在语句,写锁维持整个事务,一个事务多次加锁读可能会读到不同数据。
repeatable read(数据库默认隔离级别,效率比较高):
1 可重复读,通过在整个事务读取同一份数据快照实现,其它事务的更新不影响当前事务的读。
2 读锁维持整个事务,写锁维持整个事务,一个事务多次加锁读会返回一致数据。
java.sql.Connection:(MYSQL默认REPEATABLE-READ)
public static final int TRANSACTION_NONE = 0;
public static final int TRANSACTION_READ_UNCOMMITTED = 1;
public static final int TRANSACTION_READ_COMMITTED = 2;
public static final int TRANSACTION_REPEATABLE_READ = 4;
public static final int TRANSACTION_SERIALIZABLE = 8;
设置和查询事务隔离级别:
方法1:set [glogal | session]tx_isolation='REPEATABLE-READ',默认对会话做设置。
方法2:set [glogal | session] transaction isolation level REPEATABLE READ,默认对会话做设置。
select @@global.tx_isolation,查询全局事务隔离级别。
select @@tx_isolation,查询会话事务隔离级别。