mysql锁机制以及优化

锁分类

从性能上划分

乐观锁

适合读多的场景

悲观锁

适合写多的场景

从操作粒度划分

表锁

一般用作数据迁移、开销小加锁快

手动加表锁

lock table 表名称 read(write), 表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

页锁

只有BDB的存储引擎才支持页锁、开销介于表锁和行锁之间

行数

针对某一行数据加锁、开销大加锁慢

行锁真正锁的是索引（索引对应的索引项上打上加锁的标记）、如果更新条件中的字段没有索引则会将锁升级为表锁（该情况只适用于RR级别、RC级别不会升级为表锁）。

从对数据库操作的类型划分

读锁（S锁（Shared））

多个读操作可以同时进行而不会互相影响、会阻塞写操作；

select * from t where id = 1 lock in share mode

写锁（X锁（eXclusive））

写锁也叫排它锁、会阻塞读和写。

select * from t where id = 1 for update

意向锁（I锁）

当一个表中存在行锁时、会对该表加上标记、当有其他事务要对该表加锁时会检测该标记是否存在、如果存在则不能再施加表锁、从而避免了对该表的每行数据的索引进行扫描检测。

意向共享锁

锁定的行加的是读锁

意向排他锁

锁定的行加的是写锁

间隙锁

锁的是两个值之间的空隙、间隙是在可重复读隔离级别下才会生效。

只要在间隙范围内锁了一条不存在的记录、则会锁住整个间隙范围但不锁边界记录、这样就能放置其他session在这个间隙范围内插入数据、就解决了可重复读隔离级别的幻读问题。

临建锁

Next-Key Locks是行锁与间隙锁的组合（包含边界）、间隙锁也是针对索引的、如果字段没有索引同样会升级为表锁。

锁等待分析

show status like 'innodb_row_lock%'

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数 对于这5个状态变量，

比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

查看INFORMATION_SCHEMA系统库锁相关数据表

‐‐ 查看事务 select * from INFORMATION_SCHEMA.INNODB_TRX;

‐‐ 查看锁，8.0之后需要换成这张表data_locks select * from INFORMATION_SCHEMA.INNODB_LOCKS;

‐‐ 查看锁等待，8.0之后需要换成这张表data_lock_waits select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 kill trx_mysql_thread_id

‐‐ 查看锁等待详细信息 show engine innodb status;