Mysql——锁、事务与并发控制

mysql用的时间也不短了，但从没有做过总结。今天就来总结一下吧

mysql服务器逻辑架构

每个连接都会在mysql服务端产生一个线程（内部通过线程池管理线程），比如一个select语句进入，mysql首先会在查询缓存中查找是否缓存了这个select的结果集，如果没有则继续执行 解析、优化、执行的过程；否则会之间从缓存中获取结果集。

Mysql并发控制——共享锁、排他锁

共享锁

共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；

排他锁

排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

锁策略

锁的开销是较为昂贵的，锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。

• Mysql锁策略：talbe lock(表锁)

表锁是Mysql最基本的锁策略，也是开销最小的策略，它会锁定整个表；具体情况是：若一个用户正在执行写操作，会获取排他的“写锁”，这是会锁定整个表，阻塞其他用户的读、写操作；

若一个用户正在执行读操作，会先获取共享锁“读锁”，这个锁运行其他读锁并发的对这个表进行读取，互不干扰。只要没有写锁的进入，读锁可以是并发读取统一资源的。

• Mysql锁策略：row lock(行锁)

行锁可以最大限度的支持并发处理，当然也带来了最大开销，顾名思义，行锁的粒度实在每一条行数据。

事务

事务就是一组原子性的sql，或者说一个独立的工作单元。就是说要么mysql引擎会全部执行这一组sql语句，要么全部都不执行（比如其中一条语句失败的话）。

比如，tim要给bill转账100块钱：
1.检查tim的账户余额是否大于100块；
2.tim的账户减少100块；
3.bill的账户增加100块；
这三个操作就是一个事务，必须打包执行，要么全部成功，要么全部不执行，其中任何一个操作的失败都会导致所有三个操作“不执行”——回滚。

CREATE TABLE `employees`.`account` (  `id` BIGINT (11) NOT NULL AUTO_INCREMENT,  `p_name` VARCHAR (4),  `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,  PRIMARY KEY (`id`)) ;INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200'); INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200'); START TRANSACTION;SELECT p_money FROM account WHERE p_name="tim";//step1UPDATE account SET p_money=p_money-100 WHERE p_name="tim";//step2UPDATE account SET p_money=p_money+100 WHERE p_name="bill";//step3COMMIT;

一个良好的事务系统，必须满足ACID特点：

ACID

• A:atomiciy原子性
  一个事务必须保证其中的操作要么全部执行，要么全部回滚，不可能存在只执行了一部分这种情况出现。

• C:consistency一致性
  数据必须保证从一种一致性的状态转换为另一种一致性状态；比如上一个事务中执行了第二步时系统崩溃了，数据也不会出现bill的账户少了100块，但是tim的账户没变的情况。要么维持原装（全部回滚），要么bill少了100块同时tim多了100块，只有这两种一致性状态的

• I：isolation隔离性

在一个事务未执行完毕时，通常会保证其他事务无法看到这个事务的执行结果

• D:durability持久性
  事务一旦commit，则数据不会保存下来，即使提交完之后系统崩溃，数据也不会丢失。

隔离级别

• READ UNCOMMITTED(未提交读)

事务中的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据——脏读。脏读会导致很多问题，一般不适用这个隔离级别。

• READ COMMITTED(提交读)

一般数据库都默认使用这个隔离级别（Mysql不是），这个隔离级别保证了一个事务如果没有完全成功（commit执行完），事务中的操作对其他事务是不可见的。
- REPEATABLE READ(可重复读)
这个隔离级别解决了脏读的问题，但会产生幻读，问题。

脏读与幻读与不可重复读：

脏读：一个事务读取到另一事务未提交的更新新据。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有
提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据， 那么另
外一个事务读到的这个数据是脏数据，依据脏数据所做的操作也可能是不正确的。

不可重复读：在同一事务中，多次读取同一数据返回的结果有所不同。换句话说就是，后续读取可以读到另一事务已提交的
更新数据。相反，“可重复读”在同一事务中多次读取数据时，能够保证所读数据一样，也就是，后续读取不能读到另一事务
已提交的更新数据。

幻读：事务T1执行一次查询，然后事务T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同
的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然
出现的一样。
- SERIALIZABLE(可串行化)
最强的隔离级别，通过给事务中每次读取的行加锁（行锁），保证不产生幻读问题，但是会导致大量超时以及锁争用问题。

Mysql死锁问题

死锁，就是产生了循环等待链条，我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源，导致无线等待下去。
比如：

//Thread ASTART TRANSACTION;UPDATE account SET p_money=p_money-100 WHERE p_name="tim";UPDATE account SET p_money=p_money+100 WHERE p_name="bill";COMMIT;//Thread BSTART TRANSACTION;UPDATE account SET p_money=p_money+100 WHERE p_name="bill";UPDATE account SET p_money=p_money-100 WHERE p_name="tim";COMMIT;

当线程A执行到第一条语句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;锁定了p_name=”tim”的行数据；并且试图获取p_name=”bill”的数据；

，此时，恰好，线程B也执行到第一条语句：UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;

锁定了 p_name=”bill”的数据，同时试图获取p_name=”tim”的数据；
此时，两个线程就进入了死锁，谁也无法获取自己想要获取的资源，进入无线等待中，直到超时！

对于死锁，数据库一般通过死锁监测、死锁超时机制解决；通常会执行回滚，打破死锁状态，然后再次执行之前死锁的事务即可。

Mysql中的事务

• 自动提交（AutoCommit）

mysql默认采用AutoCommit模式，也就是每个sql都是一个事务，并不需要显示的执行事务

多版本并发控制-MVCC

MVCC是个行级锁的变种，它在很多情况下避免了加锁操作，因此开销更低。虽然实现不同，但通常都是实现非阻塞读，对于写操作只锁定必要的行。

通常MVCC实现有乐观并发控制与悲观并发控制，INNODB的MVCC通常是通过在每行数据后边保存两个隐藏的列来实现，一个保存了行的创建时间，另一个保存了行的删除时间。当然存储的并不是实际的时间值，而是系统版本号,每个事务开始，系统版本号就会递增!,每个事务开始时刻的版本号也会作为这个事务的版本号，用来和查询到的每行版本号做比较。下边在Mysql默认的Repeatable Read隔离级别下，具体看看MVCC操作：

• Select：
  a.InnoDB只查找版本号早于当前版本号的数据行，这样保证了读取的数据要么实在这个事务开始之前就已经commit了的（早于当前版本号），要么是在这个事务自身中执行操作的数据（等于当前版本号）。
  b.行的删除版本号要么未定义，要么早于当前的版本号，这样保证了事务读取到的数据在事务开始之前未被删除。

• Insert

InnoDB为这个事务中新插入的行，保存当前事务版本号的行（作为行的版本号）。

• Delete
  InnoDB为每一个删除的行保存当前事务版本号，最为行的删除标记。

• Update

InnoDB将保存当前版本号最为行版本号，同时保存当前版本号到原来行（更新前）的删除版本号标识处。