Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Mailing Lists: lists.mysql.com > commits > 07 > 084145.html (Request Expert lists.mysql.com Support)

svn commit - mysqldoc@docsrva: r7629 - in trunk: . refman-5.1

From: <paul(at)mysql.com>
Date: Fri Aug 31 2007 - 14:02:07 EDT


Author: paul
Date: 2007-08-31 20:02:06 +0200 (Fri, 31 Aug 2007) New Revision: 7629

Log:
 r24433@dhcp-100: paul | 2007-08-31 13:00:24 -0500  Add work file containing InnoDB auto-inc info.

Added:

   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:24430 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: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

Property changes on: trunk/refman-5.1/innodb-autoinc-tmp.xml


Name: svn:executable

   + *

Added: trunk/refman-5.1/innodb-autoinc-tmp.xml

  • trunk/refman-5.1/innodb-autoinc-tmp.xml (rev 0) +++ trunk/refman-5.1/innodb-autoinc-tmp.xml 2007-08-31 18:02:06 UTC (rev 7629) Changed blocks: 1, Lines Added: 523, Lines Deleted: 0; 20081 bytes
Do you need help?X

@@ -0,0 +1,523 @@ 

+
+http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+  
+  %all.entities;
+]>
+

+
+  <literal>InnoDB</literal> Auto-Increment Locking
+
+  
+    Might need a different ID/title. There is already an InnoDB
+    auto-increment section that needs to be merged with this stuff.
+  
+
+  
+    Auto-Increment Scalability with InnoDB
+  
+
+  
+    Beginning with MySQL 5.1.22, InnoDB introduces a
+    new locking strategy that significantly improves scalability and
+    performance of SQL statements that add rows to tables with
+    AUTO_INCREMENT columns. This note 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
+    with replication.
+  
+
+  
+    Background
+  
+
+  
+    To prevent problems when using the binary log for statement-based
+    replication or for certain recovery scenarios,
+    InnoDB uses a special lock called the AUTOINC
+    table-level 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.
+  
+
+  
+    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

Do you need more help?X

+    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.
+  
+
+  
+    To make this clear, consider the following example that uses this
+    table:
+  
+
+
+CREATE TABLE t (
+  c1 INT(11) NOT NULL AUTO_INCREMENT,
+  c2 VARCHAR(10) DEFAULT NULL,
+  PRIMARY KEY (c1)
+) ENGINE=InnoDB;
+
+
+  
+    There are two transactions running, each inserting rows into a table
+    with an AUTO_INCREMENT 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.
+  
+
+
+Tx1: INSERT INTO t (c2) SELECT 1000 rows from another table ...
+Tx2: INSERT INTO t (c2) VALUES ('xxx');
+
+
+  
+    InnoDB 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 t can
+    execute at a time, and the generation of auto-increment numbers by
+    different statements is not interleaved. The auto-increment value

Can we help you?X

+    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 non-deterministic, and may
+    vary from run to run.
+  
+
+  
+    New locking for AUTO-INC
+  
+
+  
+    As of MySQL 5.1.22, InnoDB 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

Can't find what you're looking for?X

+    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).
+  
+
+  
+    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 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
+    not held until the statement completes, but
+    only for the duration of the allocation process.
+  
+
+  
+    This new locking scheme allows 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
+    new configuration parameter,
+    innodb_autoinc_lock_mode. Additional
+    considerations are described following the explanation of
+    auto-increment locking behavior.
+  
+
+  
+    First, some definitions:
+  

Don't know where to look next?X

+
+  
+    "INSERT-like" statements
+  
+
+  
+    Includes 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 is known when
+    the statement is initially processed. This includes single-row and
+    multiple-row INSERT, INSERT ... ON
+    DUPLICATE KEY UPDATE, and REPLACE
+    statements that do not have a nested subquery.
+  
+
+  
+    "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.
+    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');
+
+
+  
+    Configuration parameters and modes of execution
+  
+
+  
+    Is this a system variable, a command line option, what? It's said to

Confused? Frustrated?X

+    be dynamic, but is it global-only?
+  
+
+  
+    Beginning with MySQL 5.1.22, there is a new configuration parameter,
+    innodb_autoinc_lock_mode, to control the locking
+    InnoDB does when generating values for
+    AUTO_INCREMENT columns.
+  
+
+  
+    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.
+  
+
+  
+    There are three possible settings for the
+    innodb_autoinc_lock_mode parameter:
+  
+
+  
+
+    
+      
+        innodb_autoinc_lock_mode = 0
+      
+
+      
+        This is the same as the behavior before
+        innodb_autoinc_lock_mode existed (that is, it
+        is the traditional 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 mode is provided only for backward compatibility and
+        performance testing. There is little reason to use this mode

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365X

+        (unless you use "mixed-mode inserts", and care about the minor
+        difference in semantics described later).
+      
+    
+
+    
+      
+        innodb_autoinc_lock_mode = 1
+      
+
+      
+        (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.
+      
+
+      
+        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".
+      
+
+      
+        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.
+      
+
+      
+        In this mode, there is NO CHANGE in
+        semantics versus previous releases for any statement that uses
+        auto-increment, with one minor exception. Simply put,

Do you need help?X

+        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.
+      
+
+      
+        The exception where semantics differ from V5.0 is ONLY the case
+        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 "mixed-mode
+        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
+      
+
+      
+        (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 not safe when using
+        statement-based replication or recovery scenarios when SQL
+        statements are replayed from the binary log.
+      
+
+      
+        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

Do you need more help?X

+        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.
+      
+    
+
+  
+
+  
+    Usage Considerations
+  
+
+  
+    Using Auto-increment with Replication
+  
+
+  
+    If you are using statement-based replication, set
+    innodb_autoinc_lock_mode to 0 or 1, and use the
+    same value on the master and its slaves. Using
+    innodb_autoinc_lock_mode = 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.
+  
+
+  
+    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.
+  
+
+  
+    "Lost" auto-increment values
+  
+
+  
+    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

Can we help you?X

+    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 AUTO_INCREMENT column of
+    a table.
+  
+
+  
+    Auto-increment values assigned by mixed-mode inserts
+  
+
+  
+    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 c1 is an AUTO_INCREMENT
+    column of table t1, and that the most recent
+    automatically generated sequence number is 100. Consider the
+    following "mixed-mode INSERT" statement:
+  
+
+
+INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
+
+
+  
+    With innodb_autoinc_lock_mode set to 0
+    ("traditional"), the four new rows will be:
+  
+
+
+1   a
+101 b
+5   c
+102 d
+
+
+  
+    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.
+  
+
+  
+    With innodb_autoinc_lock_mode set to 1
+    ("consecutive"), the four new rows will also be:
+  
+
+
+1   a
+101 b
+5   c
+102 d
+
+
+  
+    However, in this case, the next available auto-increment value will

Can't find what you're looking for?X

+    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).
+  
+
+  
+    With innodb_autoinc_lock_mode set to mode 2
+    ("interleaved"), the four new rows will be:
+  
+
+
+1   a
+x   b
+5   c
+y   d
+
+
+  
+    The values of x and
+    y will be unique and larger than any
+    previously generated rows. However, the specific values of
+    x and y will
+    depend on the number of auto-increment values generated by
+    concurrently executing statements.
+  
+
+  
+    Finally, consider the following statement, issued when the
+    most-recently generated sequence number was the value 4:
+  
+
+
+INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
+
+
+  
+    With any setting for innodb_autoinc_lock_mode,
+    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.
+  
+
+  
+    Gaps in auto-increment values for Bulk inserts
+  
+
+  
+    When running with innodb_autoinc_lock_mode set to
+    2 ("interleaved"), there may be gaps in the auto-increment values
+    generated by "bulk inserts", but only if there are concurrently

Don't know where to look next?X

+    executing "INSERT-like" statements.
+  
+
+  
+    With innodb_autoinc_lock_mode 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.
+  
+
+

Property changes on: trunk/refman-5.1/innodb-autoinc-tmp.xml


Name: svn:executable

   + * 

-- 
MySQL Code Commits Mailing List
For list archives: 
http://lists.mysql.com/commits
To unsubscribe:    
http://lists.mysql.com/commits?unsub=lists@pantek.com
Received on Fri Aug 31 14:04:07 2007

This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 09:01:59 EDT

Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library