MySQL三锁与事物

一、锁的概念

我们访问么个资源时，在么个时刻只能被一个线程所占有，只有当这个线程被执行完，释放这个资源别的线程才可以被访问。

二、锁的分类

我们创建一张表sys_lock，来测试一下

#创建表
CREATE TABLE `sys_lock` (
  `id` bigint(12) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `sex` varchar(20) DEFAULT NULL COMMENT '性别',
  `age` int(5) DEFAULT NULL COMMENT '年龄',
  `version` int(6) DEFAULT NULL COMMENT '版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

#插入数据
INSERT INTO sys_lock (`name`,sex,age,version) VALUES ("大宝","男",16,1)
INSERT INTO sys_lock (`name`,sex,age,version) VALUES ("小宝","女",15,1)

1、从性能上区分：乐观锁、悲观锁

乐观锁：认为数据提交时不会发生冲突，所有我们只需要在数据提交或者更新时比较版本号，如果版本号发生冲突，由用户决定数据如何处理。 一般我们通过版本号机制和CAS算法实现乐观锁。

关于CAS算法我推荐一篇文章：

下面我们说说关于版本号实现乐观锁：

1.我们查询id为1 的数据：

# 获取数据的时候同时获取当前数据的版本号
SELECT * FROM sys_lock WHERE id = 1

执行结果 2.我们根据id修改id为1的数据，并判断版本号：

# 更新数据的时候携带版本号进行校验，同时更新版本号
update sys_lock set name='大大宝',version=version+1 where id=1 and version=1;

此时修改成功

3.此时的版本号已经变为了2，当我们以版本号为1再次修改时：

# 当你再次以版本号为1来修改时，就会失败，因为此时版本号已经变为2
update sys_lock set name='大大大宝',version=version+1 where id=1 and version=1;

此时修改失败

悲观锁：总是设想最坏的结果，每次读写数据时都会加锁，一旦加锁，不同线程同时执行的时，只能有一个线程执行，其他线程在入口等待，直至锁被释放。

悲观锁在Mysql和JAVA中被广泛应用：

• Mysql的读锁、写锁、行锁等
• Java的synchronized关键字

2、从数据库分类：读锁与写锁

共享锁(读锁、S锁)：在加锁之后，允许其他事物进行查询操作，但不允许自身和其他事物进行修改、添加操作，直到锁接触操作。 案例：为sys_lock添加读锁：

# 加锁
lock table sys_lock read;

# 解锁
unlock tables;

解除锁，会话B的等待会执行成功 排他锁(X锁，写锁)：在加锁后不允许进行任何操作(不允许读也不允许写)。 加锁：

# 加锁
lock table sys_lock write;

# 解锁
unlock tables;

总结：读锁会阻塞写，不会阻塞读，写锁会阻塞读和写。

3、从数据库的操作粒度：表锁和行锁

– 表锁(MylSAM引擎)

MylSAM引擎使用的表锁，开销小，加锁快，无死锁，锁定力度大，发生锁冲突的概率最高。

• 并发度低
• 不支持事物

给表加上表锁：

• 读当前表：可以读取。
• 读其他表：不能读取。
• 写当前表：不能写。
• 写其他表：不能写。

– 行锁(InnoDB引擎)

行锁的特定： 加锁慢、开销大，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

1. 为某一行加上共享锁；

select * from sys_lock where id = 1 lock in share mode;

2. 为某一行加上写锁；

select * from sys_lock where id = 1 for update ;

三、事物

事务由单独单元的一个或者多个sql语句组成，在这个单元中，每个mysql语句时相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条sql语句一旦执行失败或者产生错误，整个单元将会回滚，也就是所有受到影响的数据将会返回到事务开始以前的状态；如果单元中的所有sql语句均执行成功，则事务被顺利执行。

MySQL 默认采用 AutoCommit 模式，也就是每个 sql 都是一个事务，并不需要显式的执行事务。 如果 autoCommit 关闭，那么每个 sql 都默认开启一个事务，只有显式的执行“commit”后这个事务才会被提交。

1、事物的ACID属性

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

– 原子性（Atomicity）

把事物看出一个原子，在对这个原子进行修改操作的时候，你要么全部执行成功，要么全部不执行。看重的是操作。

– 一致性（Consistency）

在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。【关注的是数据最终状态】

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

– 隔离性（Isolation）

数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。【关注的是中间状态】

也就是说修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

– 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

2、并发事务带来的问题

并发事务是指两个或两个以上的事务对数据库某个资源同时进行操作带来的问题。

– 数据丢失

两个事物T1和T2同时对数据库的某一条数据进行修改操作时，T2覆盖了T1的数据，到账了T1的修改丢失。

– 脏读

一个事物T1在对数据库的某一条数据进行修改操作时，修改成功，但未提交事物，如果不加以控制，这时如果出现另一个事物T2来读取这条数据，T2则会读取T1修改后的数据，待T2读取完之后T1进行数据回滚，这时T2读取的数据就是脏数据。 A读取B还没提交事物的数据，A拿去做别的修改，而此时B事物回滚了。

– 不可重复读

A在一个时间段内多次读取一条数据，而B在这个时间段中的某一个时刻，对这条数据尽心删除操作，之后A再次读取这条数据是不可读取的，不满足数据的隔离性。

– 幻读

A事物在一端时间内，用同样的查询条件查询数据，此时B事物插入了一条数据，是满足A查询条件，A会查询出条数据，不满足事物的隔离性。

3、事物的隔离级别

隔离级别如图：

– 读未提交（Read uncommitted）

最低隔离级别，事务A可以读取到事务B修改未提交的数据。 话不多说，干代码测试一下： 第一步：设置事务的隔离级别为Read uncommitted：

 -- 设置事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

第二步：连接两个客户端，开启两个事务，事务A和事务B。

-- 开启事务
begin;

第三步；查询一条数据

SELECT * FROM sys_lock WHERE id = 1

第四步：事务B修改数据之后不提交，事务A查询；

-- 事务B 先对id为1 的数据进行修改不提交，事务A再查询
UPDATE sys_lock SET name = '李四' WHERE id = 1

这里事务B并没有提交，而事务A读取到了事务B没有提交数据的。 第五步：提交事务

– 提交事务 COMMIT;

– 读已提交（Read committed）

事务A再进行中不能读取到事务B进行修改操作未提交的数据，但事务A在进行中可以读取到事务B已提交的数据。

话不多说，干代码。

这里就不截图一 一验证了，直接贴出所有的代码，自己玩； 事务A的代码如下：

-- 设置事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务A开启事务
begin;


-- 事务A查询一条数据
SELECT * FROM sys_lock WHERE id = 1


-- 提交事务
COMMIT;

事务B的代码如下：

-- 事务B开启事务
begin;

-- 事务B查询一条数据
SELECT * FROM sys_lock WHERE id = 1


-- 事务B 先对id为1 的数据进行修改不提交，事务A再查询，此时查询不到数据
UPDATE sys_lock SET name = '李四' WHERE id = 1

-- 事务B提交之后事务A再查询，事务A可以查询到数据
COMMIT;

记得最后要提交事务，做人要有始有终。

– 可重复读（Repeatable read）

可重复读也是事务的默认隔离级别，事务A进行中不会读取到事务B未提交的事务，事务A只会一次查询获得快照结果，事务A在整个运行中都会使用第一次的快照结果。 设置事务的隔离级别的未可重复读：

 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

我太懒了，其他的代码自己想吧。

– 串行化、可序化（Serializable）

讲事务隔离级别设置成串行化，该级别会进行表锁，不会出现幻读，但该级别的并发性最低，级别不怎么用，连并发都没有了，还用个锤子。 设置事务隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

太懒，测试代码自己想。

– 间隙锁与幻读

Innodb在可重复读隔离级别下为了解决幻读问题时引入的锁机制。

事务A开启事务：

-- 事务A开启事务
begin;

事务A修改：

-- 事务A修改
UPDATE sys_lock SET name = '李四' WHERE id <=4

事务B添加id=4的： 此时，事务B出现阻塞。

-- 提交事务
COMMIT;

事务A提交，事务B添加成功。

四、优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能使用低级别事务隔离

五、MVCC机制

MVCC全称是： Multiversion concurrency control，多版本并发控制，提供并发访问数据库时，对事务内读取的到的内存做处理，用来避免写操作堵塞读操作的并发问题。提高并发读写的性能、不用锁就能让多个事务并发读写。

InnoDB对MVCC机制的实现是通过在每个表的每一行添加三个隐藏的字段:

一个6字节的DB_TRX_ID字段： 表示插入或更新该行的最后一个事务的事务ID。此外，删除在内部被视为更新，在该更新中，行中的特殊位将其标记为已删除。

一个7字节的 DB_ROLL_PTR字段：称为回滚指针。回滚指针指向写入回滚段的撤消日志记录。如果行已更新，则撤消日志记录将包含在更新行之前重建行内容所必需的信息。实际就是指向该记录的一个版本链.

一个6字节的DB_ROW_ID字段：包含一个行ID，该行ID随着插入新行而增加。如果有指定主键，那么该值就是主键。如果没有主键，那么就会使用定义的第一个唯一索引。如果没有唯一索引，那么就会默认生成一个值

也就是说，事务id相当于每一条数据产生不同的版本，不同的事务读取的是对应事务版本的数据，这就保证了一致性读取。【有点乐观锁的意思，但是MVCC是无锁操作】

六、死锁

1、产生死锁的情况：

（1） 互斥条件：一个资源每次只能被一个进程使用。

（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

（3） 不可剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。

（4） 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

2、处理死锁：

查询数据进程，出现大量waiting for …lock的就是死锁

SHOW FULL PROCESSLIST;

kill调死锁

kill 进程ID