Mysql独占与共亨_3 MySQL中的锁与调优

MySQL锁与调优

① 锁的分类

MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看，行级锁又可以分为独占锁(排它锁)和共享锁。

参考链接：https://www.cnblogs.com/coder-wf/p/13386443.html

② 行锁和表锁

表锁和行锁锁的粒度不一样，表锁锁住的是一整张表，行锁锁住的是表中的一行数据,行锁是开销最大的锁策略，表锁是开销最小的锁策略。

InnoDB使用的是行级锁，MyISAM使用的是表级锁。

注意：在InnoDB中，例如模糊查询select * from tb where name like 'lin%'的时候也会锁住一整张表。

参考链接：https://www.cnblogs.com/Chenjiabing/p/12610822.html

③ 共享锁和排它锁(独占锁)

共享锁

共享锁，也叫S锁(Shared Lock)；

共享锁顾名思义，那就是其锁定的资源可以被其它用户读取，但其它用户不能修改(其他用户可以读，但不能修改)；

如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select ... lock in share mode；

排他锁(独占锁)

独占锁(排他锁)，也称X锁(Exclusive Lock)；

独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受 ；

执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL会自动使用独占锁；

但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放 ；

在select命令中使用独占锁的SQL语句为：select … for update;

这里需要注意：

一个事务在一行数据上加入了独占锁，那么其余事务不可以在该数据行上加入任何锁，

也就是说加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，

但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

参考链接：https://www.cnblogs.com/shamgod-lct/p/9318032.html

④  悲观锁和乐观锁

从程序员的角度看，数据库中的锁又可以分为悲观锁和乐观锁。

悲观锁：利用数据库的锁机制实现，在整个数据处理过程中都加入了锁，以保持排他性。

乐观锁：乐观锁可以利用CAS实现，在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否要执行本次操作。

面试题：乐观锁的ABA问题有了解吗？如何解决？

这个CAS的ABA问题在学习多线程知识的时候提到多，这里再复习一下。

ABA问题是指在当前事务读取该行数据时是A，经过别的事务修改成B，但是在当前事务要更新数据的时候，该行数据又被别的事务修改为A，事实上数据行是发生过改变的，存在并发问题。

ABA问题可以通过基于数据版本(Version)记录机制来解决。也就是为数据增加一个版本标识。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。根据当前事务的数据版本号和数据库中数据的版本号对比来决定是否更新数据。 (也就是要比较“数据+版本”两个方面)

⑤  死锁

在多线程中，多线程为了争夺资源可能会造成死锁，也就是一种环路等待的现象。MySQL中的死锁主要是多个事务使用行级锁对某行数据加锁造成的，上一小节说了MyISAM不支持行级锁，所以MySQL中的死锁主要是在说InnoDB存储引擎的死锁。

死锁解决办法 - 后续再补充

⑥ MySQL优化或者排查经验

一般情况下，我们遇到一个SQL异常的时候，比如说执行时间超时等，可以通过explain查看当前SQL语句的执行情况。explain +SQL语句可以查看当前的SQL语句使用的索引以及其扫描了多少行数据。也可以使用下边的语句来查看数据表的一些信息：

show create table TableXX；查看当前表TableXX的建表语句

show index from TableXX；查看当前表TableXX上的索引

explain +SQL语句用法：https://www.cnblogs.com/deverz/p/11066043.html