| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | September 4 2007 8:34pm |
| Subject: | svn commit - mysqldoc@docsrva: r7662 - in trunk: . refman-5.1 | ||
| View as plain text | |||
Author: paul Date: 2007-09-04 20:33:58 +0200 (Tue, 04 Sep 2007) New Revision: 7662 Log: r29813@polar: paul | 2007-09-04 12:34:53 -0500 Next round of edits to InnoDB auto-inc locking update doc. Modified: trunk/refman-5.1/innodb-autoinc-tmp.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:29657 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:24433 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:20005 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:29813 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:24433 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:20005 Modified: trunk/refman-5.1/innodb-autoinc-tmp.xml =================================================================== --- trunk/refman-5.1/innodb-autoinc-tmp.xml 2007-09-04 16:41:08 UTC (rev 7661) +++ trunk/refman-5.1/innodb-autoinc-tmp.xml 2007-09-04 18:33:58 UTC (rev 7662) Changed blocks: 9, Lines Added: 371, Lines Deleted: 293; 37042 bytes @@ -19,10 +19,10 @@ </para> <para> - Beginning with MySQL 5.1.22, <literal>InnoDB</literal> introduces a - new locking strategy that significantly improves scalability and + Beginning with MySQL 5.1.23, <literal>InnoDB</literal> introduces a + locking strategy that significantly improves scalability and performance of SQL statements that add rows to tables with - <literal>AUTO_INCREMENT</literal> columns. This note provides + <literal>AUTO_INCREMENT</literal> columns. This section provides background information on this topic, explains the new locking mechanism, documents a new configuration parameter to enable or disable the new approach, and describes its behavior and interaction @@ -30,7 +30,7 @@ </para> <para> - Background + <emphasis role="bold">Background</emphasis> </para> <para> @@ -41,29 +41,29 @@ <literal>AUTO_INCREMENT</literal> 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. + a predictable and repeatable order for a given sequence of + <literal>INSERT</literal> statements. </para> <para> This means, in the case of statement-based replication, that when a - SQL statement is replayed on a slave, the same values are used for - the auto-increment column as on the master. The result of execution - of multiple INSERT statements is deterministic, and the slaves will - reproduce 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 non-deterministic, and - could not reliably be propagated to a slave server using - statement-based replication. + 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 <literal>INSERT</literal> statements + is deterministic, and the slave reproduces the same data as on the + master. If auto-increment values generated by multiple + <literal>INSERT</literal> statements were interleaved, the result of + two concurrent <literal>INSERT</literal> statements would be + non-deterministic, and could not reliably be propagated to a slave + server using statement-based replication. </para> <para> - To make this clear, consider the following example that uses this - table: + To make this clear, consider an example that uses this table: </para> <programlisting> -CREATE TABLE t ( +CREATE TABLE t1 ( c1 INT(11) NOT NULL AUTO_INCREMENT, c2 VARCHAR(10) DEFAULT NULL, PRIMARY KEY (c1) @@ -71,29 +71,32 @@ </programlisting> <para> - There are two transactions running, each inserting rows into a table - with an <literal>AUTO_INCREMENT</literal> column. One transaction is - doing a simple INSERT statement (inserting one row), and another is - using an INSERT with a subquery that inserts 1000 rows. + Suppose that there are two transactions running, each inserting rows + into a table with an <literal>AUTO_INCREMENT</literal> column. One + transaction is using an <literal>INSERT ... SELECT</literal> + statement that inserts 1000 rows, and another is using a simple + <literal>INSERT</literal> statement that inserts one row: </para> <programlisting> -Tx1: INSERT INTO t (c2) SELECT 1000 rows from another table ... -Tx2: INSERT INTO t (c2) VALUES ('xxx'); +Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ... +Tx2: INSERT INTO t1 (c2) VALUES ('xxx'); </programlisting> <para> <literal>InnoDB</literal> cannot tell in advance how many rows will - be retrieved from the subquery 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 referencing table <literal>t</literal> 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. + be retrieved from the <literal>SELECT</literal> in the + <literal>INSERT</literal> 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 + <literal>INSERT</literal> statement referring to table + <literal>t1</literal> 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 <literal>INSERT ... + SELECT</literal> statement will be consecutive, and the (single) + auto-increment value used by the <literal>INSERT</literal> statement + in Tx2 will either be smaller or larger than all those used for Tx1, + depending on which statement executes first. </para> <para> @@ -101,49 +104,52 @@ 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. + held until the end of a statement make <literal>INSERT</literal> + 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. </para> <para> 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 non-deterministic, and may - vary from run to run. + value of the auto-increment column used for the + <literal>INSERT</literal> in Tx2 depends on precisely when the + statement executes. If the <literal>INSERT</literal> of Tx2 executes + while the <literal>INSERT</literal> of Tx1 is running (rather than + before it starts or after it completes), the specific auto-increment + values assigned by the two <literal>INSERT</literal> statements are + non-deterministic, and may vary from run to run. </para> <para> - New locking for AUTO-INC + <emphasis role="bold">New Locking for Auto-increment</emphasis> </para> <para> - As of MySQL 5.1.22, <literal>InnoDB</literal> can avoid using the - table-level AUTOINC 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 - AUTOINC table-level 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). + As of MySQL 5.1.23, <literal>InnoDB</literal> can avoid using the + table-level AUTOINC lock for a class of <literal>INSERT</literal> + 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 AUTOINC table-level 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. </para> <para> - For INSERT statements where the number of rows to be inserted is - known at the beginning of processing the statement, + For <literal>INSERT</literal> statements where the number of rows to + be inserted is known at the beginning of processing the statement, <literal>InnoDB</literal> quickly allocates the required number of - auto-increment values without taking out any lock, but only if there - is no concurrent session already holding the table-level lock - AUTOINC 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 + auto-increment values without taking any lock, but only if there is + no concurrent session already holding the table-level lock AUTOINC + lock (because that other statement will be allocating auto-increment + values one-by-one as it proceeds). More precisely, such an + <literal>INSERT</literal> statement obtains auto-increment values + under the control of a mutex (a light-weight lock) that is <emphasis>not</emphasis> held until the statement completes, but only for the duration of the allocation process. </para> @@ -155,70 +161,84 @@ auto-increment works in <literal>InnoDB</literal>, the following discussion defines some terms, and explains how <literal>InnoDB</literal> behaves using different settings of the - new configuration parameter, - <literal>innodb_autoinc_lock_mode</literal>. Additional - considerations are described following the explanation of - auto-increment locking behavior. + new <literal>innodb_autoinc_lock_mode</literal> configuration + parameter. Additional considerations are described following the + explanation of auto-increment locking behavior. </para> <para> First, some definitions: </para> - <para> - "INSERT-like" statements - </para> + <itemizedlist> - <para> - Includes all statements that generate new rows in a table, including - <literal>INSERT</literal>, <literal>INSERT ... SELECT</literal>, - <literal>REPLACE</literal>, <literal>REPLACE ... SELECT</literal>, - and <literal>LOAD DATA</literal>. - </para> + <listitem> + <para> + <quote><literal>INSERT</literal>-like</quote> statements + </para> - <para> - "Simple inserts" - </para> + <para> + Includes all statements that generate new rows in a table, + including <literal>INSERT</literal>, <literal>INSERT ... + SELECT</literal>, <literal>REPLACE</literal>, <literal>REPLACE + ... SELECT</literal>, and <literal>LOAD DATA</literal>. + </para> + </listitem> - <para> - Statements for which the number of rows to be inserted is known when - the statement is initially processed. This includes single-row and - multiple-row <literal>INSERT</literal>, <literal>INSERT ... ON - DUPLICATE KEY UPDATE</literal>, and <literal>REPLACE</literal> - statements that do not have a nested subquery. - </para> + <listitem> + <para> + <quote>Simple inserts</quote> + </para> - <para> - "Bulk inserts" - </para> + <para> + 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 + <literal>INSERT</literal>, <literal>INSERT ... ON DUPLICATE KEY + UPDATE</literal>, and <literal>REPLACE</literal> statements that + do not have a nested subquery. + </para> + </listitem> - <para> - 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 <literal>INSERT ... SELECT</literal>, <literal>REPLACE - ... SELECT</literal>, and <literal>LOAD DATA</literal> statements. - <literal>InnoDB</literal> will assign new values for the - <literal>AUTO_INCREMENT</literal> column one at a time as each row - is processed. - </para> + <listitem> + <para> + <quote>Bulk inserts</quote> + </para> - <para> - "Mixed-mode inserts" - </para> + <para> + 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 <literal>INSERT ... SELECT</literal>, + <literal>REPLACE ... SELECT</literal>, and <literal>LOAD + DATA</literal> statements. <literal>InnoDB</literal> will assign + new values for the <literal>AUTO_INCREMENT</literal> column one + at a time as each row is processed. + </para> + </listitem> - <para> - These are "simple INSERT" statements that specify the auto-increment - value for some (but not all) of the new rows. An example follows, - where <literal>c1</literal> is an <literal>AUTO_INCREMENT</literal> - column of table <literal>t1</literal>: - </para> + <listitem> + <para> + <quote>Mixed-mode inserts</quote> + </para> + <para> + These are <quote>simple insert</quote> statements that specify + the auto-increment value for some (but not all) of the new rows. + An example follows, where <literal>c1</literal> is an + <literal>AUTO_INCREMENT</literal> column of table + <literal>t1</literal>: + </para> + <programlisting> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); </programlisting> + </listitem> + </itemizedlist> + <para> - Configuration parameters and modes of execution + <emphasis role="bold">Configuration Parameters and Execution + Modes</emphasis> </para> <remark role="todo"> @@ -227,19 +247,22 @@ </remark> <para> - Beginning with MySQL 5.1.22, there is a new configuration parameter, - <literal>innodb_autoinc_lock_mode</literal>, to control the locking - <literal>InnoDB</literal> does when generating values for - <literal>AUTO_INCREMENT</literal> columns. + Beginning with MySQL 5.1.23, there is a new configuration parameter + that controls how <literal>InnoDB</literal> using locking when + generating values for <literal>AUTO_INCREMENT</literal> columns. + This parameter can be set using the + <option>--innodb-autoinc-lock-mode</option> option at + <command>mysqld</command> startup, or at runtime by setting the + <literal>innodb_autoinc_lock_mode</literal> system variable. + Although this variable can be changed at runtime, the behavior is + undefined if you do so while transactions that generate + auto-increment values are executing. </para> <para> In general, if you encounter problems with the way auto-increment works (which will most likely involve replication), you can force - use of the old behavior by setting the locking mode to 0. The - parameter can be changed at runtime, but the behavior is undefined - if its value is changed while transactions that generate - auto-increment values are executing. + use of the old behavior by setting the lock mode to 0. </para> <para> @@ -252,115 +275,126 @@ <listitem> <para> <literal>innodb_autoinc_lock_mode = 0</literal> + (<quote>traditional</quote> lock mode) </para> <para> - This is the same as the behavior before - <literal>innodb_autoinc_lock_mode</literal> existed (that is, it - is the <quote>traditional</quote> behavior). For all - "INSERT-like" statements, a special AUTOINC table-level 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 (although "gaps" can exist within a table if a - transaction that generated auto-increment values is rolled back, - as discussed later). + This lock mode provides the same behavior as before + <literal>innodb_autoinc_lock_mode</literal> existed. For all + <quote><literal>INSERT</literal>-like</quote> statements, a + special AUTOINC table-level 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 (although + <quote>gaps</quote> can exist within a table if a transaction + that generated auto-increment values is rolled back, as + discussed later). </para> <para> - This mode is provided only for backward compatibility and - performance testing. There is little reason to use this mode - (unless you use "mixed-mode inserts", and care about the minor - difference in semantics described later). + This lock mode is provided only for backward compatibility and + performance testing. There is little reason to use this lock + mode, unless you use <quote>mixed-mode inserts</quote> and care + about the minor difference in semantics described later. </para> </listitem> <listitem> <para> <literal>innodb_autoinc_lock_mode = 1</literal> + (<quote>consecutive</quote> lock mode) </para> <para> - (aka "CONSECUTIVE") This is the default mode. In this mode, - "bulk inserts" use the special table-level AUTIONC table-level - lock, and hold it until the end of the statement. This applies - to all INSERT ... SELECT, LOAD DATA, REPLACE, and REPLACE ... - SELECT statements. Only one statement holding the AUTOINC lock - can execute at a time. + This is the default lock mode. In this mode, <quote>bulk + inserts</quote> use the special table-level AUTIONC table-level + lock and holds it until the end of the statement. This applies + to all <literal>INSERT ... SELECT</literal>, <literal>LOAD + DATA</literal>, <literal>REPLACE</literal>, and <literal>REPLACE + ... SELECT</literal> statements. Only one statement holding the + AUTOINC lock can execute at a time. </para> <para> - With this 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 AUTOINC table-level lock is used, - unless an AUTOINC lock is held by another transaction. In the - case that another transaction holds an AUTOINC lock, a "simple - INSERT" waits for the AUTOINC lock, as if it too were a "bulk - INSERT". + With this lock mode, <quote>simple inserts</quote> (only) use a + new locking model where a light-weight mutex is used during the + allocation of auto-increment values, and no AUTOINC table-level + lock is used, unless an AUTOINC lock is held by another + transaction. If another transaction does hold an AUTOINC lock, a + <quote>simple insert</quote> waits for the AUTOINC lock, as if + it too were a <quote>bulk insert.</quote> </para> <para> - This locking 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 that operations are - safe for statement-based replication. + This lock mode ensures that, in the presence of + <literal>INSERT</literal> 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 <quote><literal>INSERT</literal>-like</quote> + statement are consecutive, and operations are safe for + statement-based replication. </para> <para> - <emphasis role="bold">In this mode, there is NO CHANGE in - semantics versus previous releases for any statement that uses - auto-increment, with one minor exception.</emphasis> Simply put, - the important impact of this mode is significantly better - scalability. This mode is safe for use with statement-based - replication. Further, as with V5.0, auto-increment numbers - assigned by any given statement are CONSECUTIVE. + 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 + <quote>traditional</quote> lock mode, auto-increment numbers + assigned by any given statement are + <emphasis>consecutive</emphasis>. In this mode, there is + <emphasis>no change</emphasis> in semantics compared to + <quote>traditional</quote> mode for any statement that uses + auto-increment, with one minor exception. </para> <para> - The exception where semantics differ from V5.0 is ONLY the case - where the user provides explicit values for an - <literal>AUTO_INCREMENT</literal> column for some, but not all, - rows in a multiple-row "simple INSERT". For such "mixed-mode - inserts", <literal>InnoDB</literal> 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. + The only exception where the semantics differ from + <quote>traditional</quote> mode is when the user provides + explicit values for an <literal>AUTO_INCREMENT</literal> column + for some, but not all, rows in a multiple-row <quote>simple + insert.</quote> For such <quote>mixed-mode inserts,</quote> + <literal>InnoDB</literal> 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. <quote>Excess</quote> + numbers are lost. </para> </listitem> <listitem> <para> <literal>innodb_autoinc_lock_mode = 2</literal> + (<quote>interleaved</quote> lock mode) </para> <para> - (aka "INTERLEAVED") In this mode, no "INSERT-like" statements - use the table-level AUTOINC lock, and multiple statements can - execute at the same time. This is the fastest and most scalable - mode, but it is <emphasis>not</emphasis> safe when using + In this lock mode, no + <quote><literal>INSERT</literal>-like</quote> statements use the + table-level AUTOINC lock, and multiple statements can execute at + the same time. This is the fastest and most scalable lock mode, + but it is <emphasis>not safe</emphasis> when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log. </para> <para> - In this 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. + In this lock mode, auto-increment values are guaranteed to be + unique and monotonically increasing across all concurrently + executing <quote><literal>INSERT</literal>-like</quote> + statements. However, because multiple statements can be + generating numbers at the same time (that is, allocation of + numbers is <emphasis>interleaved</emphasis> across statements), + the values generated for the rows inserted by any given + statement may not be consecutive. </para> <para> - 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 + If the only statements executing are <quote>simple + inserts</quote> 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 <quote>mixed-mode + inserts.</quote> However, when <quote>bulk inserts</quote> are + executed, there may be gaps in the auto-increment values assigned by any given statement. </para> </listitem> @@ -368,156 +402,200 @@ </itemizedlist> <para> - Usage Considerations + <emphasis role="bold">Usage Considerations</emphasis> </para> <para> - Using Auto-increment with Replication + The auto-increment locking modes have several implications: </para> - <para> - If you are using statement-based replication, set - <literal>innodb_autoinc_lock_mode</literal> to 0 or 1, and use the - same value on the master and its slaves. Using - <literal>innodb_autoinc_lock_mode</literal> = 2 ("interleaved"), or - configurations where the master and slaves do not use the same lock - mode, will not ensure that auto-increment values will be the same on - the slaves as on the master. - </para> + <itemizedlist> - <para> - Row-based replication is not sensitive to the order of execution of - the SQL statements. Therefore, all of the auto-increment locking - modes are safe with row-based replication. - </para> + <listitem> + <para> + Using auto-increment with replication + </para> - <para> - "Lost" auto-increment values - </para> + <para> + If you are using statement-based replication, you should set + <literal>innodb_autoinc_lock_mode</literal> to 0 or 1 and use + the same value on the master and its slaves. Auto-increment + values are not ensured to be the same on the slaves as on the + master if you use <literal>innodb_autoinc_lock_mode</literal> = + 2 (<quote>interleaved</quote>) or configurations where the + master and slaves do not use the same lock mode. + </para> - <para> - In ALL locking modes (0, 1, and 2), if a transaction that generated - auto-increment values rolls back, those auto-increment values are - "lost". Once a value is generated for an auto-increment column, it - cannot be rolled back, whether or not the INSERT-like statement is - completed, and whether or not the containing transaction is rolled - back. Such lost values are not reused. Thus, there may be gaps in - the values stored in an <literal>AUTO_INCREMENT</literal> column of - a table. - </para> + <para> + If you are using row-based replication, all of the + auto-increment lock modes are safe. Row-based replication is not + sensitive to the order of execution of the SQL statements. + </para> + </listitem> - <para> - Auto-increment values assigned by mixed-mode inserts - </para> + <listitem> + <para> + <quote>Lost</quote> auto-increment values + </para> - <para> - Consider "mixed-mode inserts", where a "simple INSERT" specifies the - auto-increment value for some (but not all) resulting rows. Such - statements will behave differently in modes 0, 1 and 2. For example, - assume <literal>c1</literal> is an <literal>AUTO_INCREMENT</literal> - column of table <literal>t1</literal>, and that the most recent - automatically generated sequence number is 100. Consider the - following "mixed-mode INSERT" statement: - </para> + <para> + In all lock modes (0, 1, and 2), if a transaction that generated + auto-increment values rolls back, those auto-increment values + are <quote>lost.</quote> Once a value is generated for an + auto-increment column, it cannot be rolled back, whether or not + the <quote><literal>INSERT</literal>-like</quote> statement is + completed, and whether or not the containing transaction is + rolled back. Such lost values are not reused. Thus, there may be + gaps in the values stored in an + <literal>AUTO_INCREMENT</literal> column of a table. + </para> + </listitem> + <listitem> + <para> + Auto-increment values assigned by <quote>mixed-mode + inserts</quote> + </para> + + <para> + Consider a <quote>mixed-mode insert,</quote> where a + <quote>simple insert</quote> specifies the auto-increment value + for some (but not all) resulting rows. Such a statement will + behave differently in lock modes 0, 1, and 2. For example, + assume <literal>c1</literal> is an + <literal>AUTO_INCREMENT</literal> column of table + <literal>t1</literal>, and that the most recent automatically + generated sequence number is 100. Consider the following + <quote>mixed-mode insert</quote> statement: + </para> + <programlisting> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); </programlisting> - <para> - With <literal>innodb_autoinc_lock_mode</literal> set to 0 - ("traditional"), the four new rows will be: - </para> + <para> + With <literal>innodb_autoinc_lock_mode</literal> set to 0 + (<quote>traditional</quote>), the four new rows will be: + </para> <programlisting> -1 a -101 b -5 c -102 d +<!-- +mysql> DROP TABLE IF EXISTS t1; +mysql> CREATE TABLE t1 (c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(1)); +mysql> INSERT INTO t1 VALUES(1,'a'),(101,'b'),(5,'c'),(102,'d'); +mysql> SELECT c1, c2 FROM t1; +--> ++-----+------+ +| c1 | c2 | ++-----+------+ +| 1 | a | +| 101 | b | +| 5 | c | +| 102 | d | ++-----+------+ </programlisting> - <para> - The next available auto-increment value will be 103. This is because - the auto-increment values are allocated one at a time, not all at - once at the beginning of statement execution. This result is true - whether there are concurrently executing "INSERT-like" statements - (of any type) or not. - </para> + <para> + The next available auto-increment value will be 103 because the + auto-increment values are allocated one at a time, not all at + once at the beginning of statement execution. This result is + true whether or not there are concurrently executing + <quote><literal>INSERT</literal>-like</quote> statements (of any + type). + </para> - <para> - With <literal>innodb_autoinc_lock_mode</literal> set to 1 - ("consecutive"), the four new rows will also be: - </para> + <para> + With <literal>innodb_autoinc_lock_mode</literal> set to 1 + (<quote>consecutive</quote>), the four new rows will also be: + </para> <programlisting> -1 a -101 b -5 c -102 d ++-----+------+ +| c1 | c2 | ++-----+------+ +| 1 | a | +| 101 | b | +| 5 | c | +| 102 | d | ++-----+------+ </programlisting> - <para> - However, in this case, the next available auto-increment value will - be 105, not 103. This is because four auto-increment values are - allocated at the time the statement is processed, but only two are - used. This result is true whether or not there are concurrently - executing "INSERT-like" statements (of any type). - </para> + <para> + However, in this case, the next available auto-increment value + will be 105, not 103 because four auto-increment values are + allocated at the time the statement is processed, but only two + are used. This result is true whether or not there are + concurrently executing + <quote><literal>INSERT</literal>-like</quote> statements (of any + type). + </para> - <para> - With <literal>innodb_autoinc_lock_mode</literal> set to mode 2 - ("interleaved"), the four new rows will be: - </para> + <para> + With <literal>innodb_autoinc_lock_mode</literal> set to mode 2 + (<quote>interleaved</quote>), the four new rows will be: + </para> <programlisting> -1 a -<replaceable>x</replaceable> b -5 c -<replaceable>y</replaceable> d ++-----+------+ +| c1 | c2 | ++-----+------+ +| 1 | a | +| <replaceable>x</replaceable> | b | +| 5 | c | +| <replaceable>y</replaceable> | d | ++-----+------+ </programlisting> - <para> - The values of <replaceable>x</replaceable> and - <replaceable>y</replaceable> will be unique and larger than any - previously generated rows. However, the specific values of - <replaceable>x</replaceable> and <replaceable>y</replaceable> will - depend on the number of auto-increment values generated by - concurrently executing statements. - </para> + <para> + The values of <replaceable>x</replaceable> and + <replaceable>y</replaceable> will be unique and larger than any + previously generated rows. However, the specific values of + <replaceable>x</replaceable> and <replaceable>y</replaceable> + will depend on the number of auto-increment values generated by + concurrently executing statements. + </para> - <para> - Finally, consider the following statement, issued when the - most-recently generated sequence number was the value 4: - </para> + <para> + Finally, consider the following statement, issued when the + most-recently generated sequence number was the value 4: + </para> <programlisting> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); </programlisting> - <para> - With any setting for <literal>innodb_autoinc_lock_mode</literal>, - this statement will generate a "duplicate key" error 23000 ("Can't - write; duplicate key in table") because 5 will be allocated for the - row (NULL, 'b') and insertion of the row (5, 'c') will fail. - </para> + <para> + With any <literal>innodb_autoinc_lock_mode</literal> setting, + this statement will generate a duplicate-key error 23000 + (<literal>Can't write; duplicate key in table</literal>) because + 5 will be allocated for the row <literal>(NULL, 'b')</literal> + and insertion of the row <literal>(5, 'c')</literal> will fail. + </para> + </listitem> - <para> - Gaps in auto-increment values for Bulk inserts - </para> + <listitem> + <para> + Gaps in auto-increment values for <quote>bulk inserts</quote> + </para> - <para> - When running with <literal>innodb_autoinc_lock_mode</literal> set to - 2 ("interleaved"), there may be gaps in the auto-increment values - generated by "bulk inserts", but only if there are concurrently - executing "INSERT-like" statements. - </para> + <para> + With <literal>innodb_autoinc_lock_mode</literal> set to 0 + (<quote>traditional</quote>) or 1 (<quote>consecutive</quote>), + the auto-increment values generated by any given statement will + be consecutive, without gaps, because the table-level AUTOINC + lock is held until the end of the statement, and only one such + statement can execute at a time. + </para> - <para> - With <literal>innodb_autoinc_lock_mode</literal> set to 0 - ("traditional") or 1 ("consecutive"), the auto-increment values - generated by any given statement will be consecutive, without gaps, - because the table-level AUTOINC lock is held until the end of the - statement, and only one such statement can execute at a time. - </para> + <para> + With <literal>innodb_autoinc_lock_mode</literal> set to 2 + (<quote>interleaved</quote>), there may be gaps in the + auto-increment values generated by <quote>bulk inserts,</quote> + but only if there are concurrently executing + <quote><literal>INSERT</literal>-like</quote> statements. + </para> + </listitem> + </itemizedlist> + </section>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r7662 - in trunk: . refman-5.1 | paul | 4 Sep |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.876 sec. using MySQL 5.1.14-beta-log