MySQL的锁机制之行锁2

最新推荐文章于 2024-05-29 14:41:02 发布

yjaspire

最新推荐文章于 2024-05-29 14:41:02 发布

阅读量240

点赞数

分类专栏：数据库文章标签：索引失效行锁变表锁间隙锁锁住一行分析行锁定 InnoDB监视器

本文链接：https://blog.csdn.net/yjaspire/article/details/81155317

版权

数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

行锁需要注意的地方

索引失效行锁变表锁

依旧是booktype表，添加索引index_bookNum给字段bookNum（varchar类型），此时数据如下

上图当我们修改bookNum为3的数据时，另一个会话修改bookNum为4的数据也是可以的，因为InnoDb默认锁的类型时行锁,修改bookNum为3的数据只会锁住这一条数据,不会对其他数据造成影响.

注意上图的修改还是bookNum为3的数据，但是这次没有加''，所以后面另一个会话修改bookNum为4的数据会进入锁等待,

那是因为不加''导致了数据库进行隐氏类型转换，index_bookNum索引失效了，从而导致本来锁一行变成锁表。

间隙锁-RR级别默认开启

定义:当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁,对于键值再条件范围内但并不存在的记录，叫做"间隙(GAP)"，

间隙锁定是锁定索引记录之间的间隙，或锁定在第一个或最后一个索引记录之前的间隙上。例如，

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;阻止其他事务将值15插入列t.c1，无论列中是否已存在任何此类值，因为该范围中所有现有值之间的间隙都已锁定。

危害:因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值不存在.间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成再锁定的时候无法插入锁定键值范围内的任何数据.在某些场景下可能会对性能造成很大的危害.

下一键锁

定义:索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合

InnoDB执行行级锁定时，它在搜索或扫描表索引时，会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。索引记录上的下一键锁定也会影响该索引记录之前的“ 间隙 ”。也就是说，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话R在索引中具有共享或独占锁定记录，则另一个会话不能R在索引顺序之前的间隙中插入新索引记录。

假设索引包含值10,11,13和20.此索引的可能的下一个键锁定包括以下间隔，其中圆括号表示排除间隔端点，方括号表示包含端点：

（负无穷大，10]
（10,11]
（11,13）
（13,20）
（20，正无穷大）

对于最后一个间隔，下一个键锁定将间隙锁定在索引中最大值之上，而“ supremum ” 伪记录的值高于索引中实际的任何值。supremum不是真正的索引记录，因此，实际上，此下一键锁定仅锁定最大索引值之后的间隙。

默认情况下，InnoDB以 REPEATABLE READ事务隔离级别运行。在这种情况下，InnoDB使用下一键锁进行搜索和索引扫描，这可以控制幻读。

锁住一行

查询语句后加 for update;

行锁总结

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定更高一些,但是在整体并发处理能力方面要远远由于MyISAM的表锁定.当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势了.

但是,InnoDB的行级锁定也有其脆弱的一面,当使用不当的时候,可能会让InnoDB的整体性能表现还不如MyIASM。

如何进行分析行锁定

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

show status like 'innodb_row_lock%';

状态量说明

Innodb_row_lock_current_waits 当前正在等待锁定的数量.
Innodb_row_lock_time 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg 每次等待所花平均时间
Innodb_row_lock_time_max 从系统启动后到现在等待最常的一次所花时间.
Innodb_row_lock_waits 系统启动后到现在总共等待的次数.

比较重要的是

Innodb_row_lock_time 从系统启动到现在锁定总时间长度

Innodb_row_lock_waits 系统启动后到现在总共等待的次数.

Innodb_row_lock_time_avg 每次等待所花平均时间

尤其时当等待次数很高，而且每次等待市场也不小的时候,这时就需要分析系统中为什么会有这么多的等待,然后根据分析结果开始优化.

进一步分析

启用InnoDB监视器

SHOW ENGINE INNODB STATUS每十五秒写入MySQL数据目录中的状态文件

InnoDB只有在您真正想要查看监视器信息时才应启用监视器，因为输出生成会导致某些性能下降。此外，如果监视器输出定向到错误日志，如果您以后忘记禁用监视器，则日志可能会变得非常大。

InnoDB监视器输出以包含时间戳和监视器名称的标头开头。例如：

=====================================
2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT
=====================================

启用标准InnoDB监视器

InnoDB通过将innodb_status_output系统变量设置为启用标准监视器ON。

set GLOBAL innodb_status_output = ON;

要禁用标准InnoDB监视器，请设置 innodb_status_output为 OFF。

关闭服务器时，该 innodb_status_output变量将设置为默认OFF值。

获得标准的InnoDB监视器按需输出

作为启用标准InnoDB监视器定期输出的替代方法，您可以InnoDB使用SHOW ENGINE INNODB STATUSSQL语句按需获取标准监视器输出，该语句将输出提取到客户端程序。如果您使用的是mysql 交互式客户端，那么如果您将通常的分号语句终结符替换为以下内容，则输出更具可读性\G：

SHOW ENGINE INNODB STATUS\G

SHOW ENGINE INNODB STATUSInnoDB 如果InnoDB启用了锁定监视器，则输出还包括锁定监视器数据。

启用InnoDB锁定监视器

InnoDB使用InnoDB标准监视器输出打印锁定监视器数据。无论是 InnoDB标准监视器和 InnoDB锁显示器必须能够具有 InnoDB锁定监控数据定期打印。

要启用InnoDB锁定监视器，请将innodb_status_output_locks系统变量设置为ON。无论是 InnoDB标准的监控和InnoDB锁定显示器必须能够有 InnoDB定期打印锁监视器数据：

set GLOBAL innodb_status_output = ON;
set GLOBAL innodb_status_output_locks = ON;

要禁用InnoDB锁定监视器，请设置 innodb_status_output_locks为 OFF。设置 innodb_status_output为 OFF还禁用 InnoDB标准监视器。

关闭服务器时， innodb_status_output和 innodb_status_output_locks 变量将设置为默认OFF值。

注意

要启用InnoDB锁定监视器 SHOW ENGINE INNODB STATUS输出，只需启用 innodb_status_output_locks。

如下

=====================================
2018-07-23 15:25:57 0x39ac INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 54 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 40 srv_active, 0 srv_shutdown, 18882 srv_idle
srv_master_thread log flush and writes: 18494
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 63
OS WAIT ARRAY INFO: signal count 63
RW-shared spins 0, rounds 90, OS waits 45
RW-excl spins 0, rounds 18, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 90.00 RW-shared, 18.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 447253
Purge done for trx's n:o < 447250 undo n:o < 0 state: running but idle
History list length 18
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283505182630248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 447252, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 15100, query id 1036 localhost ::1 root update
INSERT INTO booktype
(`id`,
`bookTypeName`,
`bookNum`)
VALUES ('1212',
'幻读test',
'220')
------- TRX HAS BEEN WAITING 24 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7982 page no 3 n bits 104 index PRIMARY of table `bookmain`.`booktype` trx id 447252 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 447251, ACTIVE 1839 sec
2 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 7
MySQL thread id 2, OS thread handle 12412, query id 1027 localhost ::1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1644 OS file reads, 318 OS file writes, 200 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 132, seg size 134, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
0.00 hash searches/s, 0.02 non-hash searches/s
---
LOG
---
Log sequence number 4706613499
Log flushed up to 4706613499
Pages flushed up to 4706613499
Last checkpoint at 4706613490
0 pending log flushes, 0 pending chkp writes
128 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 3615223
Buffer pool size 512
Free buffers 246
Database pages 257
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1289, created 39, written 157
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 257, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5504, Main thread ID=10048, state: sleeping
Number of rows inserted 30, updated 41, deleted 3, read 210
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
说明：

Status

此部分显示时间戳，监视器名称以及每秒平均值所基于的秒数。秒数是当前时间与上次InnoDB打印监视器输出之间经过的时间。
BACKGROUND THREAD

这些srv_master_thread行显示了主后台线程完成的工作。
SEMAPHORES

本节报告等待信号量的线程，以及有关线程在互斥锁或rw-lock信号量上需要旋转或等待的次数的统计信息。等待信号量的大量线程可能是磁盘I / O或内部争用问题的结果InnoDB。争用可能是由于查询的严重并行性或操作系统线程调度中的问题。innodb_thread_concurrency 在这种情况下，将系统变量设置为小于默认值可能会有所帮助。该Spin rounds per wait 行显示每个OS等待互斥锁的自旋锁轮数。

互斥指标由报告 SHOW ENGINE INNODB MUTEX。
LATEST FOREIGN KEY ERROR

本节提供有关最新外键约束错误的信息。如果没有发生此类错误，则不存在。内容包括失败的语句以及有关失败的约束以及引用和引用表的信息。
LATEST DETECTED DEADLOCK

本节提供有关最近死锁的信息。如果没有发生死锁，则不存在。内容显示涉及哪些事务，每个尝试执行的语句，它们具有和需要的锁定，以及哪个事务InnoDB 决定回滚以打破死锁。
TRANSACTIONS

如果此部分报告锁等待，则您的应用程序可能存在锁争用。输出还可以帮助跟踪事务死锁的原因。
FILE I/O

本节提供有关InnoDB用于执行各种类型I / O的线程的信息。其中前几个专门用于一般 InnoDB处理。内容还显示待处理I / O操作的信息和I / O性能的统计信息。

这些线程的数量由innodb_read_io_threads和 innodb_write_io_threads 参数控制。
INSERT BUFFER AND ADAPTIVE HASH INDEX

此部分显示 InnoDB插入缓冲区（也称为更改缓冲区）和自适应哈希索引的状态。
LOG

此部分显示有关InnoDB日志的信息。内容包括当前日志序列号，日志刷新到磁盘的距离以及InnoDB最后一次检查点的位置。
BUFFER POOL AND MEMORY

本节为您提供有关读取和写入页面的统计信息。您可以根据这些数字计算您的查询当前正在执行的数据文件I / O操作数。
ROW OPERATIONS

此部分显示主线程正在执行的操作，包括每种类型的行操作的数量和性能速率。

总结

尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁

合理设计索引,尽量缩小锁的范围

尽可能较少的检索条件,避免间隙锁

尽量控制事务大小,减少锁定锁定资源量和时间长度

尽可能低级别事务隔离

yjaspire

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的锁机制之行锁2

行锁需要注意的地方索引失效行锁变表锁依旧是booktype表，添加索引index_bookNum给字段bookNum（varchar类型），此时数据如下上图当我们修改bookNum为3的数据时，另一个会话修改bookNum为4的数据也是可以的，因...
复制链接

扫一扫