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层连接返回客户端。