一个不走索引的更新语句，到底会不会锁全表

经常看到有人说，如果一个DML不能走索引，就要锁全表。这正确吗？
我曾经也这样以为，直到现在我才发现，对一半错一半吧。
答案应该是：
例如如下一个表：

mysql> show create table sam\G
*************************** 1. row ***************************
       Table: sam
Create Table: CREATE TABLE `sam` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
+----+------+------+
3 rows in set (0.00 sec)

当执行update sam set col1=4 where col1=3
1.如果隔离级别是RR，那么确实会锁全表，是覆盖全表的next-key锁；
2.如果隔离级别是RC，那么只会锁住col1=3这一行，是行锁。

那么我们来验证一下吧！

一、RR隔离级别下
会话1执行update sam set col1=4 where col1=3

mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sam set col1=4 where col1=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话2执行任何加锁操作，都被堵塞

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from sam where col1=1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from sam where id=1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from sam where id=1 lock in share mode;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

所以，会话1是施加了全表的X锁

二、RC隔离级别下
会话1执行update sam set col1=4 where col1=3

mysql> show variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sam set col1=4 where col1=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话2执行delete from sam where col1=1，被堵塞

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from sam where col1=1;

这时候是不是觉得很奇怪？不是说RC下加的是行锁吗？耍猴呢？
别急，继续看
会话2继续执行delete from sam where id=1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from sam where col1=1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from sam where id=1;
Query OK, 1 row affected (0.00 sec)

这时就执行成功了，如果会话1加的是表锁，就不可能成功的。

三、什么原理？
RC下执行update sam set col1=4 where col1=3，加锁过程其实是这样的：
1.确实需要全表扫描sam表，在扫描过程中每一行都加上X锁；
2.当读到了col1=3的行，就执行更新；
3.当整个sam表都读完了，col1=3的行也就全部更新完了，这时就会把col1<>3的行锁释放；
4.所以，delete from sam where id=1才会执行成功，因为id=1可以直接定位到那一行数据，而这一行数据已经没有了会话1加的X锁了。

那么问题来了，为什么会话2执行delete from sam where col1=1不行呢，不也是id=1那一行吗？
这里由于where条件是col1=1，需要走全表扫描才能确定哪些行col1=1，而不像id=1那样，可以唯一确定是哪一行。
在全表扫描寻找col1=1的过程中，同样是需要给每一行都上X锁，所以id=1,id=2这两行，会话2其实是加X锁成功了，但当读到id=3时，由于这一行被会话1上了X锁且未提交事务，所以会话2就没办法上锁了，就只能等待。
所以会话2等待的是col1=3上的行锁，而不是表锁。