【MySQL性能优化】- MySQL事务级别与锁机制

MySQL事务级别与锁机制

😄生命不息，写作不止
🔥 继续踏上学习之路，学之分享笔记
👊 总有一天我也能像各位大佬一样
🏆 博客首页   @怒放吧德德  To记录领地
🌝分享学习心得，欢迎指正，大家一起学习成长！
转发请携带作者信息 @怒放吧德德 @一个有梦有戏的人

转发请携带作者信息 @怒放吧德德 @一个有梦有戏的人

尽量用单表查询，计算类型根据情况而定，数据量庞大情况下使用Java操作，尽量不要写复杂SQL。

简介

事务，在数据库中是一个不可分割的工作单位。在MySQL中，一个事务是由一组SQL语句组成的序列，这组SQL语句作为一个整体被执行，也就是说，要么整体执行成功，要么整体执行失败。我们在平时使用的数据库，都会并发的执行多个事务，而这些多事务就很有可能会并发对同一批数据进行CRUD操作，如果没对这些做好相应处理，就会导致脏写、脏读、不可重复读、幻读的奇奇怪怪的问题。

事务及ACID

事务处理能够保证数据的一致性、准确性。事务有4个特性，也就是我们常说的ACID。

• 原子性（Atomicity）：事务的操作是原子操作，简单说就是一个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间环节。如果事务在执行一半的时候失败了，就会进行回滚到事务开始的状态，就相当于没执行过。
• 一致性（Consistency）：在事务开始和完成时，数据必须处于一致状态。
• 隔离性（Isolation）：数据库系统提供了隔离机制，每个事务能够独立执行，不被其他事务所干扰。
• 持久性（Durability）：一旦事务完成，则其结果能够永久保存在数据库中。即使发生系统崩溃，结果也不应该受到影响。

并发事务带来的问题

在并发事务的情况下，将会带来一些问题，接下来看一下将带来的问题。

丢失修改（Lost Modify）或脏写

当两个或者多个事务对同一行记录进行修改的时候，然后基于原始选择同时更新这一行时，可能会覆盖彼此的更新。例如，A事务和B事务同时获取同行数据(money=10)，接着A事务扣掉5，B事务扣掉1，按道理最终结果要等于4，但是由于并发可能导致最后的结果不正确。

脏读（Dirty reads）

一个事务正在对一条记录进行修改，在这个事务完成并提交前，另一个事务也访问了这条记录。如果第二个事务读取了这个"脏"记录，那么可能产生问题，因为这个记录可能被回滚到开始的状态。例如，事务A读取库存信息，正在执行扣减库存，但是未提交；而事务B读到了事务A扣减的数据，然后进行修改数据，但是事务A回滚了，这就会导致数据错误了。

不可重复读（Non-repeatable reads）

在一个事务内，多次读取同一数据。在这个事务还没有结束时，另一个事务也访问了该数据。由于可能同时修改同一数据，使得第一个事务两次读取到的数据可能不致。也就是事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性。

幻读（Phantom reads）

两次读取范围内的记录，但是第二次读到了第一次没有读到的记录，这种现象称为"幻读”。也就是事务A读到了事务B提交的新增数据，不符合隔离性。

事务的隔离级别

对于“脏读”、“不可重复读”、“幻读”这些都是数据库读一致性的问题，需要通过数据库事务隔离级别机制来解决。
我们可以通过以下命令查看当前数据库的隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';

在没有修改过数据库的隔离级别，默认就是REPEATABLE-READ，可重复读。
我们可以通过set命令来设置MySQL的事务隔离级别，一般是不建议修改MySQL的默认事务级别。

SET transaction_isolation = 'repeatable-read'

在spring程序中，如果没有设置隔离级别，那就是使用MySQL的默认隔离级别，否则使用spring中设置的隔离级别。
我们通过一张表格来直观的观察隔离级别与其能解决的问题。

隔离级别	脏读 （Dirty Read）	不可重复读 （Non-repeatable Reads）	幻读 （Phantom Reads）
读未提交 (READ UNCOMMITTED)	未解决	未解决	未解决
读已提交 (READ COMMITTED)	解决	未解决	未解决
可重复读 (REPEATABLE READ)	解决	解决	未解决
串行化 (SERIALIZABLE)	解决	解决	解决

锁机制

锁是计算机来协调多个进程或线程并发访问某一资源的机制。
在数据库中，数据资源也是一种共享访问的资源，如何确保数据并发访问一致性、有效性是数据库需要解决的一大问题，锁冲突也是并发访问性能的一个重要因素。

锁分类

1)、从性能上又分为乐观锁（用版本号进行比对）和悲观锁
2)、从数据库操作类型来看有读锁和写锁（都属于悲观锁）

• 共享锁（Shared Lock，S锁）：也就是读锁，如果一个事务T对数据A加上S锁，则只允许其他事务对数据A加S锁，不允许加X锁，直到T释放A上的S锁。这样可以保证其他事务可以读A，但是不能修改A。
• 排他锁（Exclusive Lock，X锁）：也就是写锁，如果一个事务T对数据A加上X锁，则其他事务不能再对A加任何锁，直到T释放A上的X锁。这样可以保证其他事务既不能读也不能写A。

3)、从锁的细粒度又分为行级锁定和表级锁定
这是MySQL内部使用一种名为多版本并发控制（MVCC）的机制实现了高效的锁定策略。

• 行级锁定：这是最小化的锁定规模，它使数据库可以极高效地对多用户访问进行管理。开销大、加锁慢，会出现死锁，发生所冲突的概率最低，并发度最高。
• 表级锁定：对整个表进行加锁，开销比较小，加锁快，无死锁。但是并发性差，涉及表的所有查询都会被阻塞。

表锁

这是最大化的锁定规模，对整个表进行加锁，开销比较小，加锁快，无死锁。但是并发性差，涉及表的所有查询都会被阻塞。一般来说，表锁的使用是在整表的数据迁移的场景，当然，在使用FOR UPDATE也会导致表锁，从而可能会存在问题。

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `employee_id` int(0) NOT NULL,
  `first_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  `create_time` date NULL DEFAULT NULL,
  PRIMARY KEY (`employee_id`) USING BTREE,
  INDEX `idx_first_name_last_name_salary`(`first_name`, `last_name`, `salary`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `employees` VALUES (1, 'John', 'Doe', 73744.37, '2023-01-06');
INSERT INTO `employees` VALUES (2, 'Jane', 'Smith', 66799.74, '2023-02-22');
INSERT INTO `employees` VALUES (3, 'Bob', 'Johnson', 55495.71, '2023-11-21');
INSERT INTO `employees` VALUES (4, 'Alice', 'Williams', 53220.90, '2023-11-18');
INSERT INTO `employees` VALUES (5, 'Charlie', 'Brown', 61735.74, '2023-01-19');
INSERT INTO `employees` VALUES (6, 'Eva', 'Miller', 93990.88, '2023-04-27');

案例分析<