mysql事务 MyISAM InnoDB 如何加读锁快照读和当前读事务隔离级别 - 数据

一、事务支持

存储引擎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，查询会话事务隔离级别。