1、事务及ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

  • 原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规 则都必须应用于事务的修改,以保持数据的完整性。
  • 隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独 立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

  • 持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

    2、并发带来的影响

  • 更新丢失(Lost Update)或脏写

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存 在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。

  • 脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这 时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的 处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
总结：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B 事务回滚，A读取的数据无效，不符合一致性要求。

  • 不可重读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改 变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
总结：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性 。

  • 幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数 据，这种现象就称为“幻读”。
总结：事务A读取到了事务B提交的新增数据，不符合隔离性 。

3、事务隔离级别

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制 来解决。
image.png
数据库的事务隔离越严格,并发副作用越小，但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度 上“串行化”进行，这显然与“并发”是矛盾的。
同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不 敏感,可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别: show variables like ‘transaction_isolation’;
设置事务隔离级别：set transaction_isolation=’REPEATABLE-READ’;
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔 离级别，如果Spring设置了就用已经设置的隔离级别 。

4、锁分类

锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资 源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发 访问性能的一个重要因素。

锁分类

  • 从性能上分为乐观锁(用版本对比来实现)和悲观锁 。
  • 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁 。

  • 从对数据操作的粒度分，分为表锁和行锁 。

表锁 :
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景。
基本操作：

  • 手动增加表锁 lock table 表名称 read(write),表名称2 read(write);
  • 查看表上加过的锁 show open tables;
  • 删除表锁 unlock tables;

行表：
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最 高。
InnoDB与MYISAM的最大不同有两点：

  • InnoDB支持事务（TRANSACTION）
  • InnoDB支持行级锁

行锁表锁总结：
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自 动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行 锁。
读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
无索引行锁会升级为表锁 ：
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁
session1 执行：update account set balance = 800 where name = ‘lilei’;
session2 对该表任一行操作都会阻塞住
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为 表锁。
锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，
例如：select * from test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定 行的session提交。

5、记录锁、间隙锁、临键锁

记录锁（Record Lock）：
记录锁锁住的是索引记录。如果使用索引作为条件命中了记录，那么就是记录锁，被锁住的记录不能被别的事务插入相同的索引键值，修改和删除。
我们在一张表中插入4条记录，主键分别是1、4、7、10。
我们用主键或者唯一索引作为条件等值查询的时候，命中记录就是加的记录锁，如：
select from xx where id = 1 for update;
命中记录，所以id = 1这条记录就加了记录锁。
间隙锁（Gap Lock）：
间隙锁是锁在索引之间或者第一个索引前面或者最后一个索引后面。
当我们使用索引无论是等值还是范围查询，没有命中一条记录时候，加的就是间隙锁。
还是拿上面的例子，我们在表中插入4条记录，主键分别是1、4、7、10。
image.png
图中的范围区间就会被锁住，都是左开右开的区间。
update account set name = ‘123’ where id >4 and id < 7;
没有命中任何一条记录，会锁住（4，7）区间，另一个事务插入id = 6则会阻塞；
间隙锁只在可重复读隔离中存在。
临键锁（Next-Key Lock）：
当我们使用索引进行范围查询，命中了记录的情况下，就是使用了临键锁，他相当于记录锁+间隙锁。
image.png
左开右闭区间，目的是为了解决幻读的问题。
select from xx where id > 5 and id < 9;
上面的sql命中了id = 7的记录，也包含了记录不存在的区间，所以他锁住（4，7]和（7，10]区间，在这区间，别的事务插入不了数据，所以解决了幻读问题。

6、锁相关分析SQL语句

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

show status like 'innodb_row_lock%'; 对各个状态量的说明如下： Innodb_row_lock_current_waits: 当前正在等待锁定的数量 Innodb_row_lock_time: 从系统启动到现在锁定总时间长度 Innodb_row_lock_time_avg: 每次等待所花平均时间 Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间 Innodb_row_lock_waits:系统启动后到现在总共等待的次数

查看是否有锁表：

‐‐ 查看事务 select from INFORMATION_SCHEMA.INNODB_TRX; ‐‐ 查看锁 select from INFORMATION_SCHEMA.INNODB_LOCKS; ‐‐ 查看锁等待 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; ‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 kill trx_mysql_thread_id 10 11 ‐‐ 查看锁等待详细信息 show engine innodb status\G;

锁优化建议 ：

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少检索条件范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

  • 尽可能低级别事务隔离

    7.行锁与事务隔离级别演示

    案例使用sql如下：

    1. CREATE TABLE `member` (
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. `name` varchar(255) DEFAULT NULL,
    4. `balance` int(11) DEFAULT NULL,
    5. PRIMARY KEY (`id`)
    6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    8. INSERT INTO member (`name`, `balance`) VALUES ('张三', '100');
    9. INSERT INTO member (`name`, `balance`) VALUES ('李四', '200');
    10. INSERT INTO member (`name`, `balance`) VALUES ('王五', '200');

    读未提交：

    1、打开窗口1，执行如下sql，数据初始状态如下：
    image.png
    2、打开窗口2，执行如下sql，注意，先不要提交或者回滚事务

    1. set transaction_isolation='read-uncommitted';
    2. BEGIN;
    3. update member set `name` = '张三1号' where id = 1;
    4. -- commit;
    5. -- ROLLBACK;

    3、回到窗口一，运行选择的查询语句，我们发现会话1已经查询到了会话2未提交事务的、未修改的数据。这就是读未提交，造成脏读。
    image.png
    怎么解决脏读问题呢？将隔离级别设置为读已提交。

    读已提交

    1、打开窗口一，执行下面的sql，可以看到数据初始状态如下：
    image.png
    2、打开窗口二，执行下面的sql，注意不要提交或者回滚事务。

    1. set transaction_isolation='read-committed';
    2. BEGIN;
    3. update member set `name` = '张三1号' where id = 1;
    4. -- commit;
    5. -- ROLLBACK;

    3、回到窗口一，执行选中的查询sql，再次查看数据，发现并没有读到窗口二操作后导致的脏数据，解决了脏读问题。
    image.png
    4、但是！注意，窗口一我们有begin，即开启了一个事务，事务并没有提交或者回滚。我们回到窗口二，提交窗口二的事务，然后我们再在窗口一查询数据，看看数据是什么状态。是保持自己原先事务里的数据状态？还是能读到事务二操作后的数据呢？
    在窗口二，运行commit。
    image.png
    回到窗口一，运行选中的查询语句，我们发现第一行的数据已经改变了！这就是不可重复读。
    image.png
    怎么解决不可重复读问题呢？将隔离级别设置为可重复读。

    可重复读

    Mysql默认的隔离级别就是可重复读

1、打开窗口一，执行以下sql，查看一下原始数据。
image.png
2、打开窗口二，执行下面的SQL，将id=1的数据更新，并提交事务。

  1. set transaction_isolation='repeatable-read';
  2. BEGIN;
  3. update member set balance = balance +1 where id = 1;
  4. commit;
  5. -- ROLLBACK;

3、返回窗口一，运行下选中的查询语句，我们发现，虽然我们在窗口二已经修改了数据，但是在窗口一再次查询，数据仍然和第一次查询保持一致，这就表明“可重复读隔离级别”解决了“不可重复读的问题”。
image.png
4、OK，那“可重复读隔离级别”有没有解决幻读问题呢？答案是：没有完全解决，它解决了读数据情况下的幻读问题，而对于修改的操作依旧存在幻读问题。
4.1 首先验证：“它解决了读数据情况下的幻读问题”：
先来看一下目前的数据状态是这样的，在窗口一查询一下：
image.png
在窗口二，我们新增一条数据，并提交事务。

  1. set transaction_isolation='repeatable-read';
  2. BEGIN;
  3. insert into member VALUES(4,'薛六',100);
  4. commit;
  5. -- ROLLBACK;

回到窗口一，查询，发现刚才新增的数据并没有展现出来，所以，“它解决了读数据情况下的幻读问题”。
image.png
4.2 再验证：“而对于修改的操作依旧存在幻读问题”：
在窗口一执行update member set balance = balance +1 where id = 4;
然后再执行查询语句select * from member;
我们发现，薛六出现了，所以“可重复读隔离级别，对于修改的操作依旧存在幻读问题”；
image.png

可串行化

这种隔离级别并发性极低，开发中很少会用到。

1、打开窗口一，执行以下sql，将隔离级别设置为“serializable”，查一下id=4的数据。
image.png
2、打开窗口二，执行以下sql，修改id=4的数据，我们发现，事务提交不了，这一条数据被锁住了，是被窗口一的查询语句给锁住的。
image.png
3、验证结果：经过上面两个步骤，我们发现， 串行模式下innodb的查询也会被加上行锁 ，既然查询都会上锁的话，那另一个事务肯定修改不了数据，那也就肯定不会出现幻读。