【MySQL笔记】事务

事务操作

默认每一条sql语句都是一个事务，事务自动提交，查看事务的提交方式：

select @@autocommit;  -- 1

结果是1，代表自动提交，设置事务不自动提交：

set @@autocommit=0;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

测试数据：

CREATE TABLE account(
ID int(11) primary key auto_increment comment '主键ID',
name varchar(20) comment '姓名',
money int(11) comment '金额'
)

insert into account(name,money) values('张三',2000);
insert into account(name,money) values('李四',2000);

测试事务的两种方式

在一个会话中，设置事务为不自动提交，执行多条sql属于同一个事务，如下：

set @@autocommit=0;
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';
commit;

第二种方式,显式开启事务，不需要将@@autocommit设置为0：

start transaction;    -- 或 BEGIN
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';
commit;

事务的四大特性(ACID)

原子性（Atomicity）

　　　　事务是不可分隔的最小单元，多个操作要么全部成功，要么全部失败。原子性只能保证单个事务的一致性

一致性（Consistency）

　　　　事务结束时，必须所有数据都保持一致，所有内部结构（如B树索引或双向链表）都必须正确

隔离性（Isolation）

　　　　保证事务在不受外部并发操作影响的独立环境下运行。并发执行的事务不会相互影响。由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

持久性（Durability）

　　　　事务一旦提交，对数据库的更新就是持久的

事务的最终目的就是为了保证数据的一致性，所以一致性是事务最重要的特性

并发事务问题

  • 脏读
    在一个事务里读取了另一个未提交的事务中的数据。
  • 不可重复读
    在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，数据被另一个事务修改并提交了。
  • 幻读
    一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这条数据已经存在，就好象发生了幻影。

事务的隔离级别

事务的隔离级别就时为了解决并发中存在的问题，事务的隔离级别是通过锁、MVCC的方式实现
MySQL中事务的默认隔离级别是REPEATABLE-READ

  • Read uncommitted（读未提交）
    最低隔离级别，以上并发问题都有可能发生
    实现机制：在前文有说到所有写操作都会加排它锁，那还怎么读未提交呢？因为排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是对不加锁的读就不起作用了。READ UNCOMMITTED隔离级别下, 读不会加任何锁。而写会加排他锁，并到事务结束之后释放。
  • Read committed（读已提交）
    可防止数据脏读
    实现机制：事务中的修改操作会加排他锁，直到事务提交时才释放锁。读取数据不加锁而是使用了MVCC机制。因此在读已提交的级别下，都会通过MVCC获取当前数据的最新快照，不加任何锁，也无视任何锁(因为历史数据是构造出来的，身上不可能有锁)。
    为什么Read committed可以防止数据脏读：脏读是因为读取了其他事务未提交的数据，之后事务回滚了，导致脏读。但是如果在事务中修改数据时加了排他锁，并且直到事务提交时才释放排他锁，在这之间不允许其他事务查询此记录，所以不会出现脏读。
    为什么遗留了不可重复读和幻读问题：MVCC版本的生成时机: 是每次select时。这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读，即：重复读时，会出现数据不一致问题，后面我们会讲解超支现象，就是这种引起的。
  • Repeatable read
    实现机制：READ COMMITTED级别不同的是MVCC版本的生成时机，即：一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读。
  • Serializable
    可以解决全部事务并发问题
    实现机制：所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。采用的是范围锁RangeS RangeS_S模式，锁定检索范围为只读，这样就避免了幻影读问题。
    Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

隔离级别命令

查看当前数据库隔离级别：

show global variables like '%isolation%';

设置事务隔离级别：

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {Read uncommitted|Read committed|Repeatable read|Serializable}

如下：

set session transaction isolation level read uncommitted;            -- 设置read uncommitted级别
set session transaction isolation level read committed;            -- 设置read committed级别
set session transaction isolation level repeatable read;            -- 设置repeatable read级别
set session transaction isolation level serializable;            -- 设置serializable级别

ADO.NET设置事务隔离级别

var tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted)

测试几种隔离级别：

read uncommitted：
打开两个会话，先后执行会话1、会话2中的代码
会话1：

set autocommit=0;                               -- 设置不自动提交
update account set name='fan' where id=1;       -- 将姓名修改为fan，不提交

会话2：

set session transaction isolation level read uncommitted;      -- 将当前会话隔离级别设置为read uncommitted
select * from account where id=1;                          -- 可以读取到会话1修改后的数据

read committed：
会话1：

set autocommit=0;                                          
update account set name='fan' where id=1;

会话2：

set session transaction isolation level read committed;  -- 将当前会话隔离级别设置为read committed
select * from account where id=1;                        -- 会话2读取到的还是原来的数据，直到会话1提交后，会话2才可以读到修改后的数据

再测试一下是否可以重复读：
会话1：

set session transaction isolation level read committed;  
set autocommit=0;      
select * from account where id=1;                    -- 先执行这个sql，查看结果。然后再执行会话2
select * from account where id=1;                    -- 执行完会话2后再执行一次查询，对比两次结果是否一致

会话2：

update account set name='fan' where id=1;         -- 将姓名修改为fan

结果是会话1中两次查询结果不一致

repeatable read：
会话1：

set session transaction isolation level repeatable read;  
set autocommit=0;      
select * from account where id=1;                          -- 先执行这个sql，查看结果。然后再执行会话2
select * from account where id=1;                          -- 执行完会话2后再执行一次查询，对比两次结果是否一致

会话2：

update account set name='fan' where id=1;         -- 将姓名修改为fan

结果是会话1中两次查询结果一致

serializable：
会话1：

set session transaction isolation level serializable; 
set autocommit=0;
select * from account where id=1;

会话2：

update account set name='fan' where id=1;

执行完会话1，此时会话1未提交，id=1的记录加了读锁，会话2执行update会被阻塞。直到会话1执行了commit;,会话2才会update成功

数据库-MySQL

相关

标签