mysql 改表名锁表_MySQL 两表join时加锁情况

最新推荐文章于 2024-04-18 20:12:12 发布

paid Pay

最新推荐文章于 2024-04-18 20:12:12 发布

阅读量141

点赞数

文章标签： mysql 改表名锁表

本文链接：https://blog.csdn.net/weixin_42491751/article/details/113013203

版权

MariaDB [test]>desc leouser_inno;+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

+-------+--------------+------+-----+---------+-------+MariaDB [test]> select *from leouser_inno;+----+-------------+

| id | name |

+----+-------------+

| 1 | changefrom2 |

| 5 | leo2 |

| 7 | leo7 |

+----+-------------+MariaDB [test]>desc leouser2_inno;+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | bigint(20) | NO | PRI | NULL | auto_increment |

+-------+-------------+------+-----+---------+----------------+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.000sec)

MariaDB [test]> select * from leouser2_inno straight_join leouser_inno on leouser_inno.id = leouser2_inno.id where leouser2_inno.id<3 forupdate;+----+------+----+-------------+

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