MySQL基础-梁波的博客|Java|SpringBoot|Golang|Kubernetes|微服务|云原生

并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。这里主要讨论MySQL在两个层面的并发控制：服务器层和存储引擎层。

读写锁

这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也可以叫读锁（read lock）和写锁（write lock）。读锁事共享的，或者说相互不阻塞。写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。

锁粒度

所谓的锁策略，就是在锁的开销和数据安全性之间寻求平衡，这种平衡当然也会影响到性能。

MySQL 提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。下面介绍两种最重要的锁策略

1）表锁（table lock）

表锁事 MySQL 中最基本的锁策略，并且是开销最小的策略。它会锁定整张表，一个用户在对表进行写操作前，需要先获取写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁。读锁之前是不想糊阻塞的。

尽管存储引擎可以管理自己的锁，MySQL 本身会是会使用各种有效的表锁来实现不同的目的。例如：服务器会为诸如 ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制。

2）行级锁（row lock）

行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。众所周知，在 InnoDB 和 XtraDB，以及其他一些存储引擎中实现了行级锁。

事务

事务就是一组原子性的SQL操作，或者说一个独立的工作单元。

下面介绍事务的ACID概念：

原子性（Atomicity )

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事物来说，不可能只执行其中一部分操作，这就是事务的原子性。
一致性（Consistency）

数据库总是从一个一致性状态转换到另外一个一致性状态。比如用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，不可能A扣了钱，B却没收到，这就是事务的一致性。
隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性（Durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

隔离级别

隔离性其实比想象的要复杂。在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更好的并发，系统的开销也更低。

下面简单地介绍一下四种隔离级别：

READ UNCOMMITTED （未提交读）

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务中也可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，在实际应用中一般很少使用。
READ COMMITTED（提交读）

大多数数据库系统默认隔离级别都是READ COMMITTED（但MySQL不是）。它满足了前面提到的隔离性的简单定义：一个事务开始时，只能"看到"已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务时不可见的。这个级别有时候也可叫做不可重复读（noorepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）

REPEATABLE READ 解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果都是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读问题。

可重复读是 MySQL 的默认事务隔离级别。
SERIALIZABLE（可串行化）

SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用也很少用到这个隔离级别。

注意：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED	YES	YES	YES	NO
READ COMMITTED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	否	YES

死锁

死锁是指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同事锁定同一个资源时，也会产生死锁。

例如，设想下面两个事务同时处理 StockPrice 表：

事务1:
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

事务2:
START TRANSACTION;
UPDATE StockPrice SET close = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET close = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果恰巧，两个事务都执行到了第一条 UPDATE 语句，更新了一行数据，同时也锁定了该行数据，接着尝试执行第二条时，却发现该行已经被对方锁定，陷入死锁。除非有外部因素介入才可能解除死锁。

为了解决上述问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，比如 InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回错误。还有一种解决方式，就是当查询的时候打到锁等待超时的设定后放弃请求，这种方式通常来说不太好。 InnoDB 目前处理死锁的方式是，将持有最少行级排他锁的事务进行回滚。

MySQL 中的事务

自动提交（AUTOCOMMIT）

MySQL 默认采用自动提交模式。也就是说，如果不是显示地开始一个事务，则每个查询都被当作一个事务执行提交操作。在当前连接中，可通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交模式。

mysql> SET AUTOCOMMIT = 1;

1 或者 ON 表示启用， 0 或者 OFF表示禁用。当 AUTOCOMMIT= 0 时，所有的查询都是在一个事务中，直到显式地执行 COMMIT 提交或者 ROLLBACK 回滚，该事务结束。

MySQL 可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在事务中混合使用存储引擎

MySQL 服务器不管理事务，事务是由下层的存储引擎实现的。所以同一个事务中，使用多种存储引擎是不可靠的。

如果在事务中，混合使用了事务型和非事务型的表（例如 InnoDB 和 MyISAM 表），在正常提交的情况下没什么问题，如果事务需要回滚，非事务型的表上的变更就无法撤销。这会导致数据库出于不一致的状态。

隐式和显式锁定

InnoDB 支持特定的语句进行显式锁定，这些语句不属于 SQL 规范：

SELECT ... LOCL IN SHARE MODE
SELECT ... FOR UPDATE

「真诚赞赏，手留余香」

MySQL基础

标签: MySQL