mysql写事务锁表锁行问题

先说结论：mysql更新时，如果where后面是索引字段，更新时不会锁表，只会锁行，如果不是索引字段，会锁表

以下是测试内容，开两个窗口

窗口1

查看事务提交方式
use test;
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
如果是1，设置为0，关闭自动提交事物
set autocommit = 0;
查看索引：
mysql> show keys from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
显然除了主键，没有别的索引；
更新张三年龄，使用name查，不提交事物

update user set age = 30 where name = "张三";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

窗口2

开另一个窗口，更新别人，看看是否会锁表
update user set age = 100 where name = "Jone";
··· // 这里是被卡住了，因为之前那个事务没有提交，说明被锁表了
左边commit后，右边会立马完成

使用索引查询并更新

窗口1

mysql> update user set age = 20 where id = '1432966622298832897';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

窗口2

mysql> update user set age = 300 where id = '1432967887443492865';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

右边窗口立马完成更新，说明没有锁表

如果右边窗口更新的是同一条数据，则会锁行