select...for update 锁粒度问题

发表于 | 分类于 | 
1
2
3
4
5
select查询语句是不会加锁的，但是 select...for update除了有查询的作用外，还会加锁呢，而且它是悲观锁。

那么它加的是行锁还是表锁，这就要看是不是用了索引/主键。

用了唯一索引/主键的话就是行锁，否则就是表锁。
  • 提前准备
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    # 创建 user表（id为主键，code为唯一索引，再插入两条数据）
    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int DEFAULT NULL,
      `code` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_code` (`code`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `message`.`user` (`id`, `name`, `age`, `code`) VALUES (1, '陶攀峰', 11, '001');
    INSERT INTO `message`.`user` (`id`, `name`, `age`, `code`) VALUES (2, '二蛋', 22, '002');

    # 需要关闭自动提交
        -- 查询自动提交 1=自动提交 0=手动提交
        select @@autocommit;

        -- 设置为自动提交
        set @@autocommit=0;
  • 开始操作
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    -- 事务1、事务2 都设置手动提交
    t1  set @@autocommit=0;
    t2  set @@autocommit=0;

    -- 下面遇到事务2阻塞 ===> 先提交事务1（此时事务2执行update成功），再回滚事务2（保持数据干净）
    -- 下面遇到事务2不阻塞 ===> 先提交事务1，再回滚事务2（保持数据干净）
    ------------------------------------------------------------

    # 主键
    t1  select * from user where id=1 for update;
    t2  update user set age=111 where id=1;-- 会阻塞（因为锁了行 id=1）
    t2  update user set age=222 where id=2;-- 不阻塞（因为锁了行 id=1，不会影响 id=2的更新）

    ------------------------------------------------------------

    # 唯一索引
    t1  select * from user where code='001' for update;
    t2  update user set code='0011' where id=1;-- 会阻塞（因为锁了行 code='001'）
    t2  update user set code='0022' where id=2;-- 不阻塞（因为锁了行 code='001'，不会影响 id=2的更新）

    ------------------------------------------------------------

    # 普通字段（不是主键，也不是唯一索引）
    t1  select * from user where age=11 for update;
    t2  update user set age=111 where id=1;-- 会阻塞
    t2  update user set age=222 where id=2;-- 会阻塞（此时可以判断，id=1，id=2都被锁了，也就是锁表了。）