Author: paul
Date: 2009-03-12 01:04:21 +0100 (Thu, 12 Mar 2009)
New Revision: 14198
Log:
r39482@frost: paul | 2009-03-11 18:56:34 -0500
InnoDB revisions re: gap locking
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:41755
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:39473
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:36872
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:41755
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:39482
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:36872
Modified: trunk/refman-4.1/se-innodb-core.xml
===================================================================
--- trunk/refman-4.1/se-innodb-core.xml 2009-03-11 20:59:54 UTC (rev 14197)
+++ trunk/refman-4.1/se-innodb-core.xml 2009-03-12 00:04:21 UTC (rev 14198)
Changed blocks: 4, Lines Added: 24, Lines Deleted: 8; 2880 bytes
@@ -1783,9 +1783,18 @@
<para>
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"/>.)
+ locking for searches and index scans. The effect is similar to
+ setting the transaction isolation level to <literal>READ
+ COMMITTED</literal>. Enabling
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ is a global setting and affects all sessions, whereas the
+ isolation level can be set globally for all sessions, or
+ individually per sesssion. Also,
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ can be set only at server startup, whereas the isolation level
+ can be set at startup or changed at runtime. For additional
+ details about the effect of isolation level on gap locking,
+ see <xref linkend="set-transaction"/>.
</para>
<para>
@@ -1853,7 +1862,8 @@
is enabled <literal>InnoDB</literal> guarantees at most an
isolation level of <literal role="isolevel">READ
COMMITTED</literal>. (Conflict serializability is still
- guaranteed.)
+ guaranteed.) For additional information about phantoms, see
+ <xref linkend="innodb-next-key-locking"/>.
</para>
<para>
@@ -4858,10 +4868,11 @@
<para>
Gap locking is not needed for statements that lock rows using a
- unique index to search for a unique row. For example, the
- following statement uses only an index-record lock for the row
- having <literal>id</literal> value 100 and it does not matter
- whether other sessions insert rows in the preceding gap:
+ unique index to search for a unique row. For example, if the
+ <literal>id</literal> column has a unique index, the following
+ statement uses only an index-record lock for the row having
+ <literal>id</literal> value 100 and it does not matter whether
+ other sessions insert rows in the preceding gap:
</para>
<programlisting>
@@ -4869,6 +4880,11 @@
</programlisting>
<para>
+ If <literal>id</literal> is not indexed or has a non-unique
+ index, the statement does lock the preceding gap.
+ </para>
+
+ <para>
Gap locking can be disabled explicitly. This occurs if you
change the transaction isolation level to
<literal role="isolevel">READ COMMITTED</literal> or enable the
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2009-03-11 20:59:54 UTC (rev 14197)
+++ trunk/refman-5.0/se-innodb-core.xml 2009-03-12 00:04:21 UTC (rev 14198)
Changed blocks: 6, Lines Added: 37, Lines Deleted: 12; 4229 bytes
@@ -1750,9 +1750,18 @@
<para>
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"/>.)
+ locking for searches and index scans. The effect is similar to
+ setting the transaction isolation level to <literal>READ
+ COMMITTED</literal>. Enabling
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ is a global setting and affects all sessions, whereas the
+ isolation level can be set globally for all sessions, or
+ individually per sesssion. Also,
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ can be set only at server startup, whereas the isolation level
+ can be set at startup or changed at runtime. For additional
+ details about the effect of isolation level on gap locking,
+ see <xref linkend="set-transaction"/>.
</para>
<para>
@@ -1820,7 +1829,8 @@
is enabled, <literal>InnoDB</literal> guarantees at most an
isolation level of <literal role="isolevel">READ
COMMITTED</literal>. (Conflict serializability is still
- guaranteed.)
+ guaranteed.) For additional information about phantoms, see
+ <xref linkend="innodb-next-key-locking"/>.
</para>
<para>
@@ -1849,6 +1859,12 @@
</programlisting>
<para>
+ In this case, table has no indexes, so searches and index
+ scans use the hidden clustered index for record locking. See
+ <xref linkend="innodb-index-types"/>.
+ </para>
+
+ <para>
Suppose that one client performs an
<literal role="stmt">UPDATE</literal> using these statements:
</para>
@@ -4641,10 +4657,13 @@
<para>
<literal>InnoDB</literal> uses a consistent read for select in
- clauses like <literal>INSERT INTO ... SELECT</literal> and
- <literal>UPDATE ... (SELECT)</literal> that do not specify
- <literal>FOR UPDATE</literal> or <literal>IN SHARE
- MODE</literal> if the
+ clauses like <literal role="stmt" condition="insert">INSERT INTO
+ ... SELECT</literal>,
+ <literal role="stmt" condition="update">UPDATE ...
+ (SELECT)</literal>, and
+ <literal role="stmt" condition="create-table">CREATE TABLE ...
+ SELECT</literal> that do not specify <literal>FOR
+ UPDATE</literal> or <literal>IN SHARE MODE</literal> if the
<literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
option is set and the isolation level of the transaction is not
set to <literal role="isolevel">SERIALIZABLE</literal>. Thus, no
@@ -4974,10 +4993,11 @@
<para>
Gap locking is not needed for statements that lock rows using a
- unique index to search for a unique row. For example, the
- following statement uses only an index-record lock for the row
- having <literal>id</literal> value 100 and it does not matter
- whether other sessions insert rows in the preceding gap:
+ unique index to search for a unique row. For example, if the
+ <literal>id</literal> column has a unique index, the following
+ statement uses only an index-record lock for the row having
+ <literal>id</literal> value 100 and it does not matter whether
+ other sessions insert rows in the preceding gap:
</para>
<programlisting>
@@ -4985,6 +5005,11 @@
</programlisting>
<para>
+ If <literal>id</literal> is not indexed or has a non-unique
+ index, the statement does lock the preceding gap.
+ </para>
+
+ <para>
Gap locking can be disabled explicitly. This occurs if you
change the transaction isolation level to
<literal role="isolevel">READ COMMITTED</literal> or enable the
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2009-03-11 20:59:54 UTC (rev 14197)
+++ trunk/refman-5.1/se-innodb-core.xml 2009-03-12 00:04:21 UTC (rev 14198)
Changed blocks: 6, Lines Added: 37, Lines Deleted: 12; 4229 bytes
@@ -1799,9 +1799,18 @@
<para>
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"/>.)
+ locking for searches and index scans. The effect is similar to
+ setting the transaction isolation level to <literal>READ
+ COMMITTED</literal>. Enabling
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ is a global setting and affects all sessions, whereas the
+ isolation level can be set globally for all sessions, or
+ individually per sesssion. Also,
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ can be set only at server startup, whereas the isolation level
+ can be set at startup or changed at runtime. For additional
+ details about the effect of isolation level on gap locking,
+ see <xref linkend="set-transaction"/>.
</para>
<para>
@@ -1869,7 +1878,8 @@
is enabled, <literal>InnoDB</literal> guarantees at most an
isolation level of <literal role="isolevel">READ
COMMITTED</literal>. (Conflict serializability is still
- guaranteed.)
+ guaranteed.) For additional information about phantoms, see
+ <xref linkend="innodb-next-key-locking"/>.
</para>
<para>
@@ -1902,6 +1912,12 @@
</programlisting>
<para>
+ In this case, table has no indexes, so searches and index
+ scans use the hidden clustered index for record locking. See
+ <xref linkend="innodb-index-types"/>.
+ </para>
+
+ <para>
Suppose that one client performs an
<literal role="stmt">UPDATE</literal> using these statements:
</para>
@@ -5399,10 +5415,13 @@
<para>
<literal>InnoDB</literal> uses a consistent read for select in
- clauses like <literal>INSERT INTO ... SELECT</literal> and
- <literal>UPDATE ... (SELECT)</literal> that do not specify
- <literal>FOR UPDATE</literal> or <literal>IN SHARE
- MODE</literal> if the
+ clauses like <literal role="stmt" condition="insert">INSERT INTO
+ ... SELECT</literal>,
+ <literal role="stmt" condition="update">UPDATE ...
+ (SELECT)</literal>, and
+ <literal role="stmt" condition="create-table">CREATE TABLE ...
+ SELECT</literal> that do not specify <literal>FOR
+ UPDATE</literal> or <literal>IN SHARE MODE</literal> if the
<literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
option is set and the isolation level of the transaction is not
set to <literal role="isolevel">SERIALIZABLE</literal>. Thus, no
@@ -5732,10 +5751,11 @@
<para>
Gap locking is not needed for statements that lock rows using a
- unique index to search for a unique row. For example, the
- following statement uses only an index-record lock for the row
- having <literal>id</literal> value 100 and it does not matter
- whether other sessions insert rows in the preceding gap:
+ unique index to search for a unique row. For example, if the
+ <literal>id</literal> column has a unique index, the following
+ statement uses only an index-record lock for the row having
+ <literal>id</literal> value 100 and it does not matter whether
+ other sessions insert rows in the preceding gap:
</para>
<programlisting>
@@ -5743,6 +5763,11 @@
</programlisting>
<para>
+ If <literal>id</literal> is not indexed or has a non-unique
+ index, the statement does lock the preceding gap.
+ </para>
+
+ <para>
Gap locking can be disabled explicitly. This occurs if you
change the transaction isolation level to
<literal role="isolevel">READ COMMITTED</literal> or enable the
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2009-03-11 20:59:54 UTC (rev 14197)
+++ trunk/refman-6.0/se-innodb-core.xml 2009-03-12 00:04:21 UTC (rev 14198)
Changed blocks: 6, Lines Added: 37, Lines Deleted: 12; 4229 bytes
@@ -1723,9 +1723,18 @@
<para>
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"/>.)
+ locking for searches and index scans. The effect is similar to
+ setting the transaction isolation level to <literal>READ
+ COMMITTED</literal>. Enabling
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ is a global setting and affects all sessions, whereas the
+ isolation level can be set globally for all sessions, or
+ individually per sesssion. Also,
+ <literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
+ can be set only at server startup, whereas the isolation level
+ can be set at startup or changed at runtime. For additional
+ details about the effect of isolation level on gap locking,
+ see <xref linkend="set-transaction"/>.
</para>
<para>
@@ -1793,7 +1802,8 @@
is enabled, <literal>InnoDB</literal> guarantees at most an
isolation level of <literal role="isolevel">READ
COMMITTED</literal>. (Conflict serializability is still
- guaranteed.)
+ guaranteed.) For additional information about phantoms, see
+ <xref linkend="innodb-next-key-locking"/>.
</para>
<para>
@@ -1826,6 +1836,12 @@
</programlisting>
<para>
+ In this case, table has no indexes, so searches and index
+ scans use the hidden clustered index for record locking. See
+ <xref linkend="innodb-index-types"/>.
+ </para>
+
+ <para>
Suppose that one client performs an
<literal role="stmt">UPDATE</literal> using these statements:
</para>
@@ -5227,10 +5243,13 @@
<para>
<literal>InnoDB</literal> uses a consistent read for select in
- clauses like <literal>INSERT INTO ... SELECT</literal> and
- <literal>UPDATE ... (SELECT)</literal> that do not specify
- <literal>FOR UPDATE</literal> or <literal>IN SHARE
- MODE</literal> if the
+ clauses like <literal role="stmt" condition="insert">INSERT INTO
+ ... SELECT</literal>,
+ <literal role="stmt" condition="update">UPDATE ...
+ (SELECT)</literal>, and
+ <literal role="stmt" condition="create-table">CREATE TABLE ...
+ SELECT</literal> that do not specify <literal>FOR
+ UPDATE</literal> or <literal>IN SHARE MODE</literal> if the
<literal role="sysvar">innodb_locks_unsafe_for_binlog</literal>
option is set and the isolation level of the transaction is not
set to <literal role="isolevel">SERIALIZABLE</literal>. Thus, no
@@ -5560,10 +5579,11 @@
<para>
Gap locking is not needed for statements that lock rows using a
- unique index to search for a unique row. For example, the
- following statement uses only an index-record lock for the row
- having <literal>id</literal> value 100 and it does not matter
- whether other sessions insert rows in the preceding gap:
+ unique index to search for a unique row. For example, if the
+ <literal>id</literal> column has a unique index, the following
+ statement uses only an index-record lock for the row having
+ <literal>id</literal> value 100 and it does not matter whether
+ other sessions insert rows in the preceding gap:
</para>
<programlisting>
@@ -5571,6 +5591,11 @@
</programlisting>
<para>
+ If <literal>id</literal> is not indexed or has a non-unique
+ index, the statement does lock the preceding gap.
+ </para>
+
+ <para>
Gap locking can be disabled explicitly. This occurs if you
change the transaction isolation level to
<literal role="isolevel">READ COMMITTED</literal> or enable the
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r14198 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 12 Mar |
