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

2015-10-19 22:03:00
admin
原创 3435
摘要: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,查询会话事务隔离级别。

发表评论
评论通过审核之后才会显示。