MySQL-锁机制详述( 三 )

1.3.4 案例准备工作create table test_innodb_lock(id int(11),name varchar(16),sex varchar(1))engine = innodb default charset=utf8;?insert into test_innodb_lock values(1,'100','1');insert into test_innodb_lock values(3,'3','1');insert into test_innodb_lock values(4,'400','0');insert into test_innodb_lock values(5,'500','1');insert into test_innodb_lock values(6,'600','0');insert into test_innodb_lock values(7,'700','0');insert into test_innodb_lock values(8,'800','1');insert into test_innodb_lock values(9,'900','1');insert into test_innodb_lock values(1,'200','0');?create index idx_test_innodb_lock_id on test_innodb_lock(id);create index idx_test_innodb_lock_name on test_innodb_lock(name);1.3.5 行锁基本演示

MySQL-锁机制详述

文章插图
 
1.3.6 无索引行锁升级为表锁如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样 。
查看当前表的索引 : show index from test_innodb_lock ;
MySQL-锁机制详述

文章插图
 

MySQL-锁机制详述

文章插图
 
由于 执行更新时 , name字段本来为varchar类型, 我们是作为数组类型使用,存在类型转换,索引失效,最终行锁变为表锁 ;
1.3.7 间隙锁危害当我们用范围条件,而不是使用相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据进行加锁; 对于键值在条件范围内但并不存在的记录,叫做 "间隙(GAP)" , InnoDB也会对这个 "间隙" 加锁,这种锁机制就是所谓的 间隙锁(Next-Key锁)。
示例 :
MySQL-锁机制详述

文章插图
 
1.3.8 InnoDB 行锁争用情况showstatus like 'innodb_row_lock%';
MySQL-锁机制详述

文章插图
 
Innodb_row_lock_current_waits: 当前正在等待锁定的数量?Innodb_row_lock_time: 从系统启动到现在锁定总时间长度?Innodb_row_lock_time_avg:每次等待所花平均时长?Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间?Innodb_row_lock_waits: 系统启动后到现在总共等待的次数?当等待的次数很高,而且每次等待的时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手制定优化计划 。1.3.9 总结InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力方面要远远由于MyISAM的表锁的 。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势 。
但是,InnoDB的行级锁同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差 。
优化建议:
  • 尽可能让所有数据检索都能通过索引来完成,避免无索引行锁升级为表锁 。
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能减少索引条件,及索引范围,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离(但是需要业务层面满足需求)

【MySQL-锁机制详述】


推荐阅读