-
Notifications
You must be signed in to change notification settings - Fork 1
/
10-MySQL事务.html
264 lines (237 loc) · 14.6 KB
/
10-MySQL事务.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
<!DOCTYPE HTML>
<html lang="zh-CN" class="sidebar-visible no-js light">
<head>
<!-- Book generated using mdBook -->
<meta charset="UTF-8">
<title>MySQL事务 - Jesse的MySQL入门</title>
<!-- Custom HTML head -->
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<meta name="description" content="">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="theme-color" content="#ffffff" />
<link rel="icon" href="favicon.svg">
<link rel="shortcut icon" href="favicon.png">
<link rel="stylesheet" href="css/variables.css">
<link rel="stylesheet" href="css/general.css">
<link rel="stylesheet" href="css/chrome.css">
<link rel="stylesheet" href="css/print.css" media="print">
<!-- Fonts -->
<link rel="stylesheet" href="FontAwesome/css/font-awesome.css">
<link rel="stylesheet" href="fonts/fonts.css">
<!-- Highlight.js Stylesheets -->
<link rel="stylesheet" href="highlight.css">
<link rel="stylesheet" href="tomorrow-night.css">
<link rel="stylesheet" href="ayu-highlight.css">
<!-- Custom theme stylesheets -->
<!-- MathJax -->
<script async type="text/javascript"
src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
</head>
<body>
<!-- Provide site root to javascript -->
<script type="text/javascript">
var path_to_root = "";
var default_theme = window.matchMedia("(prefers-color-scheme: dark)").matches ? "navy" : "light";
</script>
<!-- Work around some values being stored in localStorage wrapped in quotes -->
<script type="text/javascript">
try {
var theme = localStorage.getItem('mdbook-theme');
var sidebar = localStorage.getItem('mdbook-sidebar');
if (theme.startsWith('"') && theme.endsWith('"')) {
localStorage.setItem('mdbook-theme', theme.slice(1, theme.length - 1));
}
if (sidebar.startsWith('"') && sidebar.endsWith('"')) {
localStorage.setItem('mdbook-sidebar', sidebar.slice(1, sidebar.length - 1));
}
} catch (e) { }
</script>
<!-- Set the theme before any content is loaded, prevents flash -->
<script type="text/javascript">
var theme;
try { theme = localStorage.getItem('mdbook-theme'); } catch (e) { }
if (theme === null || theme === undefined) { theme = default_theme; }
var html = document.querySelector('html');
html.classList.remove('no-js')
html.classList.remove('light')
html.classList.add(theme);
html.classList.add('js');
</script>
<!-- Hide / unhide sidebar before it is displayed -->
<script type="text/javascript">
var html = document.querySelector('html');
var sidebar = 'hidden';
if (document.body.clientWidth >= 1080) {
try { sidebar = localStorage.getItem('mdbook-sidebar'); } catch (e) { }
sidebar = sidebar || 'visible';
}
html.classList.remove('sidebar-visible');
html.classList.add("sidebar-" + sidebar);
</script>
<nav id="sidebar" class="sidebar" aria-label="Table of contents">
<div class="sidebar-scrollbox">
<ol class="chapter"><li class="chapter-item expanded affix "><a href="index.html">README</a></li><li class="chapter-item expanded "><a href="1-SQL基础知识.html">SQL基础知识</a></li><li class="chapter-item expanded "><a href="2-MySQL安装.html">MySQL安装</a></li><li class="chapter-item expanded "><a href="3-MySQL管理.html">MySQL管理</a></li><li class="chapter-item expanded "><a href="4-数据类型.html">数据类型</a></li><li class="chapter-item expanded "><a href="5-创建数据表.html">创建数据表</a></li><li class="chapter-item expanded "><a href="6-删除数据表.html">删除数据表</a></li><li class="chapter-item expanded "><a href="7-修改数据.html">修改数据</a></li><li class="chapter-item expanded "><a href="8-查询数据表.html">查询数据表</a></li><li class="chapter-item expanded "><a href="9-MySQL数据处理.html">MySQL数据处理</a></li><li class="chapter-item expanded "><a href="10-MySQL事务.html" class="active">MySQL事务</a></li><li class="chapter-item expanded "><a href="11-建立索引.html">建立索引</a></li><li class="chapter-item expanded "><a href="12-临时表.html">临时表</a></li><li class="chapter-item expanded "><a href="13-自增序列.html">自增序列</a></li><li class="chapter-item expanded "><a href="14-重复数据.html">重复数据</a></li><li class="chapter-item expanded "><a href="15-MySQL常用函数.html">MySQL常用函数</a></li></ol>
</div>
<div id="sidebar-resize-handle" class="sidebar-resize-handle"></div>
</nav>
<div id="page-wrapper" class="page-wrapper">
<div class="page">
<div id="menu-bar-hover-placeholder"></div>
<div id="menu-bar" class="menu-bar sticky bordered">
<div class="left-buttons">
<button id="sidebar-toggle" class="icon-button" type="button" title="Toggle Table of Contents"
aria-label="Toggle Table of Contents" aria-controls="sidebar">
<i class="fa fa-bars"></i>
</button>
<button id="theme-toggle" class="icon-button" type="button" title="Change theme"
aria-label="Change theme" aria-haspopup="true" aria-expanded="false" aria-controls="theme-list">
<i class="fa fa-paint-brush"></i>
</button>
<ul id="theme-list" class="theme-popup" aria-label="Themes" role="menu">
<li role="none"><button role="menuitem" class="theme"
id="lfe-pdp">LFE PDP</button></li>
<li role="none"><button role="menuitem" class="theme"
id="light">Light (default)</button></li>
<li role="none"><button role="menuitem" class="theme"
id="rust">Rust</button></li>
<li role="none"><button role="menuitem" class="theme"
id="coal">Coal</button></li>
<li role="none"><button role="menuitem" class="theme"
id="navy">Navy</button></li>
<li role="none"><button role="menuitem" class="theme" id="ayu">Ayu</button>
</li>
</ul>
<button id="search-toggle" class="icon-button" type="button" title="Search. (Shortkey: s)"
aria-label="Toggle Searchbar" aria-expanded="false" aria-keyshortcuts="S"
aria-controls="searchbar">
<i class="fa fa-search"></i>
</button>
</div>
<h1 class="menu-title">Jesse的MySQL入门</h1>
<div class="right-buttons">
<a href="print.html" title="Print this book" aria-label="Print this book">
<i id="print-button" class="fa fa-print"></i>
</a>
<a href="https://github.com/cherryamme/mdbook_jc" title="Git repository" aria-label="Git repository">
<i id="git-repository-button" class="fa fa-github"></i>
</a>
</div>
</div>
<div id="search-wrapper" class="hidden">
<form id="searchbar-outer" class="searchbar-outer">
<input type="search" name="search" id="searchbar" name="searchbar"
placeholder="Search this book ..." aria-controls="searchresults-outer"
aria-describedby="searchresults-header">
</form>
<div id="searchresults-outer" class="searchresults-outer hidden">
<div id="searchresults-header" class="searchresults-header"></div>
<ul id="searchresults">
</ul>
</div>
</div>
<!-- Apply ARIA attributes after the sidebar and the sidebar toggle button are added to the DOM -->
<script type="text/javascript">
document.getElementById('sidebar-toggle').setAttribute('aria-expanded', sidebar === 'visible');
document.getElementById('sidebar').setAttribute('aria-hidden', sidebar !== 'visible');
Array.from(document.querySelectorAll('#sidebar a')).forEach(function (link) {
link.setAttribute('tabIndex', sidebar === 'visible' ? 0 : -1);
});
</script>
<div id="content" class="content">
<main>
<!-- order:10 -->
<h2 id="mysql-事务"><a class="header" href="#mysql-事务">MySQL 事务</a></h2>
<p>MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！</p>
<ul>
<li>在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。</li>
<li>事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。</li>
<li>事务用来管理 insert , update , delete 语句。</li>
</ul>
<p>一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性或不可分割性）、Consistency（一致性）、Isolation（隔离性或独立性）、Durability（持久性）</p>
<ul>
<li>1、**原子性：**一组事务，要么成功；要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。</li>
<li>2、<strong>一致性</strong> ： 一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。</li>
<li>3、**隔离性：**数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离可分为：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。</li>
<li>4、持久**性：**事务在处理结束后对数据做出的修改是永久的，无法丢失。</li>
</ul>
<h3 id="事务控制语句"><a class="header" href="#事务控制语句">事务控制语句</a></h3>
<p>1，显式的开始一个事务：</p>
<pre><code class="language-sql">start transaction
</code></pre>
<p>或</p>
<pre><code class="language-sql">begin
</code></pre>
<p>2, 做保存点，一个事务中可以有多个保存点：</p>
<pre><code class="language-sql">savepoint 保存点名称
</code></pre>
<p>3, 提交事务，并使数据库中进行的所有修改成为永久性的：</p>
<pre><code class="language-sql">commit
</code></pre>
<p>或</p>
<pre><code class="language-sql">commit work
</code></pre>
<p>4，回滚结束用户的事务，并撤销正在进行的所有未提交的修改：</p>
<pre><code class="language-sql">rollback
</code></pre>
<p>或</p>
<pre><code class="language-sql">rollback work
</code></pre>
<p>5.删除一个事务的保存点，若没有指定保存点，执行该语句操作会抛错。</p>
<pre><code class="language-sql">release savepoint 保存点名称
</code></pre>
<p>6.将事务滚回标记点：</p>
<pre><code class="language-sql">rollback to 标记点
</code></pre>
<p>7.设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。</p>
<pre><code class="language-sql">set transaction
</code></pre>
<h3 id="事务处理方法"><a class="header" href="#事务处理方法">事务处理方法</a></h3>
<p>1.用 begin ， rollback ， commit 来实现事务处理。</p>
<p>2.用 set 来改变 MySQL 的自动提交模式。</p>
<ul>
<li>set autocommit = 0 （禁止自动提交）。</li>
<li>set autocommit = 1 （开启自动提交）。</li>
</ul>
</main>
<nav class="nav-wrapper" aria-label="Page navigation">
<!-- Mobile navigation buttons -->
<a rel="prev" href="9-MySQL数据处理.html" class="mobile-nav-chapters previous"
title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
<i class="fa fa-angle-left"></i>
</a>
<a rel="next" href="11-建立索引.html" class="mobile-nav-chapters next"
title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
<i class="fa fa-angle-right"></i>
</a>
<div style="clear: both"></div>
</nav>
</div>
</div>
<nav class="nav-wide-wrapper" aria-label="Page navigation">
<a rel="prev" href="9-MySQL数据处理.html" class="nav-chapters previous" title="Previous chapter"
aria-label="Previous chapter" aria-keyshortcuts="Left">
<i class="fa fa-angle-left"></i>
</a>
<a rel="next" href="11-建立索引.html" class="nav-chapters next" title="Next chapter"
aria-label="Next chapter" aria-keyshortcuts="Right">
<i class="fa fa-angle-right"></i>
</a>
</nav>
</div>
<script type="text/javascript">
window.playground_copyable = true;
</script>
<script src="ace.js" type="text/javascript" charset="utf-8"></script>
<script src="editor.js" type="text/javascript" charset="utf-8"></script>
<script src="mode-rust.js" type="text/javascript" charset="utf-8"></script>
<script src="theme-dawn.js" type="text/javascript" charset="utf-8"></script>
<script src="theme-tomorrow_night.js" type="text/javascript" charset="utf-8"></script>
<script src="elasticlunr.min.js" type="text/javascript" charset="utf-8"></script>
<script src="mark.min.js" type="text/javascript" charset="utf-8"></script>
<script src="searcher.js" type="text/javascript" charset="utf-8"></script>
<script src="clipboard.min.js" type="text/javascript" charset="utf-8"></script>
<script src="highlight.js" type="text/javascript" charset="utf-8"></script>
<script src="book.js" type="text/javascript" charset="utf-8"></script>
<!-- Custom JS scripts -->
</body>
</html>