mysql架构01：三级架构，并发控制，锁粒度，表锁，行锁，事务，隔离级别，死锁，灾害恢复

参考文章；
https://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch01.html

三级架构

三级架构.png

第一级向外提供连接，身份认证，安全维护等；
第二级：是mysql server的核心，核心功能就在这；
第三级:存储引擎，数据真正存放的地方；

第二级和存储引擎，通过存储引擎提供的api 交互；

mysql支持多个存储引擎；

连接管理和安全认证

第一层：
每个connect都会在服务进程中开启一个线程，
connect的所有操作都是在这个线程内完成的，
并且这个线程会被缓存起来多次使用；
因此每次连接不需要销毁connect，
新的连接可以反复使用已经创建的这个connect.

认证管理：每次连接都要认证：
认证有 username:password和ssl两种方式，
并且服务会控制用户的执行权限；

第二层：解析，优化和执行

执行sql语句，缓存数据，优化sql等

并发控制

MySQL has to do this at two levels: the server level and the storage engine level.

mysql是如何处理并发读写的：
并发：要求的是多任务同时执行处理，同时执行；

共享锁：读锁：允许多个线程同时读取，前提是没有写数据；
独占锁；写锁，阻止所有读取操作，只能允许一个线程写数据；

锁粒度：

锁一行；
锁多行；
锁整张表；

提高共享资源并发性的一种方法是更有选择性地锁定什么。而不是锁定整个资源，只锁定包含您需要更改的数据的部分。更好的是，只锁定您计划更改的确切数据。最大限度地减少您一次锁定的数据量，只要它们不相互冲突，就可以同时更改给定资源。

问题是锁消耗资源。每个锁定操作 - 获取锁定，检查锁定是否空闲，释放锁定等等 - 都有开销。如果系统花费太多时间来管理锁而不是存储和检索数据，那么性能就会受到影响。

锁定策略是锁定开销和数据安全之间的折衷，并且该折衷会影响性能。大多数商业数据库服务器都没有给您太多选择：
您在表中获得了所谓的行级锁定，通过各种通常很复杂的方法来提供许多锁定的良好性能。

另一方面，MySQL确实提供了选择。它的存储引擎可以实现自己的锁定策略和锁定粒度。
锁管理是存储引擎设计中非常重要的决策;
将粒度固定在一定水平可以为某些用途提供更好的性能，但使该引擎不太适合其他目的。
由于MySQL提供多个存储引擎，因此不需要单一的通用解决方案。
让我们来看看两个最重要的锁定策略。

锁类型

表锁

MySQL中最基本的锁定策略，以及开销最低的策略是表锁。表锁类似于前面描述的邮箱锁：它锁定整个表。当客户端希望写入表（插入，删除，更新等）时，它会获取写锁定。这样可以防止所有其他读写操作。当没有人写数据时，读数据可以获得读锁，这与其他读锁不冲突。

表锁在特定情况下具有良好性能的变化。例如，READ LOCAL表锁允许某些类型的并发写操作。

写锁也具有比读锁更高的优先级，因此即使读取器已经在队列中，写锁的请求也将前进到锁队列的前面（写锁可以超过队列中的读锁，但读锁不能提前过去写锁）。

虽然存储引擎可以管理自己的锁，但MySQL本身也使用各种锁，这些锁实际上是用于各种目的的表级。例如ALTER TABLE，无论存储引擎如何，服务器都会对语句使用表级锁。

行锁

提供最大并发性（并且承载最大开销）的锁定样式是使用行锁。
此策略众所周知的行级锁定可在InnoDB和XtraDB存储引擎中使用。
行锁在存储引擎中实现，而不是在服务器中实现（如果需要，请参考逻辑体系结构图）。
服务器完全不知道存储引擎中实现的锁，正如您将在本章后面和整本书中看到的那样，存储引擎都以自己的方式实现锁定。

事务

事务是一组以原子方式处理的SQL queries，作为单个工作单元。
这么一组sql queries，要么全部成功，要么全部失败。

原子性
事务必须作为单个不可分割的工作单元运行，以便应用或回滚整个事务。当事务是原子的时，就没有部分完成的事务：它是全部或全部。

一致性

数据库应始终从一个一致状态移动到下一个状态。在我们的示例中，一致性确保第3行和第4行之间的崩溃不会导致$ 200从支票帐户中消失。由于事务从未提交，因此事务的所有更改都不会反映在数据库中。

隔离性
在事务完成之前，事务的结果通常对其他事务是不可见的。这样可以确保如果银行帐户摘要在第3行之后但在我们的示例中第4行之前运行，则它仍会在支票帐户中看到$ 200。当我们讨论隔离级别时，你会理解为什么我们说通常是不可见的。

持久性
一旦提交，事务的更改是永久性的。

隔离级别

SQL标准定义了四个隔离级别，其中包含特定的规则，在事务内外都可以看到更改。
较低的隔离级别通常允许更高的并发性并具有更低的开销。
注意
每个存储引擎实现的隔离级别略有不同，如果您习惯使用其他数据库产品，它们不一定符合您的预期（因此，我们不会在本节中详细介绍）。您应该阅读您决定使用的任何存储引擎的手册。

单独的增删改查等一条sql语句必须是原子操作，也就是或行在同一时刻只能被一个线程写入;每一行在写数据的时候，都是被锁起来的，同一时间每一行只允许一个线程执行写数据。

脏读：读取未提交的数据也称为脏读。

一个事务包含多条sql语句，以转账为例，

1   START TRANSACTION;
2   SELECT balance FROM checking WHERE customer_id = 10233276;
3   UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4   UPDATE savings  SET balance = balance + 200.00 WHERE customer_id = 10233276;
5   COMMIT;

2，3，4每条执行都是原子操作，也就是当前行被锁起来了，只允许一个线程写数据；

当执行了3，后续sql还没执行呢，事务还没提交呢，这个时候用户customer_id = 10233276的户头上就消失了200美金，如果4执行失败，那问题就大了。这就是脏读。

不可重复读：运行相同的语句两次但是查看的数据是不同的；

为什么？因为事务对外不可见，举例两个事务1，事务2
事务2在事务1开始前读了数据，
恰巧事务1修改的就是事务2要读取的数据，当事务1执行并提交，
事务2再次查询，这次查询到的是事务1提交后的数据，
针对事务2来说，两次查询，但是两次查询出来的数据不一样。

大多数数据库系统（但不是MySQL！）的默认隔离级别是READ COMMITTED。
它满足前面使用的简单隔离定义：事务只会看到那些在开始时已经提交的事务所做的更改，并且在其提交之前，其他更改将不会被其他人看到。

这个级别仍然允许所谓的不可重复的阅读。
这意味着您可以运行相同的语句两次并查看不同的数据。

幻读：保证同一事务中两次读取的数据相同，但是第二次的读取数据依然是第一次版本的数据，只是“看起来相同”而已；

因为还是不能阻止其他事务在这个多行数据范围内插入新数据和修改旧数据；

REPEATABLE READ 解决了READ UNCOMMITTED允许的问题。
它保证事务读取的任何行在同一事务中的后续读取中“看起来相同”，但理论上它仍然允许另一个棘手的问题：幻影读。
简单地说，当您选择某些行范围时，可能会发生幻像读取，另一个事务会在该范围中插入新行，然后再次选择相同的范围; 然后你会看到新的“幽灵”行。

InnoDB和XtraDB使用多版本并发控制来解决幻像读取问题，我们将在本章后面解释。

image.png

死锁：只要不是多行范围锁定，都是有死锁的可能。

一个死锁是指两个或多个事务相互持有并请求锁定相同资源，从而创建依赖关系循环。当事务尝试以不同的顺序锁定资源时会发生死锁。只要多个事务锁定相同的资源，它们就会发生。例如，考虑针对StockPrice表运行的这两个事务：

Transaction #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;

Transaction #2

START TRANSACTION;
UPDATE StockPrice SET high  = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high  = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果你运气不好，每个事务都会执行第一个查询并更新一行数据，并将其锁定在进程中。然后，每个事务将尝试更新其第二行，但却发现它已被锁定。这两个事务将永远等待彼此完成，除非有什么干预来打破僵局。

为了解决这个问题，数据库系统实现了各种形式的死锁检测和超时。更复杂的系统，例如InnoDB存储引擎，会注意到循环依赖并立即返回错误。这可能是一件好事 - 否则，死锁会表现为非常慢的查询。其他人将在查询超过锁定等待超时后放弃，这并不总是好的。方式InnoDB当前处理死锁是为了回滚具有最少独占行锁的事务（其中一个近似的度量标准将是最容易回滚的）。

锁定行为和顺序是特定于存储引擎的，因此某些存储引擎可能会在某些语句序列上死锁，即使其他语句也不会。死锁具有双重性质：由于真正的数据冲突，有些是不可避免的，有些是由存储引擎的工作原理引起的。

如果不部分或全部回滚其中一个交易，就不能破解死锁。它们是事务系统中的事实，您的应用程序应该设计为处理它们。许多应用程序可以从一开始就简单地重试它们的事务。

事务记录：就是我考虑的灾害情况下：断电，数据库的记录恢复

事务记录有助于提高事务处理效率。
每次发生更改时，存储引擎都可以更改其内存中的数据副本，而不是更新磁盘上的表。
这非常快。然后，存储引擎可以将更改记录写入事务日志，该日志位于磁盘上，因此是持久的。

这也是一个相对较快的操作，因为附加日志事件涉及磁盘的一个小区域中的顺序I / O，而不是许多地方的随机I / O.
然后，稍后，进程可以更新磁盘上的表。

因此，大多数使用这种技术的存储引擎（称为预写日志记录）最后将更改写入磁盘两次。

如果在将更新写入事务日志之后但在对数据本身进行更改之前发生崩溃，则存储引擎仍可在重新启动时恢复更改。恢复方法因存储引擎而异。

我的方法：
第一步：日志记录第一行：写下事务sql；
第二步：向磁盘写数据，持久化数据；

第三步：日志记录第二行：ack

当在第二步发生了灾害：断电，事务要写的数据没有完全执行，就不会有第三步ack，当恢复数据的时候，只有看到日志记录中这个事务有 ack记录，那么才能认定事务提交并写入成功，否则的话，不会恢复没有ack的事务；

多版本并发控制

InnoDB通过在每行中存储两个额外的隐藏值来实现MVCC，这些值记录了创建行以及何时过期（或删除）。该行不存储发生这些事件的实际时间，而是存储每个事件发生时的系统版本号。这是一个每次交易开始时递增的数字。每个事务在其开始时保留其自己的当前系统版本记录。每个查询都必须根据事务的版本检查每一行的版本号。让我们看看当事务隔离级别设置为时，这如何适用于特定操作REPEATABLE READ：

InnoDB必须检查每一行以确保它符合两个标准：

InnoDB必须找到至少与事务一样旧的行的版本（即，其版本必须小于或等于事务的版本）。这可确保在事务开始之前存在行，或者事务创建或更改行。
行的删除版本必须未定义或大于事务的版本。这可确保在事务开始之前未删除该行。

通过两个测试的行可以作为查询的结果返回。

InnoDB使用新行记录当前系统版本号。

InnoDB将当前系统版本号记录为行的删除ID。

InnoDB使用新行版本的系统版本号写入该行的新副本。它还将系统版本号写为旧行的删除版本。

所有这些额外记录保留的结果是大多数读取查询永远不会获得锁定。他们只是尽可能快地读取数据，确保只选择符合条件的行。缺点是存储引擎必须在每行存储更多数据，在检查行时执行更多工作，以及处理一些额外的内务操作。

MVCC只适用于REPEATABLE READ和READ COMMITTED 隔离级别。 READ UNCOMMITTED不是MVCC兼容的^{[ 7 ]，}因为查询不会读取适合其事务版本的行版本; 他们阅读最新版本，无论如何。SERIALIZABLE 不是MVCC兼容的，因为读取锁定它们返回的每一行。

java连接池和mysql线程

java客户端都是先创建固定数量的连接池，
而连接池就对应了mysql的线程，

举例，连接池中连接数量是8个连接，
那对应就是8个线程，
当连接池都被占用，加入连接池等待队列，
所以说，mysql根本就没啥事务队列， mysql就8个线程而已，
再借助版本并发控制（mvvc），
写锁是独占锁，写数据时，不允许其他线程读数据和写数据，

读锁是共享锁，

问题01：怎么保证java客户端先后发出的两次事务，最后数据库的数据是最后一次修改后的最新数据呢？

考虑网络延迟，用户最先发出的请求，因为网络延迟，第二个到达服务端；

服务端发送给数据库的事务请求，也因为网络延迟，第一个发出的事务请求，反而第二个到达；

比如现在a 地址余额不足，
用户向a地址转账100元，然后用户在a地址花掉100元，
但是因为网络延迟，导致用户先花掉100元在先，而转账100元到地址a在后；

没办法避免的，只能通过业务手段区分；

比如转账操作，都是有到账时间的，不能说我还没到账，你就可以花钱，
开发人员对未来可能的数据变化是不知道的，
只能盯着过去已经存在的数据，也就是从表中读取数据；
对未来的数据，不用管；

只看你当前余额，未到账的余额，不计入的，

比如转账，余额充足你就转账呗，

场景03:修改商品数据
常见04；修改用户信息
针对这种场景也是需要前台业务流程的引导的；

问题02:怎么保证并发修改同一条数据，最终结果就是最新提交的数据呢？
答案：mysql管不了你的业务逻辑，mysql只知道有几个线程，
现在这个线程要执行什么任务，是阻塞，还是读，还是写，

共享锁和独占锁
reentrantreadwritelock example