文档章节

MySQL/InnoDB处理AUTO_INCREMENT(二)

Airship
 Airship
发布于 2016/01/23 19:47
字数 1682
阅读 48
收藏 0

MySQL/InnoDB处理AUTO_INCREMENT(二)


http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html




Configurable InnoDB Auto-Increment Locking

    As described in the previous section, InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable(可预言的,可预料的) and repeatable(反复的) order for a given sequence of INSERT statements.




    In the case of statement-based replication(主从复制,同步), this means that when an SQL statement is replicated(复制) on a slave server, the same values are used for the auto-increment column as on the master server. The result of execution of multiple INSERT statements is deterministic(确定性的), and the slave reproduces the same data as on the master. If auto-increment values generated by multiple INSERT statements were interleaved(交叉存取的,隔行扫描的), the result of two concurrent INSERT statements would be nondeterministic(不确定性的), and could not reliably(依靠依赖) be propagated(传播) to a slave server using statement-based replication.




    To make this clear, consider an example that uses this table:


?

1

2

3

4

5

CREATE TABLE t1 (

  c1 INT(11) NOT NULL AUTO_INCREMENT,

  c2 VARCHAR(10) DEFAULT NULL,

  PRIMARY KEY (c1)

) ENGINE=InnoDB;

    Suppose that there are two transactions running, each inserting rows into a table with an AUTO_INCREMENT column. One transaction is using an INSERT ... SELECT statement that inserts 1000 rows, and another is using a simple INSERT statement that inserts one row:


Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...


Tx2: INSERT INTO t1 (c2) VALUES ('xxx');


    InnoDB cannot tell in advance(提前) how many rows will be retrieved(取回) from the SELECT in the INSERT statement in Tx1, and it assigns(分配) the auto-increment values one at a time as the statement proceeds(前进). With a table-level lock, held to the end of the statement, only one INSERT statement referring to table t1 can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved(交叉存储的). The auto-increment value generated by the Tx1 INSERT ... SELECT statement will be consecutive(连续的,连贯的), and the (single) auto-increment value used by the INSERT statement in Tx2 will either be smaller or larger than all those used for Tx1, depending on which statement executes first.




    As long as the SQL statements execute in the same order when replayed(重放) from the binary log (when using statement-based replication, or in recovery scenarios(场景)), the results will be the same as they were when Tx1 and Tx2 first ran. Thus, table-level locks held until the end of a statement make INSERT statements using auto-increment safe for use with statement-based replication. However, those locks limit concurrency and scalability(可扩展性,可伸缩性) when multiple transactions are executing insert statements at the same time.




    In the preceding example, if there were no table-level lock, the value of the auto-increment column used for the INSERT in Tx2 depends on precisely(精确的,严谨的) when the statement executes. If the INSERT of Tx2 executes while the INSERT of Tx1 is running (rather than before it starts or after it completes), the specific auto-increment values assigned by the two INSERT statements are nondeterministic(不确定性的), and may vary(不同) from run to run.




    InnoDB can avoid using the table-level AUTO-INC lock for a class of INSERT statements where the number of rows is known in advance, and still preserve(保持,保存) deterministic(确定性的) execution and safety for statement-based replication. Further, if you are not using the binary log to replay SQL statements as part of recovery or replication, you can entirely(完全地) eliminate(排除) use of the table-level AUTO-INC lock for even greater concurrency and performance, at the cost of permitting(允许) gaps in auto-increment numbers assigned by a statement and potentially(潜在的可能的) having the numbers assigned by concurrently executing statements interleaved.




    For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values without taking any lock, but only if there is no concurrent session already holding the table-level AUTO-INC lock (because that other statement will be allocating auto-increment values one-by-one as it proceeds). More precisely(更准确的说), such an INSERT statement obtains(获得) auto-increment values under the control of a mutex (a light-weight lock) that is not held until the statement completes, but only for the duration of the allocation process.




    This new locking scheme enables much greater scalability, but it does introduce some subtle(微妙的) differences in how auto-increment values are assigned compared to the original mechanism. To describe the way auto-increment works in InnoDB, the following discussion defines some terms, and explains how InnoDB behaves using different settings of the innodb_autoinc_lock_mode configuration parameter, which you can set at server startup. Additional considerations are described following the explanation of auto-increment locking behavior.




First, some definitions:


“INSERT-like” statements


All statements that generate new rows in a table, including INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA.




“Simple inserts”


Statements for which the number of rows to be inserted can be determined in advance (when the statement is initially processed). This includes single-row and multiple-row INSERT and REPLACE statements that do not have a nested subquery, but not INSERT ... ON DUPLICATE KEY UPDATE.




“Bulk inserts”


Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements, but not plain INSERT. InnoDB will assign new values for the AUTO_INCREMENT column one at a time as each row is processed.




“Mixed-mode inserts”


These are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows. An example follows, where c1 is an AUTO_INCREMENT column of table t1:


INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');


Another type of “mixed-mode insert” is INSERT ... ON DUPLICATE KEY UPDATE, which in the worst case is in effect an INSERT followed by a UPDATE, where the allocated value for the AUTO_INCREMENT column may or may not be used during the update phase.




There are three possible settings for the innodb_autoinc_lock_mode parameter:




innodb_autoinc_lock_mode = 0 (“traditional” lock mode)


    This lock mode provides the same behavior as before innodb_autoinc_lock_mode existed. For all “INSERT-like” statements, a special table-level AUTO-INC lock is obtained and held to the end of the statement. This assures(确保) that the auto-increment values assigned by any given statement are consecutive(连续的).




This lock mode is provided for:


Backward compatibility.


Performance testing.


Working around issues with “mixed-mode inserts”, due to the possible differences in semantics described later.




innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)


default lock mode


    This is the default lock mode. In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.




    With this lock mode, “simple inserts” (only) use a new locking model where a light-weight mutex is used during the allocation of auto-increment values, and no table-level AUTO-INC lock is used, unless an AUTO-INC lock is held by another transaction. If another transaction does hold an AUTO-INC lock, a “simple insert” waits for the AUTO-INC lock, as if it too were a “bulk insert”.




    This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “INSERT-like” statement are consecutive, and operations are safe for statement-based replication.




    Simply put, the important impact of this lock mode is significantly better scalability. This mode is safe for use with statement-based replication. Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. In this mode, there is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.




    The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.




innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)


    In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.



    In this lock mode, auto-increment values are guaranteed(有保证的) to be unique and monotonically increasing across all concurrently executing “INSERT-like” statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive.




    If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there will be no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”. However, when “bulk inserts” are executed, there may be gaps in the auto-increment values assigned by any given statement.


========================END========================


本文转载自:http://my.oschina.net/xinxingegeya/blog/342075?fromerr=NGQjXEFN

Airship
粉丝 43
博文 994
码字总数 20464
作品 0
南京
高级程序员
私信 提问

暂无文章

yii2做输入跳转到分页【未测试】

首先使用引用分页类 1、 1 use yii\data\Pagination; 2、控制器 1 //分页跳转 2 public function actionPagego(){ 3 $model = Knowledge::find(); 4 //原生sql语......

dragon_tech
20分钟前
0
0
实时计算轻松上手,阿里云DataWorks Stream Studio正式发布

Stream Studio是DataWorks旗下重磅推出的全新子产品。已于2019年4月18日正式对外开放使用。Stream Studi是一站式流计算开发平台,基于阿里巴巴实时计算引擎Flink构建,集可视化拖拽DAG和SQL...

阿里云官方博客
20分钟前
2
0
【阿里云新品发布·周刊】第9期:实时大数据开发难、运维难、应用难?来,一站解决!

点击订阅新品发布会! 新产品、新版本、新技术、新功能、价格调整,评论在下方,下期更新!关注更多内容,了解更多 最新发布 DataWorks Stream Studio重磅发布 2019年5月15日15时,阿里云Dat...

zhaowei121
24分钟前
0
0
常见的Web攻击手段——CSRF攻击

一、什么是CSRF攻击? 跨站请求伪造(Cross-Site Request Forgery, CSRF),恶意网站通过脚本向当前用户浏览器打开的其它页面的 URL 发起恶意请求,由于同一浏览器进程下 Cookie 可见性,导致用...

Lienson
25分钟前
1
0
H3C设备网络组建和设置

路由器: ER8300 交换机: S3600v2 说明:这次网络配置主要是模拟,这套网络是挂在办公室网络下的.由于办公室给我的网络是:192.168.3.0/24, 网关是:192.168.3.254. 所以我们规划如下: ER8300 wa...

WinkJie
26分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部