偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高。InnoDB与MyIsAm的最大不同有两点,一是支持事务,二是采用了行级锁,
InnoDB的行锁是通过索引的索引项实现的,不是锁定在记录本身上的。案例如下
说明:如图中,在country_id上创建了索引,第一个控制台锁住了country_id=111的记录,第二个控制台尝试去锁住country_id=111 and country_name=“test2” 的记录,发现陷入了阻塞。
总结
行
锁
是
通
过
锁
住
索
引
项
来
实
现
的
,
而
不
是
记
录
本
身
。
\color{#FF0000}{行锁是通过锁住索引项来实现的,而不是记录本身。}
行锁是通过锁住索引项来实现的,而不是记录本身。
锁住索引位,在添加记录的场景中,如果创建了索引,添加了两个一样的记录,还没提交之前,另外一个也会阻塞,第一个提交后,第二个就会出现重复提示。
说明:在country_id上创建了唯一索引或者主键,添加重复记录,第一个添加后,会锁住索引位,导致第二个阻塞住。但是创建的是普通索引就不会出现这些问题。
案例
表结构
CREATE TABLE `test_innodb_lock` (
`a` int(11) NOT NULL,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `test_innodb_a_index` (`a`),
KEY `test_innodb_lock_b_index` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
特别说明,创建了两个单值索引。
将mysql的自动提交关闭,默认是;号结束就关闭。
set autocommit=0;
如图中,对一个行记录修改,但是不提交,另外的会话是无法修改的,会陷入阻塞状态,这是因为修改的是同一行记录,而不同的记录则不会出现这种问题,只有第一个控制台执行了commit第二个控制台才会执行。
案例–索引失效导致行锁变表锁
CREATE TABLE `test_innodb_lock` (
`a` int(11) NOT NULL,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `test_innodb_a_index` (`a`),
KEY `test_innodb_lock_b_index` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
b列为varchar类型。
执行如下sql
第一个sql通过b列去更新第一行记录,但是where条件判断时,字符串没有添加单引号,导致索引失效,执行后,第二个控制台去更新另外的记录,发现锁了表,无法更新,第一个控制台执行了commit后第二个控制台由阻塞变为执行状态。
案例锁–创建索引,根据索引更新
CREATE TABLE `test_lock` (
`a` int(11) NOT NULL,
`b` varchar(255) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `index_a` (`a`) USING BTREE,
KEY `index_b` (`b`) USING BTREE,
KEY `index_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在原有的基础上添加c列,并且创建索引
在一个控制台更新c=1记录,在另外一个控制台创建另外一个记录,不会阻塞
但是去掉c上面的索引
根据c列去更新发现第二个控制台执行阻塞了。
案例锁–间隙锁
如图中,不存在a值为3的记录,执行update语句,然后在第二个控制台执行插入a为3的记录,发现陷入了阻塞,第一个控制台执行commit,第二个控制台由阻塞变为执行状态。可知,update时锁住了范围内的数据,即使不存在该数据。
间隙锁:当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,Innodb会给符合条件的已有数据记录的索引项加锁,对应键值在条件范围内但并不存在的记录,叫做间隙锁。Innodb也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。
因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值 ,即使这个键值不存在。
间隙锁有个比较致命的危害,就是当锁定一个范围键值后,即使某些不存在的键值也会被无辜锁定,导致无法插入锁定键值范围内的任何数据,在某些场景下则可能会对性能造成很大的危害。
案例–如何锁定一行记录
如图,第一个控制台,执行带有for update语句,锁住a为3的记录,第二个控制台无法执行更新,必须要等待第一个控制台执行commit,第二个控制台才会自动更新。
分析行锁定
检查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:系统启动后到现在总共等待的次数。
优化建议
1 尽可能让所有数据检索通过索引来完成,避免无索引行锁升级为表锁。
合理设计索引,尽量缩小锁的范围。
尽可能较少检索条件,避免间隙锁。
尽量控制事务大小,减少锁定资源量和时间长度。
尽可能低级别事务隔离。