MySQL Transaction

2016-09-25
事务基本要素 ACID
  • Atomicity原子性:一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
  • Consistency一致性:事务开始前和结束后，数据库的完整性没有遭到破坏。
  • Isolation隔离性:在同一时间，只允许一个事务请求同一数据。
  • Durability持久性:事务完成以后，该事务对数据库所做的操作持久化在数据库中，并不会被会滚。
事务隔离级别Transaction Isolation Level
  1. READ_UNCOMMITTED(未提交读)

事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致很多问题，从性能上来说，READ_UNCOMMITTED不会比其他级别好太多，但却会缺乏其他级别的很多好处，除非真的非常有必要的理由，在实际应用中一般很少使用

  1. READ_COMMITTED(提交读)

大多数据库系统的默认隔离级别都是READ_COMMITTED(但MySQL不是)。READ_COMMITTED满足前面提到的隔离性的简单定义:一个事务开始时，只能”看见”已经提交的事务所做的修改。话句话说，一个事物从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。

  1. REPEATABLE_READ(可重复读)

解决了脏读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另一个幻读(Phantom Read)的问题。所谓幻读，指的是某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)解决了幻读的问题。可重复读是MySQL的默认事务隔离级别。

  1. SERIALIZABLE(可串行化)

最高的隔离级别。通过强制事务串行执行，避免了前面说的幻读的问题。简单来说SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁的竞争问题，实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑该级别

⚠️REPEATABLE READ:在mysql中，不会出现幻读。mysql的实现和标准定义的RR隔离级别有差别。

⚠️由上往下，级别越来越高，并发性越来越差，安全性越来越高，反之则反。

——–隔离级别——– 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ_UNCOMMITTED Yes Yes Yes No
READ_COMMITTED No Yes Yes No
REPEATABLE_READ No No Yes No
SERIALIZABLE No No No Yes
事务中常出现的并发问题
  • 脏读:一个事务读取了另一个事务操作但未提交的数据。
  • 可重复读:一个事务中的多个相同的查询返回了不同数据。
  • 幻读:事务并发执行时，其中一个事务对另一个事务中操作的结果集
死锁问题

T1

start transaction;
update user set age=15 where id=1;
update user set age=10 where id=2;
commit;

T2

start transaction;
update user set age=12 where id=2;
update user set age=10 where id=1;
commit;

如果T1,T2都执行了第一条update语句，更新了一行，同时也锁定了该行数据，接着每个事务都尝试去执行第二条update语句，却发现改行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环，除非由外部因素介入才可能解除死锁。

为了解决这个问题，数据库系统引入了各种死锁检测和死锁超时机制。

设置事务级别
set session transaction isolation level read committed;
查看事务级别
select @@tx_isolation;
查看是否自动提交
select @@autocommit;
@@autocommit
1

结果1表示自动提交，设置为禁用自动提交set autocommit=0;

事务管理

原文

探索数据库的事务隔离级别

Transaction

2021-12-25 新增补充：死锁处理；意向锁；MVCC并发控制

  • 死锁处理方案
    1. Deadlock Detection死锁检测：数据库系统根据waits-for图记录事务的等待关系，其中点代表事务，有向边代表事务在等待另一个事务放锁。当waits-for图出现环时，代表死锁出现了。系统后台会定时检测waits-for图，如果发现环，则需要选择一个合适的事务abort。
    2. Deadlock Prevention死锁预防：当事务去请求一个已经被持有的锁时，数据库系统为防止死锁，杀死其中一个事务（一般持续越久的事务，保留的优先级越高）。这种防患于未然的方法不需要waits-for图，但提高了事务被杀死的比率。
  • 意向锁 ：如果只有行锁，那么事务要更新一亿条记录，需要获取一亿个行锁，将占用大量的内存资源。 我们知道锁是用来保护数据库内部访问对象的，这些对象根据大小可能是：属性（Attribute）、记录（Tuple）、页面（Page）、表（Table），相应的锁可分为行锁、页面锁、表锁。对于事务来讲，获得最少量的锁当然是最好的，比如更新一亿条记录，或许加一个表锁就足够了。层次越高的锁（如表锁），可以有效减少对资源的占用，显著减少锁检查的次数，但会严重限制并发。层次越低的锁（如行锁），有利于并发执行，但在事务请求对象多的情况下，需要大量的锁检查。数据库系统为了解决高层次锁限制并发的问题，引入了意向（Intention）锁的概念： Intention-Shared (IS)：表明其内部一个或多个对象被S-Lock保护，例如某表加IS，表中至少一行被S-Lock保护。 Intention-Exclusive (IX)：表明其内部一个或多个对象被X-Lock保护。例如某表加IX，表中至少一行被X-Lock保护。 Shared+Intention-Exclusive (SIX)：表明内部至少一个对象被X-Lock保护，并且自身被S-Lock保护。例如某个操作要全表扫描，并更改表中几行，可以给表加SIX。
  • 意向锁的好处在于：当表加了IX，意味着表中有行正在修改。

  • 这时对表发起DDL操作，需要请求表的X锁，那么看到表持有IX就直接等待了，而不用逐个检查表内的行是否持有行锁，有效减少了检查开销。（2）这时有别的读写事务过来，由于表加的是IX而非X，并不会阻止对行的读写请求（先在表上加IX，再去记录上加S/X），事务如果没有涉及已经加了X锁的行，则可以正常执行，增大了系统的并发度。

  • 基于MVCC的并发控制：数据库维护了一条记录的多个物理版本。事务写入时，创建写入数据的新版本，读请求依据事务/语句开始时的快照信息，获取当时已经存在的最新版本数据。它带来的最直接的好处是：写不阻塞读，读也不阻塞写，读请求永远不会因此冲突失败（例如单版本T/O）或者等待（例如单版本2PL）。对数据库请求来说，读请求往往多于写请求。主流的数据库几乎都采用了这项优化技术。 MVCC是读和写请求的优化技术，没有完全解决数据库并发问题，它需要与前述的几种并发控制技术组合，才能提供完整的并发控制能力。常见的并发控制技术种类包括：MV-2PL，MV-T/O和MV-OCC
  • MVCC还有两个关键点需要考虑：多版本数据的存储和多余多版本数据的回收。多版本数据存储方式，大致可以分为两类：（a）Append only的方式，新旧版本存储在同一个表空间，例如基于LSM-Tree的存储引擎（b）主表空间记录最新版本数据，前镜像记录在其它表空间或数据段，例如InnoDB的多版本信息记录在undo log。多版本数据回收又称为垃圾回收（GC），那些没有机会再被任何读请求获取的旧版本记录，应该被及时删除。

最新文章