MySQL--行锁

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MyIsAm的最大不同有两点，一是支持事务，二是采用了行级锁，
InnoDB的行锁是通过索引的索引项实现的，不是锁定在记录本身上的。案例如下

说明：如图中，在country_id上创建了索引，第一个控制台锁住了country_id=111的记录，第二个控制台尝试去锁住country_id=111 and country_name=“test2” 的记录，发现陷入了阻塞。
总结$行锁是通过锁住索引项来实现的，而不是记录本身。$

锁住索引位，在添加记录的场景中，如果创建了索引，添加了两个一样的记录，还没提交之前，另外一个也会阻塞，第一个提交后，第二个就会出现重复提示。

说明：在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 尽可能让所有数据检索通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围。
尽可能较少检索条件，避免间隙锁。
尽量控制事务大小，减少锁定资源量和时间长度。
尽可能低级别事务隔离。