Author: paul
Date: 2009-04-21 20:07:33 +0200 (Tue, 21 Apr 2009)
New Revision: 14693
Log:
r40588@frost: paul | 2009-04-21 12:57:06 -0500
InnoDB minor updates
Add auto-link markup
Modified:
trunk/dynamic-docs/changelog/mysqld.xml
trunk/refman-4.1/news-4.0.xml
trunk/refman-4.1/se-innodb-core.xml
trunk/refman-4.1/sql-syntax-transactions.xml
trunk/refman-5.0/se-innodb-core.xml
trunk/refman-5.0/sql-syntax-transactions.xml
trunk/refman-5.1/se-innodb-core.xml
trunk/refman-5.1/sql-syntax-transactions.xml
trunk/refman-6.0/se-innodb-core.xml
trunk/refman-6.0/sql-syntax-transactions.xml
trunk/refman-common/news-innodb.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:40568
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:37553
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:41755
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:40588
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:37553
Modified: trunk/dynamic-docs/changelog/mysqld.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/dynamic-docs/changelog/mysqld.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 12, Lines Added: 39, Lines Deleted: 31; 6240 bytes
@@ -2233,10 +2233,10 @@
<message>
<para>
- Two simultaneous <literal>SELECT ... FOR UPDATE</literal>
- statements with <literal role="isolevel">READ
- COMMITTED</literal> isolation level would result in the wrong
- error message being returned.
+ Two simultaneous <literal role="stmt" condition="select">SELECT
+ ... FOR UPDATE</literal> statements with
+ <literal role="isolevel">READ COMMITTED</literal> isolation
+ level would result in the wrong error message being returned.
</para>
</message>
@@ -2520,7 +2520,8 @@
<message>
<para>
- Using <literal>SELECT ... FOR UPDATE</literal> and
+ Using <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> and
<literal role="stmt" condition="commit">ROLLBACK</literal> could
cause <literal>mysqld</literal> to hang indefinitely.
</para>
@@ -2803,7 +2804,8 @@
<message>
<para>
- Locking between sessions when using <literal>SELECT ... FOR
+ Locking between sessions when using
+ <literal role="stmt" condition="select">SELECT ... FOR
UPDATE</literal> would not work.
</para>
@@ -2824,9 +2826,9 @@
<message>
<para>
- Executing <literal>SELECT ... FOR UPDATE</literal> in a second
- connection on a newly created and populated table could cause a
- crash.
+ Executing <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> in a second connection on a newly created and
+ populated table could cause a crash.
</para>
</message>
@@ -3953,7 +3955,8 @@
<message>
<para>
- <literal>SELECT ... FOR UPDATE</literal> is now supported.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> is now supported.
</para>
</message>
@@ -35169,8 +35172,9 @@
<message>
<para>
- <literal>SELECT ... FOR UPDATE</literal>, <literal>SELECT ...
- LOCK IN SHARE MODE</literal>,
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal>, <literal role="stmt" condition="select">SELECT
+ ... LOCK IN SHARE MODE</literal>,
<literal role="stmt">DELETE</literal>, and
<literal role="stmt">UPDATE</literal> statements executed using
a full table scan were not releasing locks on rows that did not
@@ -40661,7 +40665,8 @@
<replaceable>keypartN</replaceable>=<replaceable>constant</replaceable>
ORDER BY ... FOR UPDATE</literal> sometimes were unnecessarily
blocked waiting for a lock if another transaction was using
- <literal>SELECT ... FOR UPDATE</literal> on the same table.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> on the same table.
</para>
</message>
@@ -42542,8 +42547,9 @@
<message>
<para>
- <literal>SELECT ... FOR UPDATE</literal> with partitioned tables
- could cause a server crash.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> with partitioned tables could cause a server
+ crash.
</para>
</message>
@@ -45193,11 +45199,12 @@
<message>
<para>
- For <literal>SELECT ... FOR UPDATE</literal> statements that
- used <literal>DISTINCT</literal> or <literal>GROUP BY</literal>
- over all key parts of a unique index (or primary key), the
- optimizer unnecessarily created a temporary table, thus losing
- the linkage to the underlying unique index values. This caused a
+ For <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> statements that used
+ <literal>DISTINCT</literal> or <literal>GROUP BY</literal> over
+ all key parts of a unique index (or primary key), the optimizer
+ unnecessarily created a temporary table, thus losing the linkage
+ to the underlying unique index values. This caused a
<literal>Result set not updatable</literal> error. (The
temporary table is unnecessary because under these circumstances
the distinct or grouped columns must also be unique.)
@@ -89475,11 +89482,12 @@
<message>
<para>
- When <literal>SELECT ... FOR UPDATE</literal> or <literal>SELECT
- ... LOCK IN SHARE MODE</literal> for an
- <literal>InnoDB</literal> table were executed from within a
- stored function or a trigger, they were converted to a
- non-locking consistent read.
+ When <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> or
+ <literal role="stmt" condition="select">SELECT ... LOCK IN SHARE
+ MODE</literal> for an <literal>InnoDB</literal> table were
+ executed from within a stored function or a trigger, they were
+ converted to a non-locking consistent read.
</para>
</message>
@@ -116764,8 +116772,8 @@
<message>
<para>
- <literal>SELECT ... FOR UPDATE</literal> failed to lock the
- selected rows.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> failed to lock the selected rows.
</para>
</message>
@@ -131081,10 +131089,10 @@
locks to a transaction's weight, and avoids killing transactions
that mave modified non-transactional tables. This should reduce
the likelihood of killing long-running transactions containing
- <literal>SELECT ... FOR UPDATE</literal> or
- <literal>INSERT/REPLACE INTO ... SELECT</literal> statements,
- and of causing partial updates if the target is a
- <literal>MyISAM</literal> table.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> or <literal>INSERT/REPLACE INTO ...
+ SELECT</literal> statements, and of causing partial updates if
+ the target is a <literal>MyISAM</literal> table.
</para>
</message>
Modified: trunk/refman-4.1/news-4.0.xml
===================================================================
--- trunk/refman-4.1/news-4.0.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-4.1/news-4.0.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 1, Lines Added: 4, Lines Deleted: 3; 714 bytes
@@ -9420,9 +9420,10 @@
<listitem>
<para>
- Changed <literal>SELECT ... IN SHARE MODE</literal> to
- <literal>SELECT ... LOCK IN SHARE MODE</literal> (as in MySQL
- 3.23).
+ Changed <literal role="stmt" condition="select">SELECT ... IN
+ SHARE MODE</literal> to
+ <literal role="stmt" condition="select">SELECT ... LOCK IN
+ SHARE MODE</literal> (as in MySQL 3.23).
</para>
</listitem>
Modified: trunk/refman-4.1/se-innodb-core.xml
===================================================================
--- trunk/refman-4.1/se-innodb-core.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-4.1/se-innodb-core.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 9, Lines Added: 65, Lines Deleted: 51; 10142 bytes
@@ -4613,7 +4613,8 @@
<section id="innodb-locking-reads">
- <title><literal>SELECT ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
+ <title><literal role="stmt" condition="select">SELECT ... FOR UPDATE</literal>
+ and <literal role="stmt" condition="select">SELECT ... LOCK IN
SHARE MODE</literal> Locking Reads</title>
<para>
@@ -4626,20 +4627,22 @@
<listitem>
<para>
- <literal>SELECT ... LOCK IN SHARE MODE</literal> sets a
- shared mode lock on the rows read. A shared mode lock
- enables other sessions to read the rows but not to modify
- them. The rows read are the latest available, so if they
- belong to another transaction that has not yet committed,
- the read blocks until that transaction ends.
+ <literal role="stmt" condition="select">SELECT ... LOCK IN
+ SHARE MODE</literal> sets a shared mode lock on the rows
+ read. A shared mode lock enables other sessions to read the
+ rows but not to modify them. The rows read are the latest
+ available, so if they belong to another transaction that has
+ not yet committed, the read blocks until that transaction
+ ends.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FOR UPDATE</literal> sets an exclusive
- lock on the rows read. An exclusive lock prevents other
- sessions from accessing the rows for reading or writing.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> sets an exclusive lock on the rows read. An
+ exclusive lock prevents other sessions from accessing the
+ rows for reading or writing.
</para>
</listitem>
@@ -4746,17 +4749,19 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
- available data, setting exclusive locks on each row it reads.
- Thus, it sets the same locks a searched SQL
- <literal role="stmt">UPDATE</literal> would set on the rows.
+ A <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> reads the latest available data, setting
+ exclusive locks on each row it reads. Thus, it sets the same
+ locks a searched SQL <literal role="stmt">UPDATE</literal> would
+ set on the rows.
</para>
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> works. In MySQL, the specific task of
+ generating a unique identifier actually can be accomplished
+ using only a single access to the table:
</para>
<programlisting>
@@ -5122,11 +5127,13 @@
</para>
<para>
- For <literal>SELECT ... FOR UPDATE</literal> or <literal>SELECT
- ... IN SHARE MODE</literal>, locks are acquired for scanned
- rows, and expected to be released for rows that do not qualify
- for inclusion in the result set (for example, if they do not
- meet the criteria given in the <literal>WHERE</literal> clause).
+ For <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> or
+ <literal role="stmt" condition="select">SELECT ... IN SHARE
+ MODE</literal>, locks are acquired for scanned rows, and
+ expected to be released for rows that do not qualify for
+ inclusion in the result set (for example, if they do not meet
+ the criteria given in the <literal>WHERE</literal> clause).
However, in some cases, rows might not be unlocked immediately
because the relationship between a result row and its original
source is lost during query execution. For example, in a
@@ -5143,16 +5150,17 @@
follows. If a secondary index is used in the search and index
record locks to be set are exclusive, <literal>InnoDB</literal>
also retrieves the corresponding clustered index records and
- sets lock on them.
+ sets locks on them.
</para>
<itemizedlist>
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal role="stmt" condition="select">SELECT ...
+ FROM</literal> is a consistent read, reading a snapshot of
+ the database and setting no locks unless the transaction
+ isolation level is set to
<literal role="isolevel">SERIALIZABLE</literal>. For
<literal role="isolevel">SERIALIZABLE</literal> level, the
search sets shared next-key locks on the index records it
@@ -5162,43 +5170,44 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ LOCK IN SHARE MODE</literal> sets shared next-key locks on
+ all index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ FOR UPDATE</literal> sets exclusive next-key locks on all
+ index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal role="stmt" condition="update">UPDATE ... WHERE
+ ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
- encounters.
+ <literal role="stmt" condition="delete">DELETE FROM ...
+ WHERE ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
<literal role="stmt">INSERT</literal> sets an exclusive lock
- on the inserted row. This lock is an index record lock
- without a gap lock (that is, it is not a next-key lock) and
- does not prevent other sessions from inserting into the gap
- before the inserted row. If a duplicate-key error occurs, a
- shared lock on the duplicate index record is set.
+ on the inserted row. This lock is an index-record lock, not
+ a next-key lock (that is, there is no gap lock) and does not
+ prevent other sessions from inserting into the gap before
+ the inserted row. If a duplicate-key error occurs, a shared
+ lock on the duplicate index record is set.
</para>
<para>
@@ -5257,11 +5266,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 rolls back, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
<para>
@@ -5308,11 +5319,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 commits, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
</listitem>
@@ -5553,7 +5566,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
+ If you are using locking reads
+ (<literal role="stmt" condition="select">SELECT ... FOR
UPDATE</literal> or <literal>... LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal role="isolevel">READ COMMITTED</literal>.
Modified: trunk/refman-4.1/sql-syntax-transactions.xml
===================================================================
--- trunk/refman-4.1/sql-syntax-transactions.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-4.1/sql-syntax-transactions.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 2; 1043 bytes
@@ -1350,8 +1350,9 @@
This level is like <literal role="isolevel">REPEATABLE
READ</literal>, but <literal>InnoDB</literal> implicitly
converts all plain <literal role="stmt">SELECT</literal>
- statements to <literal>SELECT ... LOCK IN SHARE MODE</literal>
- if autocommit is disabled. If autocommit is enabled, the
+ statements to <literal role="stmt" condition="select">SELECT
+ ... LOCK IN SHARE MODE</literal> if autocommit is disabled. If
+ autocommit is enabled, the
<literal role="stmt">SELECT</literal> is its own transaction.
It therefore is known to be read only and can be serialized if
performed as a consistent (non-locking) read and need not
Modified: trunk/refman-5.0/se-innodb-core.xml
===================================================================
--- trunk/refman-5.0/se-innodb-core.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-5.0/se-innodb-core.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 9, Lines Added: 65, Lines Deleted: 51; 10142 bytes
@@ -4725,7 +4725,8 @@
<section id="innodb-locking-reads">
- <title><literal>SELECT ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
+ <title><literal role="stmt" condition="select">SELECT ... FOR UPDATE</literal>
+ and <literal role="stmt" condition="select">SELECT ... LOCK IN
SHARE MODE</literal> Locking Reads</title>
<para>
@@ -4738,20 +4739,22 @@
<listitem>
<para>
- <literal>SELECT ... LOCK IN SHARE MODE</literal> sets a
- shared mode lock on the rows read. A shared mode lock
- enables other sessions to read the rows but not to modify
- them. The rows read are the latest available, so if they
- belong to another transaction that has not yet committed,
- the read blocks until that transaction ends.
+ <literal role="stmt" condition="select">SELECT ... LOCK IN
+ SHARE MODE</literal> sets a shared mode lock on the rows
+ read. A shared mode lock enables other sessions to read the
+ rows but not to modify them. The rows read are the latest
+ available, so if they belong to another transaction that has
+ not yet committed, the read blocks until that transaction
+ ends.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FOR UPDATE</literal> sets an exclusive
- lock on the rows read. An exclusive lock prevents other
- sessions from accessing the rows for reading or writing.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> sets an exclusive lock on the rows read. An
+ exclusive lock prevents other sessions from accessing the
+ rows for reading or writing.
</para>
</listitem>
@@ -4858,17 +4861,19 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
- available data, setting exclusive locks on each row it reads.
- Thus, it sets the same locks a searched SQL
- <literal role="stmt">UPDATE</literal> would set on the rows.
+ A <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> reads the latest available data, setting
+ exclusive locks on each row it reads. Thus, it sets the same
+ locks a searched SQL <literal role="stmt">UPDATE</literal> would
+ set on the rows.
</para>
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> works. In MySQL, the specific task of
+ generating a unique identifier actually can be accomplished
+ using only a single access to the table:
</para>
<programlisting>
@@ -5243,11 +5248,13 @@
</para>
<para>
- For <literal>SELECT ... FOR UPDATE</literal> or <literal>SELECT
- ... IN SHARE MODE</literal>, locks are acquired for scanned
- rows, and expected to be released for rows that do not qualify
- for inclusion in the result set (for example, if they do not
- meet the criteria given in the <literal>WHERE</literal> clause).
+ For <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> or
+ <literal role="stmt" condition="select">SELECT ... IN SHARE
+ MODE</literal>, locks are acquired for scanned rows, and
+ expected to be released for rows that do not qualify for
+ inclusion in the result set (for example, if they do not meet
+ the criteria given in the <literal>WHERE</literal> clause).
However, in some cases, rows might not be unlocked immediately
because the relationship between a result row and its original
source is lost during query execution. For example, in a
@@ -5264,16 +5271,17 @@
follows. If a secondary index is used in the search and index
record locks to be set are exclusive, <literal>InnoDB</literal>
also retrieves the corresponding clustered index records and
- sets lock on them.
+ sets locks on them.
</para>
<itemizedlist>
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal role="stmt" condition="select">SELECT ...
+ FROM</literal> is a consistent read, reading a snapshot of
+ the database and setting no locks unless the transaction
+ isolation level is set to
<literal role="isolevel">SERIALIZABLE</literal>. For
<literal role="isolevel">SERIALIZABLE</literal> level, the
search sets shared next-key locks on the index records it
@@ -5283,43 +5291,44 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ LOCK IN SHARE MODE</literal> sets shared next-key locks on
+ all index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ FOR UPDATE</literal> sets exclusive next-key locks on all
+ index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal role="stmt" condition="update">UPDATE ... WHERE
+ ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
- encounters.
+ <literal role="stmt" condition="delete">DELETE FROM ...
+ WHERE ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
<literal role="stmt">INSERT</literal> sets an exclusive lock
- on the inserted row. This lock is an index record lock
- without a gap lock (that is, it is not a next-key lock) and
- does not prevent other sessions from inserting into the gap
- before the inserted row. If a duplicate-key error occurs, a
- shared lock on the duplicate index record is set.
+ on the inserted row. This lock is an index-record lock, not
+ a next-key lock (that is, there is no gap lock) and does not
+ prevent other sessions from inserting into the gap before
+ the inserted row. If a duplicate-key error occurs, a shared
+ lock on the duplicate index record is set.
</para>
<para>
@@ -5378,11 +5387,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 rolls back, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
<para>
@@ -5429,11 +5440,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 commits, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
</listitem>
@@ -5655,7 +5668,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
+ If you are using locking reads
+ (<literal role="stmt" condition="select">SELECT ... FOR
UPDATE</literal> or <literal>... LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal role="isolevel">READ COMMITTED</literal>.
Modified: trunk/refman-5.0/sql-syntax-transactions.xml
===================================================================
--- trunk/refman-5.0/sql-syntax-transactions.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-5.0/sql-syntax-transactions.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 2; 1043 bytes
@@ -1566,8 +1566,9 @@
This level is like <literal role="isolevel">REPEATABLE
READ</literal>, but <literal>InnoDB</literal> implicitly
converts all plain <literal role="stmt">SELECT</literal>
- statements to <literal>SELECT ... LOCK IN SHARE MODE</literal>
- if autocommit is disabled. If autocommit is enabled, the
+ statements to <literal role="stmt" condition="select">SELECT
+ ... LOCK IN SHARE MODE</literal> if autocommit is disabled. If
+ autocommit is enabled, the
<literal role="stmt">SELECT</literal> is its own transaction.
It therefore is known to be read only and can be serialized if
performed as a consistent (non-locking) read and need not
Modified: trunk/refman-5.1/se-innodb-core.xml
===================================================================
--- trunk/refman-5.1/se-innodb-core.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-5.1/se-innodb-core.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 9, Lines Added: 65, Lines Deleted: 51; 10142 bytes
@@ -5524,7 +5524,8 @@
<section id="innodb-locking-reads">
- <title><literal>SELECT ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
+ <title><literal role="stmt" condition="select">SELECT ... FOR UPDATE</literal>
+ and <literal role="stmt" condition="select">SELECT ... LOCK IN
SHARE MODE</literal> Locking Reads</title>
<para>
@@ -5537,20 +5538,22 @@
<listitem>
<para>
- <literal>SELECT ... LOCK IN SHARE MODE</literal> sets a
- shared mode lock on the rows read. A shared mode lock
- enables other sessions to read the rows but not to modify
- them. The rows read are the latest available, so if they
- belong to another transaction that has not yet committed,
- the read blocks until that transaction ends.
+ <literal role="stmt" condition="select">SELECT ... LOCK IN
+ SHARE MODE</literal> sets a shared mode lock on the rows
+ read. A shared mode lock enables other sessions to read the
+ rows but not to modify them. The rows read are the latest
+ available, so if they belong to another transaction that has
+ not yet committed, the read blocks until that transaction
+ ends.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FOR UPDATE</literal> sets an exclusive
- lock on the rows read. An exclusive lock prevents other
- sessions from accessing the rows for reading or writing.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> sets an exclusive lock on the rows read. An
+ exclusive lock prevents other sessions from accessing the
+ rows for reading or writing.
</para>
</listitem>
@@ -5657,17 +5660,19 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
- available data, setting exclusive locks on each row it reads.
- Thus, it sets the same locks a searched SQL
- <literal role="stmt">UPDATE</literal> would set on the rows.
+ A <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> reads the latest available data, setting
+ exclusive locks on each row it reads. Thus, it sets the same
+ locks a searched SQL <literal role="stmt">UPDATE</literal> would
+ set on the rows.
</para>
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> works. In MySQL, the specific task of
+ generating a unique identifier actually can be accomplished
+ using only a single access to the table:
</para>
<programlisting>
@@ -6048,11 +6053,13 @@
</para>
<para>
- For <literal>SELECT ... FOR UPDATE</literal> or <literal>SELECT
- ... IN SHARE MODE</literal>, locks are acquired for scanned
- rows, and expected to be released for rows that do not qualify
- for inclusion in the result set (for example, if they do not
- meet the criteria given in the <literal>WHERE</literal> clause).
+ For <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> or
+ <literal role="stmt" condition="select">SELECT ... IN SHARE
+ MODE</literal>, locks are acquired for scanned rows, and
+ expected to be released for rows that do not qualify for
+ inclusion in the result set (for example, if they do not meet
+ the criteria given in the <literal>WHERE</literal> clause).
However, in some cases, rows might not be unlocked immediately
because the relationship between a result row and its original
source is lost during query execution. For example, in a
@@ -6069,16 +6076,17 @@
follows. If a secondary index is used in the search and index
record locks to be set are exclusive, <literal>InnoDB</literal>
also retrieves the corresponding clustered index records and
- sets lock on them.
+ sets locks on them.
</para>
<itemizedlist>
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal role="stmt" condition="select">SELECT ...
+ FROM</literal> is a consistent read, reading a snapshot of
+ the database and setting no locks unless the transaction
+ isolation level is set to
<literal role="isolevel">SERIALIZABLE</literal>. For
<literal role="isolevel">SERIALIZABLE</literal> level, the
search sets shared next-key locks on the index records it
@@ -6088,43 +6096,44 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ LOCK IN SHARE MODE</literal> sets shared next-key locks on
+ all index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ FOR UPDATE</literal> sets exclusive next-key locks on all
+ index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal role="stmt" condition="update">UPDATE ... WHERE
+ ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
- encounters.
+ <literal role="stmt" condition="delete">DELETE FROM ...
+ WHERE ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
<literal role="stmt">INSERT</literal> sets an exclusive lock
- on the inserted row. This lock is an index record lock
- without a gap lock (that is, it is not a next-key lock) and
- does not prevent other sessions from inserting into the gap
- before the inserted row. If a duplicate-key error occurs, a
- shared lock on the duplicate index record is set.
+ on the inserted row. This lock is an index-record lock, not
+ a next-key lock (that is, there is no gap lock) and does not
+ prevent other sessions from inserting into the gap before
+ the inserted row. If a duplicate-key error occurs, a shared
+ lock on the duplicate index record is set.
</para>
<para>
@@ -6183,11 +6192,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 rolls back, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
<para>
@@ -6234,11 +6245,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 commits, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
</listitem>
@@ -6472,7 +6485,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
+ If you are using locking reads
+ (<literal role="stmt" condition="select">SELECT ... FOR
UPDATE</literal> or <literal>... LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal role="isolevel">READ COMMITTED</literal>.
Modified: trunk/refman-5.1/sql-syntax-transactions.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-transactions.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-5.1/sql-syntax-transactions.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 2; 1043 bytes
@@ -1637,8 +1637,9 @@
This level is like <literal role="isolevel">REPEATABLE
READ</literal>, but <literal>InnoDB</literal> implicitly
converts all plain <literal role="stmt">SELECT</literal>
- statements to <literal>SELECT ... LOCK IN SHARE MODE</literal>
- if autocommit is disabled. If autocommit is enabled, the
+ statements to <literal role="stmt" condition="select">SELECT
+ ... LOCK IN SHARE MODE</literal> if autocommit is disabled. If
+ autocommit is enabled, the
<literal role="stmt">SELECT</literal> is its own transaction.
It therefore is known to be read only and can be serialized if
performed as a consistent (non-locking) read and need not
Modified: trunk/refman-6.0/se-innodb-core.xml
===================================================================
--- trunk/refman-6.0/se-innodb-core.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-6.0/se-innodb-core.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 9, Lines Added: 65, Lines Deleted: 51; 10142 bytes
@@ -5315,7 +5315,8 @@
<section id="innodb-locking-reads">
- <title><literal>SELECT ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
+ <title><literal role="stmt" condition="select">SELECT ... FOR UPDATE</literal>
+ and <literal role="stmt" condition="select">SELECT ... LOCK IN
SHARE MODE</literal> Locking Reads</title>
<para>
@@ -5328,20 +5329,22 @@
<listitem>
<para>
- <literal>SELECT ... LOCK IN SHARE MODE</literal> sets a
- shared mode lock on the rows read. A shared mode lock
- enables other sessions to read the rows but not to modify
- them. The rows read are the latest available, so if they
- belong to another transaction that has not yet committed,
- the read blocks until that transaction ends.
+ <literal role="stmt" condition="select">SELECT ... LOCK IN
+ SHARE MODE</literal> sets a shared mode lock on the rows
+ read. A shared mode lock enables other sessions to read the
+ rows but not to modify them. The rows read are the latest
+ available, so if they belong to another transaction that has
+ not yet committed, the read blocks until that transaction
+ ends.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FOR UPDATE</literal> sets an exclusive
- lock on the rows read. An exclusive lock prevents other
- sessions from accessing the rows for reading or writing.
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> sets an exclusive lock on the rows read. An
+ exclusive lock prevents other sessions from accessing the
+ rows for reading or writing.
</para>
</listitem>
@@ -5448,17 +5451,19 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
- available data, setting exclusive locks on each row it reads.
- Thus, it sets the same locks a searched SQL
- <literal role="stmt">UPDATE</literal> would set on the rows.
+ A <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> reads the latest available data, setting
+ exclusive locks on each row it reads. Thus, it sets the same
+ locks a searched SQL <literal role="stmt">UPDATE</literal> would
+ set on the rows.
</para>
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> works. In MySQL, the specific task of
+ generating a unique identifier actually can be accomplished
+ using only a single access to the table:
</para>
<programlisting>
@@ -5839,11 +5844,13 @@
</para>
<para>
- For <literal>SELECT ... FOR UPDATE</literal> or <literal>SELECT
- ... IN SHARE MODE</literal>, locks are acquired for scanned
- rows, and expected to be released for rows that do not qualify
- for inclusion in the result set (for example, if they do not
- meet the criteria given in the <literal>WHERE</literal> clause).
+ For <literal role="stmt" condition="select">SELECT ... FOR
+ UPDATE</literal> or
+ <literal role="stmt" condition="select">SELECT ... IN SHARE
+ MODE</literal>, locks are acquired for scanned rows, and
+ expected to be released for rows that do not qualify for
+ inclusion in the result set (for example, if they do not meet
+ the criteria given in the <literal>WHERE</literal> clause).
However, in some cases, rows might not be unlocked immediately
because the relationship between a result row and its original
source is lost during query execution. For example, in a
@@ -5860,16 +5867,17 @@
follows. If a secondary index is used in the search and index
record locks to be set are exclusive, <literal>InnoDB</literal>
also retrieves the corresponding clustered index records and
- sets lock on them.
+ sets locks on them.
</para>
<itemizedlist>
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal role="stmt" condition="select">SELECT ...
+ FROM</literal> is a consistent read, reading a snapshot of
+ the database and setting no locks unless the transaction
+ isolation level is set to
<literal role="isolevel">SERIALIZABLE</literal>. For
<literal role="isolevel">SERIALIZABLE</literal> level, the
search sets shared next-key locks on the index records it
@@ -5879,43 +5887,44 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ LOCK IN SHARE MODE</literal> sets shared next-key locks on
+ all index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the search
- encounters.
+ <literal role="stmt" condition="select">SELECT ... FROM ...
+ FOR UPDATE</literal> sets exclusive next-key locks on all
+ index records the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal role="stmt" condition="update">UPDATE ... WHERE
+ ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
- encounters.
+ <literal role="stmt" condition="delete">DELETE FROM ...
+ WHERE ...</literal> sets an exclusive next-key lock on every
+ record the search encounters.
</para>
</listitem>
<listitem>
<para>
<literal role="stmt">INSERT</literal> sets an exclusive lock
- on the inserted row. This lock is an index record lock
- without a gap lock (that is, it is not a next-key lock) and
- does not prevent other sessions from inserting into the gap
- before the inserted row. If a duplicate-key error occurs, a
- shared lock on the duplicate index record is set.
+ on the inserted row. This lock is an index-record lock, not
+ a next-key lock (that is, there is no gap lock) and does not
+ prevent other sessions from inserting into the gap before
+ the inserted row. If a duplicate-key error occurs, a shared
+ lock on the duplicate index record is set.
</para>
<para>
@@ -5974,11 +5983,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 rolls back, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
<para>
@@ -6025,11 +6036,13 @@
<para>
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
- in a duplicate-key error and they both acquire a shared lock
+ in a duplicate-key error and they both request a shared lock
for the row. When session 1 commits, it releases its
- exclusive lock on the row. At this point, sessions 2 and 3
- deadlock: Neither can acquire an exclusive lock for the row
- because of the shared lock held by the other.
+ exclusive lock on the row and the queued shared lock
+ requests for sessions 2 and 3 are granted. At this point,
+ sessions 2 and 3 deadlock: Neither can acquire an exclusive
+ lock for the row because of the shared lock held by the
+ other.
</para>
</listitem>
@@ -6263,7 +6276,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
+ If you are using locking reads
+ (<literal role="stmt" condition="select">SELECT ... FOR
UPDATE</literal> or <literal>... LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal role="isolevel">READ COMMITTED</literal>.
Modified: trunk/refman-6.0/sql-syntax-transactions.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-transactions.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-6.0/sql-syntax-transactions.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 2; 1043 bytes
@@ -2295,8 +2295,9 @@
This level is like <literal role="isolevel">REPEATABLE
READ</literal>, but <literal>InnoDB</literal> implicitly
converts all plain <literal role="stmt">SELECT</literal>
- statements to <literal>SELECT ... LOCK IN SHARE MODE</literal>
- if autocommit is disabled. If autocommit is enabled, the
+ statements to <literal role="stmt" condition="select">SELECT
+ ... LOCK IN SHARE MODE</literal> if autocommit is disabled. If
+ autocommit is enabled, the
<literal role="stmt">SELECT</literal> is its own transaction.
It therefore is known to be read only and can be serialized if
performed as a consistent (non-locking) read and need not
Modified: trunk/refman-common/news-innodb.xml
===================================================================
--- trunk/refman-common/news-innodb.xml 2009-04-21 16:18:08 UTC (rev 14692)
+++ trunk/refman-common/news-innodb.xml 2009-04-21 18:07:33 UTC (rev 14693)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 580 bytes
@@ -3793,8 +3793,8 @@
<listitem>
<para>
- The new syntax <literal>SELECT ... LOCK IN SHARE
- MODE</literal> is introduced.
+ The new syntax <literal role="stmt" condition="select">SELECT
+ ... LOCK IN SHARE MODE</literal> is introduced.
</para>
</listitem>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r14693 - in trunk: . dynamic-docs/changelog refman-4.1 refman-5.0 refman-5.1 refman-6.0 refman-common | paul.dubois | 21 Apr |
