MySQL 两表join时加锁情况

MariaDB [test]> desc leouser_inno;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
MariaDB [test]> select * from leouser_inno;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | changefrom2 |
|  5 | leo2        |
|  7 | leo7        |
+----+-------------+




MariaDB [test]> desc leouser2_inno;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

MariaDB [test]> select * from leouser2_inno;
+----+------+
| id | name |
+----+------+
|  1 | leo  |
|  4 | leo4 |
+----+------+

 

事务隔离级别为Repeatable Read

test1: begin;
select * from leouser_inno straight_join leouser2_inno on leouser_inno.id = leouser2_inno.id for update;
--锁住两个表的所有record和gap

test2： begin； -- 加入主键过滤
select * from leouser_inno straight_join leouser2_inno on leouser_inno.id = leouser2_inno.id where leouser2_inno.id=1 for update;
--各自lock id=1的record， 没有lock gap

test2.1:begin; -- change join table order
-- exactly the same result with test2

test3：begin; -- 主键范围过滤
MariaDB [test]> select * from leouser_inno straight_join leouser2_inno on leouser_inno.id = leouser2_inno.id where leouser2_inno.id<3 for update;
-- leouser2_inno的id=1的记录lock，id=4右边gaplock，id=2，3的gap没有lock，但是没有选中的记录不会lock。由于先select leouser_inno的所有记录，对leouser2_inno 的select很名确（where id=1 or id=5），id=5在leouser2_inno 的最大id=4的右边，如果不对id=4的右侧gap加锁，可能导致幻读，而对id=2，3的insert在leouser_inno
-- leouser_inno的所有record都lock，所有gap都lock。
insert into leouser2_inno(id) values(0);的时候有个小插曲，不能插入，以为id=1的左侧也是gap lock，想不通。其实是因为leouser2_inno.id是autoincrement的，当id=0或者null的时候自动增加到mysql维护的下一个id，也就是id=5，而id=5是gaplock状态导致不能insert。
SET sql_mode='NO_AUTO_VALUE_ON_ZERO';
当前session设置后，可以insert id=0的record，因此，不矛盾。

test3.1 -- reorder join tables;

MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select * from leouser2_inno straight_join leouser_inno on leouser_inno.id = leouser2_inno.id where leouser2_inno.id<3 for update;
+----+------+----+-------------+
| id | name | id | name        |
+----+------+----+-------------+
|  1 | leo  |  1 | changefrom2 |
+----+------+----+-------------+
1 row in set (0.004 sec)

 

leouser2_inno 发现所有record被加锁；相关gap被lock。why all records locked but not all gaps locked？？——是因为RR事务隔离级别下，唯一索引上查询时使用的lock类型next-key lock，除非等号过滤条件（可以是or连接的多个相等条件），并且对应的等号条件查询到了一条记录，MySQL做了优化此时退化为record lock，本测试中不适用于优化情形，仍是next-key lock。leouser2_inno中的锁有3个：(-infinite,1],(1,4],(4,+infinite)。

leouser_inno 只有符合条件的id=1一条record被lock，只有record lock，因此其他的record没有被加锁。
=============
总结，join的时候mysql straight_join阻止优化select表的顺序，按照从左到右结合where条件查询，对第一个表加相应的锁，得到记录后作为条件查询第二个表，对第二个表加锁。多表join时属于嵌套情况。join情况的加锁就是多个表依次查询——加锁，和分析单表查询相同，其结果通过mysql server层连接返回客户端。