Author: paul
Date: 2008-11-25 21:17:36 +0100 (Tue, 25 Nov 2008)
New Revision: 12651
Log:
r36135@frost: paul | 2008-11-25 14:16:15 -0500
Reorganize some of the innodb_locks_unsafe_for_binlog description
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:36125
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:36135
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-25 19:16:04 UTC (rev 12650)
+++ trunk/refman-4.1/se-innodb-core.xml 2008-11-25 20:17:36 UTC (rev 12651)
Changed blocks: 16, Lines Added: 100, Lines Deleted: 60; 12387 bytes
@@ -1716,7 +1716,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -1735,7 +1735,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -1750,36 +1750,46 @@
</indexterm>
<para>
+ This variable controls whether <literal>InnoDB</literal> uses
+ gap locking for searches and index scans.
+ </para>
+
+ <para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
<literal>InnoDB</literal> performs row-level locking in such a
way that when it searches or scans a table index, it sets
shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
+ Thus, the row-level locks are actually index-record locks. In
+ addition, a next-key lock on an index record also affects the
+ <quote>gap</quote> before that index record. That is, a
+ next-key lock is an index-record lock plus a gap lock on the
+ gap on the gap preceding the index record. If one session has
+ a shared or exclusive lock on record <literal>R</literal> in
+ an index, another session cannot insert a new index record in
+ the gap immediately before <literal>R</literal> in the index
+ order.
</para>
<para>
- A next-key lock on an index record also affects the
- <quote>gap</quote> before that index record. That is, a
- next-key lock is an index record lock plus a gap lock. If a
- user has a shared or exclusive lock on record
- <literal>R</literal> in an index, another user cannot insert a
- new index record in the gap immediately before
- <literal>R</literal> in the index order.
+ 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:
+ <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.
</para>
<para>
- 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.
+ Use of gap locking is also dependent on the transaction
+ isolation level (see <xref linkend="set-transaction"/>).
</para>
<para>
- The value of <literal>innodb_locks_unsafe_for_binlog</literal>
- does not affect the use of next-key locking for foreign-key
+ 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
@@ -1789,11 +1799,13 @@
<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 of the <literal>child</literal>
- table and that you want to read and lock all rows from the
- table with an identifier value larger than 100, with the
- intention of updating some column in the selected rows later:
+ cause phantom problems because when gap locking is disabled,
+ other sessions can insert new rows into the gaps. 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
+ than 100, with the intention of updating some column in the
+ selected rows later:
</para>
<programlisting>
@@ -1803,14 +1815,15 @@
<para>
The query scans the index starting from the first record where
<literal>id</literal> is greater than 100. If the locks set on
- the index records did not lock out inserts made in the gaps,
- another session would be able to insert a new row into the
- table. Consequently, if you were to execute the same
+ the index records do not lock out inserts made in the gaps,
+ another session can insert a new row into the table.
+ Consequently, if you were to 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
+ serializability. Therefore, if
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled
<literal>InnoDB</literal> guarantees at most an isolation
level of <literal role="isolevel">READ COMMITTED</literal>.
(Conflict serializability is still guaranteed.)
@@ -3913,6 +3926,16 @@
</para>
<para>
+ A session that has autocommit enabled can perform a
+ multiple-statement transaction by starting it with an explicit
+ <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>.
+ </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
@@ -3930,16 +3953,6 @@
current transaction.
</para>
- <para>
- A session that has autocommit enabled can perform a
- multiple-statement transaction by starting it with an explicit
- <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>.
- </para>
-
<indexterm>
<primary>InnoDB</primary>
<secondary>transaction isolation levels</secondary>
@@ -3980,7 +3993,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -3999,7 +4012,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -4525,7 +4538,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4544,7 +4557,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -4588,6 +4601,37 @@
</itemizedlist>
+ <para>
+ By default, <literal>InnoDB</literal> operates in
+ <literal role="isolevel">REPEATABLE READ</literal> transaction
+ isolation level and with the
+ <literal>innodb_locks_unsafe_for_binlog</literal> system
+ variable disabled. In this case, <literal>InnoDB</literal> uses
+ next-key locks for searches and index scans, which prevents
+ phantom rows (see <xref linkend="innodb-next-key-locking"/>).
+ Under some circumstances, gap locking is disabled, so that
+ searches and index scans use only index-record locks:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you change the transaction isolation level to
+ <literal role="isolevel">READ COMMITTED</literal>, locking
+ reads do not lock gaps.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
+ disables gap locking for searchs and index scans.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
</section>
<section id="innodb-next-key-locking">
@@ -4601,7 +4645,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4620,7 +4664,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -4636,22 +4680,18 @@
<para>
In row-level locking, <literal>InnoDB</literal> uses an
- algorithm called <emphasis>next-key locking</emphasis>.
- <literal>InnoDB</literal> performs the row-level locking in such
- a way that when it searches or scans a table index, it sets
- shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
- </para>
-
- <para>
- A next-key lock on an index record also affects the
+ algorithm called <firstterm>next-key locking</firstterm>.
+ <literal>InnoDB</literal> performs row-level locking in such a
+ way that when it searches or scans a table index, it sets shared
+ or exclusive locks on the index records it encounters. Thus, the
+ row-level locks are actually index-record locks. In addition, a
+ next-key lock on an index record also affects the
<quote>gap</quote> before that index record. That is, a next-key
- lock is an index record lock plus a gap lock. If a user has a
- shared or exclusive lock on record <literal>R</literal> in an
- index, another user cannot insert a new index record in the gap
- immediately before <literal>R</literal> in the index order. (A
- gap lock refers to a lock that locks only a gap before some
- index record.)
+ lock is an index-record lock plus a gap lock on the gap on the
+ gap preceding the index record. If one session has a shared or
+ exclusive lock on record <literal>R</literal> in an index,
+ another session cannot insert a new index record in the gap
+ immediately before <literal>R</literal> in the index order.
</para>
<para>
@@ -4676,9 +4716,9 @@
column and the table contains row with <literal>id</literal>
values of 90 and 102. The query scans that index starting from
the first record where <literal>id</literal> is bigger than 100.
- If the locks set on the index records did not lock out inserts
+ If the locks set on the index records do not lock out inserts
made in the gaps (in this case, the gap between 90 and 102),
- another session might insert a new row into the table with an
+ another session can insert a new row into the table with an
<literal>id</literal> of 101. If you were to execute the same
<literal role="stmt">SELECT</literal> within the same
transaction, you would see a new row with an
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2008-11-25 19:16:04 UTC (rev 12650)
+++ trunk/refman-5.0/se-innodb-core.xml 2008-11-25 20:17:36 UTC (rev 12651)
Changed blocks: 18, Lines Added: 146, Lines Deleted: 96; 16899 bytes
@@ -1674,7 +1674,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -1693,7 +1693,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -1708,36 +1708,46 @@
</indexterm>
<para>
+ This variable controls whether <literal>InnoDB</literal> uses
+ gap locking for searches and index scans.
+ </para>
+
+ <para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
<literal>InnoDB</literal> performs row-level locking in such a
way that when it searches or scans a table index, it sets
shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
+ Thus, the row-level locks are actually index-record locks. In
+ addition, a next-key lock on an index record also affects the
+ <quote>gap</quote> before that index record. That is, a
+ next-key lock is an index-record lock plus a gap lock on the
+ gap on the gap preceding the index record. If one session has
+ a shared or exclusive lock on record <literal>R</literal> in
+ an index, another session cannot insert a new index record in
+ the gap immediately before <literal>R</literal> in the index
+ order.
</para>
<para>
- A next-key lock on an index record also affects the
- <quote>gap</quote> before that index record. That is, a
- next-key lock is an index record lock plus a gap lock. If a
- user has a shared or exclusive lock on record
- <literal>R</literal> in an index, another user cannot insert a
- new index record in the gap immediately before
- <literal>R</literal> in the index order.
+ 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:
+ <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.
</para>
<para>
- 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.
+ Use of gap locking is also dependent on the transaction
+ isolation level (see <xref linkend="set-transaction"/>).
</para>
<para>
- The value of <literal>innodb_locks_unsafe_for_binlog</literal>
- does not affect the use of next-key locking for foreign-key
+ 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
@@ -1747,11 +1757,13 @@
<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 of the <literal>child</literal>
- table and that you want to read and lock all rows from the
- table with an identifier value larger than 100, with the
- intention of updating some column in the selected rows later:
+ cause phantom problems because when gap locking is disabled,
+ other sessions can insert new rows into the gaps. 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
+ than 100, with the intention of updating some column in the
+ selected rows later:
</para>
<programlisting>
@@ -1761,27 +1773,30 @@
<para>
The query scans the index starting from the first record where
<literal>id</literal> is greater than 100. If the locks set on
- the index records did not lock out inserts made in the gaps,
- another session would be able to insert a new row into the
- table. Consequently, if you were to execute the same
+ the index records do not lock out inserts made in the gaps,
+ another session can insert a new row into the table.
+ Consequently, if you were to 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
+ serializability. Therefore, if
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled,
<literal>InnoDB</literal> guarantees at most an isolation
level of <literal role="isolevel">READ COMMITTED</literal>.
(Conflict serializability is still guaranteed.)
</para>
<para>
- Starting from MySQL 5.0.2, enabling this variable is even more
- unsafe. For an <literal role="stmt">UPDATE</literal> or a
+ Starting from MySQL 5.0.2, enabling
+ <literal>innodb_locks_unsafe_for_binlog</literal> has an
+ additional effect: For an
+ <literal role="stmt">UPDATE</literal> or a
<literal role="stmt">DELETE</literal>,
- <literal>InnoDB</literal> locks only rows that it updates or
- deletes. This greatly reduces the probability of deadlocks,
- but they can still happen. Note that enabling this variable
- still does not allow operations such as
+ <literal>InnoDB</literal> holds locks only for rows that it
+ updates or deletes. This greatly reduces the probability of
+ deadlocks, but they can still happen. Note that enabling this
+ variable still does not allow operations such as
<literal role="stmt">UPDATE</literal> to overtake other
similar operations (such as another
<literal role="stmt">UPDATE</literal>) even when they affect
@@ -1805,47 +1820,40 @@
</programlisting>
<para>
- Then suppose that another client executes these statements
- following those of the first client:
+ <literal>InnoDB</literal> executes the first
+ <literal role="stmt">UPDATE</literal> as follows:
</para>
<programlisting>
-SET autocommit = 0;
-UPDATE t SET b = 4 WHERE b = 2;
+x-lock(1,2)
+unlock(1,2)
+x-lock(2,3)
+update(2,3) to (2,5); retain x-lock
+x-lock(3,2)
+unlock(3,2)
+x-lock(4,3)
+update(4,3) to (4,5); retain x-lock
+x-lock(5,2)
+unlock(5,2)
</programlisting>
<para>
- In this case, the second <literal role="stmt">UPDATE</literal>
- must wait for a commit or rollback of the first
- <literal role="stmt">UPDATE</literal>, even though if affects
- different rows. The first
- <literal role="stmt">UPDATE</literal> has an exclusive lock on
- 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 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. If not, it releases the unnecessary
- lock if the <literal>innodb_locks_unsafe_for_binlog</literal>
+ <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.
</para>
<para>
- Therefore, <literal>InnoDB</literal> executes the first
- <literal role="stmt">UPDATE</literal> as follows:
+ Then suppose that another client executes these statements
+ following those of the first client:
</para>
<programlisting>
-x-lock(1,2)
-unlock(1,2)
-x-lock(2,3)
-update(2,3) to (2,5); do not unlock
-x-lock(3,2)
-unlock(3,2)
-x-lock(4,3)
-update(4,3) to (4,5); do not unlock
-x-lock(5,2)
-unlock(5,2)
+SET autocommit = 0;
+UPDATE t SET b = 4 WHERE b = 2;
</programlisting>
<para>
@@ -1855,9 +1863,24 @@
<programlisting>
x-lock(1,2)
-update(1,2) to (1,4); do not unlock
-x-lock(2,3) - wait for first UPDATE commit or rollback
+update(1,2) to (1,4); retain x-lock
+x-lock(2,3) - wait for first UPDATE to commit or rollback
</programlisting>
+
+ <para>
+ In this case, the second <literal role="stmt">UPDATE</literal>
+ must wait for a commit or rollback of the first
+ <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
+ <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.
+ </para>
</listitem>
<listitem>
@@ -3966,6 +3989,16 @@
</para>
<para>
+ A session that has autocommit enabled can perform a
+ multiple-statement transaction by starting it with an explicit
+ <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>.
+ </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
@@ -3983,16 +4016,6 @@
current transaction.
</para>
- <para>
- A session that has autocommit enabled can perform a
- multiple-statement transaction by starting it with an explicit
- <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>.
- </para>
-
<indexterm>
<primary>InnoDB</primary>
<secondary>transaction isolation levels</secondary>
@@ -4028,7 +4051,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4047,7 +4070,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -4590,7 +4613,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4609,7 +4632,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -4653,6 +4676,37 @@
</itemizedlist>
+ <para>
+ By default, <literal>InnoDB</literal> operates in
+ <literal role="isolevel">REPEATABLE READ</literal> transaction
+ isolation level and with the
+ <literal>innodb_locks_unsafe_for_binlog</literal> system
+ variable disabled. In this case, <literal>InnoDB</literal> uses
+ next-key locks for searches and index scans, which prevents
+ phantom rows (see <xref linkend="innodb-next-key-locking"/>).
+ Under some circumstances, gap locking is disabled, so that
+ searches and index scans use only index-record locks:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you change the transaction isolation level to
+ <literal role="isolevel">READ COMMITTED</literal>, locking
+ reads do not lock gaps.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
+ disables gap locking for searchs and index scans.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
</section>
<section id="innodb-next-key-locking">
@@ -4666,7 +4720,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4685,7 +4739,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -4701,22 +4755,18 @@
<para>
In row-level locking, <literal>InnoDB</literal> uses an
- algorithm called <emphasis>next-key locking</emphasis>.
- <literal>InnoDB</literal> performs the row-level locking in such
- a way that when it searches or scans a table index, it sets
- shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
- </para>
-
- <para>
- A next-key lock on an index record also affects the
+ algorithm called <firstterm>next-key locking</firstterm>.
+ <literal>InnoDB</literal> performs row-level locking in such a
+ way that when it searches or scans a table index, it sets shared
+ or exclusive locks on the index records it encounters. Thus, the
+ row-level locks are actually index-record locks. In addition, a
+ next-key lock on an index record also affects the
<quote>gap</quote> before that index record. That is, a next-key
- lock is an index record lock plus a gap lock. If a user has a
- shared or exclusive lock on record <literal>R</literal> in an
- index, another user cannot insert a new index record in the gap
- immediately before <literal>R</literal> in the index order. (A
- gap lock refers to a lock that locks only a gap before some
- index record.)
+ lock is an index-record lock plus a gap lock on the gap on the
+ gap preceding the index record. If one session has a shared or
+ exclusive lock on record <literal>R</literal> in an index,
+ another session cannot insert a new index record in the gap
+ immediately before <literal>R</literal> in the index order.
</para>
<para>
@@ -4741,9 +4791,9 @@
column and the table contains row with <literal>id</literal>
values of 90 and 102. The query scans that index starting from
the first record where <literal>id</literal> is bigger than 100.
- If the locks set on the index records did not lock out inserts
+ If the locks set on the index records do not lock out inserts
made in the gaps (in this case, the gap between 90 and 102),
- another session might insert a new row into the table with an
+ another session can insert a new row into the table with an
<literal>id</literal> of 101. If you were to execute the same
<literal role="stmt">SELECT</literal> within the same
transaction, you would see a new row with an
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2008-11-25 19:16:04 UTC (rev 12650)
+++ trunk/refman-5.1/se-innodb-core.xml 2008-11-25 20:17:36 UTC (rev 12651)
Changed blocks: 18, Lines Added: 144, Lines Deleted: 95; 16788 bytes
@@ -1716,7 +1716,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -1735,7 +1735,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -1750,36 +1750,46 @@
</indexterm>
<para>
+ This variable controls whether <literal>InnoDB</literal> uses
+ gap locking for searches and index scans.
+ </para>
+
+ <para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
<literal>InnoDB</literal> performs row-level locking in such a
way that when it searches or scans a table index, it sets
shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
+ Thus, the row-level locks are actually index-record locks. In
+ addition, a next-key lock on an index record also affects the
+ <quote>gap</quote> before that index record. That is, a
+ next-key lock is an index-record lock plus a gap lock on the
+ gap on the gap preceding the index record. If one session has
+ a shared or exclusive lock on record <literal>R</literal> in
+ an index, another session cannot insert a new index record in
+ the gap immediately before <literal>R</literal> in the index
+ order.
</para>
<para>
- A next-key lock on an index record also affects the
- <quote>gap</quote> before that index record. That is, a
- next-key lock is an index record lock plus a gap lock. If a
- user has a shared or exclusive lock on record
- <literal>R</literal> in an index, another user cannot insert a
- new index record in the gap immediately before
- <literal>R</literal> in the index order.
+ 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:
+ <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.
</para>
<para>
- 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.
+ Use of gap locking is also dependent on the transaction
+ isolation level (see <xref linkend="set-transaction"/>).
</para>
<para>
- The value of <literal>innodb_locks_unsafe_for_binlog</literal>
- does not affect the use of next-key locking for foreign-key
+ 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
@@ -1789,11 +1799,13 @@
<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 of the <literal>child</literal>
- table and that you want to read and lock all rows from the
- table with an identifier value larger than 100, with the
- intention of updating some column in the selected rows later:
+ cause phantom problems because when gap locking is disabled,
+ other sessions can insert new rows into the gaps. 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
+ than 100, with the intention of updating some column in the
+ selected rows later:
</para>
<programlisting>
@@ -1803,27 +1815,29 @@
<para>
The query scans the index starting from the first record where
<literal>id</literal> is greater than 100. If the locks set on
- the index records did not lock out inserts made in the gaps,
- another session would be able to insert a new row into the
- table. Consequently, if you were to execute the same
+ the index records do not lock out inserts made in the gaps,
+ another session can insert a new row into the table.
+ Consequently, if you were to 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,
+ serializability. Therefore, if
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled,
<literal>InnoDB</literal> guarantees at most an isolation
level of <literal role="isolevel">READ COMMITTED</literal>.
(Conflict serializability is still guaranteed.)
</para>
<para>
- Enabling this variable has an additional effect: For an
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal> has
+ an additional effect: For an
<literal role="stmt">UPDATE</literal> or a
<literal role="stmt">DELETE</literal>,
- <literal>InnoDB</literal> locks only rows that it updates or
- deletes. This greatly reduces the probability of deadlocks,
- but they can still happen. Note that enabling this variable
- still does not allow operations such as
+ <literal>InnoDB</literal> holds locks only for rows that it
+ updates or deletes. This greatly reduces the probability of
+ deadlocks, but they can still happen. Note that enabling this
+ variable still does not allow operations such as
<literal role="stmt">UPDATE</literal> to overtake other
similar operations (such as another
<literal role="stmt">UPDATE</literal>) even when they affect
@@ -1847,47 +1861,40 @@
</programlisting>
<para>
- Then suppose that another client executes these statements
- following those of the first client:
+ <literal>InnoDB</literal> executes the first
+ <literal role="stmt">UPDATE</literal> as follows:
</para>
<programlisting>
-SET autocommit = 0;
-UPDATE t SET b = 4 WHERE b = 2;
+x-lock(1,2)
+unlock(1,2)
+x-lock(2,3)
+update(2,3) to (2,5); retain x-lock
+x-lock(3,2)
+unlock(3,2)
+x-lock(4,3)
+update(4,3) to (4,5); retain x-lock
+x-lock(5,2)
+unlock(5,2)
</programlisting>
<para>
- In this case, the second <literal role="stmt">UPDATE</literal>
- must wait for a commit or rollback of the first
- <literal role="stmt">UPDATE</literal>, even though if affects
- different rows. The first
- <literal role="stmt">UPDATE</literal> has an exclusive lock on
- 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 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. If not, it releases the unnecessary
- lock if the <literal>innodb_locks_unsafe_for_binlog</literal>
+ <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.
</para>
<para>
- Therefore, <literal>InnoDB</literal> executes the first
- <literal role="stmt">UPDATE</literal> as follows:
+ Then suppose that another client executes these statements
+ following those of the first client:
</para>
<programlisting>
-x-lock(1,2)
-unlock(1,2)
-x-lock(2,3)
-update(2,3) to (2,5); do not unlock
-x-lock(3,2)
-unlock(3,2)
-x-lock(4,3)
-update(4,3) to (4,5); do not unlock
-x-lock(5,2)
-unlock(5,2)
+SET autocommit = 0;
+UPDATE t SET b = 4 WHERE b = 2;
</programlisting>
<para>
@@ -1897,9 +1904,24 @@
<programlisting>
x-lock(1,2)
-update(1,2) to (1,4); do not unlock
-x-lock(2,3) - wait for first UPDATE commit or rollback
+update(1,2) to (1,4); retain x-lock
+x-lock(2,3) - wait for first UPDATE to commit or rollback
</programlisting>
+
+ <para>
+ In this case, the second <literal role="stmt">UPDATE</literal>
+ must wait for a commit or rollback of the first
+ <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
+ <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.
+ </para>
</listitem>
<listitem>
@@ -4664,6 +4686,16 @@
</para>
<para>
+ A session that has autocommit enabled can perform a
+ multiple-statement transaction by starting it with an explicit
+ <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>.
+ </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
@@ -4681,16 +4713,6 @@
current transaction.
</para>
- <para>
- A session that has autocommit enabled can perform a
- multiple-statement transaction by starting it with an explicit
- <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>.
- </para>
-
<indexterm>
<primary>InnoDB</primary>
<secondary>transaction isolation levels</secondary>
@@ -4726,7 +4748,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4745,7 +4767,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -5298,7 +5320,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -5317,7 +5339,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -5361,6 +5383,37 @@
</itemizedlist>
+ <para>
+ By default, <literal>InnoDB</literal> operates in
+ <literal role="isolevel">REPEATABLE READ</literal> transaction
+ isolation level and with the
+ <literal>innodb_locks_unsafe_for_binlog</literal> system
+ variable disabled. In this case, <literal>InnoDB</literal> uses
+ next-key locks for searches and index scans, which prevents
+ phantom rows (see <xref linkend="innodb-next-key-locking"/>).
+ Under some circumstances, gap locking is disabled, so that
+ searches and index scans use only index-record locks:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you change the transaction isolation level to
+ <literal role="isolevel">READ COMMITTED</literal>, locking
+ reads do not lock gaps.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
+ disables gap locking for searchs and index scans.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
</section>
<section id="innodb-next-key-locking">
@@ -5374,7 +5427,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -5393,7 +5446,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -5409,22 +5462,18 @@
<para>
In row-level locking, <literal>InnoDB</literal> uses an
- algorithm called <emphasis>next-key locking</emphasis>.
- <literal>InnoDB</literal> performs the row-level locking in such
- a way that when it searches or scans a table index, it sets
- shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
- </para>
-
- <para>
- A next-key lock on an index record also affects the
+ algorithm called <firstterm>next-key locking</firstterm>.
+ <literal>InnoDB</literal> performs row-level locking in such a
+ way that when it searches or scans a table index, it sets shared
+ or exclusive locks on the index records it encounters. Thus, the
+ row-level locks are actually index-record locks. In addition, a
+ next-key lock on an index record also affects the
<quote>gap</quote> before that index record. That is, a next-key
- lock is an index record lock plus a gap lock. If a user has a
- shared or exclusive lock on record <literal>R</literal> in an
- index, another user cannot insert a new index record in the gap
- immediately before <literal>R</literal> in the index order. (A
- gap lock refers to a lock that locks only a gap before some
- index record.)
+ lock is an index-record lock plus a gap lock on the gap on the
+ gap preceding the index record. If one session has a shared or
+ exclusive lock on record <literal>R</literal> in an index,
+ another session cannot insert a new index record in the gap
+ immediately before <literal>R</literal> in the index order.
</para>
<para>
@@ -5449,9 +5498,9 @@
column and the table contains row with <literal>id</literal>
values of 90 and 102. The query scans that index starting from
the first record where <literal>id</literal> is bigger than 100.
- If the locks set on the index records did not lock out inserts
+ If the locks set on the index records do not lock out inserts
made in the gaps (in this case, the gap between 90 and 102),
- another session might insert a new row into the table with an
+ another session can insert a new row into the table with an
<literal>id</literal> of 101. If you were to execute the same
<literal role="stmt">SELECT</literal> within the same
transaction, you would see a new row with an
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2008-11-25 19:16:04 UTC (rev 12650)
+++ trunk/refman-6.0/se-innodb-core.xml 2008-11-25 20:17:36 UTC (rev 12651)
Changed blocks: 18, Lines Added: 144, Lines Deleted: 95; 16788 bytes
@@ -1645,7 +1645,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -1664,7 +1664,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -1679,36 +1679,46 @@
</indexterm>
<para>
+ This variable controls whether <literal>InnoDB</literal> uses
+ gap locking for searches and index scans.
+ </para>
+
+ <para>
Normally, <literal>InnoDB</literal> uses an algorithm called
<firstterm>next-key locking</firstterm>.
<literal>InnoDB</literal> performs row-level locking in such a
way that when it searches or scans a table index, it sets
shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
+ Thus, the row-level locks are actually index-record locks. In
+ addition, a next-key lock on an index record also affects the
+ <quote>gap</quote> before that index record. That is, a
+ next-key lock is an index-record lock plus a gap lock on the
+ gap on the gap preceding the index record. If one session has
+ a shared or exclusive lock on record <literal>R</literal> in
+ an index, another session cannot insert a new index record in
+ the gap immediately before <literal>R</literal> in the index
+ order.
</para>
<para>
- A next-key lock on an index record also affects the
- <quote>gap</quote> before that index record. That is, a
- next-key lock is an index record lock plus a gap lock. If a
- user has a shared or exclusive lock on record
- <literal>R</literal> in an index, another user cannot insert a
- new index record in the gap immediately before
- <literal>R</literal> in the index order.
+ 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:
+ <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.
</para>
<para>
- 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.
+ Use of gap locking is also dependent on the transaction
+ isolation level (see <xref linkend="set-transaction"/>).
</para>
<para>
- The value of <literal>innodb_locks_unsafe_for_binlog</literal>
- does not affect the use of next-key locking for foreign-key
+ 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
@@ -1718,11 +1728,13 @@
<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 of the <literal>child</literal>
- table and that you want to read and lock all rows from the
- table with an identifier value larger than 100, with the
- intention of updating some column in the selected rows later:
+ cause phantom problems because when gap locking is disabled,
+ other sessions can insert new rows into the gaps. 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
+ than 100, with the intention of updating some column in the
+ selected rows later:
</para>
<programlisting>
@@ -1732,27 +1744,29 @@
<para>
The query scans the index starting from the first record where
<literal>id</literal> is greater than 100. If the locks set on
- the index records did not lock out inserts made in the gaps,
- another session would be able to insert a new row into the
- table. Consequently, if you were to execute the same
+ the index records do not lock out inserts made in the gaps,
+ another session can insert a new row into the table.
+ Consequently, if you were to 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,
+ serializability. Therefore, if
+ <literal>innodb_locks_unsafe_for_binlog</literal> is enabled,
<literal>InnoDB</literal> guarantees at most an isolation
level of <literal role="isolevel">READ COMMITTED</literal>.
(Conflict serializability is still guaranteed.)
</para>
<para>
- Enabling this variable has an additional effect: For an
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal> has
+ an additional effect: For an
<literal role="stmt">UPDATE</literal> or a
<literal role="stmt">DELETE</literal>,
- <literal>InnoDB</literal> locks only rows that it updates or
- deletes. This greatly reduces the probability of deadlocks,
- but they can still happen. Note that enabling this variable
- still does not allow operations such as
+ <literal>InnoDB</literal> holds locks only for rows that it
+ updates or deletes. This greatly reduces the probability of
+ deadlocks, but they can still happen. Note that enabling this
+ variable still does not allow operations such as
<literal role="stmt">UPDATE</literal> to overtake other
similar operations (such as another
<literal role="stmt">UPDATE</literal>) even when they affect
@@ -1776,47 +1790,40 @@
</programlisting>
<para>
- Then suppose that another client executes these statements
- following those of the first client:
+ <literal>InnoDB</literal> executes the first
+ <literal role="stmt">UPDATE</literal> as follows:
</para>
<programlisting>
-SET autocommit = 0;
-UPDATE t SET b = 4 WHERE b = 2;
+x-lock(1,2)
+unlock(1,2)
+x-lock(2,3)
+update(2,3) to (2,5); retain x-lock
+x-lock(3,2)
+unlock(3,2)
+x-lock(4,3)
+update(4,3) to (4,5); retain x-lock
+x-lock(5,2)
+unlock(5,2)
</programlisting>
<para>
- In this case, the second <literal role="stmt">UPDATE</literal>
- must wait for a commit or rollback of the first
- <literal role="stmt">UPDATE</literal>, even though if affects
- different rows. The first
- <literal role="stmt">UPDATE</literal> has an exclusive lock on
- 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 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. If not, it releases the unnecessary
- lock if the <literal>innodb_locks_unsafe_for_binlog</literal>
+ <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.
</para>
<para>
- Therefore, <literal>InnoDB</literal> executes the first
- <literal role="stmt">UPDATE</literal> as follows:
+ Then suppose that another client executes these statements
+ following those of the first client:
</para>
<programlisting>
-x-lock(1,2)
-unlock(1,2)
-x-lock(2,3)
-update(2,3) to (2,5); do not unlock
-x-lock(3,2)
-unlock(3,2)
-x-lock(4,3)
-update(4,3) to (4,5); do not unlock
-x-lock(5,2)
-unlock(5,2)
+SET autocommit = 0;
+UPDATE t SET b = 4 WHERE b = 2;
</programlisting>
<para>
@@ -1826,9 +1833,24 @@
<programlisting>
x-lock(1,2)
-update(1,2) to (1,4); do not unlock
-x-lock(2,3) - wait for first UPDATE commit or rollback
+update(1,2) to (1,4); retain x-lock
+x-lock(2,3) - wait for first UPDATE to commit or rollback
</programlisting>
+
+ <para>
+ In this case, the second <literal role="stmt">UPDATE</literal>
+ must wait for a commit or rollback of the first
+ <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
+ <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.
+ </para>
</listitem>
<listitem>
@@ -4531,6 +4553,16 @@
</para>
<para>
+ A session that has autocommit enabled can perform a
+ multiple-statement transaction by starting it with an explicit
+ <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>.
+ </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
@@ -4548,16 +4580,6 @@
current transaction.
</para>
- <para>
- A session that has autocommit enabled can perform a
- multiple-statement transaction by starting it with an explicit
- <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>.
- </para>
-
<indexterm>
<primary>InnoDB</primary>
<secondary>transaction isolation levels</secondary>
@@ -4593,7 +4615,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -4612,7 +4634,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -5165,7 +5187,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -5184,7 +5206,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -5228,6 +5250,37 @@
</itemizedlist>
+ <para>
+ By default, <literal>InnoDB</literal> operates in
+ <literal role="isolevel">REPEATABLE READ</literal> transaction
+ isolation level and with the
+ <literal>innodb_locks_unsafe_for_binlog</literal> system
+ variable disabled. In this case, <literal>InnoDB</literal> uses
+ next-key locks for searches and index scans, which prevents
+ phantom rows (see <xref linkend="innodb-next-key-locking"/>).
+ Under some circumstances, gap locking is disabled, so that
+ searches and index scans use only index-record locks:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you change the transaction isolation level to
+ <literal role="isolevel">READ COMMITTED</literal>, locking
+ reads do not lock gaps.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Enabling <literal>innodb_locks_unsafe_for_binlog</literal>
+ disables gap locking for searchs and index scans.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
</section>
<section id="innodb-next-key-locking">
@@ -5241,7 +5294,7 @@
<indexterm>
<primary>InnoDB</primary>
- <secondary>index record lock</secondary>
+ <secondary>index-record lock</secondary>
</indexterm>
<indexterm>
@@ -5260,7 +5313,7 @@
</indexterm>
<indexterm>
- <primary>index record lock</primary>
+ <primary>index-record lock</primary>
<secondary>InnoDB</secondary>
</indexterm>
@@ -5276,22 +5329,18 @@
<para>
In row-level locking, <literal>InnoDB</literal> uses an
- algorithm called <emphasis>next-key locking</emphasis>.
- <literal>InnoDB</literal> performs the row-level locking in such
- a way that when it searches or scans a table index, it sets
- shared or exclusive locks on the index records it encounters.
- Thus, the row-level locks are actually index record locks.
- </para>
-
- <para>
- A next-key lock on an index record also affects the
+ algorithm called <firstterm>next-key locking</firstterm>.
+ <literal>InnoDB</literal> performs row-level locking in such a
+ way that when it searches or scans a table index, it sets shared
+ or exclusive locks on the index records it encounters. Thus, the
+ row-level locks are actually index-record locks. In addition, a
+ next-key lock on an index record also affects the
<quote>gap</quote> before that index record. That is, a next-key
- lock is an index record lock plus a gap lock. If a user has a
- shared or exclusive lock on record <literal>R</literal> in an
- index, another user cannot insert a new index record in the gap
- immediately before <literal>R</literal> in the index order. (A
- gap lock refers to a lock that locks only a gap before some
- index record.)
+ lock is an index-record lock plus a gap lock on the gap on the
+ gap preceding the index record. If one session has a shared or
+ exclusive lock on record <literal>R</literal> in an index,
+ another session cannot insert a new index record in the gap
+ immediately before <literal>R</literal> in the index order.
</para>
<para>
@@ -5316,9 +5365,9 @@
column and the table contains row with <literal>id</literal>
values of 90 and 102. The query scans that index starting from
the first record where <literal>id</literal> is bigger than 100.
- If the locks set on the index records did not lock out inserts
+ If the locks set on the index records do not lock out inserts
made in the gaps (in this case, the gap between 90 and 102),
- another session might insert a new row into the table with an
+ another session can insert a new row into the table with an
<literal>id</literal> of 101. If you were to execute the same
<literal role="stmt">SELECT</literal> within the same
transaction, you would see a new row with an
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r12651 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 25 Nov |
