MySQL - 无索引行锁升级为表锁

---

示例

mysql> desc country;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| countryname | varchar(255) | YES  |     | NULL    |                |
| countrycode | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set

mysql> show index from country;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| country |          0 | PRIMARY  |            1 | id          | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

这个表，只有主键索引，其他字段上未建立二级索引 。

**现在使用没有建立索引的字段进行操作，观察其结果 **

session1	session2
begin 模拟开启事务
update country set countryname = ‘CCC’ where countrycode = ‘xxx’ ; --更新成功，但未提交事务
	begin 模拟开启事务
	update country set countryname = ‘DDD’ where countrycode = ‘anotherline’ ; ---- 一直被阻塞 ，直到超时 1205 - Lock wait timeout exceeded; try restarting transaction

我们知道锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁 , 从上面的测试中也可以验证这个观点，第二个

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁

---

SELECT … LOCK IN SHARE MODE & SELECT … FOR UPDATE