5分钟快速了解数据库死锁产生的场景和解决方法
<h3>前言</h3>
<p>
加锁（Locking）是数据库在并发访问时保证数据一致性和完整性的主要机制。任何事务都需要获得相应对象上的锁才能访问数据，读取数据的事务通常只需要获得读锁（共享锁），修改数据的事务需要获得写锁（排他锁）。当两个事务互相之间需要等待对方释放获得的资源时，如果系统不进行干预则会一直等待下去，也就是进入了死锁（deadlock）状态。</p>
<blockquote>
<p>
以下内容适用于各种常见的数据库管理系统，包括 Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL 等。</p>
</blockquote>
<h3>
死锁是如何产生的？</h3>
<p>
演示死锁的产生非常简单，我们只需要创建一个包含两行数据的简单示例表：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_775066">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">CREATE</code> <code class="sql keyword">TABLE</code> <code class="sql plain">t_lock(id </code><code class="sql keyword">int</code> <code class="sql keyword">PRIMARY</code> <code class="sql keyword">KEY</code><code class="sql plain">, col </code><code class="sql keyword">int</code><code class="sql plain">);</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">t_lock </code><code class="sql keyword">VALUES</code> <code class="sql plain">(1, 100);</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">t_lock </code><code class="sql keyword">VALUES</code> <code class="sql plain">(2, 200);</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">* </code><code class="sql keyword">FROM</code> <code class="sql plain">t_lock;</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">id|col|</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql comments">--+---+</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces"> </code><code class="sql plain">1|100|</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces"> </code><code class="sql plain">2|200|</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
如果我们在不同事务中以不同的顺序修改数据，就可能引起事务之间的相互等待。一个事务等待另一个事务释放资源不会产生什么问题，但是如果两个事务互相等待对方的资源，数据库管理系统只有两个选择：无限等待或者中止一个事务并让另一个事务成功执行。</p>
<p>
显然无限等待不是解决问题的方法，因此数据库通常是等待一定时间之后中止其中一个事务。</p>
<p>
以下是一个死锁的演示案例：</p>
<table>
<thead><tr>
<th align="left">
事务一</th>
<th align="left">
事务二</th>
<th align="left">
备注</th>
</tr></thead>
<tbody>
<tr>
<td align="left">
BEGIN;</td>
<td align="left">
BEGIN;</td>
<td align="left">
分别开始两个事务</td>
</tr>
<tr>
<td align="left">
UPDATE t_lock<br>
SET col = col + 100<br>
WHERE id = 1;</td>
<td align="left">
UPDATE t_lock<br>
SET col = col + 200<br>
WHERE id = 2;</td>
<td align="left">
事务一修改 id=1 的数据，事务二修改 id=2 的数据</td>
</tr>
<tr>
<td align="left">
UPDATE t_lock<br>
SET col = col + 100<br>
WHERE id = 2;</td>
<td align="left">
</td>
<td align="left">
事务一修改 id=2 的数据，需要等待事务二释放写锁</td>
</tr>
<tr>
<td align="left">
等待中…</td>
<td align="left">
UPDATE t_lock<br>
SET col = col + 200<br>
WHERE id = 1;</td>
<td align="left">
事务二修改 id=1 的数据，需要等待事务一释放写锁</td>
</tr>
<tr>
<td align="left">
死锁</td>
<td align="left">
死锁</td>
<td align="left">
数据库检测到死锁，选择中止一个事务</td>
</tr>
<tr>
<td align="left">
更新成功</td>
<td align="left">
返回错误</td>
<td align="left">
</td>
</tr>
</tbody>
</table>
<p>
对于 MySQL InnoDB，默认启用了 innodb_deadlock_detect 选项，事务二返回以下错误信息：</p>
<blockquote>
<p>
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction</p>
</blockquote>
<p>
如果我们禁用 InnoDB 死锁检测选项，事务二在等待 50 s（innodb_lock_wait_timeout ）后提示等待超时：</p>
<blockquote>
<p>
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</p>
</blockquote>
<p>
Oracle 检测到死锁时返回以下错误：</p>
<blockquote>
<p>
ORA-00060: 等待资源时检测到死锁</p>
</blockquote>
<p>
Microsoft SQL Server 检测到死锁时返回的错误如下</p>
<blockquote>
<p>
消息 1205，级别 13，状态 51，第 7 行<br>
事务(进程 ID 67)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。</p>
</blockquote>
<p>
PostgreSQL 检测到死锁时返回的错误如下：</p>
<blockquote>
<p>
SQL 错误 : 错误: 检测到死锁<br>
详细：进程32等待在事务 4765上的ShareLock; 由进程16552阻塞.<br>
进程16552等待在事务 4766上的ShareLock; 由进程32阻塞.<br>
建议：详细信息请查看服务器日志.<br>
在位置：当更新关系"t_lock"的元组(0, 1)时</p>
</blockquote>
<h3>
如何解决并避免死锁</h3>
<p>
死锁不是数据库自身的问题，我们无法通过优化数据库配置来解决或者避免死锁，只能通过修改应用程序来解决。简单来说，我们应该在程序中按照相同的顺序修改数据，避免产生相互等待资源的情况发生。例如：</p>
<table>
<thead><tr>
<th align="left">
事务一</th>
<th align="left">
事务二</th>
<th align="left">
备注</th>
</tr></thead>
<tbody>
<tr>
<td align="left">
BEGIN;</td>
<td align="left">
BEGIN;</td>
<td align="left">
分别开始两个事务</td>
</tr>
<tr>
<td align="left">
UPDATE t_lock<br>
SET col = col + 100<br>
WHERE id = 1;</td>
<td align="left">
<br>
UPDATE t_lock<br>
SET col = col + 200<br>
WHERE id = 1;</td>
<td align="left">
事务一和事务二都修改 id=1 的数据，后执行的事务需要等待</td>
</tr>
<tr>
<td align="left">
UPDATE t_lock<br>
SET col = col + 100<br>
WHERE id = 2;</td>
<td align="left">
等待中…</td>
<td align="left">
事务一修改 id=1 的数据，事务二等待中</td>
</tr>
<tr>
<td align="left">
COMMIT;</td>
<td align="left">
等待中…</td>
<td align="left">
事务一提交</td>
</tr>
<tr>
<td align="left">
</td>
<td align="left">
UPDATE t_lock<br>
SET col = col + 200<br>
WHERE id = 2;</td>
<td align="left">
事务二继续修改 id=2 的数据</td>
</tr>
<tr>
<td align="left">
</td>
<td align="left">
COMMIT;</td>
<td align="left">
事务二提交</td>
</tr>
</tbody>
</table>
<p>
以上场景不会产生死锁。不过，我们在实际应用中可能无法完全按照相同顺序修改数据。如果出现了不可避免的死锁情况，另一种解决方法就是捕获系统返回的死锁异常并在程序中加入重试机制。</p>
<h3>
总结</h3>
<p>
本文简要介绍了数据库死锁产生的原因和解决方法。到此这篇关于5分钟快速了解数据库死锁产生的场景和解决方法的文章就介绍到这了,更多相关数据库死锁内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://blog.csdn.net/horses/article/details/116503824</p>
頁:
[1]