一. 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可以确定最后一个死锁产生的原因