-
Notifications
You must be signed in to change notification settings - Fork 0
/
55610.html
307 lines (298 loc) · 30 KB
/
55610.html
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>MySQL实战 基础篇（四） | 征蓬</title><meta name="keywords" content="MySQL"><meta name="author" content="huy"><meta name="copyright" content="huy"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="基础篇四：锁（全局锁、表级锁、行锁）。">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL实战 基础篇（四）">
<meta property="og:url" content="http://example.com/55610.html">
<meta property="og:site_name" content="征蓬">
<meta property="og:description" content="基础篇四：锁（全局锁、表级锁、行锁）。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://example.com/pics/scenery/16.jpg">
<meta property="article:published_time" content="2021-06-21T00:58:52.000Z">
<meta property="article:modified_time" content="2021-08-31T07:46:51.016Z">
<meta property="article:author" content="huy">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://example.com/pics/scenery/16.jpg"><link rel="shortcut icon" href="/pics/avatar/8.png"><link rel="canonical" href="http://example.com/55610"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://lf3-cdn-tos.bytecdntp.com/cdn/expire-1-M/node-snackbar/0.1.16/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="/font/wcsf.ttf" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = {
root: '/',
algolia: undefined,
localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
translate: undefined,
noticeOutdate: undefined,
highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
copy: {
success: '复制成功',
error: '复制错误',
noSupport: '浏览器不支持'
},
relativeDate: {
homepage: false,
post: false
},
runtime: '',
date_suffix: {
just: '刚刚',
min: '分钟前',
hour: '小时前',
day: '天前',
month: '个月前'
},
copyright: undefined,
lightbox: 'fancybox',
Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"bottom-left"},
source: {
jQuery: 'https://lf26-cdn-tos.bytecdntp.com/cdn/expire-1-M/jquery/3.6.0/jquery.min.js',
justifiedGallery: {
js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
},
fancybox: {
js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
}
},
isPhotoFigcaption: false,
islazyload: false,
isanchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
title: 'MySQL实战 基础篇（四）',
isPost: true,
isHome: false,
isHighlightShrink: false,
isToc: true,
postUpdate: '2021-08-31 15:46:51'
}</script><noscript><style type="text/css">
#nav {
opacity: 1
}
.justified-gallery img {
opacity: 1
}
#recent-posts time,
#post-meta time {
display: inline !important
}
</style></noscript><script>(win=>{
win.saveToLocal = {
set: function setWithExpiry(key, value, ttl) {
if (ttl === 0) return
const now = new Date()
const expiryDay = ttl * 86400000
const item = {
value: value,
expiry: now.getTime() + expiryDay,
}
localStorage.setItem(key, JSON.stringify(item))
},
get: function getWithExpiry(key) {
const itemStr = localStorage.getItem(key)
if (!itemStr) {
return undefined
}
const item = JSON.parse(itemStr)
const now = new Date()
if (now.getTime() > item.expiry) {
localStorage.removeItem(key)
return undefined
}
return item.value
}
}
win.getScript = url => new Promise((resolve, reject) => {
const script = document.createElement('script')
script.src = url
script.async = true
script.onerror = reject
script.onload = script.onreadystatechange = function() {
const loadState = this.readyState
if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
script.onload = script.onreadystatechange = null
resolve()
}
document.head.appendChild(script)
})
win.activateDarkMode = function () {
document.documentElement.setAttribute('data-theme', 'dark')
if (document.querySelector('meta[name="theme-color"]') !== null) {
document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
}
}
win.activateLightMode = function () {
document.documentElement.setAttribute('data-theme', 'light')
if (document.querySelector('meta[name="theme-color"]') !== null) {
document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
}
}
const t = saveToLocal.get('theme')
if (t === 'dark') activateDarkMode()
else if (t === 'light') activateLightMode()
const asideStatus = saveToLocal.get('aside-status')
if (asideStatus !== undefined) {
if (asideStatus === 'hide') {
document.documentElement.classList.add('hide-aside')
} else {
document.documentElement.classList.remove('hide-aside')
}
}
const detectApple = () => {
if (GLOBAL_CONFIG_SITE.isHome && /iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
document.documentElement.classList.add('apple')
}
}
detectApple()
})(window)</script><link rel="stylesheet" href="/css/custom.css" media="defer" onload="this.media='all'"><!-- hexo injector head_end start --><link rel="stylesheet" href="https://npm.elemecdn.com/hexo-butterfly-tag-plugins-plus@latest/lib/assets/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hexo-butterfly-tag-plugins-plus@latest/lib/tag_plugins.min.css" media="defer" onload="this.media='all'"><script async src="https://npm.elemecdn.com/hexo-butterfly-tag-plugins-plus@latest/lib/assets/carousel-touch.js"></script><!-- hexo injector head_end end --><meta name="generator" content="Hexo 5.4.1"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/pics/Asoul/8.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">54</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">17</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">12</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('/pics/scenery/16.jpg')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">征蓬</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">MySQL实战 基础篇（四）</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-06-21T00:58:52.000Z" title="发表于 2021-06-21 08:58:52">2021-06-21</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-08-31T07:46:51.016Z" title="更新于 2021-08-31 15:46:51">2021-08-31</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/MySQL/">MySQL</a></span></div><div class="meta-secondline"></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><blockquote>
<p>该系列是极客时间林晓斌的 MySQL实战45讲 课程笔记。</p>
</blockquote>
<h2 id="简介-5">简介</h2>
<p>MySQL 的锁大概分为三种：全局锁、表级锁、行锁。</p>
<p>行锁是在引擎层，由各个引擎实现的：</p>
<ul>
<li>MyISAM 引擎不支持行锁；</li>
<li>InnoDB 支持行锁。</li>
</ul>
<h2 id="全局锁">全局锁</h2>
<p>MySQL 提供了一个对整个数据库实例加锁的命令，可以使整个数据库处于<strong>只读</strong>的状态，通常在做全库逻辑备份时使用：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 全局锁命令</span></span><br><span class="line">Flush tables <span class="keyword">with</span> read lock (FTWRL)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 解除全局锁（或者退出执行加锁命令的窗口）</span></span><br><span class="line">unlock tables</span><br></pre></td></tr></table></figure>
<p>执行改命令后，以下语句会被阻塞：</p>
<ol>
<li>数据更新语句（增删改）；</li>
<li>数据定义语句（建表、修改表结构等）；</li>
<li>更新类事务的提交语句。</li>
</ol>
<p>在主从数据库中使用该命令备份，容易碰到以下问题：</p>
<ol>
<li>主库备份：备份期间不能更新数据，业务基本上停摆：</li>
<li>从库备份：如果是读写分类的，从库在备份期间不能执行主库传来的 binlog，造成主从延迟。</li>
</ol>
<h3 id="常见问题-2">常见问题</h3>
<ol>
<li>
<p>备份不加锁会怎样？<br>
备份系统备份的数据和原数据库的逻辑视图是不一致的，也就是前后数据不一致。</p>
</li>
<li>
<p>官方有自带的逻辑备份工具，为什么还需要 FTWRL 呢？<br>
官方自带的 mysqldump 可以用来备份，使用参数 –single-transaction 时，会在导出数据前开启一个事务，保证视图一致性。<br>
由于 MVCC 的支持，整个过程中还可以正常更新数据。<br>
但是，MyISAM 引擎不支持事务，所以要用 FTWRL。</p>
</li>
<li>
<p>为什么不使用 set global read_only=true 的方式呢？<br>
设置全库只读时不使用该属性主要有两个原因：</p>
<ol>
<li>有些系统中，read_only 属性被用作其他逻辑。例如：判断是主库还是备库。</li>
<li>如果将 read_only 设置为 true，在发生异常后到恢复正常前的这段时间，整个库将处于只读的状态，风险较高；如果使用 FTWRL 时发生异常，MySQL 会自动释放全局锁，整个库恢复至正常可读写的状态。</li>
</ol>
</li>
</ol>
<h2 id="表级锁">表级锁</h2>
<p>表级锁通常分为两种：表锁和元数据锁。</p>
<h3 id="表锁">表锁</h3>
<p>语法是：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">lock tables <span class="keyword">TABLE</span> read<span class="operator">/</span>write;</span><br></pre></td></tr></table></figure>
<p>与 FTWRL 类似，可以使用 unlock TABLE 主动解锁，也可以在客户端断开连接时自动解锁。</p>
<p><strong>不仅会限制其他线程的读写，也限制了当前线程的操作对象。</strong></p>
<p>例如：在线程A中执行了该命令，</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">lock <span class="keyword">table</span> table1 read, table2 write;</span><br></pre></td></tr></table></figure>
<ol>
<li>其他线程的 写table1、读写table2 的操作会被禁止；</li>
<li>线程A只能执行 读table1、读写table2。</li>
</ol>
<p>对于 InnoDB 这种支持行锁的引擎，一般很少用到 lock tables 命令控制并发，整个表都被锁住的影响太大了。</p>
<h3 id="元数据锁">元数据锁</h3>
<p>元数据锁 MDL（metadata lock)，不需要显式使用，每次访问表时会自动添加，在 MySQL 5.5 版本加入。</p>
<ol>
<li>增删改查时，为该表加 MDL读锁</li>
</ol>
<p>读锁之间不互斥，所以可以有多个线程同时进行增删改查。</p>
<ol start="2">
<li>操作表结构时，加 MDL写锁</li>
</ol>
<p>读写锁之间、写锁之间是互斥的，需要保证表结构修改的安全性。</p>
<p>如果有两个线程同时给一个表添加字段，其中一个要等另一个执行完成后，才可以执行。</p>
<h4 id="MDL-的-session-阻塞问题">MDL 的 session 阻塞问题</h4>
<p>假设这里的 user_info 表是小表，有四个进程在使用：</p>
<p><img src="/pics/MySQL/MDL%E7%9A%84session%E9%98%BB%E5%A1%9E%E9%97%AE%E9%A2%98%E7%A4%BA%E6%84%8F%E5%9B%BE.png" alt="MDL的session阻塞问题示意图.png"></p>
<ol>
<li>session A 先启动，这时给表添加了 <strong>MDL 读锁</strong>；</li>
<li>session B 也需要 <strong>MDL 读锁</strong>，因此可以正常执行；</li>
<li>session C 会被 blocked，是因为 session A 的读锁还没有释放，而 session C 需要 <strong>MDL 写锁</strong>；</li>
<li>因为 session C 被阻塞，前面也提到：所有对表的增删改查操作都需要先申请 <strong>MDL 读锁</strong>；</li>
<li>现状就是，该表不可进行读写，直到 session A、B 的 <strong>MDL 读锁</strong> 释放。</li>
</ol>
<article class="message is-danger"><div class="message-header">
<p>这种情况如何安全地添加字段？</p>
</div><div class="message-body">
<ol>
<li>解决长事务<br><br>
长事务在提交前会一直占用 MDL 锁，如果要修改表结构，建议先查询是否有正在执行的长事务。<br><br></li>
<li>设置等待时间<br><br>
例如：在 ALTER 语句中设置等待时间，如果在时间内拿不到 MDL 锁，则放弃执行，避免阻塞之后的业务 SQL。</li>
</ol>
</div></article>
<h4 id="MySQL-5-6-支持-online-DDL，还会阻塞吗？">MySQL 5.6 支持 online DDL，还会阻塞吗？</h4>
<p>online DDL 的过程如下：</p>
<ol>
<li>拿到 MDL 写锁</li>
<li>降级为 MDL 读锁</li>
<li>执行 DDL</li>
<li>升级为 MDL 写锁</li>
<li>释放 MDL</li>
</ol>
<p>整个过程中只有 3 是真正处理 DDL，占用了大量时间，这期间，表可以正常读写数据；<br>
1、2、4、5如果没有锁冲突执行速度会很快。</p>
<p>前面提到例子，是在第一步就发生了阻塞。</p>
<h2 id="行锁">行锁</h2>
<p>行锁就是数据表中行记录的锁，当事务A更新了一行，事务B也要更新同一行时，必须等事务A操作完成后才可以更新。</p>
<h3 id="两阶段锁">两阶段锁</h3>
<p>两阶段锁定义：<strong>InnoDB 的事务中，行锁在需要的时候加上，事务结束时释放。（而不是不需要时立刻释放）</strong></p>
<p><img src="/pics/MySQL/%E4%B8%A4%E9%98%B6%E6%AE%B5%E9%94%81%E4%B8%BE%E4%BE%8B.png" alt="两阶段锁举例.png"></p>
<p>有事务 A 和事务 B，字段 id 为主键，在事务 A 更新完成后，事务 B 的语句会被阻塞，直到事务 A 提交后才会执行。</p>
<hr>
<p><strong>如果事务中需要锁多个行，需要把容易造成锁冲突、影响并发的锁尽量靠后。</strong></p>
<p>例如，电影购票时有下列三个操作：</p>
<ol>
<li>顾客 A 的账户余额中扣除电影票价；</li>
<li>影院 B 的账户余额中增加电影票价；</li>
<li>记录一条交易日志。</li>
</ol>
<p>完成这个交易需要进行两个 update 操作，一个 insert 操作。为了保证原子性，这三个操作必须放到一个事务里执行。</p>
<p>三个操作的顺序应该怎么排列呢？</p>
<p>如果有另一个顾客 C 要买票，事务的操作和上文中一样，则有冲突的部分就是 2。需要修改影院的账户余额，是同一行记录。</p>
<p>根据两阶段锁定义，只有事务结束时才释放锁，所以需要把受冲突的行放到最后，尽量减少行被锁的时间，提升了并发度。所以应该该着 3、1、2 的顺序排列语句。</p>
<h3 id="死锁和死锁检测">死锁和死锁检测</h3>
<p>死锁定义：<strong>当并发系统中不同线程出现循环资源依赖，设计的线程都在等待别的线程释放资源时，就会导致这几个线程进入无限等待的状态。</strong></p>
<p><img src="/pics/MySQL/%E6%AD%BB%E9%94%81%E4%B8%BE%E4%BE%8B.png" alt="死锁举例.png"></p>
<p>上图中：事务 B 等待事务 A 释放 id = ‘200’ 的行锁，事务 A 在等待事务 B 释放 id = ‘300’ 的行锁。<br>
事务 A 和事务 B 互相等待对方的资源释放，就进入了死锁状态。</p>
<h4 id="死锁的解决策略">死锁的解决策略</h4>
<ol>
<li>直接进入等待，直到超时。</li>
</ol>
<p>超时时间可以通过参数 innodb_lock_wait_timeout 设置，默认值为 50s。</p>
<p>但是在系统中，等待 50s 是无法接受的；又不能把该参数设置的特别小，比如 1s 左右，容易误伤到普通的锁等待。</p>
<ol start="2">
<li>发起死锁检测</li>
</ol>
<p>通过检测发现死锁后，主动回滚死锁链条中的某个事务，其他事务可以继续执行。<br>
设置参数 innodb_deadlock_detect 为 on（默认值即为 on），表示开启这个逻辑。</p>
<p>每当一个事务被锁时，就要循环判断它所依赖的线程是否被其他线程锁住。</p>
<p>每个新启动的、被堵住的线程都需要判断是否因为自己的加入导致了死锁，时间复杂度为 O(n)。假设同时有 1000 个并发线程同时更新一行，死锁检测是 100 万这个量级的，消耗大量 CPU 资源，事务执行速度也很慢。</p>
<h4 id="如何解决热点行更新导致的性能问题">如何解决热点行更新导致的性能问题</h4>
<p>在上面 死锁的解决策略 中的 2 提到了该问题，主要难点在于，<strong>死锁检测需要消耗大量的 CPU 资源。</strong></p>
<p>常用的解决方法有以下几种：</p>
<ol>
<li>关闭死锁检测（不推荐）</li>
</ol>
<p>业务设计时一般不会把死锁当做严重错误，如果出现死锁，就回滚事务，然后通过重试就可以了。</p>
<p>如果关闭检测，会出现大量的业务超时情况，因此不推荐该方法。</p>
<ol start="2">
<li>控制并发</li>
</ol>
<p>在 Server 层或者中间件做并发控制，限制同一时间进入更新的线程数量。</p>
<p>也就是对于热点行的更新操作，要在进入引擎前排队，减少大量的死锁检测。</p>
<ol start="3">
<li>拆行</li>
</ol>
<p>将一条记录改为逻辑上的多行，以减少锁冲突。</p>
<p>以上文的影院账户为例，将其拆分为 10 行，影院的账户总额为这 10 行记录的和，这样在每次增加金额时，只需要选择其中一行增加就可以，锁冲突的几率为 1/10，减少锁等待个数，也降低 CPU 消耗。</p>
<p>但是需要业务逻辑的详细设计，比如在退票时，其中一条记录为 0，代码需要有特殊处理。</p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">huy</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="http://example.com/55610.html">http://example.com/55610.html</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://example.com" target="_blank">征蓬</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/MySQL/">MySQL</a></div><div class="post_share"><div class="social-share" data-image="/pics/scenery/16.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://jsdelivr.pai233.top/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://jsdelivr.pai233.top/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/11256.html" title="MySQL实战 基础篇（一）"><img class="cover" src="/pics/scenery/06.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-17</div><div class="title">MySQL实战 基础篇（一）</div></div></a></div><div><a href="/48002.html" title="MySQL实战 基础篇（二）"><img class="cover" src="/pics/scenery/22.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-04-26</div><div class="title">MySQL实战 基础篇（二）</div></div></a></div><div><a href="/47099.html" title="MySQL实战 基础篇（三）"><img class="cover" src="/pics/scenery/29.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-05-12</div><div class="title">MySQL实战 基础篇（三）</div></div></a></div><div><a href="/7044.html" title="MySQL实战 基础篇（五）"><img class="cover" src="/pics/scenery/28.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-07-02</div><div class="title">MySQL实战 基础篇（五）</div></div></a></div><div><a href="/26960.html" title="MySQL常用"><img class="cover" src="/pics/scenery/29.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-08-17</div><div class="title">MySQL常用</div></div></a></div><div><a href="/51857.html" title="索引补充"><img class="cover" src="/pics/scenery/27.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-09-01</div><div class="title">索引补充</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%AE%80%E4%BB%8B-5"><span class="toc-number">1.</span> <span class="toc-text">简介</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%85%A8%E5%B1%80%E9%94%81"><span class="toc-number">2.</span> <span class="toc-text">全局锁</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98-2"><span class="toc-number">2.1.</span> <span class="toc-text">常见问题</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A1%A8%E7%BA%A7%E9%94%81"><span class="toc-number">3.</span> <span class="toc-text">表级锁</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8%E9%94%81"><span class="toc-number">3.1.</span> <span class="toc-text">表锁</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%85%83%E6%95%B0%E6%8D%AE%E9%94%81"><span class="toc-number">3.2.</span> <span class="toc-text">元数据锁</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#MDL-%E7%9A%84-session-%E9%98%BB%E5%A1%9E%E9%97%AE%E9%A2%98"><span class="toc-number">3.2.1.</span> <span class="toc-text">MDL 的 session 阻塞问题</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#MySQL-5-6-%E6%94%AF%E6%8C%81-online-DDL%EF%BC%8C%E8%BF%98%E4%BC%9A%E9%98%BB%E5%A1%9E%E5%90%97%EF%BC%9F"><span class="toc-number">3.2.2.</span> <span class="toc-text">MySQL 5.6 支持 online DDL，还会阻塞吗？</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A1%8C%E9%94%81"><span class="toc-number">4.</span> <span class="toc-text">行锁</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%A4%E9%98%B6%E6%AE%B5%E9%94%81"><span class="toc-number">4.1.</span> <span class="toc-text">两阶段锁</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%AD%BB%E9%94%81%E5%92%8C%E6%AD%BB%E9%94%81%E6%A3%80%E6%B5%8B"><span class="toc-number">4.2.</span> <span class="toc-text">死锁和死锁检测</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%AD%BB%E9%94%81%E7%9A%84%E8%A7%A3%E5%86%B3%E7%AD%96%E7%95%A5"><span class="toc-number">4.2.1.</span> <span class="toc-text">死锁的解决策略</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A6%82%E4%BD%95%E8%A7%A3%E5%86%B3%E7%83%AD%E7%82%B9%E8%A1%8C%E6%9B%B4%E6%96%B0%E5%AF%BC%E8%87%B4%E7%9A%84%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98"><span class="toc-number">4.2.2.</span> <span class="toc-text">如何解决热点行更新导致的性能问题</span></a></li></ol></li></ol></li></ol></div></div></div></div></main><footer id="footer" style="background-image: url('/pics/scenery/16.jpg')"><div id="footer-wrap"><div class="copyright">©2021 - 2022 By huy</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><div class="search-dialog__title" id="local-search-title">本地搜索</div><div id="local-input-panel"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div></div><hr/><div id="local-search-results"></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="https://lf26-cdn-tos.bytecdntp.com/cdn/expire-1-M/node-snackbar/0.1.16/snackbar.min.js"></script><script src="/js/search/local-search.js"></script><div class="js-pjax"></div><script defer="defer" id="ribbon" src="https://jsdelivr.pai233.top/npm/butterfly-extsrc@1/dist/canvas-ribbon.min.js" size="150" alpha="0.6" zIndex="-1" mobile="false" data-click="false"></script><script id="click-heart" src="https://jsdelivr.pai233.top/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="false"></script></div><!-- hexo injector body_end start --><script async src="//at.alicdn.com/t/font_2032782_8d5kxvn09md.js"></script><!-- hexo injector body_end end --></body></html>