mysql锁详细分析及常见问题和建议_索引失效,行锁变表锁-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_39412825/article/details/122583135

按照操作来分：

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响；
当前session加了表锁，只能读自己锁定，不能读其他，不能修改自己及其他；
其他session可以读被读锁锁的表，也可以读其他可读的表，修改读锁的表会阻塞。直到读锁的表解锁才会执行；

写锁（排它锁）

当前写操作没有完成前，它会阻断其他写锁和读锁；
当前session 可以读可以写当前锁定的表，不能去读和修改其他表；
其他session 读取加写锁表会阻塞，直至写锁解锁；

总结
MyIsam 在执行查询语句前，会自动给设计的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
mysql的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
1、对MyIsam表读操作，不会阻塞其他进程对同一表的读请求。但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作；
2、对MyIsam表写操作，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读和写操作；

读锁会阻塞写，但是不堵塞读；写锁则会把读写都阻塞；

MyIsam 的读写锁调度是写优先，这也是其不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会是的查询很难得到锁，从而造成永远阻塞；

按照对数据操作的粒度分：

行锁（偏写）

偏向innoDB存储引擎，开销大，加锁慢，会出现思索，锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
InnoDB和MyISAM的最大不同有两点：一是支持事务（TRANSACTION）（ACID 原子性、一致性、隔离性、持久性）；二是采用了行级锁；
读几之所写，阻塞其余同一行的写操作，读不到其他线程的修改未提交操作；

总结
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyIsam的表级锁定。当系统并发量比较高的时候，InnoDB的整体性能和MyIsam相比就会有比较明显的优势；
但是使用不当（行锁变表锁）可能会适得其反；

建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量减小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度；
尽可能低级别事务隔离；

表锁（偏读）

偏向MyIsam存储引擎，开销小，加锁快；无死锁；锁定力度大，发生锁冲突的概率最高，并发度最低。

页锁

开销和加锁时间介于表锁和行锁之间，会出现死锁；锁粒度介于表锁和行锁之间，并发度一般；

事务相关

ACID 原子性、一致性、隔离性、持久性

常见问题：
更新丢失（lost update）
脏读（dirty reads）读取到已修改但尚未提交的数据；
不可重复读（non-repeatable-reads）事务a读到了事务b的修改提交数据
幻读（phantom reads）事务a读到了事务b的新增提交数据

事务的隔离级别
读未提交（Read uncommitted）最低级别，只能保证不读取物理上损坏的数据（脏读，不可重复读，幻读）
读已提交（Read committed）语句级（不可重复读，幻读）
可重复读（Repeatable read）事务级别（幻读） mysql 默认级别
可序列化（Serializable）最高级，事务级（没有问题）

常见问题

1、索引失效行锁变表锁
索引失效时，会用不了行锁，变成表锁；阻塞其他线程对当前表的读写操作；

2、间隙锁的危害
当我们用范围条件而不是相等条件检索数据，并请求共享或者排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙；其他线程对不存在的记录进行插入的时候会进行阻塞；
InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-key）
query执行过程中通过范围查找的话，他会锁定整个范围内的所有索引键值，即使这个键值不存在；
间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使当某些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下会对性能造成较大的危害。

如何锁定一行

begin;
 select * from tableName where id=1 for update;
commit;
select....for update锁定某一行后，它的操作会被阻塞，直到锁定行的会话提交commit；

常见命令

show open tables

查看当前表的加锁状态；

lock table tableName1 read，tableName2 write

给tableName1加读锁，给tableName2加写锁；

unlock tables

释放表锁；

show status like “table%”

可以通过检查table_locks_waited和table_locks_immediate 状态变量来分析系统上的表锁定；
table_locks_immediate ：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加一；
table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较重的表级锁争用情况；

show variables like “tx_isolation”;

查看当前事务级别；

set autocommit=0

关闭自动的事务提交；

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：系统启动后到现在总共等待的次数；
参数不理想时，使用showprofile进行分析；

主从复制

基本原理
1、master将改变记录到二进制日志 binary log。这些记录过程叫二进制日志事件，binary log events；
2、slaves将master的binary log events拷贝到他的中继日志（relay log）；
3、slave重做中继日志中的事件，将改变应用到自己的数据库中。
mysql复制是异步的且串行化的；