浅析MYSQL的事务和锁_mysql 事务锁

1. MySQL的事务和锁

1.1 事务的介绍：

概念：事务指逻辑上的一组操作，组成这个操作的单元，要么全部成功执行，要么全部执行失败

个人理解：某个业务执行更新语句，整个业务下得sql语句（单指更新）全部成功执行，或者执行全部失败

常见的mysql执行命令：

事务的特性（acid）:

事务假如不存在隔离性（也就是多个事务可以同时操作数据库）会导致以下问题

事务的隔离级别(理解成教室立在上课，外面有学生在等待，等待的学生有多种选择，直接进入教室，或者等到下课再进教室--事务A在操作数据库的表，事务B根据隔离级别能否操作该张表)

案例解析：

mysql（预热小案例）

在mysql数据库中事务是默认开启的

代码理解：

在t_user表里面有三行数据，我们直接删除id=3的数据发现直接会执行成功，并且再次查询数据永久改变（原因事务自动提交，可以理解成一条sql语句为一个事务）。

事务小案例：

开始事务后意味着把自动提交事务给关闭了

start transaction;

删除t_user表中id=2的数据

delete from t_user where id=2;

删除后有两种方式：commit和rollback，commit会真正的删除数据中的数据，rollback会撤销删除id=2的sql语句（开启事务之后commit之间的内容是一段逻辑，意味着更新语句在这中间要么全成功要么全失败）。

提示：事务操作实现的方式有很多，我以JDBC配置和spring配置为例(事务一般配置在service层的实现类里)JDBC 伪代码演示：

方法体（参数） {
    // 开启事务
    Connection.setAutoCommit(false);
    // 准备多条sql语句
    string sql1 = "";
    string sql2 = "";
    
    
    // 在try-catch中编写事务
    try {
        // 操作数据库操作和业务
    } catch (Exection e) {
        Connection.rollback;
    } finally {
        Connection.commit;
    }
}

事务的隔离级别(用隔离级别去演示脏读，不可重复读，虚读)：

1. read uncommit(读未提交)，级别最低效率最高，会发生脏读，不可重复读，虚读

通过show variables like '%commit';查看数据库隔离级别，mysql默认是REPEATABLE-READ

oracle的隔离级别默认是read commited

首先理解一下脏读：

概念：一个事务读取了另一个未提交的事务

举例：小明给小红转了100元，但是没有提交事务，让小红去看账户余额，此时多了100，但是小明心眼坏坏（回滚事务），小红的余额又变的跟原来一样。

演示脏读过程：

开启两个cmd mysql的控制台程序，把两个控制台程序的隔离等级都设置为read uncommit

补充设置隔离等级的命令：set session transaction isolation level 隔离等级;

小明的操作：（做完这些操作之后通知小红，已经转账完了）

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update t_account set money=money-1000 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update t_account set money=money+1000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

小红的操作（小红的钱由10000变成了11000）：小红对小明说钱我收到了

mysql> select * from t_account where id=2;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 小红   | 11000 |
+----+--------+-------+
1 row in set (0.00 sec)

小明接着回滚事务：

mysql> rollback
    -> ;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from t_account where id=1;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 10000 |
+----+--------+-------+
1 row in set (0.00 sec)

小红再次查看数据库时：

mysql> select * from t_account where id=2;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 小红   | 10000 |
+----+--------+-------+
1 row in set (0.00 sec)

脏读：数据还没提交，别人就可以查到数据，解决方法把隔离等级设置成Read committed。

脏读：解决方案

小明的操作：

mysql> set session transaction isolation level Read committed;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_account where id=1;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 10000 |
+----+--------+-------+
1 row in set (0.00 sec)

小红的操作：

mysql> set session transaction isolation level Read committed;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_account where id=2;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 小红   | 10000 |
+----+--------+-------+
1 row in set (0.00 sec)

小明给小红转账1000元：然后通知小红查收。

mysql> update t_account set money=money-1000 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update t_account set money=money+1000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from t_account where id=1;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   |  9000 |
+----+--------+-------+
1 row in set (0.00 sec)

小红：小红说没收到

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_account where id=2;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 小红   | 10000 |
+----+--------+-------+
1 row in set (0.00 sec)

小明：

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

小红：

mysql> select * from t_account where id=2;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 小红   | 11000 |
+----+--------+-------+
1 row in set (0.00 sec)

解决不可重复读问题：

不可重复读：先后两次读取数据不一致，第二次读取到第一次已经提交的数据（指update）

案例小明查询账户一万元，小红向小明转5000，小明的第一次查是5000，第二次查是15000；

下面的方案是直接解决不可重复读问题

小红：

set session transaction isolation level Repeatable read;
start transaction;

小明：

set session transaction isolation level Repeatable read;
start transaction;

小红：

mysql> update t_account set money=money-5000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> update t_account set money=money+5000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

小明：

mysql> select * from t_account where id=1;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 10000 |
+----+--------+-------+
1 row in set (0.01 sec)

小红：

commit

小明：（小红提交了事务，小明还是没有显示15000）

mysql> select * from t_account where id=1;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 10000 |
+----+--------+-------+
1 row in set (0.01 sec)

小明：提交事务：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_account where id=1;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 15000 |
+----+--------+-------+
1 row in set (0.00 sec)

原因：小红提交了事务，但是小明还没提交事务，所以显示不出来，只有小明也提交事务，结果发生了改变（两次查询结果一致）。而在读为提交时，转账的一方提交了事务，收款的一方就能查询出来（两次查询结果不一致）。

虚读问题演示：

还是Repeatable read的隔离等级

两边开始事务，

小红：

mysql> select * from t_account;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 15000 |
|  2 | 小红   |  5000 |
+----+--------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into t_account values ('3','小亮','10000');
Query OK, 1 row affected (0.01 sec)

小明查询：两条数据

mysql> select * from t_account;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 15000 |
|  2 | 小红   |  5000 |
+----+--------+-------+
2 rows in set (0.00 sec)

小红提交，然后小明再查发现还是两条数据，但是这时，小明没选择提交而是把所有的工资全部改掉，但是结果会发现有三条数据发生改变，但是数据库查询出来的结果只有两条（幻读可以理解成我数据库里只有两条数据但是结果有三条数据发生改变，好像出现了幻觉，多了一条更改的数据）

mysql> select * from t_account;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 小明   | 15000 |
|  2 | 小红   |  5000 |
+----+--------+-------+
2 rows in set (0.00 sec)
 
mysql> update t_account set money=2000;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

最后一种就是序列化了，但是相当于锁表，效率太低（应用场景比较少）。

结论实际开发中，不会选择serializable 和 read uncommitted

丢失更新：

多个事务对同一条数据进行操作，后提交的事务将先提交的事务操作覆盖了。

解决丢失更新的两种方式（针对某一行或者表，或者数据库加锁）：悲观锁和乐观锁

悲观锁：假设丢失更新一定会发生

原理：使用数据库内部锁机制，进行数据库表的锁定,就是在A管理员修改数据时，A管理员将数据锁定，此时B管理员无法进行修改，查询。避免两个事务同时修改，也就解决了丢失更新问题

1.共享锁(s锁->读锁),首先开启事务:start transaction; select * from 表名 lock in share mode(读取数据加锁),此时该表就被加上了读锁,只允许加锁的事务修改(哪个事务执行了该语句，哪个事务就可以进行删除，修改操作)但是,如果两个事务都执行该语句,则会因为连个窗口都在互相等待对方释放锁从而发生死锁问题,强调一下,读锁是非常容易发生死锁问题。

2.排它锁(x锁->写锁),首先开启事务:start transaction;一张表只能加一个排它锁,排它锁和其他共享锁都具有互斥效果。通俗一点就是说,一张表如果想加排它锁,在它之前就不能加别的共享锁和排它锁。当一张表字一个事务中加上了写锁后，别的事务将不能够修改该表数据,因为修改数据会自动加上读锁,进而产生互斥。select * from 表名 for update(在修改数据时加锁)------注意：update语句默认添加排它锁(对同一条数据操作时)

乐观锁：假设更新丢失不会发生-----采用程序中添加版本字段解决丢失更新问题

原理：让事务并进行并发修改，不对事务进行锁定，由程序员自己解决，可以通过给数据表添加自增的version字段或者时间戳timestamp,进行数据修改时,数据库会检测version字段或者时间戳是否和原来一致，若不一致抛出异常或者重新查询

CREATE TABLE t_product(
id INT,
NAME VARCHAR(20),
updatetime TIMESTAMP,
version INT    
);
insert into t_product values(1,'冰箱',null,0);
update t_product set name='洗衣机',version=version+1 where id = 1 and version=0;

不用开启事务,A,B管理员分别查询数据并修改数据,一个修改成功,一个则修改失败,每次修改过记录后,版本字段都会更新,如果读取的是版本字段,与修改时版本字段不一致,都说明别人进行修改过数据(重改)时间戳机制,和上面的version版本类似,也是在更新前取到时间戳进行对比,如果一致则ok，否者则版本冲突。

锁：

 1. 对MySQL的锁的了解

当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序，锁机制。就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以进入。

2. 隔离级别与锁的关系

在Read Uncommitted级别下,读取数据不需要加共享锁,就这样就不会跟被修改的数据上的排他锁冲突,在Read Committed级别下,读操作需要加共享锁,但是在语句执行完以后释放共享锁。在Repeatable Read级别下,读操作需要加共享锁,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁。SERIALIZABLE 是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成

3. 按照锁的粒度分数据库锁有哪些？锁机制与InnoDB锁算法

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

3.1 MyISAM和InnoDB存储引擎使用的锁

MyISAM采用表级锁(table-level locking)。

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

3.2 行级锁,表级锁和页级锁对比

行级锁：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

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

表级锁：MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

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

页级锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

4. 从锁的类别上分Mysql都有哪些锁呢？

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁: 又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

5. MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 ID 是有索引键的列，如果 ID不是索引键那么InnoDB将完成表锁，并发将无从谈起

6. InnoDB存储引擎的锁的算法有三种

1.Record lock：单个行记录上的锁

2.Gap lock：间隙锁，锁定一个范围，不包括记录本身

3.Next-key lock：record+gap 锁定一个范围，包含记录本身

7. 相关知识点

Innodb对于行的查询使用next-key lock

Next-locking keying为了解决Phantom Problem幻读问题

当查询的索引含有唯一属性时，将next-key lock降级为record key

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）

A. 将事务隔离级别设置为RC

B. 将参数innodb_locks_unsafe_for_binlog设置为1

8. 什么事死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

8.1 常见解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

9. 数据库的乐观锁和悲观锁是什么? 怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。

实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过Version的方式来进行锁定。

实现方式：一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行Retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。