mysql的锁

mysql的锁

Mysql三种级别的锁：表级锁、行级锁、页级锁

表级锁适合只读更新很少的情况，行级锁适合更改较多，并发较多的情况

表级锁加锁开销小加锁快，粒度大，行级锁开销大加锁慢，粒度小，页级锁开销和粒度都在两种锁之间

锁与隔离等级

避免脏写（隐式锁）：如果某个事务要修改某条记录，先检查有没有记录有没有独占锁，有的话就必须等锁释放，没有的话就检查记录的事务ID是不是当前的活跃事务ID，如果是就加独占锁，其余事务想修改记录必须等该事务提交释放锁。（修改记录的时候要加独占锁，直至该事务提交再把锁消除）

Serializable的实现：其实mysql在read committed级别就已经实现了避免幻读，避免幻读用的是一种称为gap locks的锁，它也有独占和共享之分，对一条记录加了这种锁之后，记录和之前记录的间隙处不能插入新纪录，这样在同一事务的范围查询就不会出现不同的结果了。

几种常见的锁

表锁和行锁又分为独占锁x和共享锁s，有一把共享锁之后还可以与其他事务共持共享锁，但不能获得独占锁，而有一把独占锁之后其他事务不能再获得独占锁或共享锁

Intention shared/exclusive lock，每次给行加锁的时候就会给表加一个这种锁，当表获取锁时检查表拥有的ix或is锁决定是否加锁成功，因为当表中记录有独占锁时表不能获得独占锁，有了这种锁之后就可以确定到底是否满足加表锁的条件。

Auto-inc锁：自增字段会有这种锁，这种锁会在赋值时加锁，执行结束后释放，来保证获得的值连续。

Next-key锁：x和s锁与gap locks锁的合体（这种锁会带来一些负面影响，一次范围查询会降低并发访问能力，所以要注意sql优化）

Insert intention locks插入意向锁：想插入记录却被阻塞时，此时在内存中生成一把锁来排队

Innodb锁

在更新时会自动加x锁，读时不会加任何锁。

行锁是针对索引加的锁而不是对记录加的锁，访问不同行记录但用到相同索引也会发生冲突。要注意sql优化，因为一旦命中索引失败会放弃使用索引，这样就会加表锁，降低并发能力。

如果锁竞争严重就需要进行锁的优化：

1、合理使用索引，避免全表查询从而加表级锁

2、谨慎使用基于范围的过滤条件，避免nextkey锁锁定不该锁的内容

3、在业务满足要求的前提下，尽可能使用低隔离级别，提升性能

4、控制事务的大小，减少锁占用时间

Innodb表级锁：如果更新表的操作多，事务涉及多个表容易造成死锁，可以用，但是这样的读操作太多时推荐用myISAM。

MyISAM锁

只支持表级锁，而且不支持事务，锁是针对会话的，会话结束锁才会释放。MyISAM下进行查询和更新都会自动加s锁和x锁，使用myISAM时有两个变量记载加锁次数和锁等待的次数，由此可以推导锁的竞争情况，如果竞争情况较严重就要进行锁的优化，优化从这几个角度进行：

1、拆分操作，缩短锁定时间

2、利用myISAM的并发插入特性ConcurrentInsert，修改参数concurrent_insert，2代表有无空洞都可以在表尾并发插入、1代表无空洞可以在表尾并发插入，0代表关闭并发插入

3、合理利用读写优先级。两个进程同时申请读锁和写锁，是优先会拿到写锁的，而且在等待队列中写锁会插队进入读操作前，这可能会导致关键的读操作进入饥饿状态，可以通过修改参数来调整优先级。

死锁及其避免方法

MyISAM是不会产生死锁的，因为myISAM总是一次性获得事务需要的全部锁，执行完毕再释放，所以不会产生死锁。Innodb有检测死锁的机制，如果产生死锁会分析占用锁的事务，然后回滚其中一个较小的事务来解除死锁。

几种可能出现死锁的情况：

1、多个事务交替访问资源

一个事务访问了表A，然后同时另一个事务访问了表B，然后第一个事务企图访问表B，第二个事务企图访问表A，因为互相锁住导致死锁

2、修改同一记录

一个事务查询了一条记录，并给记录加了s锁，然后改事务要修改这条记录，另一个事务同时也要修改这条记录，此时由于有事务b排队，事务a不能加x锁，而由于a加了s锁事务b也不能加x锁，进入死锁状态

3、变成全表查询

4、并发插入引起的死锁问题

避免死锁的方法：

1、如果同时并发处理多个表，要按照相同的顺序处理

2、同一个事务尽可能一次性申请所有的锁

3、非常容易产生死锁的部分可以增大锁的粒度

4、避免多事务交互

封锁及封锁协议、活锁和数据库乐观锁

封锁：事务对某个数据对象加锁的操作，在锁释放之前，其他事务不能操作数据

一级封锁协议：事务在写数据时必须要先加x锁，事务结束之后再释放锁，这样可以阻止脏写。

二级封锁协议：事务在读数据前必须加s锁，读完之后释放锁，这样可以阻止脏读（mysql不是用这种方式来阻止脏写的）

三级封锁协议：事务在读数据前必须加s锁，事务结束之后释放锁，这样在一次事务中不可能读到两次不一样的结果（mysql中也不是这样处理的）

可串行化：当某组并发事务交叉调度产生的结果和这些事务的某一串行调度结果相同时，称这个交叉调度为可串行化的

两段锁协议：如果事务满足两段锁协议，那么这些事务交叉调度的结果一定是可串行化的，协议的内容是在对任何数据操作之前都要获得它的锁，在释放了一把锁之后不再获得其他锁（其实就是一口气把所有锁拿到，执行完再一口气把所有锁释放掉）

活锁：两个事务可以拿到数据却互相谦让，最后谁都没有拿到

数据库的乐观锁：不需要数据库底层的介入，用版本号机制或者时间戳就可以解决

myISAM和Innodb的区别

MyISAM是强调性能的引擎，适合读密集的表，每次查询具有原子性，不会产生死锁，执行速度更快，不支持事务，只支持表级锁，支持压缩特性可以尽量降低数据的存储，表锁的粒度大，并发性能不好

Innodb支持事务、外键，表锁的粒度小且支持行锁，并发性能更好

转载于:https://www.cnblogs.com/shizhuoping/p/11562976.html