V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
hjcpnr
V2EX  ›  MySQL

Mysql select for update 的加锁机制?

  •  
  •   hjcpnr · 246 天前 · 1685 次点击
    这是一个创建于 246 天前的主题,其中的信息可能已经有所发展或是发生改变。
    如下表,age 上有唯一索引,隔离级别 RC

    CREATE TABLE `test` (
    `id` int NOT NULL AUTO_INCREMENT,
    `age` char(10) NOT NULL,
    `name` char(10) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=70032 DEFAULT CHARSET=utf8mb3


    | id | age | name |
    +-------+-------+--------+
    | 70031 | 80009 | n79999 |
    | 70030 | 80008 | n79998 |
    | 70029 | 80007 | n79997 |
    | 70028 | 80006 | n79996 |
    | 70027 | 80005 | n79995 |
    +-------+-------+--------+

    事务 1:
    select * from test where age = 80009 for update;

    事务 2:
    select * from test where age = 80008 for update;

    事务 2 会阻塞,data_locks 显示事务 2 在等待 id=70031 的 record lock ,这是为什么?
    12 条回复    2023-09-25 08:53:21 +08:00
    hjcpnr
        1
    hjcpnr  
    OP
       246 天前
    补充一下

    事务 1:
    select * from test where age = 80009 for update;

    事务 2 ,需要阻塞等待事务 1 结束:
    select * from test where age = 80008 for update;

    事务 3 ,不会阻塞,成功执行:
    update test set name ="haha" where age = 80008;

    另外,如果把 age 的类型从 char 改成 int ,三个事务都不会互相阻塞
    gumusu
        2
    gumusu  
       246 天前 via Android
    字段类型不一致,隐式类型转换导致没走索引? explain 一下看看吧
    mightybruce
        3
    mightybruce  
       246 天前
    select for update 百分百会加锁, 而且是悲观锁。
    尽量少用 select for update
    mightybruce
        4
    mightybruce  
       246 天前
    看 mysql 文档,了解一下间隙锁、临键锁、记录锁的区别。
    rekulas
        5
    rekulas  
       246 天前
    没走索引,字符串你要用 age = '80008'
    mightybruce
        6
    mightybruce  
       246 天前
    age 在 select 中 从 char 变为 int 发生了隐式转换,这个时候不会走索引
    sansui3
        7
    sansui3  
       246 天前
    @hjcpnr 你这个有问题吧,事务 3 也会被阻塞,我在本地试了一下(我的版本是 8.0.33 );为什么被阻塞,和其他人的结论是一样的,因为字段类型不一致所以其实锁的是全表,为什么显示事务 2 在等待 id=70031 的 record lock ,就是因为事务 2 也需要按“顺序”给全表的所有数据加锁,所以在给 70027 加锁的时候就在等待了,因为事务 1 已经给 70027 这条加索了。
    jorneyr
        8
    jorneyr  
       246 天前
    MySQL 的自动类型转换非常不好,遇到过几次问题都是书写不规范导致了使用自动类型转换造成的。
    uselessVisitor
        9
    uselessVisitor  
       246 天前
    @mightybruce rc 只有行锁吧
    uselessVisitor
        10
    uselessVisitor  
       246 天前
    看看是否走了索引,走索引就是行锁,没走的话是表锁
    katsusan
        11
    katsusan  
       246 天前
    explain 一下就能看出来 age=80009 锁了全表.

    ```mysql
    mysql> explain select * from test where age = 80009 for update;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | 1 | SIMPLE | test | NULL | ALL | idx_age | NULL | NULL | NULL | 3 | 33.33 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 3 warnings (0.00 sec)

    mysql> explain select * from test where age = '80009' for update;
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | 1 | SIMPLE | test | NULL | const | idx_age | idx_age | 40 | const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    ```
    9fan
        12
    9fan  
       245 天前
    @gumusu 这位哥说的一针见血,既然都定义为 char ,还是按照规定格式来写吧,将值放在 ''
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2879 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 12:50 · PVG 20:50 · LAX 05:50 · JFK 08:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.