MySQL锁机制

最新推荐文章于 2024-03-09 23:59:08 发布

兰叶

最新推荐文章于 2024-03-09 23:59:08 发布

阅读量172

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 java Powered by 金山文档

本文链接：https://blog.csdn.net/u012143270/article/details/129064574

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL锁分类

常用锁分类

InnoDB行锁

InnoDB的行锁实际上是针对索引加的锁（在索引对应的索引项上做标记）

该索引不能失效，否则会从行锁升级成表锁（RR级别会升级成表锁，RC级别不会）

怎么理解给索引加锁？

答：执行update语句的时候，如果where条件是索引字段，那么就会给扫描到的索引对应的索引项加锁；如果where条件不是索引字段，那么可能会锁住整张表

RR级别，行锁升级成表锁的原因：

因为RR级别需要解决不可重复读和幻读的问题，因此在加行锁的时候会把所有扫描过的索引和间隙都加上锁，防止扫描过的索引被其他事务修改（解决不可重复读）或者间隙被其他事务插入数据（解决幻读），所以当where条件不走索引时可能会锁住整张表

InnoDB和MyISAM执行SQL语句时加锁的区别

InnoDB
执行select不会加锁
执行insert、update、delete会加行锁

MyISAM
执行select会给所有涉及到的表加读锁
执行insert、update、delete会给所有涉及到的表加写锁

间隙锁（只有在RR级别下才生效）

锁住两个值之间的间隙=>表中只要可以插入记录的地方就是间隙，比如：表中有id为1、5、7、10的记录，那么(1,5) (5,7) (7,10) (10,∞）都是间隙。

只要在间隙范围内锁住一条不存在的记录，就会锁住整个间隙（不锁边界）。=>为了解决RR级别下的幻读问题，锁住间隙，就防止了其他session在这个间隙插入记录。

比如：select * from table where id=4 for update;
锁住的是间隙(1,5)之间一条不存在的id为4的记录，最终会把(1,5)间隙都锁住，无法插入id为2、3、4的记录。

临建锁

临建锁是行锁和间隙锁的组合，除了间隙还锁了边界。

比如：select * from table where id>1 and id<=5 for update;
锁住的是(1,5)的间隙，以及id=5的行。

锁等待分析

通过innodb_row_lock状态变量来分析系统上行锁的争夺情况

-- 查看数据库锁相关统计信息
show status like 'innodb_row_lock%';

Variable_name                |Value  |
-----------------------------+-------+
Innodb_row_lock_current_waits|0      |===>当前正在等锁的事务数量
Innodb_row_lock_time         |2522801|===>从系统启动到现在，所有等锁的总时长
Innodb_row_lock_time_avg     |354    |===>从系统启动到现在，平均的等锁时长
Innodb_row_lock_time_max     |121145 |===>从系统启动到现在，最大的等锁时长
Innodb_row_lock_waits        |7119   |===>从系统启动到现在，所有等锁的事务总数

INFORMATION_SCHEMA系统库锁相关数据库表

-- 查看当前事务详细信息
select * from information_schema.INNODB_TRX;

trx_id|trx_state|trx_started|trx_requested_lock_id|trx_wait_started|trx_weight|trx_mysql_thread_id|trx_query|trx_operation_state|trx_tables_in_use|trx_tables_locked|trx_lock_structs|trx_lock_memory_bytes|trx_rows_locked|trx_rows_modified|trx_concurrency_tickets|trx_isolation_level|trx_unique_checks|trx_foreign_key_checks|trx_last_foreign_key_error|trx_adaptive_hash_latched|trx_adaptive_hash_timeout|trx_is_read_only|trx_autocommit_non_locking|
------+---------+-----------+---------------------+----------------+----------+-------------------+---------+-------------------+-----------------+-----------------+----------------+---------------------+---------------+-----------------+-----------------------+-------------------+-----------------+----------------------+--------------------------+-------------------------+-------------------------+----------------+--------------------------+

-- 查看当前锁详细信息
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|
-------+-----------+---------+---------+----------+----------+----------+---------+--------+---------+

-- 查看当前锁等待详细信息
select * from information_schema.INNODB_LOCK_WAITS;

requesting_trx_id|requested_lock_id|blocking_trx_id|blocking_lock_id|
-----------------+-----------------+---------------+----------------+