MySQL 锁机制

1. 锁机制

1.1 介绍

相当于厕所的门

1.2 作用：

保证事务之间的隔离性。也保证了数据的一致性。
保证资源不会争用。锁是属于资源的，不是某个事务的特性。
每次事务需要资源的时候，需要申请持有资源的锁。

1.3 锁类型

资源 :
内存锁：mutex， latch，保证内存数据页资源不被争用，不被置换。
对象锁：
MDL(元数据锁) ：修改元数据时。DDL --》 alter，备份
Table_lock ：表锁，DDL，备份(FTWRL全局表锁)，
mysql> lock tables t1 read;
mysql> unlock tables;
record(row) lock ；行锁，索引锁，锁定聚簇索引。
GAP ：间隙 锁，RR级别，普通辅助索引间隙锁。
Next-lock ：下一键锁，GAP+record lock，普通辅助索引的范围锁


对象锁粒度：
MDL(元数据锁) ：修改元数据时。DDL --》 alter，备份
Table_lock ：表锁，DDL，备份(FTWRL全局表锁)，
mysql> lock tables t1 read;
mysql> unlock tables;
record(row) lock ；行锁，索引锁，锁定聚簇索引。
GAP ：间隙 锁，RR级别，普通辅助索引间隙锁。
Next-lock ：下一键锁，GAP+record lock，普通辅助索引的范围锁

功能分类：
IS ：意向共享锁 ，表级别
S ：共享锁，读锁 ，行级别
IX ：意向排他锁 ，表级别
X ：排他锁，写锁 ，行级别

create table x(id int unsigned not null auto_increment primary key,num varchar(20));
insert into x(num) values
(1),(3),(5),(7),
(11),(13),(15),(17),
(21),(23),(25),(27),
(31),(33),(35),(37),
(41),(43),(45),(47),
(51),(53),(55),(57),
(111),(113),(115),(117),
(211),(213),(215),(217),
(311),(313),(315),(317),
(411),(413),(415),(417);

mysql> select * from sys.innodb_lock_waits\G

2. 事物的一致性ACID的c特性

A：原子性，undo，redo
D：持久性, redo（WAL）
I：隔离性，ISOLATION Level, Lock ,MVCC(UNDO)
C：保证工作前，中，后，数据的状态都是完整的，一致的。
所以C的特性是以上所以特性都是来保证一致性的
写一致性：undo，redo，ISO，lock
读一致性：ISOLATION Level, MVCC(UNDO)
数据页的一致性：
double write buffer（磁盘区域 ）

3 InnoDB存储引擎核心特性-参数补充
双一标准之一：redo log

1. innodb_flush_log_at_trx_commit=1/0/2
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. Innodb_flush_method=fsync/O_DIRECT/O_SYNC
作用：控制MySQL刷写磁盘时，是否使用OS Cache

fsync 模式：
buffer pool的数据写磁盘的时候，需要先经历os cache，然后在写到磁盘
redo buffer的数据写磁盘的时候，需要先经历os cache，然后在写到磁盘

O_SYNC 模式：
buffer pool的数据写磁盘的时候，需要先经历os cache，然后在写到磁盘
redo buffer的数据写磁盘的时候，穿过os cache直接写到磁盘

O_DIRECT 模式：
buffer pool的数据写磁盘的时候，直接写到磁盘，跨过OS cache
redo buffer的数据写磁盘的时候，穿过os cache直接写到磁盘

生产中建议使用O_DIRECT，最后是配合固态盘使用

3. innodb_buffer_pool_size
作用：数据缓冲区的总大小。缓冲数据页和索引页。是MySQL最大的内存区域。
默认：128M
官方建议：80-90%
生产建议：75以下，酌情设置，安需调配
SET GLOBAL innodb_buffer_pool_size=402653184;
show engine innodb status\G

死锁

show engine innodb status \G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks=1