Mysql锁

一. MyISAM表锁

1.查看表级锁争用情况

show status like 'table%';

如果table_locks_waited的值比较高，说明锁争用情况严重

2. 表锁的锁模式

2.1 如果加读锁，不会阻止另外一个会话读，但是会阻塞另外一个写

2.2 如果加写锁，另外一个会话的读和写都会阻塞。

3. 如何加表锁

lock tables orders read local, order_detail read local;

select sum(total) from orders;

select sum(subtotal) from order_detail;

unlock tables;

要给所有要操作的表，都加锁，不然操作会报错

4. MyISAM 的锁调度

读写锁互斥，如果同时进来读请求和写请求，mysql会优先写请求。即使mysql有读请求在排队，一个写请求也会优先处理。这样就会导致有些读请求永远在排队，可以通过设置max_write_lock_count来调节

二. InnoDB锁问题

2.1 查看锁争用情况

show status like 'innodb_row_lock%';

如果innodb_row_lock_waits和innodb_row_lock_time_avg的值比较高，就是锁争用比较严重。有2种方式查看：

select * from innodb_locks;

select * from innodb_lock_waits;

第二种：innodb monitor来观察锁

2.2 行锁模式

两种类型的行锁：

共享锁：允许一个事务读取一行，阻止其他事务写

排它锁：允许获得该锁的事务更新数据，阻止其他事务获得共享锁和排他锁。其他事务还是可以读这一行的。

为了防止表在修改数据的时候结构发生变化，还有2种锁是加在表上面的：

意向共享锁：在加共享锁的时候，先要给表加意向共享锁。

意向排它锁：在加排它锁的时候，先要给表加意向排它锁。

对于update,insert,delete语句，InnoDB会自动给要操作的数据加排它锁；对于普通的select语句不会加任何锁。

2.3 行锁实现方式

2.3.1

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，mysql通过影藏的聚簇索引来对记录加锁。行锁分3种场景：

1. record lock 对索引项加锁

2. Gap lock: 对索引之间的间隙，第一条记录前的间隙或最后1条的间隙加锁。

3. Next-key lock: 前两种的组合，对记录及其前面的间隙加锁。

如果，不通过索引检索数据，那么InnoDB将对索引的记录加锁，效果如同锁表，切记。

需要注意如下几点：

1. 因为是通过索引加的锁，即使访问的是不同记录，如果用的是同一个索引项，那么也会相互阻塞。比如

表里面有2条记录：id为1和name为1，id为1和name为4的记录，表只有在id列上有索引。sql语句如下：

select  * from table where id=1 and name=1 for update;

select * from table where id=1 and name=4 for update;

这2个sql语句也会相互阻塞。

2. 即使条件中的字段上面有索引，如果mysql判断不走索引，那么也会导致全部扫描，比如sql语句里面有隐式的类型转换。比如：

表phone列是字符串，而sql语句为：select * from table where phone=13438943432; 这样就会导致全部扫描

2.3.2 next-key锁

update时候，使用范围条件检索数据，innerDB会给符合条件的记录加锁，还会对条件范围内不存在的记录（间隙）加锁。这就叫做next-key锁。

比如，emp表只有101条记录，其中empid值分别为1,2,3,4...101。

如下sql语句： select * from emp where empid>100 for update;

InnoDB不仅会对符合条件的empid为101的记录加锁，也会对empid大于101的间隙加锁。

目的2个：

1. 防止幻想读（如果事务隔离基本是不可幻想读的 话）

2. 满足恢复和复制的需要（后续介绍）

2.3.3 恢复和复制的需要

mysql通过binlog记录执行成功的insert,update,delete等改变数据的操作，并基于此，实现mysql的恢复和主从复制。mysql支持3种日志格式：

基于语句的日志格式SBL

基于行的日志格式RBL

混合模式（前2种的混合）

如果是基于语句的日志格式，日志是按照事务的提交先后顺序记录的。比如：会话1要删除id大于100的记录，会话2 要插入id等于300的记录。如果会话1先执行，执行完了，还没提交（加排他锁，只给要删除的记录加）。会话2这个时候进来执行并且提交了。那么这个时候，表里面还会有1条id大于100的记录（会话2插入的）。如果从库根据binlog来恢复日志，会话2先提交，先执行会话2，后执行会话1。这个时候，表里面就没有id大于100的记录了，导致主从数据不同步。所以会话1在删除记录的时候，应该加间隙锁。

其他类似insert into target_tab select * from source_tab where ....   create table new_tab ....  select * from source_tab where .... 也会给source_tab 加锁，如果select的条件是范围，还会给源表加next-key锁。因为是这样的，插入目标表的时候，如果来一个会话更新了插入的数据，会导致插入目标表的数据，跟源表的数据不一致。这2种sql严禁在生产环境使用。

2.3.4 InnodB在不同隔离级别下一致性读及锁的差异性

可以看到delete，update 范围的时候都是next-key，无论何种隔离级别。

2.3.5 如何避免死锁

1. 都以约定的顺序访问表

2. 事务更新记录，不要先申请共享锁，更新的时候再申请排它锁。因为你申请排他锁的时候，别人可能也申请了共享锁。

3. 如果出现死锁，通过命令show innodb status可以确定最后一个死锁产生的原因