【数据库】详解MySQL数据库中的事务与锁

当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态

以银行转账事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态

(3) 隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的

(4) 持久性

一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中

事务的 ACID 原则保证了一个事务要么成功提交，要么失败回滚。因此，它对事务的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态

1.2.事务开启的方式

查看事务自动提交是否开启

SHOW VARIABLES LIKE 'autocommit';

设置事务自动提交

SET SESSION autocommit = on;

事务结束的三种方式

commit、rollback、断开客户端链接

1.3.读一致性问题及其解决

原子性：事务具有回滚的能力，InnoDB引擎使用undo log日志表来进行回滚操作

持久性：InnoDB引擎使用redo log日志表来保证数据的持久性

事务的隔离性产生的问题：

脏读：一个事务读取到了另一个事务未提交的数据

不可重复读：一个事务读取到了另一个事务已提交的数据，在同一个事务内不管进行多少次查询，查询到的结果总是相同的

幻读（虚读）：一个事务读取了另一个事务提交的新增数据

事务并发的三大问题都是数据库的读一致性问题，必须由数据库提供一定的事务隔离机制来解决

MySQL数据库的隔离级别：

Read Uncommitted (未提交读)：未解决任何并发问题，事务未提交的数据对其他事务也是可见的，会出现脏读
Read Committed (已提交读)：解决脏读问题，一个事务开始之后，只能看到已提交的事务所做的修改，会出现不可重复读
Repeatable Read (可重复读)：解决不可重复读问题，在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题
Serializable (串行化)：解决所有问题，最高的隔离级别，通过强制事务的串行执行

InnoDB对事务隔离级别的支持程度：

如果要解决读一致性的问题，保证一个事务中前后两次读取数据结果一致，实现事务隔离，应该怎样做？

方法一：在读取数据前对其加锁，阻止其他事务对数据进行修改

（LBCC）Lock Based Concurrency Control

方法二：生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取

（MVCC）Multi Version Concurrency Control，MVCC只在RC、RR中使用

2.MVCC解决读一致性问题原理

环境：MySQL数据，InnoDB存储引擎、Repeatable Read（可重复读）隔离级别

2.1.MVCC概念

（MVCC）Multi Version Concurrency Control。MVCC只在Read Committed （已提交读）和Repeatable Read（可重复读）中使用

MVCC是一种并发控制的方法，一般在数据库管理系统中实现对数据库的并发访问，在编程语言中实现事务内存

如果有人从数据库中读数据的同时，有另外的人写入数据，有可能读数据的人会看到“半写”或者不一致的数据。有很多种方法来解决这个问题，叫做并发控制方法。最简单的方法，通过加锁，让所有的读者等待写者工作完成，但是这样效率会很低。MVCC使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的

当一个MVCC数据库需要更改一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时（obsolete）并在别处增加新版本的数据。这样就会有存储多个版本的数据，但是只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改、删除了，也对先前正在读的用户没有影响。这种多版本的方式避免了填充删除操作在内存和磁盘存储结构造成的空洞的开销，但是需要系统周期性整理（sweep through）以删除过时的数据

MVCC提供了时点（point in time）一致性视图。MVCC并发控制下的读事务一般使用时间戳或者事务ID去标记当前读的数据库的状态（版本），读取这个版本的数据。读、写事务相互隔离，不需要加锁。读写并存的时候，写操作会根据目前数据库的状态，创建一个新版本，并发的读则依旧访问旧版本的数据。

一句话讲，MVCC用“同一份数据临时保留多版本”的方式，实现并发控制。

MySQL会在表中添加2个隐藏的字段，DB_TRX_ID（插入或者更新行的最后一个事务的ID）和DB_ROLL_PTR（回滚指针）。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。回滚指针用来查找历史版本的数据

2.2.准备环境

MVCC效果演示

创建表结构，添加数据

CREATE TABLE `t_mvcc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建测试表

CREATE TABLE `t_mvcc_demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3.MySQL中的锁

锁的概念：

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，数据是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突是影响数据库并发访问性能的一个重要因素

MySQL中的锁：

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制，比如：

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

仅从锁的角度来说：表级锁更适合于以查询为主；行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用

总结：

锁定粒度：表锁>行锁

加锁效率：表锁>行锁

冲突概率：表锁>行锁

并发性能：表锁<行锁

3.1.行锁之共享锁

共享锁，又称为读锁，简称S锁

共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

加锁：

select * from t_person_lock where id=1 LOCK IN SHARE MODE;

释放锁：事务结束后会释放锁

应用场景：例如现在有主表order_info，从表order_detail，现在正在修改order_detail表中的数据，但是不希望该数据对应的主表数据被修改，那么就可以在主表的数据上加共享锁，其他的事务就不能修改数据了

3.2.行锁之排它锁

排它锁又称写锁，简称X锁

排它锁不能与其它锁并存，如一个事务获取了某个数据行的排它锁，其他事务就不能再获取该行的锁，只有获取了排它锁的事务可以对数据行进行读取和修改

加锁：

自动：delete / update / insert默认加上X锁

手动：

select * from t_person_lock where id=1 FOR UPDATE;

释放锁：事务结束后会释放锁