Author: paul
Date: 2008-11-26 19:07:38 +0100 (Wed, 26 Nov 2008)
New Revision: 12675
Log:
r36187@frost: paul | 2008-11-26 12:07:53 -0500
InnoDB general revisions
Modified:
trunk/refman-4.1/se-innodb-core.xml
trunk/refman-5.0/se-innodb-core.xml
trunk/refman-5.1/se-innodb-core.xml
trunk/refman-6.0/se-innodb-core.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:36186
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34596
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:36187
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34596
Modified: trunk/refman-4.1/se-innodb-core.xml
===================================================================
--- trunk/refman-4.1/se-innodb-core.xml 2008-11-26 18:07:28 UTC (rev 12674)
+++ trunk/refman-4.1/se-innodb-core.xml 2008-11-26 18:07:38 UTC (rev 12675)
Changed blocks: 9, Lines Added: 52, Lines Deleted: 44; 8641 bytes
@@ -1750,8 +1750,10 @@
</indexterm>
<para>
- This variable controls whether <literal>InnoDB</literal> uses
- gap locking for searches and index scans.
+ This variable affects how <literal>InnoDB</literal> uses gap
+ locking for searches and index scans. (Gap locking also
+ depends on the transaction isolation level; see
+ <xref linkend="set-transaction"/>.)
</para>
<para>
@@ -1772,38 +1774,29 @@
</para>
<para>
- controls whether <literal>InnoDB</literal> uses gap searches
- and index scans. By default, the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- 0 (disabled), which means that gap locking is enabled:
+ By default, the value of
+ <literal>innodb_locks_unsafe_for_binlog</literal> is 0
+ (disabled), which means that gap locking is enabled:
<literal>InnoDB</literal> uses next-key locks (index-record
- locks plus gap locks). To enable the variable, set it to 1.
- This causes gap locking to be disabled for searches and index
- scans: <literal>InnoDB</literal> uses only index-record locks.
+ locks plus gap locks) for searches and index scans. To enable
+ the variable, set it to 1. This causes gap locking to be
+ disabled: <literal>InnoDB</literal> uses only index-record
+ locks for searches and index scans.
</para>
<para>
- Use of gap locking is also dependent on the transaction
- isolation level (see <xref linkend="set-transaction"/>).
- </para>
-
- <para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- does not disable the use of next-key locking for foreign-key
- constraint checking or duplicate-key checking. To affect those
- types of checking, set the
- <literal role="sysvar">foreign_key_checks</literal> and
- <literal role="sysvar">unique_checks</literal> session
- variables (see <xref linkend="innodb-tuning"/>).
+ does not disable the use of gap locking for foreign-key
+ constraint checking or duplicate-key checking.
</para>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
- cause phantom problems because when gap locking is disabled,
- other sessions can insert new rows into the gaps. Suppose that
+ cause phantom problems because other sessions can insert new
+ rows into the gaps when gap locking is disabled. Suppose that
there is an index on the <literal>id</literal> column of the
<literal>child</literal> table and that you want to read and
- lock all rows from the table with an identifier value larger
+ lock all rows from the table having an identifier value larger
than 100, with the intention of updating some column in the
selected rows later:
</para>
@@ -3922,7 +3915,11 @@
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If autocommit mode is enabled, each SQL statement
forms a single transaction on its own. By default, MySQL starts
- the session for each new connection with autocommit enabled.
+ the session for each new connection with autocommit enabled, so
+ MySQL does a commit after each SQL statement if that statement did
+ not return an error. If a statement returns an error, the commit
+ or rollback behavior depends on the error. See
+ <xref linkend="innodb-error-handling"/>.
</para>
<para>
@@ -3931,19 +3928,23 @@
<literal role="stmt" condition="commit">START
TRANSACTION</literal> or
<literal role="stmt" condition="commit">BEGIN</literal> statement
- and ending it with <literal role="stmt">COMMIT</literal> or
- <literal role="stmt" condition="commit">ROLLBACK</literal>.
+ and ending it with a <literal role="stmt">COMMIT</literal> or
+ <literal role="stmt" condition="commit">ROLLBACK</literal>
+ statement.
</para>
<para>
If autocommit mode is disabled within a session with <literal>SET
autocommit = 0</literal>, the session always has a transaction
- open. An SQL <literal role="stmt">COMMIT</literal> or
+ open. A <literal role="stmt">COMMIT</literal> or
<literal role="stmt" condition="commit">ROLLBACK</literal>
- statement ends the current transaction and a new one starts. A
- <literal role="stmt">COMMIT</literal> means that the changes made
- in the current transaction are made permanent and become visible
- to other sessions. A
+ statement ends the current transaction and a new one starts.
+ </para>
+
+ <para>
+ A <literal role="stmt">COMMIT</literal> means that the changes
+ made in the current transaction are made permanent and become
+ visible to other sessions. A
<literal role="stmt" condition="commit">ROLLBACK</literal>
statement, on the other hand, cancels all modifications made by
the current transaction. Both
@@ -4034,8 +4035,8 @@
the gap immediately before the index record. A next-key lock
refers to a lock that locks an index record and the gap before it.
A gap lock refers to a lock that locks only the gap before some
- index record. Next-key locking for searches or index scans can be
- disabled by enabling the
+ index record. Use of gap locking for searches or index scans can
+ be disabled by enabling the
<literal>innodb_locks_unsafe_for_binlog</literal> system variable.
</para>
@@ -4336,7 +4337,7 @@
changes made by later or uncommitted transactions. The exception
to this rule is that the query sees the changes made by earlier
statements within the same transaction. This exception causes
- the following anomaly: if you update some rows in a table, a
+ the following anomaly: If you update some rows in a table, a
<literal role="stmt">SELECT</literal> will see the latest
version of the updated rows, but it might also see older
versions of any rows. If other users simultaneously update the
@@ -4345,15 +4346,22 @@
</para>
<para>
- If you are running with the default
- <literal role="isolevel">REPEATABLE READ</literal> isolation
- level, all consistent reads within the same transaction read the
- snapshot established by the first such read in that transaction.
- You can get a fresher snapshot for your queries by committing
- the current transaction and after that issuing new queries.
+ If the transaction isolation level is
+ <literal role="isolevel">REPEATABLE READ</literal> (the default
+ level), all consistent reads within the same transaction read
+ the snapshot established by the first such read in that
+ transaction. You can get a fresher snapshot for your queries by
+ committing the current transaction and after that issuing new
+ queries.
</para>
<para>
+ With <literal role="isolevel">READ COMMITTED</literal> isolation
+ level, each consistent read within a transaction sets and reads
+ its own fresh snapshot.
+ </para>
+
+ <para>
Consistent read is the default mode in which
<literal>InnoDB</literal> processes
<literal role="stmt">SELECT</literal> statements in
@@ -4626,7 +4634,7 @@
<listitem>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- disables gap locking for searchs and index scans.
+ disables gap locking for searches and index scans.
</para>
</listitem>
@@ -5049,9 +5057,9 @@
<para>
By default, MySQL begins each client connection with autocommit
mode enabled, so MySQL does a commit after each SQL statement if
- that statement did not return an error. If an SQL statement
- returns an error, the commit or rollback behavior depends on the
- error. See <xref linkend="innodb-error-handling"/>.
+ that statement did not return an error. If a statement returns
+ an error, the commit or rollback behavior depends on the error.
+ See <xref linkend="innodb-error-handling"/>.
</para>
<para>
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2008-11-26 18:07:28 UTC (rev 12674)
+++ trunk/refman-5.0/se-innodb-core.xml 2008-11-26 18:07:38 UTC (rev 12675)
Changed blocks: 12, Lines Added: 61, Lines Deleted: 56; 10476 bytes
@@ -1708,8 +1708,10 @@
</indexterm>
<para>
- This variable controls whether <literal>InnoDB</literal> uses
- gap locking for searches and index scans.
+ This variable affects how <literal>InnoDB</literal> uses gap
+ locking for searches and index scans. (Gap locking also
+ depends on the transaction isolation level; see
+ <xref linkend="set-transaction"/>.)
</para>
<para>
@@ -1730,38 +1732,29 @@
</para>
<para>
- controls whether <literal>InnoDB</literal> uses gap searches
- and index scans. By default, the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- 0 (disabled), which means that gap locking is enabled:
+ By default, the value of
+ <literal>innodb_locks_unsafe_for_binlog</literal> is 0
+ (disabled), which means that gap locking is enabled:
<literal>InnoDB</literal> uses next-key locks (index-record
- locks plus gap locks). To enable the variable, set it to 1.
- This causes gap locking to be disabled for searches and index
- scans: <literal>InnoDB</literal> uses only index-record locks.
+ locks plus gap locks) for searches and index scans. To enable
+ the variable, set it to 1. This causes gap locking to be
+ disabled: <literal>InnoDB</literal> uses only index-record
+ locks for searches and index scans.
</para>
<para>
- Use of gap locking is also dependent on the transaction
- isolation level (see <xref linkend="set-transaction"/>).
- </para>
-
- <para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- does not disable the use of next-key locking for foreign-key
- constraint checking or duplicate-key checking. To affect those
- types of checking, set the
- <literal role="sysvar">foreign_key_checks</literal> and
- <literal role="sysvar">unique_checks</literal> session
- variables (see <xref linkend="innodb-tuning"/>).
+ does not disable the use of gap locking for foreign-key
+ constraint checking or duplicate-key checking.
</para>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
- cause phantom problems because when gap locking is disabled,
- other sessions can insert new rows into the gaps. Suppose that
+ cause phantom problems because other sessions can insert new
+ rows into the gaps when gap locking is disabled. Suppose that
there is an index on the <literal>id</literal> column of the
<literal>child</literal> table and that you want to read and
- lock all rows from the table with an identifier value larger
+ lock all rows from the table having an identifier value larger
than 100, with the intention of updating some column in the
selected rows later:
</para>
@@ -1820,7 +1813,7 @@
</programlisting>
<para>
- <literal>InnoDB</literal> executes the first
+ <literal>InnoDB</literal> executes the
<literal role="stmt">UPDATE</literal> as follows:
</para>
@@ -1838,12 +1831,12 @@
</programlisting>
<para>
- <literal>InnoDB</literal> acquires an exclusive lock for each
- row. If <literal>InnoDB</literal> modifies the row, it retains
- the lock until the transaction ends. If
- <literal>InnoDB</literal> does not modify the row, it releases
- the lock if <literal>innodb_locks_unsafe_for_binlog</literal>
- variable is enabled.
+ <literal>InnoDB</literal> first acquires an exclusive lock for
+ each row, and then determines whether to modify it. If
+ <literal>InnoDB</literal> does not modify the row and
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled,
+ it releases the lock. Otherwise, <literal>InnoDB</literal>
+ retains the lock until the end of the transaction.
</para>
<para>
@@ -1873,13 +1866,10 @@
<literal role="stmt">UPDATE</literal>, even though it affects
different rows. The first
<literal role="stmt">UPDATE</literal> has an exclusive lock on
- row (2,3) which it has not released. As the second
+ row (2,3) that it has not released. As the second
<literal role="stmt">UPDATE</literal> scans rows, it tries to
acquire an exclusive lock for that same row, which it cannot
- have. This is because the second
- <literal role="stmt">UPDATE</literal> first acquires an
- exclusive lock on a row and then determines whether the row
- belongs to the result set.
+ have.
</para>
</listitem>
@@ -3985,7 +3975,11 @@
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If autocommit mode is enabled, each SQL statement
forms a single transaction on its own. By default, MySQL starts
- the session for each new connection with autocommit enabled.
+ the session for each new connection with autocommit enabled, so
+ MySQL does a commit after each SQL statement if that statement did
+ not return an error. If a statement returns an error, the commit
+ or rollback behavior depends on the error. See
+ <xref linkend="innodb-error-handling"/>.
</para>
<para>
@@ -3994,19 +3988,23 @@
<literal role="stmt" condition="commit">START
TRANSACTION</literal> or
<literal role="stmt" condition="commit">BEGIN</literal> statement
- and ending it with <literal role="stmt">COMMIT</literal> or
- <literal role="stmt" condition="commit">ROLLBACK</literal>.
+ and ending it with a <literal role="stmt">COMMIT</literal> or
+ <literal role="stmt" condition="commit">ROLLBACK</literal>
+ statement.
</para>
<para>
If autocommit mode is disabled within a session with <literal>SET
autocommit = 0</literal>, the session always has a transaction
- open. An SQL <literal role="stmt">COMMIT</literal> or
+ open. A <literal role="stmt">COMMIT</literal> or
<literal role="stmt" condition="commit">ROLLBACK</literal>
- statement ends the current transaction and a new one starts. A
- <literal role="stmt">COMMIT</literal> means that the changes made
- in the current transaction are made permanent and become visible
- to other sessions. A
+ statement ends the current transaction and a new one starts.
+ </para>
+
+ <para>
+ A <literal role="stmt">COMMIT</literal> means that the changes
+ made in the current transaction are made permanent and become
+ visible to other sessions. A
<literal role="stmt" condition="commit">ROLLBACK</literal>
statement, on the other hand, cancels all modifications made by
the current transaction. Both
@@ -4092,8 +4090,8 @@
the gap immediately before the index record. A next-key lock
refers to a lock that locks an index record and the gap before it.
A gap lock refers to a lock that locks only the gap before some
- index record. Next-key locking for searches or index scans can be
- disabled by enabling the
+ index record. Use of gap locking for searches or index scans can
+ be disabled by enabling the
<literal>innodb_locks_unsafe_for_binlog</literal> system variable.
</para>
@@ -4394,7 +4392,7 @@
changes made by later or uncommitted transactions. The exception
to this rule is that the query sees the changes made by earlier
statements within the same transaction. This exception causes
- the following anomaly: if you update some rows in a table, a
+ the following anomaly: If you update some rows in a table, a
<literal role="stmt">SELECT</literal> will see the latest
version of the updated rows, but it might also see older
versions of any rows. If other users simultaneously update the
@@ -4403,15 +4401,22 @@
</para>
<para>
- If you are running with the default
- <literal role="isolevel">REPEATABLE READ</literal> isolation
- level, all consistent reads within the same transaction read the
- snapshot established by the first such read in that transaction.
- You can get a fresher snapshot for your queries by committing
- the current transaction and after that issuing new queries.
+ If the transaction isolation level is
+ <literal role="isolevel">REPEATABLE READ</literal> (the default
+ level), all consistent reads within the same transaction read
+ the snapshot established by the first such read in that
+ transaction. You can get a fresher snapshot for your queries by
+ committing the current transaction and after that issuing new
+ queries.
</para>
<para>
+ With <literal role="isolevel">READ COMMITTED</literal> isolation
+ level, each consistent read within a transaction sets and reads
+ its own fresh snapshot.
+ </para>
+
+ <para>
Consistent read is the default mode in which
<literal>InnoDB</literal> processes
<literal role="stmt">SELECT</literal> statements in
@@ -4701,7 +4706,7 @@
<listitem>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- disables gap locking for searchs and index scans.
+ disables gap locking for searches and index scans.
</para>
</listitem>
@@ -5113,9 +5118,9 @@
<para>
By default, MySQL begins each client connection with autocommit
mode enabled, so MySQL does a commit after each SQL statement if
- that statement did not return an error. If an SQL statement
- returns an error, the commit or rollback behavior depends on the
- error. See <xref linkend="innodb-error-handling"/>.
+ that statement did not return an error. If a statement returns
+ an error, the commit or rollback behavior depends on the error.
+ See <xref linkend="innodb-error-handling"/>.
</para>
<para>
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2008-11-26 18:07:28 UTC (rev 12674)
+++ trunk/refman-5.1/se-innodb-core.xml 2008-11-26 18:07:38 UTC (rev 12675)
Changed blocks: 12, Lines Added: 61, Lines Deleted: 56; 10476 bytes
@@ -1750,8 +1750,10 @@
</indexterm>
<para>
- This variable controls whether <literal>InnoDB</literal> uses
- gap locking for searches and index scans.
+ This variable affects how <literal>InnoDB</literal> uses gap
+ locking for searches and index scans. (Gap locking also
+ depends on the transaction isolation level; see
+ <xref linkend="set-transaction"/>.)
</para>
<para>
@@ -1772,38 +1774,29 @@
</para>
<para>
- controls whether <literal>InnoDB</literal> uses gap searches
- and index scans. By default, the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- 0 (disabled), which means that gap locking is enabled:
+ By default, the value of
+ <literal>innodb_locks_unsafe_for_binlog</literal> is 0
+ (disabled), which means that gap locking is enabled:
<literal>InnoDB</literal> uses next-key locks (index-record
- locks plus gap locks). To enable the variable, set it to 1.
- This causes gap locking to be disabled for searches and index
- scans: <literal>InnoDB</literal> uses only index-record locks.
+ locks plus gap locks) for searches and index scans. To enable
+ the variable, set it to 1. This causes gap locking to be
+ disabled: <literal>InnoDB</literal> uses only index-record
+ locks for searches and index scans.
</para>
<para>
- Use of gap locking is also dependent on the transaction
- isolation level (see <xref linkend="set-transaction"/>).
- </para>
-
- <para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- does not disable the use of next-key locking for foreign-key
- constraint checking or duplicate-key checking. To affect those
- types of checking, set the
- <literal role="sysvar">foreign_key_checks</literal> and
- <literal role="sysvar">unique_checks</literal> session
- variables (see <xref linkend="innodb-tuning"/>).
+ does not disable the use of gap locking for foreign-key
+ constraint checking or duplicate-key checking.
</para>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
- cause phantom problems because when gap locking is disabled,
- other sessions can insert new rows into the gaps. Suppose that
+ cause phantom problems because other sessions can insert new
+ rows into the gaps when gap locking is disabled. Suppose that
there is an index on the <literal>id</literal> column of the
<literal>child</literal> table and that you want to read and
- lock all rows from the table with an identifier value larger
+ lock all rows from the table having an identifier value larger
than 100, with the intention of updating some column in the
selected rows later:
</para>
@@ -1861,7 +1854,7 @@
</programlisting>
<para>
- <literal>InnoDB</literal> executes the first
+ <literal>InnoDB</literal> executes the
<literal role="stmt">UPDATE</literal> as follows:
</para>
@@ -1879,12 +1872,12 @@
</programlisting>
<para>
- <literal>InnoDB</literal> acquires an exclusive lock for each
- row. If <literal>InnoDB</literal> modifies the row, it retains
- the lock until the transaction ends. If
- <literal>InnoDB</literal> does not modify the row, it releases
- the lock if <literal>innodb_locks_unsafe_for_binlog</literal>
- variable is enabled.
+ <literal>InnoDB</literal> first acquires an exclusive lock for
+ each row, and then determines whether to modify it. If
+ <literal>InnoDB</literal> does not modify the row and
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled,
+ it releases the lock. Otherwise, <literal>InnoDB</literal>
+ retains the lock until the end of the transaction.
</para>
<para>
@@ -1914,13 +1907,10 @@
<literal role="stmt">UPDATE</literal>, even though it affects
different rows. The first
<literal role="stmt">UPDATE</literal> has an exclusive lock on
- row (2,3) which it has not released. As the second
+ row (2,3) that it has not released. As the second
<literal role="stmt">UPDATE</literal> scans rows, it tries to
acquire an exclusive lock for that same row, which it cannot
- have. This is because the second
- <literal role="stmt">UPDATE</literal> first acquires an
- exclusive lock on a row and then determines whether the row
- belongs to the result set.
+ have.
</para>
</listitem>
@@ -4682,7 +4672,11 @@
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If autocommit mode is enabled, each SQL statement
forms a single transaction on its own. By default, MySQL starts
- the session for each new connection with autocommit enabled.
+ the session for each new connection with autocommit enabled, so
+ MySQL does a commit after each SQL statement if that statement did
+ not return an error. If a statement returns an error, the commit
+ or rollback behavior depends on the error. See
+ <xref linkend="innodb-error-handling"/>.
</para>
<para>
@@ -4691,19 +4685,23 @@
<literal role="stmt" condition="commit">START
TRANSACTION</literal> or
<literal role="stmt" condition="commit">BEGIN</literal> statement
- and ending it with <literal role="stmt">COMMIT</literal> or
- <literal role="stmt" condition="commit">ROLLBACK</literal>.
+ and ending it with a <literal role="stmt">COMMIT</literal> or
+ <literal role="stmt" condition="commit">ROLLBACK</literal>
+ statement.
</para>
<para>
If autocommit mode is disabled within a session with <literal>SET
autocommit = 0</literal>, the session always has a transaction
- open. An SQL <literal role="stmt">COMMIT</literal> or
+ open. A <literal role="stmt">COMMIT</literal> or
<literal role="stmt" condition="commit">ROLLBACK</literal>
- statement ends the current transaction and a new one starts. A
- <literal role="stmt">COMMIT</literal> means that the changes made
- in the current transaction are made permanent and become visible
- to other sessions. A
+ statement ends the current transaction and a new one starts.
+ </para>
+
+ <para>
+ A <literal role="stmt">COMMIT</literal> means that the changes
+ made in the current transaction are made permanent and become
+ visible to other sessions. A
<literal role="stmt" condition="commit">ROLLBACK</literal>
statement, on the other hand, cancels all modifications made by
the current transaction. Both
@@ -4789,8 +4787,8 @@
the gap immediately before the index record. A next-key lock
refers to a lock that locks an index record and the gap before it.
A gap lock refers to a lock that locks only the gap before some
- index record. Next-key locking for searches or index scans can be
- disabled by enabling the
+ index record. Use of gap locking for searches or index scans can
+ be disabled by enabling the
<literal>innodb_locks_unsafe_for_binlog</literal> system variable.
</para>
@@ -5101,7 +5099,7 @@
changes made by later or uncommitted transactions. The exception
to this rule is that the query sees the changes made by earlier
statements within the same transaction. This exception causes
- the following anomaly: if you update some rows in a table, a
+ the following anomaly: If you update some rows in a table, a
<literal role="stmt">SELECT</literal> will see the latest
version of the updated rows, but it might also see older
versions of any rows. If other users simultaneously update the
@@ -5110,15 +5108,22 @@
</para>
<para>
- If you are running with the default
- <literal role="isolevel">REPEATABLE READ</literal> isolation
- level, all consistent reads within the same transaction read the
- snapshot established by the first such read in that transaction.
- You can get a fresher snapshot for your queries by committing
- the current transaction and after that issuing new queries.
+ If the transaction isolation level is
+ <literal role="isolevel">REPEATABLE READ</literal> (the default
+ level), all consistent reads within the same transaction read
+ the snapshot established by the first such read in that
+ transaction. You can get a fresher snapshot for your queries by
+ committing the current transaction and after that issuing new
+ queries.
</para>
<para>
+ With <literal role="isolevel">READ COMMITTED</literal> isolation
+ level, each consistent read within a transaction sets and reads
+ its own fresh snapshot.
+ </para>
+
+ <para>
Consistent read is the default mode in which
<literal>InnoDB</literal> processes
<literal role="stmt">SELECT</literal> statements in
@@ -5408,7 +5413,7 @@
<listitem>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- disables gap locking for searchs and index scans.
+ disables gap locking for searches and index scans.
</para>
</listitem>
@@ -5846,9 +5851,9 @@
<para>
By default, MySQL begins each client connection with autocommit
mode enabled, so MySQL does a commit after each SQL statement if
- that statement did not return an error. If an SQL statement
- returns an error, the commit or rollback behavior depends on the
- error. See <xref linkend="innodb-error-handling"/>.
+ that statement did not return an error. If a statement returns
+ an error, the commit or rollback behavior depends on the error.
+ See <xref linkend="innodb-error-handling"/>.
</para>
<para>
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2008-11-26 18:07:28 UTC (rev 12674)
+++ trunk/refman-6.0/se-innodb-core.xml 2008-11-26 18:07:38 UTC (rev 12675)
Changed blocks: 12, Lines Added: 61, Lines Deleted: 56; 10476 bytes
@@ -1679,8 +1679,10 @@
</indexterm>
<para>
- This variable controls whether <literal>InnoDB</literal> uses
- gap locking for searches and index scans.
+ This variable affects how <literal>InnoDB</literal> uses gap
+ locking for searches and index scans. (Gap locking also
+ depends on the transaction isolation level; see
+ <xref linkend="set-transaction"/>.)
</para>
<para>
@@ -1701,38 +1703,29 @@
</para>
<para>
- controls whether <literal>InnoDB</literal> uses gap searches
- and index scans. By default, the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- 0 (disabled), which means that gap locking is enabled:
+ By default, the value of
+ <literal>innodb_locks_unsafe_for_binlog</literal> is 0
+ (disabled), which means that gap locking is enabled:
<literal>InnoDB</literal> uses next-key locks (index-record
- locks plus gap locks). To enable the variable, set it to 1.
- This causes gap locking to be disabled for searches and index
- scans: <literal>InnoDB</literal> uses only index-record locks.
+ locks plus gap locks) for searches and index scans. To enable
+ the variable, set it to 1. This causes gap locking to be
+ disabled: <literal>InnoDB</literal> uses only index-record
+ locks for searches and index scans.
</para>
<para>
- Use of gap locking is also dependent on the transaction
- isolation level (see <xref linkend="set-transaction"/>).
- </para>
-
- <para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- does not disable the use of next-key locking for foreign-key
- constraint checking or duplicate-key checking. To affect those
- types of checking, set the
- <literal role="sysvar">foreign_key_checks</literal> and
- <literal role="sysvar">unique_checks</literal> session
- variables (see <xref linkend="innodb-tuning"/>).
+ does not disable the use of gap locking for foreign-key
+ constraint checking or duplicate-key checking.
</para>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
- cause phantom problems because when gap locking is disabled,
- other sessions can insert new rows into the gaps. Suppose that
+ cause phantom problems because other sessions can insert new
+ rows into the gaps when gap locking is disabled. Suppose that
there is an index on the <literal>id</literal> column of the
<literal>child</literal> table and that you want to read and
- lock all rows from the table with an identifier value larger
+ lock all rows from the table having an identifier value larger
than 100, with the intention of updating some column in the
selected rows later:
</para>
@@ -1790,7 +1783,7 @@
</programlisting>
<para>
- <literal>InnoDB</literal> executes the first
+ <literal>InnoDB</literal> executes the
<literal role="stmt">UPDATE</literal> as follows:
</para>
@@ -1808,12 +1801,12 @@
</programlisting>
<para>
- <literal>InnoDB</literal> acquires an exclusive lock for each
- row. If <literal>InnoDB</literal> modifies the row, it retains
- the lock until the transaction ends. If
- <literal>InnoDB</literal> does not modify the row, it releases
- the lock if <literal>innodb_locks_unsafe_for_binlog</literal>
- variable is enabled.
+ <literal>InnoDB</literal> first acquires an exclusive lock for
+ each row, and then determines whether to modify it. If
+ <literal>InnoDB</literal> does not modify the row and
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled,
+ it releases the lock. Otherwise, <literal>InnoDB</literal>
+ retains the lock until the end of the transaction.
</para>
<para>
@@ -1843,13 +1836,10 @@
<literal role="stmt">UPDATE</literal>, even though it affects
different rows. The first
<literal role="stmt">UPDATE</literal> has an exclusive lock on
- row (2,3) which it has not released. As the second
+ row (2,3) that it has not released. As the second
<literal role="stmt">UPDATE</literal> scans rows, it tries to
acquire an exclusive lock for that same row, which it cannot
- have. This is because the second
- <literal role="stmt">UPDATE</literal> first acquires an
- exclusive lock on a row and then determines whether the row
- belongs to the result set.
+ have.
</para>
</listitem>
@@ -4549,7 +4539,11 @@
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If autocommit mode is enabled, each SQL statement
forms a single transaction on its own. By default, MySQL starts
- the session for each new connection with autocommit enabled.
+ the session for each new connection with autocommit enabled, so
+ MySQL does a commit after each SQL statement if that statement did
+ not return an error. If a statement returns an error, the commit
+ or rollback behavior depends on the error. See
+ <xref linkend="innodb-error-handling"/>.
</para>
<para>
@@ -4558,19 +4552,23 @@
<literal role="stmt" condition="commit">START
TRANSACTION</literal> or
<literal role="stmt" condition="commit">BEGIN</literal> statement
- and ending it with <literal role="stmt">COMMIT</literal> or
- <literal role="stmt" condition="commit">ROLLBACK</literal>.
+ and ending it with a <literal role="stmt">COMMIT</literal> or
+ <literal role="stmt" condition="commit">ROLLBACK</literal>
+ statement.
</para>
<para>
If autocommit mode is disabled within a session with <literal>SET
autocommit = 0</literal>, the session always has a transaction
- open. An SQL <literal role="stmt">COMMIT</literal> or
+ open. A <literal role="stmt">COMMIT</literal> or
<literal role="stmt" condition="commit">ROLLBACK</literal>
- statement ends the current transaction and a new one starts. A
- <literal role="stmt">COMMIT</literal> means that the changes made
- in the current transaction are made permanent and become visible
- to other sessions. A
+ statement ends the current transaction and a new one starts.
+ </para>
+
+ <para>
+ A <literal role="stmt">COMMIT</literal> means that the changes
+ made in the current transaction are made permanent and become
+ visible to other sessions. A
<literal role="stmt" condition="commit">ROLLBACK</literal>
statement, on the other hand, cancels all modifications made by
the current transaction. Both
@@ -4656,8 +4654,8 @@
the gap immediately before the index record. A next-key lock
refers to a lock that locks an index record and the gap before it.
A gap lock refers to a lock that locks only the gap before some
- index record. Next-key locking for searches or index scans can be
- disabled by enabling the
+ index record. Use of gap locking for searches or index scans can
+ be disabled by enabling the
<literal>innodb_locks_unsafe_for_binlog</literal> system variable.
</para>
@@ -4968,7 +4966,7 @@
changes made by later or uncommitted transactions. The exception
to this rule is that the query sees the changes made by earlier
statements within the same transaction. This exception causes
- the following anomaly: if you update some rows in a table, a
+ the following anomaly: If you update some rows in a table, a
<literal role="stmt">SELECT</literal> will see the latest
version of the updated rows, but it might also see older
versions of any rows. If other users simultaneously update the
@@ -4977,15 +4975,22 @@
</para>
<para>
- If you are running with the default
- <literal role="isolevel">REPEATABLE READ</literal> isolation
- level, all consistent reads within the same transaction read the
- snapshot established by the first such read in that transaction.
- You can get a fresher snapshot for your queries by committing
- the current transaction and after that issuing new queries.
+ If the transaction isolation level is
+ <literal role="isolevel">REPEATABLE READ</literal> (the default
+ level), all consistent reads within the same transaction read
+ the snapshot established by the first such read in that
+ transaction. You can get a fresher snapshot for your queries by
+ committing the current transaction and after that issuing new
+ queries.
</para>
<para>
+ With <literal role="isolevel">READ COMMITTED</literal> isolation
+ level, each consistent read within a transaction sets and reads
+ its own fresh snapshot.
+ </para>
+
+ <para>
Consistent read is the default mode in which
<literal>InnoDB</literal> processes
<literal role="stmt">SELECT</literal> statements in
@@ -5275,7 +5280,7 @@
<listitem>
<para>
Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- disables gap locking for searchs and index scans.
+ disables gap locking for searches and index scans.
</para>
</listitem>
@@ -5713,9 +5718,9 @@
<para>
By default, MySQL begins each client connection with autocommit
mode enabled, so MySQL does a commit after each SQL statement if
- that statement did not return an error. If an SQL statement
- returns an error, the commit or rollback behavior depends on the
- error. See <xref linkend="innodb-error-handling"/>.
+ that statement did not return an error. If a statement returns
+ an error, the commit or rollback behavior depends on the error.
+ See <xref linkend="innodb-error-handling"/>.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r12675 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 26 Nov |
