***** ## 数据库锁 - 表锁 - 行锁 - 间隙锁 锁是计算机协调多个进程或线程并发访问某一资源的机制 ### 表锁(偏读) 偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低 #### 表锁案例 ~~~ create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e'); ~~~ 手动增加表锁 ~~~ lock table 表名字 read(write),表名字2 read(write); ~~~ 释放表锁 ~~~ unlock tables; ~~~ ### 表锁总结 MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁 * 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进行的写操作 * 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作 ### 行锁(偏写) 偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁。锁定粒度最小,发生锁冲突的概率最低,并发度也最高 <br>InnoDB与MyISAM的最大不同点,支持事务,采用了行级锁 #### 行锁案例 ~~~ create table test_innodb_lock(a int(11),b varchar(16))engine=innodb; insert into test_innodb_lock values(1,'b2'); insert into test_innodb_lock values(3,'3'); insert into test_innodb_lock values(4,'4000'); insert into test_innodb_lock values(5,'5000'); create index idx_test_innodb_a on test_innodb_lock(a); create index idx_test_innodb_b on test_innodb_lock(b); ~~~ #### 如何分析行锁定 ~~~ 通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况 show status like 'innodb_row_lock%'; ~~~ 各个状态量的说明 ~~~ Innodb_row_lock_current_waits 当前正在等待锁定的数量 * Innodb_row_lock_time 从系统启动到现在锁定的总时间长度 * Innodb_row_lock_time_avg 每次等待所花费平均时间 Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花费的时间 * Innodb_row_lock_waits 系统启动后到现在总共等待的次数 ~~~ ### 什么是间隙锁 当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,innodb会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做"间隙" <br>innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁 ### 间隙锁的危害 因为SQL执行过程中通过范围查找的话,他会锁定整个范围内所有的索引值,即使这个键值并不存在 <br>间隙锁有一个比较致命的弱点,就是当锁定以为范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害 ### 如何锁定一行 ~~~ select * from test_innodb_lock where a = 8 for update; ~~~