locak tables city read; 🔒
unlock table; (open)🔓
select * from city

InnoDB事务的ACID如何保证？

1.0一些概念

redo log:重做日志,记录数据页的变化，ib_logfile0,ib_logfile1 🌟🌟🌟🌟🌟
redo log buffer :redo log的缓冲区（内存区） 🌟🌟🌟🌟🌟
ibd：表空间的数据文件，以段区页 🌟🌟🌟🌟🌟
buffer pool:数据页缓冲区 🌟🌟🌟🌟🌟
LSN: Log seq num 日志序列号,redo log\logbuffer\ibd\buffer pool
WAL:write ahead log 日志优先写磁盘
脏页:dirty page 在内存中被修改的数据页
CKPT: checkpoint,将内存脏页回写到磁盘的动作
TXID: transaction_id,事务ID,伴随着事务的整个生命周期.
undo log: 回滚日志,ibdata1

1.1 redo功能

1.1.1.1 redo简介

重做日志,ib_logfile0,ib_logfile1

1.1.2

记录数据页的变化

1.1.3

主要 保证ACID中的"D"，持久化功能，对于AC也有相应的保证
加快了commit命令的速度，提高了事务的并发。
并且实现了在mysql crash时，ACSR中前滚的功能。

Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

Redo Log- 原理
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，
不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

1.2 undo功能

1.2.1 undo简介

回滚日志5.7,存储在ibdata1,8.0 undo单独存储。

1.2.2 undo记录的内容。

记录的是逆操作。

1.2.3 作用

主要保证了A的特性,CI也有相应的功能。

1.3 隔离级别和锁机制

1.3.1 功能

主要提供了ACID中的“I”的隔离性的功能，C的功能受到一定影响。

1.3.2 隔离级别介绍

RU:读未提交
脏读
不可重复读
幻读:开两个窗口A和B,A开启begin模式,A窗口新建一个表，然后插入一条语句，把其中一项的所有值修改掉，然后切换B窗口,B窗口执行插入一条在A窗口中执行外的语句，然后查看，发现新插入的数据已提交成功，再切换A窗口commit提交，查看数据，发现修改的只是之前A窗口设定的条件，而B窗口新添加的语句并未执行。

transaction_isolation

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like "%tx%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
| tx_read_only  | OFF             |
+---------------+-----------------+
2 rows in set (0.01 sec)

设置

vim /etc/my.cnf
tranaction_isolation=REPETABLE-READ
transaction_isolation=READ-COMMITTED

RC : 读已提交

transaction_isolation=READ-COMMITTED
A:  
mysql -uroot -p
use world 
1. oldguo[world]>begin;
2. oldguo[world]>update city set population=1000 where id=10;
4. oldguo[world]>commit;

B:
mysql -uroot -p
use world 
3. select * from city where id=10;
5. select * from city where id=10;

防止脏读.
会出现,不可重复读.
会出现,幻读.

A: 
create table t1 (id int ,name varchar(20));
insert into t1 values(1,'a'),(2,'b'),(4,'c'),(6,'d');
commit;
B:

(3) RR : 可重复读
防止脏读
防止不可重复读 (MVCC,一致性快照读)
可能会出现幻读 (GAP ,next-lock)
(4) SR : 可串行化

1.3.3 Innodb的锁（主要解决I）

record lock 记录锁 行级锁

AB两个窗口同时执行一个修改数据命令，A窗口不提交，B窗口执行语句后会造成锁等待现象。一直提交一直不释放。

2.InnoDB核心参数介绍

2.1 默认存储引擎

percona: XtraDB 默认
mariaDB:InnoDB 默认 ，my rocks tokudb

2.2 innodb_buffer_pool_size

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.01 sec)

设置

vim /etc/my.cnf
innodb_buffer_pool_size=2G

官方建议:最多95%
生产建议:不超过80%,50-70%

1.业务够用的
2.公司硬件有预留
3.MySQL还有可能额外使用到其他的内存结构
4.公司做的多实例

查看缓存
show engine innodb status\G;

2.3 redo innodb log_buffe_sizer

mysql> show variables like '%log_buffer%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.01 sec)

2.4 redo的参数 🌟 🌟 🌟 🌟 🌟

mysql> show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.00 sec)

2.5 redo log的刷写策略（双一） 🌟 🌟 🌟 🌟 🌟🌟

mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.01 sec)

默认:
1⃣️.在事务提交时，立即刷写redo buffer中的日志到OS buffer,立即FSYNC到磁盘。
0⃣️.每秒 刷写redo buffer中的日志到OS buffer,每秒FSYNC到磁盘。
2⃣️.在每个事务提交时,立即刷写redo buffer中日志到OS buffer，每秒FSYNC到磁盘。

---

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

---

2.6 Innodb_flush_method

查询方法

控制buffer pool,和redo buffer的刷盘策略。
mysql> select @@innodb_flush_method;
+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| O_DIRECT              |
+-----------------------+

默认:
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer

最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync