mysql写数据流程和锁

基本文件概念

更好的额理解写的流程，需要了解以下日志概念

• undolog
  逻辑日志，用于事务的回滚+mvcc，和事务的atomicity 相关。
  innodb_file_per_table参数，存储在一个文件中。记录的是与原sql相反的sql。
• redolog
  物理日志，记录数据库变化的文件，用于系统crash后的恢复数据，可以配置多个文件
  配置这个参数innodb_log_files_in_group，比如 ib_logfile0、 ib_logfile1，可以生成多个文件
  和事务的consistency相关，redolog为两阶段提交
• binlog
  事务提交之后，记录到归档日志中。用于数据的恢复和同步数据。
  记录的sql语句，是server 层的写入。
• relaylog
  从master获取到slave的中转日志文件，sql_thread则会应用relay log并重放于从机器。
• slowlog
  慢日志，查询语句时间记录情况
• querylog
  查询日志记录

更新数据逻辑分析

---

更新数据流程

• 分配事务id,获取行锁，数据缓冲池中跟新数据页，没有则通过聚簇索引，查数据行的数据页在缓冲池中更新
• 生成undolog ,redlog到内存buffer，redo log 为两阶段提交，prepare完成
• 将undolog redolog 通过os 异步写入磁盘
• server 层生成binglog 并fsyn 到磁盘
• 事务commited，redolog 状态提交,释放锁

相关参数

---

show variables like ‘%innodb_flush_log_at_trx_commit%’;

---

buffer pool
innodb_buffer_pool_size
该参数控制innodb缓存大小，用于缓存应用访问的数据，推荐配置为系统可用内存的80%。
redolog参数
innodb_flush_log_at_trx_commit 默认1
值为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.
值为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.
值为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set, 1 warning (0.01 sec)

binlog 参数
sync_binlog=N 默认值是0，MYSQL在每写N次二进制文件时，会将文件同步刷到磁盘
binlog_cache_size 控制二进制日志缓冲大小，当事务还没有提交时，事务日志存放于cache，当遇到大事务cache不够用的时，mysql会把uncommitted的部分写入临时文件。

锁

悲观锁
在数据更新前加锁，既有强烈的独占性和排他性，适用多写少读的场景。
实现—java synchronized，数据库中的行锁，表锁等，读锁，写锁
乐观锁
乐观锁时进行数据提交更新的时候，才对数据的冲突与否进行检测，适用写多读少的场景。
实现—CAS和多版本控制
ABA问题-可以适用版本+多版本进行规避
记录锁
锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。所以在进行查询的时候尽量采用索引进行查询，这样可以降低锁的冲突
gap锁
在索引记录间歇上的锁,锁定一个区间
共享锁
共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁
排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取(当前读)和修改。
next-key锁
是记录锁和在此索引记录之前的gap上的锁的结合，锁定行记录+区间。
意向共享锁
依次表示接下来的一个事务将会获得共享锁还是排他锁
意向排他锁
实现并发控制的手段大致可以分为乐观锁和悲观锁控制并发

# 建表语句
mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `addr` varchar(40) NOT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_name_addr` (`id`,`name`,`addr`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
# 查看隔离级别为重复读
select @@transaction_isolation;
REPEATABLE-READ 
#设置自动提交关闭
set autocommit='off';
#写入数据id=5
insert into user values (5,'name5','chengdu','2020-10-27');
update user set name='nametem' where id=1;
# 锁行索引
select * from user where id=1 for update;
#主键不明确锁表
select * from user where id<>3 for update; # 走索引
select * from user where id like '%3%' for update; # 不做索引
# 锁表索引
select * from user where name='tian' for update;