sqlmysql

MySQL调优笔记（二)：锁

事务

ACID：

• 原子性(Atomicity)：整个事务操作要么全部成功，要么全部失败。
• 一致性(Consistency)： 在事务开始和完成时，数据必须保持一致状态，
• 隔离性(Isolation)：一个事务所做的修改在提交之前，对其他事务是不可见的。也就是说事务互不影响。
• 持久性(Durability)：一旦事务提交，则其所做的修改就会永久保存到数据库中。

隔离级别

MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。

• 未提交读（READUNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。
• 提交读（READCOMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。
• 可重复读（REPEATABLEREAD）。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象（稍后解释）。
• 串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。解决了幻读，但不建议使用。

四个级别逐渐增强，每个级别解决一个并发带来的问题。

• 脏读，最容易理解。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。
• 不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。
• 幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。
• 更新丢失：版本覆盖问题，最后的更新覆盖了其他事务的更新

锁

按操作类型分，

• 读锁
  • 共享锁，针对同一份数据，多个读操作可以同时进行、互不影响
  • 其他事务的写操作会被阻塞（即，读阻塞写）
• 写锁
  • 排它锁，当前写操作没有完成时，会阻塞其他写锁和读锁

按操作粒度分

• 表锁
  • 偏向MyISAM引擎
  • 开销小，加锁快，无死锁
  • 锁定粒度较大
  • 发生锁冲突概率最大，并发度最低
• 行锁
  • 偏向InnoDB引擎
  • 开销大，加锁慢，可能出现死锁
  • 锁定粒度最小
  • 发生锁冲突概率最低，并发度最高
• 页锁
  • 介于表锁与行锁之间

查看表锁: show open tables

上锁： locak 表名 read/write

解锁： unlock tables

索引失效及间隙锁

类型转换导致的索引失效，会使得InnoDB的行写锁便成表写锁，导致并发操作大大变慢。因此，生产中要注意，varchar字段的where子句，update子句一定要加引号

间隙锁危害：

间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。

当我们使用范围条件而不是相等条件检索数据，并请求共享或者排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围之内，但不存在的记录，叫做间隙（Gap）。InnoDB同样会对这个间隙加锁，这种机制成为间隙锁。根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。其作用就是防止其他事务的插入操作，以此防止幻读的发生。

Query查询语句通过范围条件查找数据，MySql会锁定整个范围的所有的索引键值，及时这个键值并不存在，此时，将无法插入锁定键值内的任何数据。

Innodb自动使用间隙锁的条件：

1. 必须在Repeatable Read级别下
2. 检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

比如，一张表的id列有值 (1,3,5,6,7,8,9),使用范围条件 id < '6' 查询数据；如果事务没有结束，此时插入id为2的数据，这个插入操作将被阻塞。在大量并发的情况下，很容易造成性能急速下降。

要禁止间隙锁，可以启用参数innodb_locks_unsafe_for_binlog

如何锁定一行

有时候编写存储过程或者函数时，需要在操作过程中锁定查询的记录，这样能够保证在存储过程执行的过程中，数据不会被其他会话或者事务打乱。此时，可以在查询语句之后加for update手动为查询出来的行加锁。比如，select id, name from user where id='8' for update;

监控锁命令

show processlist;

SHOW PROCESSLIST显示哪些线程正在运行。如果有线程在update或者insert 某个表，此时进程的status为updating 或者 sending data。非常详细。大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

show open tables;

这条命令能够查看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁。可以用来查看表锁。一般InnoDB排查用不到。

show status like '%lock%';

所相关的MySql状态，其中有两条状态需要特别关注：

• Innodb_row_lock_current_waits：当前正在等待锁的次数
• Innodb_row_lock_watis：Mysql启动至今总共等待的次数
• Innodb_row_lock_time：Mysql启动至今总共等待的时长

如果上述参数出现异常值，需要通过show profile命令排查性能问题。

锁优化

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

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2019/02/05 22:05