mysql innodb lock机制原理
发表于：2024-11-12 作者：千家信息网编辑
千家信息网最后更新 2024年11月12日，一.lock 和 latchlatch 一般称为闩锁，目的是用来保证并发线程操作临界资源的正确性，无死锁检测机制。分为：mutex(互斥量)，rwlock(读写锁)lock 的对象是事务，用来锁定的数
千家信息网最后更新 2024年11月12日mysql innodb lock机制原理一.lock 和 latch
latch 一般称为闩锁，目的是用来保证并发线程操作临界资源的正确性，无死锁检测机制。分为：mutex(互斥量)，rwlock(读写锁)
lock 的对象是事务，用来锁定的数据库中的对象。
二.lock 种类
1.行级锁
共享锁(S lock)：允许事务读取一行数据
排他锁(X lock)：允许事务修改或删除一行数据
2.表级锁
意向共享锁(IS lock):在对行加S锁之前，先对其表追加IS锁
意向排他锁(IX lock):在对行加X锁之前，先对其表追加IX锁
表级意向锁和行级锁的兼容性：
三.锁的应用场景
1.一致性非锁定读
2.一致性锁定读
通过select * from table for update; 或 select * from table lock in share mode; 来锁定读取数据，在数据读取过程中其他事务不能修改该数据。
3.自增长与锁
含有自增长值的表，都有一个自增长计数器，当对该表进行插入操作时，执行如下语句来得到计数器的值。
select max(auto_inc_col) from table for update;
该锁不是事务完成之后才释放，而是insert命令执行完成后就释放该锁。
mysql5.1.22之前，该模式对于有自增值列的表的并发插入性能较差。
mysql5.1.22开始，innodb提供了一种轻量级的互斥量的自增长实现机制，这种机制大大提高了自增长值的插入性能。
相关参数，innodb_autoinc_lock_mode,默认值为1
4.外键和锁
在innodb存储引擎下，外键列如果没有显式的建立index，mysql会为该列自动添加index，避免发生表锁。
对于外键值的插入和更新，会先select父表，但该select操作并不是一致性非锁定读，而是一致性锁定读（对父表追加S锁）。因此当父表被其他事务加上X锁时，子表的操作会被阻塞。
--主表
CREATE TABLE `wwj`.`t1` (
`deptno` INT NOT NULL,
`deptname` VARCHAR(45) NOT NULL,
`address` VARCHAR(45) NOT NULL,
PRIMARY KEY (`deptno`));
--子表
CREATE TABLE `wwj`.`t2` (
`empno` INT NOT NULL,
`empname` VARCHAR(45) NOT NULL,
`age` INT NOT NULL,
`deptno` INT NOT NULL,
PRIMARY KEY (`empno`),
INDEX `deptno_idx` (`deptno` ASC),
CONSTRAINT `deptno`
FOREIGN KEY (`deptno`)
REFERENCES `wwj`.`t1` (`deptno`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
insert into wwj.t1 values(1,'it','北京');
insert into wwj.t1 values(2,'product','天津');
insert into wwj.t1 values(3,'haha','上海'); 
mysql> select * from information_schema.innodb_locks\G; *************************** 1. row *************************** lock_id: 1303:26:3:2 lock_trx_id: 1303 lock_mode: S lock_type: RECORD lock_table: `wwj`.`t1` lock_index: PRIMARY lock_space: 26 lock_page: 3 lock_rec: 2 lock_data: 1 *************************** 2. row *************************** lock_id: 1298:26:3:2 lock_trx_id: 1298 lock_mode: X lock_type: RECORD lock_table: `wwj`.`t1` lock_index: PRIMARY lock_space: 26 lock_page: 3 lock_rec: 2 lock_data: 1 2 rows in set, 1 warning (0.00 sec)
四.锁的算法
行锁的三种算法：
1.record lock
单个记录上的锁
2.gap lock
间隙锁，锁定一个范围，但不包含记录本身
3.next-key lock
record lock+gap lock 锁定一个范围，但不包含记录本身
--场景模拟
CREATE TABLE `wwj`.`t3` (
`idt3` INT NOT NULL,
`idt4` INT NOT NULL,
PRIMARY KEY (`idt3`),
INDEX `idx-1` (`idt4` ASC));
insert into wwj.t3 values(1,10); insert into wwj.t3 values(3,30); insert into wwj.t3 values(5,50);
mysql> select * from wwj.t3; +------+------+ | idt3 | idt4 | +------+------+ | 1 | 10 | | 3 | 30 | | 5 | 50 | +------+------+ 唯一索引的锁定范围
因为idt3上有唯一索引，因此锁定的只是idt3=3这个值，而不是(1,3)这个范围，即锁定由next-key lock降级为record lock
辅助索引的锁定范围
五.一条sql的加锁范围
对于各种情况下加锁的分析
mysql> show full processlist;
+----+------+-----------+------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+-----------------------+
| 11 | root | localhost | NULL | Sleep | 99 | | NULL |
| 12 | root | localhost | NULL | Sleep | 81 | | NULL |
| 13 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+----+------+-----------+------+---------+------+----------+-----------------------+
杀掉线程:
KILL [CONNECTION | QUERY] thread_id
kill 13 --杀掉线程连接
kill query 13 --杀掉正在执行的语句，保留连接
mysql> show engine innodb status\G;
------------
TRANSACTIONS
------------
Trx id counter 1296
Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421324408397424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1295, ACTIVE 396 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 139848225883904, query id 61 localhost root executing
insert into wwj.t3 select 4,20
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 72 index idx-1 of table `wwj`.`t3` trx id 1295 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000003; asc ;;
---------------------
TRANSACTION 1294, ACTIVE 449 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11, OS thread handle 139848226150144, query id 50 localhost root
Trx read view will not see trx with id >= 1294, sees < 1294
mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1302 | 1302:26:4:3 | 1301 | 1301:26:4:3 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from information_schema.INNODB_LOCKs;
+-------------+-------------+-----------+-----------+------------+------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index |
+-------------+-------------+-----------+-----------+------------+------------+
| 1302:26:4:3 | 1302 | X,GAP | RECORD | `wwj`.`t3` | idx-1 |
| 1301:26:4:3 | 1301 | X | RECORD | `wwj`.`t3` | idx-1 |
+-------------+-------------+-----------+-----------+------------+------------+
2 rows in set, 1 warning (0.00 sec)
SELECT p2.`HOST` Blockedhost, #被阻塞方host
p2.`USER` BlockedUser, #被阻塞方用户
r.trx_id BlockedTrxId, #被阻塞方事务id
r.trx_mysql_thread_id BlockedThreadId, #被阻塞方线程号
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) WaitTime, #等待时间
r.trx_query BlockedQuery, #被阻塞的查询
l.lock_table BlockedTable, #阻塞方锁住的表
m.`lock_mode` BlockedLockMode, #被阻塞方的锁模式
m.`lock_type` BlockedLockType, #被阻塞方的锁类型(表锁还是行锁)
m.`lock_index` BlockedLockIndex, #被阻塞方锁住的索引
m.`lock_space` BlockedLockSpace, #被阻塞方锁对象的space_id
m.lock_page BlockedLockPage, #被阻塞方事务锁定页的数量
m.lock_rec BlockedLockRec, #被阻塞方事务锁定行的数量
m.lock_data BlockedLockData, #被阻塞方事务锁定记录的主键值
p.`HOST` blocking_host, #阻塞方主机
p.`USER` blocking_user, #阻塞方用户
b.trx_id BlockingTrxid, #阻塞方事务id
b.trx_mysql_thread_id BlockingThreadId, #阻塞方线程号
b.trx_query BlockingQuery, #阻塞方查询
l.`lock_mode` BlockingLockMode, #阻塞方的锁模式
l.`lock_type` BlockingLockType, #阻塞方的锁类型(表锁还是行锁)
l.`lock_index` BlockingLockIndex, #阻塞方锁住的索引
l.`lock_space` BlockingLockSpace, #阻塞方锁对象的space_id
l.lock_page BlockingLockPage, #阻塞方事务锁定页的数量
l.lock_rec BlockingLockRec, #阻塞方事务锁定行的数量
l.lock_data BlockingLockData, #阻塞方事务锁定记录的主键值
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx #阻塞方事务空闲的时间
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
WaitTime DESC;
latch 一般称为闩锁，目的是用来保证并发线程操作临界资源的正确性，无死锁检测机制。分为：mutex(互斥量)，rwlock(读写锁)
lock 的对象是事务，用来锁定的数据库中的对象。
二.lock 种类
1.行级锁
共享锁(S lock)：允许事务读取一行数据
排他锁(X lock)：允许事务修改或删除一行数据
2.表级锁
意向共享锁(IS lock):在对行加S锁之前，先对其表追加IS锁
意向排他锁(IX lock):在对行加X锁之前，先对其表追加IX锁
表级意向锁和行级锁的兼容性：
三.锁的应用场景
1.一致性非锁定读
2.一致性锁定读
通过select * from table for update; 或 select * from table lock in share mode; 来锁定读取数据，在数据读取过程中其他事务不能修改该数据。
3.自增长与锁
含有自增长值的表，都有一个自增长计数器，当对该表进行插入操作时，执行如下语句来得到计数器的值。
select max(auto_inc_col) from table for update;
该锁不是事务完成之后才释放，而是insert命令执行完成后就释放该锁。
mysql5.1.22之前，该模式对于有自增值列的表的并发插入性能较差。
mysql5.1.22开始，innodb提供了一种轻量级的互斥量的自增长实现机制，这种机制大大提高了自增长值的插入性能。
相关参数，innodb_autoinc_lock_mode,默认值为1
4.外键和锁
在innodb存储引擎下，外键列如果没有显式的建立index，mysql会为该列自动添加index，避免发生表锁。
对于外键值的插入和更新，会先select父表，但该select操作并不是一致性非锁定读，而是一致性锁定读（对父表追加S锁）。因此当父表被其他事务加上X锁时，子表的操作会被阻塞。
四.锁的算法
行锁的三种算法：
1.record lock
单个记录上的锁
2.gap lock
间隙锁，锁定一个范围，但不包含记录本身
3.next-key lock
record lock+gap lock 锁定一个范围，但不包含记录本身
--场景模拟
因为idt3上有唯一索引，因此锁定的只是idt3=3这个值，而不是(1,3)这个范围，即锁定由next-key lock降级为record lock
辅助索引的锁定范围
五.一条sql的加锁范围
对于各种情况下加锁的分析
p2.`USER` BlockedUser, #被阻塞方用户
r.trx_id BlockedTrxId, #被阻塞方事务id
r.trx_mysql_thread_id BlockedThreadId, #被阻塞方线程号
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) WaitTime, #等待时间
r.trx_query BlockedQuery, #被阻塞的查询
l.lock_table BlockedTable, #阻塞方锁住的表
m.`lock_mode` BlockedLockMode, #被阻塞方的锁模式
m.`lock_type` BlockedLockType, #被阻塞方的锁类型(表锁还是行锁)
m.`lock_index` BlockedLockIndex, #被阻塞方锁住的索引
m.`lock_space` BlockedLockSpace, #被阻塞方锁对象的space_id
m.lock_page BlockedLockPage, #被阻塞方事务锁定页的数量
m.lock_rec BlockedLockRec, #被阻塞方事务锁定行的数量
m.lock_data BlockedLockData, #被阻塞方事务锁定记录的主键值
p.`HOST` blocking_host, #阻塞方主机
p.`USER` blocking_user, #阻塞方用户
b.trx_id BlockingTrxid, #阻塞方事务id
b.trx_mysql_thread_id BlockingThreadId, #阻塞方线程号
b.trx_query BlockingQuery, #阻塞方查询
l.`lock_mode` BlockingLockMode, #阻塞方的锁模式
l.`lock_type` BlockingLockType, #阻塞方的锁类型(表锁还是行锁)
l.`lock_index` BlockingLockIndex, #阻塞方锁住的索引
l.`lock_space` BlockingLockSpace, #阻塞方锁对象的space_id
l.lock_page BlockingLockPage, #阻塞方事务锁定页的数量
l.lock_rec BlockingLockRec, #阻塞方事务锁定行的数量
l.lock_data BlockingLockData, #阻塞方事务锁定记录的主键值
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx #阻塞方事务空闲的时间
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
WaitTime DESC;
参考书籍:
MySQL技术内幕：InnoDB存储引擎
阻塞
事务
数据
范围
索引
线程
增长
一致
一致性
对象
数量
机制
意向
模式
一行
场景
增长值
引擎
性能
时间
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
超跃网络技术有限公司
2020年网络安全活动H5
软件开发遵循的规范
河北回收二手服务器电话
数据库中性别的字段名
sql数据库语句on
文明重启哪个服务器安全
如何构筑网络安全新屏障
dbeaver连接瀚高数据库
t6一直提示服务器端有更新补丁
浪潮服务器开v-t
根据网络安全法第27条规定
数据库的备份文件怎么提取
安全数据库源代码
在数据库服务器上具有全部的权限
网络安全专业以后发展如何
构筑企业网络安全体系架构
网络安全组织的使命
A公司是从事一家软件开发
网络安全10月12日
出国 网络技术认证
计算机网络技术专科有用吗
软件开发小职员工资
akcase数据库
南通住宿软件开发
国泰安数据库学校登录
WOW数据库技术分析
软件开发企业增值税优惠
数据库安全采取措施
坦克世界 服务器忙
