Mysql自增锁(Auto-inc Locks)用法及说明
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1、innode_autoinc_lock_mode=0（传统模式）</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1）特点</a></li><li><a href="#_lab2_0_1">2）问题</a></li><li><a href="#_lab2_0_2">3）易混淆点</a></li></ul><li><a href="#_label1">2、innode_autoinc_lock_mode=1（默认，连续模式）</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">1）特点</a></li><li><a href="#_lab2_1_4">2）和AUTO-INC锁对比</a></li></ul><li><a href="#_label2">3、innode_autoinc_lock_mode=2（交错模式/无锁模式）</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_5">1）特点</a></li><li><a href="#_lab2_2_6">2）例子</a></li></ul><li><a href="#_label3">总结</a></li><ul class="second_class_ul"></ul></ul></div><p>当表中存在 AUTO_INCREMENT 列时，MySQL 在生成自增值的过程中，需要一种机制保证并发插入时不产生重复 ID，这就涉及 自增锁。</p><p>这个锁用于 控制自增值分配的顺序和唯一性，而不是保护整行数据。</p>
<p>自 MySQL 5.1 起支持通过参数innodb_autoinc_lock_mode来控制自增锁模式，常见值有 0 / 1 / 2。</p>
<div class="jb51code"><pre class="brush:sql;">#查看
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
#运行时设置
SET GLOBAL innodb_autoinc_lock_mode = 2;
#配置设置
innodb_autoinc_lock_mode = 1</pre></div>
<p class="maodian"><a name="_label0"></a></p><h2>1、innode_autoinc_lock_mode=0（传统模式）</h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><p class="maodian"><a name="_lab2_1_3"></a></p><p class="maodian"><a name="_lab2_2_5"></a></p><h3>1）特点</h3>
<ul><li>插入时使用 表级 AUTO-INC 锁，也就是说：如果一个事务正在往表中插入记录，所有其他事务的插入必须等待。</li><li>串行生成自增 ID，锁一直持有到 语句结束</li></ul>
<p>说这种模式下可以保证，<span>一个事物内，一次insert插入多条，或多次insert插入数据的ID一定是连续的，不受其他事物影响（其他事物需要等待）。但是也无法保证整张表的ID都是连续的，因为存在回滚</span>。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2）问题</h3>
<ul><li>并发插入性能差，容易成为瓶颈</li></ul>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3）易混淆点</h3>
<p>innodb_autoinc_lock_mode = 0 只解决“并发插入顺序”问题，并不能解决“事务回滚回收自增 ID”的问题。</p>
<p>也就是说：</p>
<ul><li>同一事务内、多事务并发下，插入顺序是串行的</li><li>回滚的自增 ID 不会被重用，所以事务回滚仍然会导致自增 ID 跳号。</li></ul>
<p>所以，不要在代码中使用自增ID作为业务连续性的逻辑处理。</p>
<p class="maodian"><a name="_label1"></a></p><h2>2、innode_autoinc_lock_mode=1（默认，连续模式）</h2>
<p>性能和安全性这种，多数业务推荐使用。</p>
<h3>1）特点</h3>
<ul><li>Simple INSERT：几乎不加表锁</li><li>Bulk / 非确定性 INSERT：仍使用 AUTO-INC 锁</li><li>这种模式下可以支持：<span>保证同一条语句内自增值连续</span>（insert into table(a,b,c) values (), ()...）</li></ul>
<p><strong>什么是Simple INSERT？</strong></p>
<div class="jb51code"><pre class="brush:sql;">#一下为Simple INSERT
INSERT INTO t(a) VALUES (1);
INSERT INTO t(a) VALUES (1),(2);
#非 Simple INSERT
INSERT INTO t(a) SELECT ...;
LOAD DATA INFILE ...</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2）和AUTO-INC锁对比</h3>
<p>上面介绍了，AUTO-INC模式下可以保证一个事物内的插入操作产生的ID是连续的；</p>
<p>而autonic_lock_mode=1这种模式下只能保证一个insert多次插入的ID是连续的。</p>
<p>因为，在这种模式下，AUTO_INCREMENT 的分配与事务提交无关，只与“INSERT 语句执行顺序”有关，所以同一个事务内多次 INSERT，也不能保证 ID 连续</p>
<p>看一个例子：</p>
<div class="jb51code"><pre class="brush:sql;">#1.事务A插入，但是不提交，插入后id=1
BEGIN;
INSERT INTO t(v) VALUES (1);
#2.事务B插入，并提交，此时id=2
BEGIN;
INSERT INTO t(v) VALUES (2);
COMMIT;
#3.事务A继续插入，插入后id=3
INSERT INTO t(v) VALUES (3);
#4.这时在事务A中插叙，发现id为1和3
SELECT id, v FROM t;</pre></div>
<p>可以看到，因为没有了锁，ID自增器类似于一个发号器，每个insert都从这里取号。</p>
<p class="maodian"><a name="_label2"></a></p><h2>3、innode_autoinc_lock_mode=2（交错模式/无锁模式）</h2>
<h3>1）特点</h3>
<ul><li>完全不使用 AUTO-INC 表锁，多线程并发性能最好</li><li>自增值只保证唯一，不保证连续</li></ul>
<p>这种模式下，即使同一条 INSERT 内 ID 可能不连续，即：出现跳号。</p>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>2）例子</h3>
<div class="jb51code"><pre class="brush:sql;">#线程A
INSERT INTO t(a) VALUES (1),(2);
#线程B
INSERT INTO t(a) VALUES (3),(4);
#查询结果
实际 ID 可能交错：1,3,2,4</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>总结</h2>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]