mysql锁

作者: 喏喏2021 | 来源:发表于2022-02-07 16:51 被阅读0次

    1. 相关概念

    按锁的粒度分: 表锁、页锁、行锁

    行锁是锁表粒度最小、最细的一种锁,能大大减少数据库冲突的概率,当然锁表的开锁也是最大的
    表锁是粒度最大的一种锁,是对整张表进行加锁,是对整张表进行加锁,不会出现死锁的情况,但锁冲突的概率较大,常用的InnoDB引擎支持行锁和表锁
    页级锁是粒度介于行锁和表锁之间,BDB引擎支持页锁

    行级锁按使用方式分:共享锁、排它锁

    共享锁:也叫读锁,或是S锁, 数据使用共享锁后,不能对数据进行修改,其它事务也只能使用共享锁,而不能使用排它锁
    排它锁:也叫写锁、独占锁,或是X锁,使用排它锁的事务,可以读也可以写,其它事务不能使用共享锁或是排它锁

    行级锁按类型分:记录锁、间隔锁、临键锁、插入意向锁

    记录锁Record Lock,锁住特定的记录,当然如果没有查询到记录时,就会上升到表锁
    间隔锁Gap Lock,就是在索引的间隙上加上锁,这里是实现防止可重复读的主要原因,是一个左开右开的区间
    临键锁Next-key Lock,就是记录锁+间隔锁,是一个左开右闭的区间
    插入意向锁Insert Intention Lock,是一种间隔锁,会和间隔锁及临键锁发生冲突,以阻止其他插入操作执行,以提高并发插入的性能

    聚簇索引、非聚簇索引

    聚簇索引:查找的数据就在索引中,比如主键,或第一个唯一键,或数据库内部维护的行ID
    非聚簇索引:查找的数据不在索引中,一般为普通二级索引,真实数据还需要从聚簇索引中获取

    2. 与锁相关的参数

    锁等待的时间
    mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_lock_wait_timeout | 50    |
    +--------------------------+-------+
    1 row in set (0.00 sec)
    

    缺省为50秒,

    mysql> set innodb_lock_wait_timeout=5;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_lock_wait_timeout | 5     |
    +--------------------------+-------+
    1 row in set (0.00 sec)
    

    这里我们将缺省时间调整了5秒

    当前是否有锁表
    mysql> show OPEN TABLES where In_use > 0;
    +----------+-------+--------+-------------+
    | Database | Table | In_use | Name_locked |
    +----------+-------+--------+-------------+
    | my_order | goods |      1 |           0 |
    +----------+-------+--------+-------------+
    1 row in set (0.00 sec)
    

    这里我们可以看,goods被锁住了

    当前正在锁的表
    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    +-------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
    | lock_id           | lock_trx_id | lock_mode | lock_type | lock_table         | lock_index | lock_space | lock_page | lock_rec | lock_data |
    +-------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
    | 196181479:385:3:2 | 196181479   | X         | RECORD    | `my_order`.`goods` | PRIMARY    |        385 |         3 |        2 | 1         |
    | 196181478:385:3:2 | 196181478   | X         | RECORD    | `my_order`.`goods` | PRIMARY    |        385 |         3 |        2 | 1         |
    +-------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
    2 rows in set (0.00 sec)
    

    可以看出,goods表有两条锁记录,模式是排他锁,是记录锁

    等待锁的表
    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    +-------------------+-------------------+-----------------+-------------------+
    | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id  |
    +-------------------+-------------------+-----------------+-------------------+
    | 196181479         | 196181479:385:3:2 | 196181478       | 196181478:385:3:2 |
    +-------------------+-------------------+-----------------+-------------------+
    1 row in set (0.00 sec)
    

    这个表可以看到一条记录,一个是当前请求事务ID,还是阻塞中的事务ID

    查看详细的运行时信息
    mysql> show engine innodb status\G;
    LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
    MySQL thread id 4926, OS thread handle 0x6318, query id 1728630 localhost ::1 root statistics
    select * from goods where id=1 for update
    ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 385 page no 3 n bits 80 index `PRIMARY` of table `my_order`.`goods` trx id 196181479 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
    

    上面是截取了一小段,这里可以看出锁表的语句

    超时相关的参数
    mysql> show variables like '%timeout%';
    +-----------------------------+----------+
    | Variable_name               | Value    |
    +-----------------------------+----------+
    | connect_timeout             | 10       |
    | delayed_insert_timeout      | 300      |
    | innodb_flush_log_at_timeout | 1        |
    | innodb_lock_wait_timeout    | 30       |
    | innodb_rollback_on_timeout  | OFF      |
    | interactive_timeout         | 28800    |
    | lock_wait_timeout           | 31536000 |
    | net_read_timeout            | 30       |
    | net_write_timeout           | 60       |
    | rpl_stop_slave_timeout      | 31536000 |
    | slave_net_timeout           | 3600     |
    | wait_timeout                | 28800    |
    +-----------------------------+----------+
    12 rows in set (0.00 sec)
    

    超时相关的参数,包括前面的innodb,锁超时时间

    3. 示例

    间隔锁示例

    1)初始表中数据


    初始表数据.png

    id为主键索引,num为非唯一索引
    2)事务1锁表

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from goods where num=5 for update;
    +----+-----------+------+---------------------+---------------------+---------------+
    | id | name      | num  | ctime               | utime               | desc          |
    +----+-----------+------+---------------------+---------------------+---------------+
    |  3 | 小米汽车3 |    5 | 2022-01-30 22:14:49 | 2022-01-30 22:14:49 | 添加小米汽车3 |
    |  4 | 小米汽车3 |    5 | 2022-01-30 22:20:36 | 2022-01-30 22:20:36 | 添加小米汽车3 |
    |  5 | 小米汽车3 |    5 | 2022-01-30 22:38:49 | 2022-01-30 22:38:49 | 添加小米汽车3 |
    +----+-----------+------+---------------------+---------------------+---------------+
    3 rows in set (0.00 sec)
    

    这里语句是对num=5进行锁定,因为缺省是可重复读隔离级别,实际这里是间隔锁,一个是<5,还有一个是(5,31)共两段
    3)事务2插入num=2记录

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    mysql> insert into goods(name,num) values('mi',2);
    Query OK, 1 row affected (29.77 sec)
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    

    事务2在插入num=2时,会进入阻塞等待状态,等待事务1提交
    4)事务1提交

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from goods;
    +----+-----------+------+---------------------+---------------------+----------------+
    | id | name      | num  | ctime               | utime               | desc           |
    +----+-----------+------+---------------------+---------------------+----------------+
    |  1 | 小米汽车  |   31 | 2022-01-29 10:51:32 | 2022-01-30 22:38:49 | addNum方法更新 |
    |  3 | 小米汽车3 |    5 | 2022-01-30 22:14:49 | 2022-01-30 22:14:49 | 添加小米汽车3  |
    |  4 | 小米汽车3 |    5 | 2022-01-30 22:20:36 | 2022-01-30 22:20:36 | 添加小米汽车3  |
    |  5 | 小米汽车3 |    5 | 2022-01-30 22:38:49 | 2022-01-30 22:38:49 | 添加小米汽车3  |
    |  6 | mi        |    2 | 2022-02-07 16:34:15 | 2022-02-07 16:34:15 | NULL           |
    +----+-----------+------+---------------------+---------------------+----------------+
    5 rows in set (0.00 sec)
    

    事务1进行提交,事务2再提交后,再查询一下,看到了刚插入的num=2的记录。
    举手之劳,不要吝惜您的赞!-_-

    相关文章

      网友评论

          本文标题:mysql锁

          本文链接:https://www.haomeiwen.com/subject/myqmkrtx.html