Author: paul
Date: 2008-11-05 22:04:30 +0100 (Wed, 05 Nov 2008)
New Revision: 12296
Log:
r35323@frost: paul | 2008-11-05 14:53:49 -0500
General InnoDB 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:35322
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34100
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:35323
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34100
Modified: trunk/refman-4.1/se-innodb-core.xml
===================================================================
--- trunk/refman-4.1/se-innodb-core.xml 2008-11-05 21:04:21 UTC (rev 12295)
+++ trunk/refman-4.1/se-innodb-core.xml 2008-11-05 21:04:30 UTC (rev 12296)
Changed blocks: 3, Lines Added: 34, Lines Deleted: 27; 4159 bytes
@@ -1696,21 +1696,14 @@
<literal>innodb_locks_unsafe_for_binlog</literal>
</para>
- <para>
- <indexterm>
- <primary>next-key lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>next-key lock</primary>
+ </indexterm>
- <indexterm>
- <primary>gap lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>gap lock</primary>
+ </indexterm>
- This variable controls next-key locking in
- <literal>InnoDB</literal> searches and index scans. By
- default, this variable is 0 (disabled), which means that
- next-key locking is enabled.
- </para>
-
<para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
@@ -1730,18 +1723,32 @@
</para>
<para>
- Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- causes <literal>InnoDB</literal> not to use next-key locking
- in searches or index scans. Next-key locking is still used to
- ensure foreign key constraints and duplicate key checking.
- Note that enabling this variable may cause phantom problems:
- Suppose that there is an index on the <literal>id</literal>
- column and that you want to read and lock all children from
- the <literal>child</literal> table with an identifier value
- larger than 100, with the intention of updating some column in
- the selected rows later:
+ The <literal>innodb_locks_unsafe_for_binlog</literal> variable
+ controls next-key locking in <literal>InnoDB</literal>
+ searches and index scans. By default, this variable is 0
+ (disabled), which means that next-key locking is enabled. To
+ enable the variable (and disable next-key locking for searches
+ and index scans), set it to 1.
</para>
+ <para>
+ The value of <literal>innodb_locks_unsafe_for_binlog</literal>
+ does not affect the use of next-key locking for foreign-key
+ constraint checking or duplicate-key checking. To affect those
+ types of checking, set the
+ <literal>FOREIGN_KEY_CHECKS</literal> and
+ <literal>UNIQUE_CHECKS</literal> session variables.
+ </para>
+
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
+ cause phantom problems: Suppose that there is an index on the
+ <literal>id</literal> column and that you want to read and
+ lock all children from the <literal>child</literal> table with
+ an identifier value larger than 100, with the intention of
+ updating some column in the selected rows later:
+ </para>
+
<programlisting>
SELECT * FROM child WHERE id > 100 FOR UPDATE;
</programlisting>
@@ -1751,10 +1758,10 @@
<literal>id</literal> is greater than 100. If the locks set on
the index records do not lock out inserts made in the gaps,
another client can insert a new row into the table. If you
- execute the same <literal role="stmt">SELECT</literal> within
- the same transaction, you see a new row in the result set
- returned by the query. This also means that if new items are
- added to the database, <literal>InnoDB</literal> does not
+ execute the same <literal role="stmt">SELECT</literal> again
+ within the same transaction, you see a new row in the result
+ set returned by the query. This also means that if new items
+ are added to the database, <literal>InnoDB</literal> does not
guarantee serializability. Therefore, if this variable is
enabled <literal>InnoDB</literal> guarantees at most isolation
level <literal>READ COMMITTED</literal>. (Conflict
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2008-11-05 21:04:21 UTC (rev 12295)
+++ trunk/refman-5.0/se-innodb-core.xml 2008-11-05 21:04:30 UTC (rev 12296)
Changed blocks: 7, Lines Added: 43, Lines Deleted: 38; 5871 bytes
@@ -1656,21 +1656,14 @@
<literal>innodb_locks_unsafe_for_binlog</literal>
</para>
- <para>
- <indexterm>
- <primary>next-key lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>next-key lock</primary>
+ </indexterm>
- <indexterm>
- <primary>gap lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>gap lock</primary>
+ </indexterm>
- This variable controls next-key locking in
- <literal>InnoDB</literal> searches and index scans. By
- default, this variable is 0 (disabled), which means that
- next-key locking is enabled.
- </para>
-
<para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
@@ -1690,18 +1683,32 @@
</para>
<para>
- Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- causes <literal>InnoDB</literal> not to use next-key locking
- in searches or index scans. Next-key locking is still used to
- ensure foreign key constraints and duplicate key checking.
- Note that enabling this variable may cause phantom problems:
- Suppose that there is an index on the <literal>id</literal>
- column and that you want to read and lock all children from
- the <literal>child</literal> table with an identifier value
- larger than 100, with the intention of updating some column in
- the selected rows later:
+ The <literal>innodb_locks_unsafe_for_binlog</literal> variable
+ controls next-key locking in <literal>InnoDB</literal>
+ searches and index scans. By default, this variable is 0
+ (disabled), which means that next-key locking is enabled. To
+ enable the variable (and disable next-key locking for searches
+ and index scans), set it to 1.
</para>
+ <para>
+ The value of <literal>innodb_locks_unsafe_for_binlog</literal>
+ does not affect the use of next-key locking for foreign-key
+ constraint checking or duplicate-key checking. To affect those
+ types of checking, set the
+ <literal>FOREIGN_KEY_CHECKS</literal> and
+ <literal>UNIQUE_CHECKS</literal> session variables.
+ </para>
+
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
+ cause phantom problems: Suppose that there is an index on the
+ <literal>id</literal> column and that you want to read and
+ lock all children from the <literal>child</literal> table with
+ an identifier value larger than 100, with the intention of
+ updating some column in the selected rows later:
+ </para>
+
<programlisting>
SELECT * FROM child WHERE id > 100 FOR UPDATE;
</programlisting>
@@ -1711,10 +1718,10 @@
<literal>id</literal> is greater than 100. If the locks set on
the index records do not lock out inserts made in the gaps,
another client can insert a new row into the table. If you
- execute the same <literal role="stmt">SELECT</literal> within
- the same transaction, you see a new row in the result set
- returned by the query. This also means that if new items are
- added to the database, <literal>InnoDB</literal> does not
+ execute the same <literal role="stmt">SELECT</literal> again
+ within the same transaction, you see a new row in the result
+ set returned by the query. This also means that if new items
+ are added to the database, <literal>InnoDB</literal> does not
guarantee serializability. Therefore, if this variable is
enabled <literal>InnoDB</literal> guarantees at most isolation
level <literal>READ COMMITTED</literal>. (Conflict
@@ -1737,8 +1744,8 @@
</para>
<programlisting>
-CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
-INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
+CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
+INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
</programlisting>
@@ -1748,7 +1755,7 @@
<programlisting>
SET AUTOCOMMIT = 0;
-UPDATE A SET B = 5 WHERE B = 3;
+UPDATE t SET b = 5 WHERE b = 3;
</programlisting>
<para>
@@ -1758,7 +1765,7 @@
<programlisting>
SET AUTOCOMMIT = 0;
-UPDATE A SET B = 4 WHERE B = 2;
+UPDATE t SET b = 4 WHERE b = 2;
</programlisting>
<para>
@@ -1766,16 +1773,14 @@
must wait for a commit or rollback of the first
<literal role="stmt">UPDATE</literal>. The first
<literal role="stmt">UPDATE</literal> has an exclusive lock on
- row (2,3), and the second
- <literal role="stmt">UPDATE</literal> while scanning rows also
- tries to acquire an exclusive lock for the same row, which it
- cannot have. This is because
+ row (2,3). As the second <literal role="stmt">UPDATE</literal>
+ scans rows, it also tries to acquire an exclusive lock for the
+ same row, which it cannot have. This is because
<literal role="stmt">UPDATE</literal> two first acquires an
exclusive lock on a row and then determines whether the row
belongs to the result set. If not, it releases the unnecessary
- lock, when the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- enabled.
+ lock if the <literal>innodb_locks_unsafe_for_binlog</literal>
+ variable is enabled.
</para>
<para>
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2008-11-05 21:04:21 UTC (rev 12295)
+++ trunk/refman-5.1/se-innodb-core.xml 2008-11-05 21:04:30 UTC (rev 12296)
Changed blocks: 7, Lines Added: 43, Lines Deleted: 38; 5872 bytes
@@ -1699,21 +1699,14 @@
<literal>innodb_locks_unsafe_for_binlog</literal>
</para>
- <para>
- <indexterm>
- <primary>next-key lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>next-key lock</primary>
+ </indexterm>
- <indexterm>
- <primary>gap lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>gap lock</primary>
+ </indexterm>
- This variable controls next-key locking in
- <literal>InnoDB</literal> searches and index scans. By
- default, this variable is 0 (disabled), which means that
- next-key locking is enabled.
- </para>
-
<para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
@@ -1733,18 +1726,32 @@
</para>
<para>
- Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- causes <literal>InnoDB</literal> not to use next-key locking
- in searches or index scans. Next-key locking is still used to
- ensure foreign key constraints and duplicate key checking.
- Note that enabling this variable may cause phantom problems:
- Suppose that there is an index on the <literal>id</literal>
- column and that you want to read and lock all children from
- the <literal>child</literal> table with an identifier value
- larger than 100, with the intention of updating some column in
- the selected rows later:
+ The <literal>innodb_locks_unsafe_for_binlog</literal> variable
+ controls next-key locking in <literal>InnoDB</literal>
+ searches and index scans. By default, this variable is 0
+ (disabled), which means that next-key locking is enabled. To
+ enable the variable (and disable next-key locking for searches
+ and index scans), set it to 1.
</para>
+ <para>
+ The value of <literal>innodb_locks_unsafe_for_binlog</literal>
+ does not affect the use of next-key locking for foreign-key
+ constraint checking or duplicate-key checking. To affect those
+ types of checking, set the
+ <literal>FOREIGN_KEY_CHECKS</literal> and
+ <literal>UNIQUE_CHECKS</literal> session variables.
+ </para>
+
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
+ cause phantom problems: Suppose that there is an index on the
+ <literal>id</literal> column and that you want to read and
+ lock all children from the <literal>child</literal> table with
+ an identifier value larger than 100, with the intention of
+ updating some column in the selected rows later:
+ </para>
+
<programlisting>
SELECT * FROM child WHERE id > 100 FOR UPDATE;
</programlisting>
@@ -1754,10 +1761,10 @@
<literal>id</literal> is greater than 100. If the locks set on
the index records do not lock out inserts made in the gaps,
another client can insert a new row into the table. If you
- execute the same <literal role="stmt">SELECT</literal> within
- the same transaction, you see a new row in the result set
- returned by the query. This also means that if new items are
- added to the database, <literal>InnoDB</literal> does not
+ execute the same <literal role="stmt">SELECT</literal> again
+ within the same transaction, you see a new row in the result
+ set returned by the query. This also means that if new items
+ are added to the database, <literal>InnoDB</literal> does not
guarantee serializability. Therefore, if this variable is
enabled, <literal>InnoDB</literal> guarantees at most
isolation level <literal>READ COMMITTED</literal>. (Conflict
@@ -1780,8 +1787,8 @@
</para>
<programlisting>
-CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
-INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
+CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
+INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
</programlisting>
@@ -1791,7 +1798,7 @@
<programlisting>
SET AUTOCOMMIT = 0;
-UPDATE A SET B = 5 WHERE B = 3;
+UPDATE t SET b = 5 WHERE b = 3;
</programlisting>
<para>
@@ -1801,7 +1808,7 @@
<programlisting>
SET AUTOCOMMIT = 0;
-UPDATE A SET B = 4 WHERE B = 2;
+UPDATE t SET b = 4 WHERE b = 2;
</programlisting>
<para>
@@ -1809,16 +1816,14 @@
must wait for a commit or rollback of the first
<literal role="stmt">UPDATE</literal>. The first
<literal role="stmt">UPDATE</literal> has an exclusive lock on
- row (2,3), and the second
- <literal role="stmt">UPDATE</literal> while scanning rows also
- tries to acquire an exclusive lock for the same row, which it
- cannot have. This is because
+ row (2,3). As the second <literal role="stmt">UPDATE</literal>
+ scans rows, it also tries to acquire an exclusive lock for the
+ same row, which it cannot have. This is because
<literal role="stmt">UPDATE</literal> two first acquires an
exclusive lock on a row and then determines whether the row
belongs to the result set. If not, it releases the unnecessary
- lock, when the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- enabled.
+ lock if the <literal>innodb_locks_unsafe_for_binlog</literal>
+ variable is enabled.
</para>
<para>
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2008-11-05 21:04:21 UTC (rev 12295)
+++ trunk/refman-6.0/se-innodb-core.xml 2008-11-05 21:04:30 UTC (rev 12296)
Changed blocks: 7, Lines Added: 43, Lines Deleted: 38; 5872 bytes
@@ -1628,21 +1628,14 @@
<literal>innodb_locks_unsafe_for_binlog</literal>
</para>
- <para>
- <indexterm>
- <primary>next-key lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>next-key lock</primary>
+ </indexterm>
- <indexterm>
- <primary>gap lock</primary>
- </indexterm>
+ <indexterm>
+ <primary>gap lock</primary>
+ </indexterm>
- This variable controls next-key locking in
- <literal>InnoDB</literal> searches and index scans. By
- default, this variable is 0 (disabled), which means that
- next-key locking is enabled.
- </para>
-
<para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
@@ -1662,18 +1655,32 @@
</para>
<para>
- Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
- causes <literal>InnoDB</literal> not to use next-key locking
- in searches or index scans. Next-key locking is still used to
- ensure foreign key constraints and duplicate key checking.
- Note that enabling this variable may cause phantom problems:
- Suppose that there is an index on the <literal>id</literal>
- column and that you want to read and lock all children from
- the <literal>child</literal> table with an identifier value
- larger than 100, with the intention of updating some column in
- the selected rows later:
+ The <literal>innodb_locks_unsafe_for_binlog</literal> variable
+ controls next-key locking in <literal>InnoDB</literal>
+ searches and index scans. By default, this variable is 0
+ (disabled), which means that next-key locking is enabled. To
+ enable the variable (and disable next-key locking for searches
+ and index scans), set it to 1.
</para>
+ <para>
+ The value of <literal>innodb_locks_unsafe_for_binlog</literal>
+ does not affect the use of next-key locking for foreign-key
+ constraint checking or duplicate-key checking. To affect those
+ types of checking, set the
+ <literal>FOREIGN_KEY_CHECKS</literal> and
+ <literal>UNIQUE_CHECKS</literal> session variables.
+ </para>
+
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal> may
+ cause phantom problems: Suppose that there is an index on the
+ <literal>id</literal> column and that you want to read and
+ lock all children from the <literal>child</literal> table with
+ an identifier value larger than 100, with the intention of
+ updating some column in the selected rows later:
+ </para>
+
<programlisting>
SELECT * FROM child WHERE id > 100 FOR UPDATE;
</programlisting>
@@ -1683,10 +1690,10 @@
<literal>id</literal> is greater than 100. If the locks set on
the index records do not lock out inserts made in the gaps,
another client can insert a new row into the table. If you
- execute the same <literal role="stmt">SELECT</literal> within
- the same transaction, you see a new row in the result set
- returned by the query. This also means that if new items are
- added to the database, <literal>InnoDB</literal> does not
+ execute the same <literal role="stmt">SELECT</literal> again
+ within the same transaction, you see a new row in the result
+ set returned by the query. This also means that if new items
+ are added to the database, <literal>InnoDB</literal> does not
guarantee serializability. Therefore, if this variable is
enabled, <literal>InnoDB</literal> guarantees at most
isolation level <literal>READ COMMITTED</literal>. (Conflict
@@ -1709,8 +1716,8 @@
</para>
<programlisting>
-CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
-INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
+CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
+INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
</programlisting>
@@ -1720,7 +1727,7 @@
<programlisting>
SET AUTOCOMMIT = 0;
-UPDATE A SET B = 5 WHERE B = 3;
+UPDATE t SET b = 5 WHERE b = 3;
</programlisting>
<para>
@@ -1730,7 +1737,7 @@
<programlisting>
SET AUTOCOMMIT = 0;
-UPDATE A SET B = 4 WHERE B = 2;
+UPDATE t SET b = 4 WHERE b = 2;
</programlisting>
<para>
@@ -1738,16 +1745,14 @@
must wait for a commit or rollback of the first
<literal role="stmt">UPDATE</literal>. The first
<literal role="stmt">UPDATE</literal> has an exclusive lock on
- row (2,3), and the second
- <literal role="stmt">UPDATE</literal> while scanning rows also
- tries to acquire an exclusive lock for the same row, which it
- cannot have. This is because
+ row (2,3). As the second <literal role="stmt">UPDATE</literal>
+ scans rows, it also tries to acquire an exclusive lock for the
+ same row, which it cannot have. This is because
<literal role="stmt">UPDATE</literal> two first acquires an
exclusive lock on a row and then determines whether the row
belongs to the result set. If not, it releases the unnecessary
- lock, when the
- <literal>innodb_locks_unsafe_for_binlog</literal> variable is
- enabled.
+ lock if the <literal>innodb_locks_unsafe_for_binlog</literal>
+ variable is enabled.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r12296 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 5 Nov |
