| List: | Commits | « Previous MessageNext Message » | |
| From: | paul.dubois | Date: | November 6 2008 9:27pm |
| Subject: | svn commit - mysqldoc@docsrva: r12313 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | ||
| View as plain text | |||
Author: paul Date: 2008-11-06 21:27:12 +0100 (Thu, 06 Nov 2008) New Revision: 12313 Log: r35363@frost: paul | 2008-11-06 14:24:43 -0500 General InnoDB revisions Modified: trunk/refman-4.1/se-innodb-core.xml trunk/refman-5.0/se-innodb-core.xml trunk/refman-5.1/se-innodb-core.xml trunk/refman-6.0/se-innodb-core.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:35361 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34100 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:39854 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:35363 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:34100 Modified: trunk/refman-4.1/se-innodb-core.xml =================================================================== --- trunk/refman-4.1/se-innodb-core.xml 2008-11-06 20:02:39 UTC (rev 12312) +++ trunk/refman-4.1/se-innodb-core.xml 2008-11-06 20:27:12 UTC (rev 12313) Changed blocks: 21, Lines Added: 136, Lines Deleted: 121; 20738 bytes @@ -92,9 +92,9 @@ <literal>InnoDB</literal> is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on <literal>InnoDB</literal>. Mytrix, - Inc. stores more than 1TB of data in <literal>InnoDB</literal>, and - another site handles an average load of 800 inserts/updates per - second in <literal>InnoDB</literal>. + Inc. stores more than 1TB of data in <literal>InnoDB</literal>, + and another site handles an average load of 800 inserts/updates + per second in <literal>InnoDB</literal>. </para> <para> @@ -3588,19 +3588,20 @@ <para> If there is database page corruption, you may want to dump your - tables from the database with <literal>SELECT INTO + tables from the database with <literal>SELECT INTO ... OUTFILE</literal>. Usually, most of the data obtained in this - way is intact. Even so, the corruption may cause <literal>SELECT - * FROM <replaceable>tbl_name</replaceable></literal> statements - or <literal>InnoDB</literal> background operations to crash or - assert, or even to cause <literal>InnoDB</literal> roll-forward - recovery to crash. Starting from MySQL 3.23.44, there is an - <literal>InnoDB</literal> variable that you can use to force the - <literal>InnoDB</literal> storage engine to start up, and you - can also prevent background operations from running, so that you - are able to dump your tables. For example, you can add the - following line to the <literal>[mysqld]</literal> section of - your option file before restarting the server: + way is intact. However, it is possible that the corruption might + cause <literal>SELECT * FROM + <replaceable>tbl_name</replaceable></literal> statements or + <literal>InnoDB</literal> background operations to crash or + assert, or even cause <literal>InnoDB</literal> roll-forward + recovery to crash. In such cases, starting from MySQL 3.23.44, + you can use the <literal>innodb_force_recovery</literal> option + to force the <literal>InnoDB</literal> storage engine to start + up, and you can also prevent background operations from running, + so that you are able to dump your tables. For example, you can + add the following line to the <literal>[mysqld]</literal> + section of your option file before restarting the server: </para> <programlisting> @@ -3618,14 +3619,16 @@ </programlisting> <para> - The allowable non-zero values for - <literal>innodb_force_recovery</literal> follow. A larger number - includes all precautions of smaller numbers. If you are able to - dump your tables with an option value of at most 4, then you are - relatively safe that only some data on corrupt individual pages - is lost. A value of 6 is more drastic because database pages are - left in an obsolete state, which in turn may introduce more - corruption into B-trees and other database structures. + <literal>innodb_force_recovery</literal> is 0 by default (normal + startup without forced recovery) The allowable non-zero values + for <literal>innodb_force_recovery</literal> follow. A larger + number includes all precautions of smaller numbers. If you are + able to dump your tables with an option value of at most 4, then + you are relatively safe that only some data on corrupt + individual pages is lost. A value of 6 is more drastic because + database pages are left in an obsolete state, which in turn may + introduce more corruption into B-trees and other database + structures. </para> <itemizedlist> @@ -3675,9 +3678,8 @@ </para> <para> - Prevent also insert buffer merge operations. If they would - cause a crash, do not do them. Do not calculate table - statistics. + Prevent insert buffer merge operations. If they would cause + a crash, do not do them. Do not calculate table statistics. </para> </listitem> @@ -3708,6 +3710,17 @@ </itemizedlist> <para> + <emphasis>The database must not otherwise be used with any + non-zero value of + <literal>innodb_force_recovery</literal></emphasis>. As a safety + measure, <literal>InnoDB</literal> prevents users from + performing <literal role="stmt">INSERT</literal>, + <literal role="stmt">UPDATE</literal>, or + <literal role="stmt">DELETE</literal> operations when + <literal>innodb_force_recovery</literal> is greater than 0. + </para> + + <para> Starting from MySQL 3.23.53 and 4.0.4, you can <literal role="stmt">SELECT</literal> from tables to dump them, or <literal>DROP</literal> or <literal>CREATE</literal> a table @@ -3722,22 +3735,11 @@ rollback. </para> - <para> - <emphasis>The database must not otherwise be used with any - non-zero value of - <literal>innodb_force_recovery</literal></emphasis>. As a safety - measure, <literal>InnoDB</literal> prevents users from - performing <literal role="stmt">INSERT</literal>, - <literal role="stmt">UPDATE</literal>, or - <literal role="stmt">DELETE</literal> operations when - <literal>innodb_force_recovery</literal> is greater than 0. - </para> - </section> <section id="innodb-checkpoints"> - <title>Checkpoints</title> + <title><literal>InnoDB</literal> Checkpoints</title> <para> <literal>InnoDB</literal> implements a checkpoint mechanism @@ -3774,9 +3776,9 @@ <para> The preceding description explains why making your log files - very large may save disk I/O in checkpointing. It often makes - sense to set the total size of the log files as big as the - buffer pool or even bigger. The disadvantage of using large log + very large may reduce disk I/O in checkpointing. It often makes + sense to set the total size of the log files as large as the + buffer pool or even larger. The disadvantage of using large log files is that crash recovery can take longer because there is more logged information to apply to the database. </para> @@ -3793,9 +3795,9 @@ On Windows, <literal>InnoDB</literal> always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should - have all table and database names in lowercase. A convenient way - to accomplish this is to add the following line to the - <literal>[mysqld]</literal> section of your + create all databases and tables using lowercase names. A + convenient way to accomplish this is to add the following line to + the <literal>[mysqld]</literal> section of your <filename>my.cnf</filename> or <filename>my.ini</filename> file before creating any databases or tables: </para> @@ -3815,9 +3817,10 @@ <literal role="type">FLOAT</literal> or <literal role="type">DOUBLE</literal> data types in your tables, then the procedure is the same: simply copy the relevant files. If - the formats differ and your tables contain floating-point data, - you must use <command>mysqldump</command> to dump your tables on - one machine and then import the dump files on the other machine. + you use <command>mysqldump</command> to dump your tables on one + machine and then import the dump files on the other machine, it + does not matter whether the formats differ or your tables contain + floating-point data. </para> <para> @@ -3832,7 +3835,7 @@ <section id="innodb-transaction-model"> - <title><literal>InnoDB</literal> Transaction Model and Locking</title> + <title>The <literal>InnoDB</literal> Transaction Model and Locking</title> <para> In the <literal>InnoDB</literal> transaction model, the goal is to @@ -3842,8 +3845,9 @@ consistent reads by default, in the style of Oracle. The lock table in <literal>InnoDB</literal> is stored so space-efficiently that lock escalation is not needed: Typically several users are - allowed to lock every row in the database, or any random subset of - the rows, without <literal>InnoDB</literal> running out of memory. + allowed to lock every row in <literal>InnoDB</literal> tables, or + any random subset of the rows, without causing + <literal>InnoDB</literal> memory exhaustion. </para> <section id="innodb-lock-modes"> @@ -3860,7 +3864,7 @@ <listitem> <para> A shared (<replaceable>S</replaceable>) lock allows a - transaction to read a row (tuple). + transaction to read a row. </para> </listitem> @@ -3875,28 +3879,29 @@ <para> If transaction <literal>T1</literal> holds a shared - (<replaceable>S</replaceable>) lock on tuple - <literal>t</literal>, then + (<replaceable>S</replaceable>) lock on row <literal>r</literal>, + then requests from some distinct transaction + <literal>T2</literal> for a lock on row <literal>r</literal> are + handled as follows: </para> <itemizedlist> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>S</replaceable> - lock on <literal>t</literal> can be granted immediately. As - a result, both <literal>T1</literal> and + A request by <literal>T2</literal> for an + <replaceable>S</replaceable> lock can be granted + immediately. As a result, both <literal>T1</literal> and <literal>T2</literal> hold an <replaceable>S</replaceable> - lock on <literal>t</literal>. + lock on <literal>r</literal>. </para> </listitem> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>X</replaceable> - lock on <literal>t</literal> cannot be granted immediately. + A request by <literal>T2</literal> for an + <replaceable>X</replaceable> lock cannot be granted + immediately. </para> </listitem> @@ -3904,13 +3909,12 @@ <para> If a transaction <literal>T1</literal> holds an exclusive - (<replaceable>X</replaceable>) lock on tuple - <literal>t</literal>, then a request from some distinct - transaction <literal>T2</literal> for a lock of either type on - <literal>t</literal> cannot be granted immediately. Instead, - transaction <literal>T2</literal> has to wait for transaction - <literal>T1</literal> to release its lock on tuple - <literal>t</literal>. + (<replaceable>X</replaceable>) lock on row <literal>r</literal>, + a request from some distinct transaction <literal>T2</literal> + for a lock of either type on <literal>r</literal> cannot be + granted immediately. Instead, transaction <literal>T2</literal> + has to wait for transaction <literal>T1</literal> to release its + lock on row <literal>r</literal>. </para> <para> @@ -3926,7 +3930,7 @@ are two types of intention locks used in <literal>InnoDB</literal> (assume that transaction <literal>T</literal> has requested a lock of the indicated type - on table <literal>R</literal>): + on table <literal>t</literal>): </para> <itemizedlist> @@ -3936,7 +3940,7 @@ Intention shared (<replaceable>IS</replaceable>): Transaction <literal>T</literal> intends to set <replaceable>S</replaceable> locks on individual rows in - table <literal>R</literal>. + table <literal>t</literal>. </para> </listitem> @@ -3958,19 +3962,20 @@ <listitem> <para> - Before a given transaction can acquire an - <replaceable>S</replaceable> lock on a given row, it must - first acquire an <replaceable>IS</replaceable> or stronger - lock on the table containing that row. + Before a transaction can acquire an + <replaceable>S</replaceable> lock on a row in table + <literal>t</literal>, it must first acquire an + <replaceable>IS</replaceable> or stronger lock on + <literal>t</literal>. </para> </listitem> <listitem> <para> - Before a given transaction can acquire an - <replaceable>X</replaceable> lock on a given row, it must - first acquire an <replaceable>IX</replaceable> lock on the - table containing that row. + Before a transaction can acquire an + <replaceable>X</replaceable> lock on a row, it must first + acquire an <replaceable>IX</replaceable> lock on + <literal>t</literal>. </para> </listitem> @@ -4030,12 +4035,11 @@ <para> A lock is granted to a requesting transaction if it is - compatible with existing locks. A lock is not granted to a - requesting transaction if it conflicts with existing locks. A - transaction waits until the conflicting existing lock is - released. If a lock request conflicts with an existing lock and - cannot be granted because it would cause deadlock, an error - occurs. + compatible with existing locks, but not if it conflicts with + existing locks. A transaction waits until the conflicting + existing lock is released. If a lock request conflicts with an + existing lock and cannot be granted because it would cause + deadlock, an error occurs. </para> <para> @@ -4137,31 +4141,32 @@ <para> In <literal>InnoDB</literal>, all user activity occurs inside a - transaction. If the autocommit mode is enabled, each SQL - statement forms a single transaction on its own. By default, - MySQL starts new connections with autocommit enabled. + transaction. If autocommit mode is enabled, each SQL statement + forms a single transaction on its own. By default, MySQL starts + new connections with autocommit enabled. </para> <para> - If the autocommit mode is switched off with <literal>SET - AUTOCOMMIT = 0</literal>, then we can consider that a user - always has a transaction open. An SQL <literal>COMMIT</literal> - or <literal>ROLLBACK</literal> statement ends the current + If autocommit mode is disabled within a session with + <literal>SET AUTOCOMMIT = 0</literal>, the session always has a + transaction open. An SQL <literal>COMMIT</literal> or + <literal>ROLLBACK</literal> statement ends the current transaction and a new one starts. A <literal>COMMIT</literal> means that the changes made in the current transaction are made - permanent and become visible to other users. A + permanent and become visible to other sessions. A <literal>ROLLBACK</literal> statement, on the other hand, cancels all modifications made by the current transaction. Both - statements release all <literal>InnoDB</literal> locks that were - set during the current transaction. + <literal>COMMIT</literal> and <literal>ROLLBACK</literal> + release all <literal>InnoDB</literal> locks that were set during + the current transaction. </para> <para> - If the connection has autocommit enabled, the user can still - perform a multiple-statement transaction by starting it with an - explicit <literal>START TRANSACTION</literal> or - <literal>BEGIN</literal> statement and ending it with - <literal>COMMIT</literal> or <literal>ROLLBACK</literal>. + If the session has autocommit enabled, a multiple-statement + transaction can be performed by starting it with an explicit + <literal>START TRANSACTION</literal> or <literal>BEGIN</literal> + statement and ending it with <literal>COMMIT</literal> or + <literal>ROLLBACK</literal>. </para> </section> @@ -4186,39 +4191,50 @@ <programlisting> [mysqld] -transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED - | REPEATABLE-READ | SERIALIZABLE} +transaction-isolation = <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is + <literal>READ-UNCOMMITTED</literal>, + <literal>READ-COMMITTED</literal>, <literal>REPEATABLE-READ + </literal>, or <literal>SERIALIZABLE</literal>. + </para> + + <para> A user can change the isolation level for a single session or - for all new incoming connections with the - <literal role="stmt">SET TRANSACTION</literal> statement. Its - syntax is as follows: + for all subsequent connections with the <literal role="stmt">SET + TRANSACTION</literal> statement. Its syntax is as follows: </para> <programlisting> -SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL - {READ UNCOMMITTED | READ COMMITTED - | REPEATABLE READ | SERIALIZABLE} +SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is <literal>READ + UNCOMMITTED</literal>, <literal>READ COMMITTED</literal>, + <literal>REPEATABLE READ </literal>, or + <literal>SERIALIZABLE</literal>. + </para> + + <para> Note that there are hyphens in the level names for the <option>--transaction-isolation</option> option, but not for the <literal role="stmt">SET TRANSACTION</literal> statement. </para> <para> - The default behavior is to set the isolation level for the next - (not started) transaction. If you use the + The default behavior (without any <literal>SESSION</literal> or + <literal>GLOBAL</literal> keyword) is to set the isolation level + for the next (not started) transaction. If you use the <literal>GLOBAL</literal> keyword, the statement sets the - default transaction level globally for all new connections - created from that point on (but not for existing connections). - You need the <literal>SUPER</literal> privilege to do this. - Using the <literal>SESSION</literal> keyword sets the default - transaction level for all future transactions performed on the - current connection. + default transaction level globally for all connections created + from that point on (but not for existing connections). You need + the <literal>SUPER</literal> privilege to do this. Using the + <literal>SESSION</literal> keyword sets the default transaction + level for all future transactions performed within the current + session. </para> <para> @@ -4283,12 +4299,11 @@ A consistent read means that <literal>InnoDB</literal> uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by - those transactions that committed before that point of time, and - no changes made by later or uncommitted transactions. The - exception to this rule is that the query sees the changes made - by earlier statements within the same transaction. Note that the - exception to the rule causes the following anomaly: if you - update some rows in a table, a + transactions that committed before that point of time, and no + changes made by later or uncommitted transactions. The exception + to this rule is that the query sees the changes made by earlier + statements within the same transaction. This exception causes + the following anomaly: if you update some rows in a table, a <literal role="stmt">SELECT</literal> will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the Modified: trunk/refman-5.0/se-innodb-core.xml =================================================================== --- trunk/refman-5.0/se-innodb-core.xml 2008-11-06 20:02:39 UTC (rev 12312) +++ trunk/refman-5.0/se-innodb-core.xml 2008-11-06 20:27:12 UTC (rev 12313) Changed blocks: 22, Lines Added: 132, Lines Deleted: 116; 20663 bytes @@ -90,9 +90,9 @@ <literal>InnoDB</literal> is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on <literal>InnoDB</literal>. Mytrix, - Inc. stores more than 1TB of data in <literal>InnoDB</literal>, and - another site handles an average load of 800 inserts/updates per - second in <literal>InnoDB</literal>. + Inc. stores more than 1TB of data in <literal>InnoDB</literal>, + and another site handles an average load of 800 inserts/updates + per second in <literal>InnoDB</literal>. </para> <para> @@ -3656,13 +3656,15 @@ <para> If there is database page corruption, you may want to dump your - tables from the database with <literal>SELECT INTO + tables from the database with <literal>SELECT INTO ... OUTFILE</literal>. Usually, most of the data obtained in this - way is intact. Even so, the corruption may cause <literal>SELECT - * FROM <replaceable>tbl_name</replaceable></literal> statements - or <literal>InnoDB</literal> background operations to crash or - assert, or even to cause <literal>InnoDB</literal> roll-forward - recovery to crash. However, you can force the + way is intact. However, it is possible that the corruption might + cause <literal>SELECT * FROM + <replaceable>tbl_name</replaceable></literal> statements or + <literal>InnoDB</literal> background operations to crash or + assert, or even cause <literal>InnoDB</literal> roll-forward + recovery to crash. In such cases, you can use the + <literal>innodb_force_recovery</literal> option to force the <literal>InnoDB</literal> storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following @@ -3676,14 +3678,16 @@ </programlisting> <para> - The allowable non-zero values for - <literal>innodb_force_recovery</literal> follow. A larger number - includes all precautions of smaller numbers. If you are able to - dump your tables with an option value of at most 4, then you are - relatively safe that only some data on corrupt individual pages - is lost. A value of 6 is more drastic because database pages are - left in an obsolete state, which in turn may introduce more - corruption into B-trees and other database structures. + <literal>innodb_force_recovery</literal> is 0 by default (normal + startup without forced recovery) The allowable non-zero values + for <literal>innodb_force_recovery</literal> follow. A larger + number includes all precautions of smaller numbers. If you are + able to dump your tables with an option value of at most 4, then + you are relatively safe that only some data on corrupt + individual pages is lost. A value of 6 is more drastic because + database pages are left in an obsolete state, which in turn may + introduce more corruption into B-trees and other database + structures. </para> <itemizedlist> @@ -3733,9 +3737,8 @@ </para> <para> - Prevent also insert buffer merge operations. If they would - cause a crash, do not do them. Do not calculate table - statistics. + Prevent insert buffer merge operations. If they would cause + a crash, do not do them. Do not calculate table statistics. </para> </listitem> @@ -3766,6 +3769,17 @@ </itemizedlist> <para> + <emphasis>The database must not otherwise be used with any + non-zero value of + <literal>innodb_force_recovery</literal></emphasis>. As a safety + measure, <literal>InnoDB</literal> prevents users from + performing <literal role="stmt">INSERT</literal>, + <literal role="stmt">UPDATE</literal>, or + <literal role="stmt">DELETE</literal> operations when + <literal>innodb_force_recovery</literal> is greater than 0. + </para> + + <para> You can <literal role="stmt">SELECT</literal> from tables to dump them, or <literal>DROP</literal> or <literal>CREATE</literal> tables even if forced recovery is @@ -3780,22 +3794,11 @@ rollback. </para> - <para> - <emphasis>The database must not otherwise be used with any - non-zero value of - <literal>innodb_force_recovery</literal></emphasis>. As a safety - measure, <literal>InnoDB</literal> prevents users from - performing <literal role="stmt">INSERT</literal>, - <literal role="stmt">UPDATE</literal>, or - <literal role="stmt">DELETE</literal> operations when - <literal>innodb_force_recovery</literal> is greater than 0. - </para> - </section> <section id="innodb-checkpoints"> - <title>Checkpoints</title> + <title><literal>InnoDB</literal> Checkpoints</title> <para> <literal>InnoDB</literal> implements a checkpoint mechanism @@ -3832,9 +3835,9 @@ <para> The preceding description explains why making your log files - very large may save disk I/O in checkpointing. It often makes - sense to set the total size of the log files as big as the - buffer pool or even bigger. The disadvantage of using large log + very large may reduce disk I/O in checkpointing. It often makes + sense to set the total size of the log files as large as the + buffer pool or even larger. The disadvantage of using large log files is that crash recovery can take longer because there is more logged information to apply to the database. </para> @@ -3851,9 +3854,9 @@ On Windows, <literal>InnoDB</literal> always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should - have all table and database names in lowercase. A convenient way - to accomplish this is to add the following line to the - <literal>[mysqld]</literal> section of your + create all databases and tables using lowercase names. A + convenient way to accomplish this is to add the following line to + the <literal>[mysqld]</literal> section of your <filename>my.cnf</filename> or <filename>my.ini</filename> file before creating any databases or tables: </para> @@ -3873,9 +3876,10 @@ <literal role="type">FLOAT</literal> or <literal role="type">DOUBLE</literal> data types in your tables, then the procedure is the same: simply copy the relevant files. If - the formats differ and your tables contain floating-point data, - you must use <command>mysqldump</command> to dump your tables on - one machine and then import the dump files on the other machine. + you use <command>mysqldump</command> to dump your tables on one + machine and then import the dump files on the other machine, it + does not matter whether the formats differ or your tables contain + floating-point data. </para> <para> @@ -3890,7 +3894,7 @@ <section id="innodb-transaction-model"> - <title><literal>InnoDB</literal> Transaction Model and Locking</title> + <title>The <literal>InnoDB</literal> Transaction Model and Locking</title> <para> In the <literal>InnoDB</literal> transaction model, the goal is to @@ -3900,8 +3904,9 @@ consistent reads by default, in the style of Oracle. The lock table in <literal>InnoDB</literal> is stored so space-efficiently that lock escalation is not needed: Typically several users are - allowed to lock every row in the database, or any random subset of - the rows, without <literal>InnoDB</literal> running out of memory. + allowed to lock every row in <literal>InnoDB</literal> tables, or + any random subset of the rows, without causing + <literal>InnoDB</literal> memory exhaustion. </para> <section id="innodb-lock-modes"> @@ -3918,7 +3923,7 @@ <listitem> <para> A shared (<replaceable>S</replaceable>) lock allows a - transaction to read a row (tuple). + transaction to read a row. </para> </listitem> @@ -3933,28 +3938,29 @@ <para> If transaction <literal>T1</literal> holds a shared - (<replaceable>S</replaceable>) lock on tuple - <literal>t</literal>, then + (<replaceable>S</replaceable>) lock on row <literal>r</literal>, + then requests from some distinct transaction + <literal>T2</literal> for a lock on row <literal>r</literal> are + handled as follows: </para> <itemizedlist> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>S</replaceable> - lock on <literal>t</literal> can be granted immediately. As - a result, both <literal>T1</literal> and + A request by <literal>T2</literal> for an + <replaceable>S</replaceable> lock can be granted + immediately. As a result, both <literal>T1</literal> and <literal>T2</literal> hold an <replaceable>S</replaceable> - lock on <literal>t</literal>. + lock on <literal>r</literal>. </para> </listitem> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>X</replaceable> - lock on <literal>t</literal> cannot be granted immediately. + A request by <literal>T2</literal> for an + <replaceable>X</replaceable> lock cannot be granted + immediately. </para> </listitem> @@ -3962,13 +3968,12 @@ <para> If a transaction <literal>T1</literal> holds an exclusive - (<replaceable>X</replaceable>) lock on tuple - <literal>t</literal>, then a request from some distinct - transaction <literal>T2</literal> for a lock of either type on - <literal>t</literal> cannot be granted immediately. Instead, - transaction <literal>T2</literal> has to wait for transaction - <literal>T1</literal> to release its lock on tuple - <literal>t</literal>. + (<replaceable>X</replaceable>) lock on row <literal>r</literal>, + a request from some distinct transaction <literal>T2</literal> + for a lock of either type on <literal>r</literal> cannot be + granted immediately. Instead, transaction <literal>T2</literal> + has to wait for transaction <literal>T1</literal> to release its + lock on row <literal>r</literal>. </para> <para> @@ -3984,7 +3989,7 @@ are two types of intention locks used in <literal>InnoDB</literal> (assume that transaction <literal>T</literal> has requested a lock of the indicated type - on table <literal>R</literal>): + on table <literal>t</literal>): </para> <itemizedlist> @@ -3994,7 +3999,7 @@ Intention shared (<replaceable>IS</replaceable>): Transaction <literal>T</literal> intends to set <replaceable>S</replaceable> locks on individual rows in - table <literal>R</literal>. + table <literal>t</literal>. </para> </listitem> @@ -4016,19 +4021,20 @@ <listitem> <para> - Before a given transaction can acquire an - <replaceable>S</replaceable> lock on a given row, it must - first acquire an <replaceable>IS</replaceable> or stronger - lock on the table containing that row. + Before a transaction can acquire an + <replaceable>S</replaceable> lock on a row in table + <literal>t</literal>, it must first acquire an + <replaceable>IS</replaceable> or stronger lock on + <literal>t</literal>. </para> </listitem> <listitem> <para> - Before a given transaction can acquire an - <replaceable>X</replaceable> lock on a given row, it must - first acquire an <replaceable>IX</replaceable> lock on the - table containing that row. + Before a transaction can acquire an + <replaceable>X</replaceable> lock on a row, it must first + acquire an <replaceable>IX</replaceable> lock on + <literal>t</literal>. </para> </listitem> @@ -4088,12 +4094,11 @@ <para> A lock is granted to a requesting transaction if it is - compatible with existing locks. A lock is not granted to a - requesting transaction if it conflicts with existing locks. A - transaction waits until the conflicting existing lock is - released. If a lock request conflicts with an existing lock and - cannot be granted because it would cause deadlock, an error - occurs. + compatible with existing locks, but not if it conflicts with + existing locks. A transaction waits until the conflicting + existing lock is released. If a lock request conflicts with an + existing lock and cannot be granted because it would cause + deadlock, an error occurs. </para> <para> @@ -4195,31 +4200,32 @@ <para> In <literal>InnoDB</literal>, all user activity occurs inside a - transaction. If the autocommit mode is enabled, each SQL - statement forms a single transaction on its own. By default, - MySQL starts new connections with autocommit enabled. + transaction. If autocommit mode is enabled, each SQL statement + forms a single transaction on its own. By default, MySQL starts + new connections with autocommit enabled. </para> <para> - If the autocommit mode is switched off with <literal>SET - AUTOCOMMIT = 0</literal>, then we can consider that a user - always has a transaction open. An SQL <literal>COMMIT</literal> - or <literal>ROLLBACK</literal> statement ends the current + If autocommit mode is disabled within a session with + <literal>SET AUTOCOMMIT = 0</literal>, the session always has a + transaction open. An SQL <literal>COMMIT</literal> or + <literal>ROLLBACK</literal> statement ends the current transaction and a new one starts. A <literal>COMMIT</literal> means that the changes made in the current transaction are made - permanent and become visible to other users. A + permanent and become visible to other sessions. A <literal>ROLLBACK</literal> statement, on the other hand, cancels all modifications made by the current transaction. Both - statements release all <literal>InnoDB</literal> locks that were - set during the current transaction. + <literal>COMMIT</literal> and <literal>ROLLBACK</literal> + release all <literal>InnoDB</literal> locks that were set during + the current transaction. </para> <para> - If the connection has autocommit enabled, the user can still - perform a multiple-statement transaction by starting it with an - explicit <literal>START TRANSACTION</literal> or - <literal>BEGIN</literal> statement and ending it with - <literal>COMMIT</literal> or <literal>ROLLBACK</literal>. + If the session has autocommit enabled, a multiple-statement + transaction can be performed by starting it with an explicit + <literal>START TRANSACTION</literal> or <literal>BEGIN</literal> + statement and ending it with <literal>COMMIT</literal> or + <literal>ROLLBACK</literal>. </para> </section> @@ -4243,39 +4249,50 @@ <programlisting> [mysqld] -transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED - | REPEATABLE-READ | SERIALIZABLE} +transaction-isolation = <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is + <literal>READ-UNCOMMITTED</literal>, + <literal>READ-COMMITTED</literal>, <literal>REPEATABLE-READ + </literal>, or <literal>SERIALIZABLE</literal>. + </para> + + <para> A user can change the isolation level for a single session or - for all new incoming connections with the - <literal role="stmt">SET TRANSACTION</literal> statement. Its - syntax is as follows: + for all subsequent connections with the <literal role="stmt">SET + TRANSACTION</literal> statement. Its syntax is as follows: </para> <programlisting> -SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL - {READ UNCOMMITTED | READ COMMITTED - | REPEATABLE READ | SERIALIZABLE} +SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is <literal>READ + UNCOMMITTED</literal>, <literal>READ COMMITTED</literal>, + <literal>REPEATABLE READ </literal>, or + <literal>SERIALIZABLE</literal>. + </para> + + <para> Note that there are hyphens in the level names for the <option>--transaction-isolation</option> option, but not for the <literal role="stmt">SET TRANSACTION</literal> statement. </para> <para> - The default behavior is to set the isolation level for the next - (not started) transaction. If you use the + The default behavior (without any <literal>SESSION</literal> or + <literal>GLOBAL</literal> keyword) is to set the isolation level + for the next (not started) transaction. If you use the <literal>GLOBAL</literal> keyword, the statement sets the - default transaction level globally for all new connections - created from that point on (but not for existing connections). - You need the <literal>SUPER</literal> privilege to do this. - Using the <literal>SESSION</literal> keyword sets the default - transaction level for all future transactions performed on the - current connection. + default transaction level globally for all connections created + from that point on (but not for existing connections). You need + the <literal>SUPER</literal> privilege to do this. Using the + <literal>SESSION</literal> keyword sets the default transaction + level for all future transactions performed within the current + session. </para> <para> @@ -4333,12 +4350,11 @@ A consistent read means that <literal>InnoDB</literal> uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by - those transactions that committed before that point of time, and - no changes made by later or uncommitted transactions. The - exception to this rule is that the query sees the changes made - by earlier statements within the same transaction. Note that the - exception to the rule causes the following anomaly: if you - update some rows in a table, a + transactions that committed before that point of time, and no + changes made by later or uncommitted transactions. The exception + to this rule is that the query sees the changes made by earlier + statements within the same transaction. This exception causes + the following anomaly: if you update some rows in a table, a <literal role="stmt">SELECT</literal> will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the @@ -4390,7 +4406,7 @@ MODE</literal> if the <literal>innodb_locks_unsafe_for_binlog</literal> option is set and the isolation level of the transaction is not set to - serializable. Thus no locks are set to rows read from selected + serializable. Thus, no locks are set on rows read from selected table. Otherwise, <literal>InnoDB</literal> uses stronger locks and the <literal role="stmt">SELECT</literal> part acts like <literal>READ COMMITTED</literal>, where each consistent read, Modified: trunk/refman-5.1/se-innodb-core.xml =================================================================== --- trunk/refman-5.1/se-innodb-core.xml 2008-11-06 20:02:39 UTC (rev 12312) +++ trunk/refman-5.1/se-innodb-core.xml 2008-11-06 20:27:12 UTC (rev 12313) Changed blocks: 22, Lines Added: 132, Lines Deleted: 116; 20663 bytes @@ -90,9 +90,9 @@ <literal>InnoDB</literal> is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on <literal>InnoDB</literal>. Mytrix, - Inc. stores more than 1TB of data in <literal>InnoDB</literal>, and - another site handles an average load of 800 inserts/updates per - second in <literal>InnoDB</literal>. + Inc. stores more than 1TB of data in <literal>InnoDB</literal>, + and another site handles an average load of 800 inserts/updates + per second in <literal>InnoDB</literal>. </para> <para> @@ -4355,13 +4355,15 @@ <para> If there is database page corruption, you may want to dump your - tables from the database with <literal>SELECT INTO + tables from the database with <literal>SELECT INTO ... OUTFILE</literal>. Usually, most of the data obtained in this - way is intact. Even so, the corruption may cause <literal>SELECT - * FROM <replaceable>tbl_name</replaceable></literal> statements - or <literal>InnoDB</literal> background operations to crash or - assert, or even to cause <literal>InnoDB</literal> roll-forward - recovery to crash. However, you can force the + way is intact. However, it is possible that the corruption might + cause <literal>SELECT * FROM + <replaceable>tbl_name</replaceable></literal> statements or + <literal>InnoDB</literal> background operations to crash or + assert, or even cause <literal>InnoDB</literal> roll-forward + recovery to crash. In such cases, you can use the + <literal>innodb_force_recovery</literal> option to force the <literal>InnoDB</literal> storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following @@ -4375,14 +4377,16 @@ </programlisting> <para> - The allowable non-zero values for - <literal>innodb_force_recovery</literal> follow. A larger number - includes all precautions of smaller numbers. If you are able to - dump your tables with an option value of at most 4, then you are - relatively safe that only some data on corrupt individual pages - is lost. A value of 6 is more drastic because database pages are - left in an obsolete state, which in turn may introduce more - corruption into B-trees and other database structures. + <literal>innodb_force_recovery</literal> is 0 by default (normal + startup without forced recovery) The allowable non-zero values + for <literal>innodb_force_recovery</literal> follow. A larger + number includes all precautions of smaller numbers. If you are + able to dump your tables with an option value of at most 4, then + you are relatively safe that only some data on corrupt + individual pages is lost. A value of 6 is more drastic because + database pages are left in an obsolete state, which in turn may + introduce more corruption into B-trees and other database + structures. </para> <itemizedlist> @@ -4432,9 +4436,8 @@ </para> <para> - Prevent also insert buffer merge operations. If they would - cause a crash, do not do them. Do not calculate table - statistics. + Prevent insert buffer merge operations. If they would cause + a crash, do not do them. Do not calculate table statistics. </para> </listitem> @@ -4465,6 +4468,17 @@ </itemizedlist> <para> + <emphasis>The database must not otherwise be used with any + non-zero value of + <literal>innodb_force_recovery</literal></emphasis>. As a safety + measure, <literal>InnoDB</literal> prevents users from + performing <literal role="stmt">INSERT</literal>, + <literal role="stmt">UPDATE</literal>, or + <literal role="stmt">DELETE</literal> operations when + <literal>innodb_force_recovery</literal> is greater than 0. + </para> + + <para> You can <literal role="stmt">SELECT</literal> from tables to dump them, or <literal>DROP</literal> or <literal>CREATE</literal> tables even if forced recovery is @@ -4479,22 +4493,11 @@ rollback. </para> - <para> - <emphasis>The database must not otherwise be used with any - non-zero value of - <literal>innodb_force_recovery</literal></emphasis>. As a safety - measure, <literal>InnoDB</literal> prevents users from - performing <literal role="stmt">INSERT</literal>, - <literal role="stmt">UPDATE</literal>, or - <literal role="stmt">DELETE</literal> operations when - <literal>innodb_force_recovery</literal> is greater than 0. - </para> - </section> <section id="innodb-checkpoints"> - <title>Checkpoints</title> + <title><literal>InnoDB</literal> Checkpoints</title> <para> <literal>InnoDB</literal> implements a checkpoint mechanism @@ -4531,9 +4534,9 @@ <para> The preceding description explains why making your log files - very large may save disk I/O in checkpointing. It often makes - sense to set the total size of the log files as big as the - buffer pool or even bigger. The disadvantage of using large log + very large may reduce disk I/O in checkpointing. It often makes + sense to set the total size of the log files as large as the + buffer pool or even larger. The disadvantage of using large log files is that crash recovery can take longer because there is more logged information to apply to the database. </para> @@ -4550,9 +4553,9 @@ On Windows, <literal>InnoDB</literal> always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should - have all table and database names in lowercase. A convenient way - to accomplish this is to add the following line to the - <literal>[mysqld]</literal> section of your + create all databases and tables using lowercase names. A + convenient way to accomplish this is to add the following line to + the <literal>[mysqld]</literal> section of your <filename>my.cnf</filename> or <filename>my.ini</filename> file before creating any databases or tables: </para> @@ -4572,9 +4575,10 @@ <literal role="type">FLOAT</literal> or <literal role="type">DOUBLE</literal> data types in your tables, then the procedure is the same: simply copy the relevant files. If - the formats differ and your tables contain floating-point data, - you must use <command>mysqldump</command> to dump your tables on - one machine and then import the dump files on the other machine. + you use <command>mysqldump</command> to dump your tables on one + machine and then import the dump files on the other machine, it + does not matter whether the formats differ or your tables contain + floating-point data. </para> <para> @@ -4589,7 +4593,7 @@ <section id="innodb-transaction-model"> - <title><literal>InnoDB</literal> Transaction Model and Locking</title> + <title>The <literal>InnoDB</literal> Transaction Model and Locking</title> <para> In the <literal>InnoDB</literal> transaction model, the goal is to @@ -4599,8 +4603,9 @@ consistent reads by default, in the style of Oracle. The lock table in <literal>InnoDB</literal> is stored so space-efficiently that lock escalation is not needed: Typically several users are - allowed to lock every row in the database, or any random subset of - the rows, without <literal>InnoDB</literal> running out of memory. + allowed to lock every row in <literal>InnoDB</literal> tables, or + any random subset of the rows, without causing + <literal>InnoDB</literal> memory exhaustion. </para> <section id="innodb-lock-modes"> @@ -4617,7 +4622,7 @@ <listitem> <para> A shared (<replaceable>S</replaceable>) lock allows a - transaction to read a row (tuple). + transaction to read a row. </para> </listitem> @@ -4632,28 +4637,29 @@ <para> If transaction <literal>T1</literal> holds a shared - (<replaceable>S</replaceable>) lock on tuple - <literal>t</literal>, then + (<replaceable>S</replaceable>) lock on row <literal>r</literal>, + then requests from some distinct transaction + <literal>T2</literal> for a lock on row <literal>r</literal> are + handled as follows: </para> <itemizedlist> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>S</replaceable> - lock on <literal>t</literal> can be granted immediately. As - a result, both <literal>T1</literal> and + A request by <literal>T2</literal> for an + <replaceable>S</replaceable> lock can be granted + immediately. As a result, both <literal>T1</literal> and <literal>T2</literal> hold an <replaceable>S</replaceable> - lock on <literal>t</literal>. + lock on <literal>r</literal>. </para> </listitem> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>X</replaceable> - lock on <literal>t</literal> cannot be granted immediately. + A request by <literal>T2</literal> for an + <replaceable>X</replaceable> lock cannot be granted + immediately. </para> </listitem> @@ -4661,13 +4667,12 @@ <para> If a transaction <literal>T1</literal> holds an exclusive - (<replaceable>X</replaceable>) lock on tuple - <literal>t</literal>, then a request from some distinct - transaction <literal>T2</literal> for a lock of either type on - <literal>t</literal> cannot be granted immediately. Instead, - transaction <literal>T2</literal> has to wait for transaction - <literal>T1</literal> to release its lock on tuple - <literal>t</literal>. + (<replaceable>X</replaceable>) lock on row <literal>r</literal>, + a request from some distinct transaction <literal>T2</literal> + for a lock of either type on <literal>r</literal> cannot be + granted immediately. Instead, transaction <literal>T2</literal> + has to wait for transaction <literal>T1</literal> to release its + lock on row <literal>r</literal>. </para> <para> @@ -4683,7 +4688,7 @@ are two types of intention locks used in <literal>InnoDB</literal> (assume that transaction <literal>T</literal> has requested a lock of the indicated type - on table <literal>R</literal>): + on table <literal>t</literal>): </para> <itemizedlist> @@ -4693,7 +4698,7 @@ Intention shared (<replaceable>IS</replaceable>): Transaction <literal>T</literal> intends to set <replaceable>S</replaceable> locks on individual rows in - table <literal>R</literal>. + table <literal>t</literal>. </para> </listitem> @@ -4715,19 +4720,20 @@ <listitem> <para> - Before a given transaction can acquire an - <replaceable>S</replaceable> lock on a given row, it must - first acquire an <replaceable>IS</replaceable> or stronger - lock on the table containing that row. + Before a transaction can acquire an + <replaceable>S</replaceable> lock on a row in table + <literal>t</literal>, it must first acquire an + <replaceable>IS</replaceable> or stronger lock on + <literal>t</literal>. </para> </listitem> <listitem> <para> - Before a given transaction can acquire an - <replaceable>X</replaceable> lock on a given row, it must - first acquire an <replaceable>IX</replaceable> lock on the - table containing that row. + Before a transaction can acquire an + <replaceable>X</replaceable> lock on a row, it must first + acquire an <replaceable>IX</replaceable> lock on + <literal>t</literal>. </para> </listitem> @@ -4787,12 +4793,11 @@ <para> A lock is granted to a requesting transaction if it is - compatible with existing locks. A lock is not granted to a - requesting transaction if it conflicts with existing locks. A - transaction waits until the conflicting existing lock is - released. If a lock request conflicts with an existing lock and - cannot be granted because it would cause deadlock, an error - occurs. + compatible with existing locks, but not if it conflicts with + existing locks. A transaction waits until the conflicting + existing lock is released. If a lock request conflicts with an + existing lock and cannot be granted because it would cause + deadlock, an error occurs. </para> <para> @@ -4894,31 +4899,32 @@ <para> In <literal>InnoDB</literal>, all user activity occurs inside a - transaction. If the autocommit mode is enabled, each SQL - statement forms a single transaction on its own. By default, - MySQL starts new connections with autocommit enabled. + transaction. If autocommit mode is enabled, each SQL statement + forms a single transaction on its own. By default, MySQL starts + new connections with autocommit enabled. </para> <para> - If the autocommit mode is switched off with <literal>SET - AUTOCOMMIT = 0</literal>, then we can consider that a user - always has a transaction open. An SQL <literal>COMMIT</literal> - or <literal>ROLLBACK</literal> statement ends the current + If autocommit mode is disabled within a session with + <literal>SET AUTOCOMMIT = 0</literal>, the session always has a + transaction open. An SQL <literal>COMMIT</literal> or + <literal>ROLLBACK</literal> statement ends the current transaction and a new one starts. A <literal>COMMIT</literal> means that the changes made in the current transaction are made - permanent and become visible to other users. A + permanent and become visible to other sessions. A <literal>ROLLBACK</literal> statement, on the other hand, cancels all modifications made by the current transaction. Both - statements release all <literal>InnoDB</literal> locks that were - set during the current transaction. + <literal>COMMIT</literal> and <literal>ROLLBACK</literal> + release all <literal>InnoDB</literal> locks that were set during + the current transaction. </para> <para> - If the connection has autocommit enabled, the user can still - perform a multiple-statement transaction by starting it with an - explicit <literal>START TRANSACTION</literal> or - <literal>BEGIN</literal> statement and ending it with - <literal>COMMIT</literal> or <literal>ROLLBACK</literal>. + If the session has autocommit enabled, a multiple-statement + transaction can be performed by starting it with an explicit + <literal>START TRANSACTION</literal> or <literal>BEGIN</literal> + statement and ending it with <literal>COMMIT</literal> or + <literal>ROLLBACK</literal>. </para> </section> @@ -4942,39 +4948,50 @@ <programlisting> [mysqld] -transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED - | REPEATABLE-READ | SERIALIZABLE} +transaction-isolation = <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is + <literal>READ-UNCOMMITTED</literal>, + <literal>READ-COMMITTED</literal>, <literal>REPEATABLE-READ + </literal>, or <literal>SERIALIZABLE</literal>. + </para> + + <para> A user can change the isolation level for a single session or - for all new incoming connections with the - <literal role="stmt">SET TRANSACTION</literal> statement. Its - syntax is as follows: + for all subsequent connections with the <literal role="stmt">SET + TRANSACTION</literal> statement. Its syntax is as follows: </para> <programlisting> -SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL - {READ UNCOMMITTED | READ COMMITTED - | REPEATABLE READ | SERIALIZABLE} +SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is <literal>READ + UNCOMMITTED</literal>, <literal>READ COMMITTED</literal>, + <literal>REPEATABLE READ </literal>, or + <literal>SERIALIZABLE</literal>. + </para> + + <para> Note that there are hyphens in the level names for the <option>--transaction-isolation</option> option, but not for the <literal role="stmt">SET TRANSACTION</literal> statement. </para> <para> - The default behavior is to set the isolation level for the next - (not started) transaction. If you use the + The default behavior (without any <literal>SESSION</literal> or + <literal>GLOBAL</literal> keyword) is to set the isolation level + for the next (not started) transaction. If you use the <literal>GLOBAL</literal> keyword, the statement sets the - default transaction level globally for all new connections - created from that point on (but not for existing connections). - You need the <literal>SUPER</literal> privilege to do this. - Using the <literal>SESSION</literal> keyword sets the default - transaction level for all future transactions performed on the - current connection. + default transaction level globally for all connections created + from that point on (but not for existing connections). You need + the <literal>SUPER</literal> privilege to do this. Using the + <literal>SESSION</literal> keyword sets the default transaction + level for all future transactions performed within the current + session. </para> <para> @@ -5042,12 +5059,11 @@ A consistent read means that <literal>InnoDB</literal> uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by - those transactions that committed before that point of time, and - no changes made by later or uncommitted transactions. The - exception to this rule is that the query sees the changes made - by earlier statements within the same transaction. Note that the - exception to the rule causes the following anomaly: if you - update some rows in a table, a + transactions that committed before that point of time, and no + changes made by later or uncommitted transactions. The exception + to this rule is that the query sees the changes made by earlier + statements within the same transaction. This exception causes + the following anomaly: if you update some rows in a table, a <literal role="stmt">SELECT</literal> will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the @@ -5099,7 +5115,7 @@ MODE</literal> if the <literal>innodb_locks_unsafe_for_binlog</literal> option is set and the isolation level of the transaction is not set to - serializable. Thus no locks are set to rows read from selected + serializable. Thus, no locks are set on rows read from selected table. Otherwise, <literal>InnoDB</literal> uses stronger locks and the <literal role="stmt">SELECT</literal> part acts like <literal>READ COMMITTED</literal>, where each consistent read, Modified: trunk/refman-6.0/se-innodb-core.xml =================================================================== --- trunk/refman-6.0/se-innodb-core.xml 2008-11-06 20:02:39 UTC (rev 12312) +++ trunk/refman-6.0/se-innodb-core.xml 2008-11-06 20:27:12 UTC (rev 12313) Changed blocks: 22, Lines Added: 132, Lines Deleted: 116; 20663 bytes @@ -90,9 +90,9 @@ <literal>InnoDB</literal> is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on <literal>InnoDB</literal>. Mytrix, - Inc. stores more than 1TB of data in <literal>InnoDB</literal>, and - another site handles an average load of 800 inserts/updates per - second in <literal>InnoDB</literal>. + Inc. stores more than 1TB of data in <literal>InnoDB</literal>, + and another site handles an average load of 800 inserts/updates + per second in <literal>InnoDB</literal>. </para> <para> @@ -4222,13 +4222,15 @@ <para> If there is database page corruption, you may want to dump your - tables from the database with <literal>SELECT INTO + tables from the database with <literal>SELECT INTO ... OUTFILE</literal>. Usually, most of the data obtained in this - way is intact. Even so, the corruption may cause <literal>SELECT - * FROM <replaceable>tbl_name</replaceable></literal> statements - or <literal>InnoDB</literal> background operations to crash or - assert, or even to cause <literal>InnoDB</literal> roll-forward - recovery to crash. However, you can force the + way is intact. However, it is possible that the corruption might + cause <literal>SELECT * FROM + <replaceable>tbl_name</replaceable></literal> statements or + <literal>InnoDB</literal> background operations to crash or + assert, or even cause <literal>InnoDB</literal> roll-forward + recovery to crash. In such cases, you can use the + <literal>innodb_force_recovery</literal> option to force the <literal>InnoDB</literal> storage engine to start up while preventing background operations from running, so that you are able to dump your tables. For example, you can add the following @@ -4242,14 +4244,16 @@ </programlisting> <para> - The allowable non-zero values for - <literal>innodb_force_recovery</literal> follow. A larger number - includes all precautions of smaller numbers. If you are able to - dump your tables with an option value of at most 4, then you are - relatively safe that only some data on corrupt individual pages - is lost. A value of 6 is more drastic because database pages are - left in an obsolete state, which in turn may introduce more - corruption into B-trees and other database structures. + <literal>innodb_force_recovery</literal> is 0 by default (normal + startup without forced recovery) The allowable non-zero values + for <literal>innodb_force_recovery</literal> follow. A larger + number includes all precautions of smaller numbers. If you are + able to dump your tables with an option value of at most 4, then + you are relatively safe that only some data on corrupt + individual pages is lost. A value of 6 is more drastic because + database pages are left in an obsolete state, which in turn may + introduce more corruption into B-trees and other database + structures. </para> <itemizedlist> @@ -4299,9 +4303,8 @@ </para> <para> - Prevent also insert buffer merge operations. If they would - cause a crash, do not do them. Do not calculate table - statistics. + Prevent insert buffer merge operations. If they would cause + a crash, do not do them. Do not calculate table statistics. </para> </listitem> @@ -4332,6 +4335,17 @@ </itemizedlist> <para> + <emphasis>The database must not otherwise be used with any + non-zero value of + <literal>innodb_force_recovery</literal></emphasis>. As a safety + measure, <literal>InnoDB</literal> prevents users from + performing <literal role="stmt">INSERT</literal>, + <literal role="stmt">UPDATE</literal>, or + <literal role="stmt">DELETE</literal> operations when + <literal>innodb_force_recovery</literal> is greater than 0. + </para> + + <para> You can <literal role="stmt">SELECT</literal> from tables to dump them, or <literal>DROP</literal> or <literal>CREATE</literal> tables even if forced recovery is @@ -4346,22 +4360,11 @@ rollback. </para> - <para> - <emphasis>The database must not otherwise be used with any - non-zero value of - <literal>innodb_force_recovery</literal></emphasis>. As a safety - measure, <literal>InnoDB</literal> prevents users from - performing <literal role="stmt">INSERT</literal>, - <literal role="stmt">UPDATE</literal>, or - <literal role="stmt">DELETE</literal> operations when - <literal>innodb_force_recovery</literal> is greater than 0. - </para> - </section> <section id="innodb-checkpoints"> - <title>Checkpoints</title> + <title><literal>InnoDB</literal> Checkpoints</title> <para> <literal>InnoDB</literal> implements a checkpoint mechanism @@ -4398,9 +4401,9 @@ <para> The preceding description explains why making your log files - very large may save disk I/O in checkpointing. It often makes - sense to set the total size of the log files as big as the - buffer pool or even bigger. The disadvantage of using large log + very large may reduce disk I/O in checkpointing. It often makes + sense to set the total size of the log files as large as the + buffer pool or even larger. The disadvantage of using large log files is that crash recovery can take longer because there is more logged information to apply to the database. </para> @@ -4417,9 +4420,9 @@ On Windows, <literal>InnoDB</literal> always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should - have all table and database names in lowercase. A convenient way - to accomplish this is to add the following line to the - <literal>[mysqld]</literal> section of your + create all databases and tables using lowercase names. A + convenient way to accomplish this is to add the following line to + the <literal>[mysqld]</literal> section of your <filename>my.cnf</filename> or <filename>my.ini</filename> file before creating any databases or tables: </para> @@ -4439,9 +4442,10 @@ <literal role="type">FLOAT</literal> or <literal role="type">DOUBLE</literal> data types in your tables, then the procedure is the same: simply copy the relevant files. If - the formats differ and your tables contain floating-point data, - you must use <command>mysqldump</command> to dump your tables on - one machine and then import the dump files on the other machine. + you use <command>mysqldump</command> to dump your tables on one + machine and then import the dump files on the other machine, it + does not matter whether the formats differ or your tables contain + floating-point data. </para> <para> @@ -4456,7 +4460,7 @@ <section id="innodb-transaction-model"> - <title><literal>InnoDB</literal> Transaction Model and Locking</title> + <title>The <literal>InnoDB</literal> Transaction Model and Locking</title> <para> In the <literal>InnoDB</literal> transaction model, the goal is to @@ -4466,8 +4470,9 @@ consistent reads by default, in the style of Oracle. The lock table in <literal>InnoDB</literal> is stored so space-efficiently that lock escalation is not needed: Typically several users are - allowed to lock every row in the database, or any random subset of - the rows, without <literal>InnoDB</literal> running out of memory. + allowed to lock every row in <literal>InnoDB</literal> tables, or + any random subset of the rows, without causing + <literal>InnoDB</literal> memory exhaustion. </para> <section id="innodb-lock-modes"> @@ -4484,7 +4489,7 @@ <listitem> <para> A shared (<replaceable>S</replaceable>) lock allows a - transaction to read a row (tuple). + transaction to read a row. </para> </listitem> @@ -4499,28 +4504,29 @@ <para> If transaction <literal>T1</literal> holds a shared - (<replaceable>S</replaceable>) lock on tuple - <literal>t</literal>, then + (<replaceable>S</replaceable>) lock on row <literal>r</literal>, + then requests from some distinct transaction + <literal>T2</literal> for a lock on row <literal>r</literal> are + handled as follows: </para> <itemizedlist> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>S</replaceable> - lock on <literal>t</literal> can be granted immediately. As - a result, both <literal>T1</literal> and + A request by <literal>T2</literal> for an + <replaceable>S</replaceable> lock can be granted + immediately. As a result, both <literal>T1</literal> and <literal>T2</literal> hold an <replaceable>S</replaceable> - lock on <literal>t</literal>. + lock on <literal>r</literal>. </para> </listitem> <listitem> <para> - A request from some distinct transaction - <literal>T2</literal> for an <replaceable>X</replaceable> - lock on <literal>t</literal> cannot be granted immediately. + A request by <literal>T2</literal> for an + <replaceable>X</replaceable> lock cannot be granted + immediately. </para> </listitem> @@ -4528,13 +4534,12 @@ <para> If a transaction <literal>T1</literal> holds an exclusive - (<replaceable>X</replaceable>) lock on tuple - <literal>t</literal>, then a request from some distinct - transaction <literal>T2</literal> for a lock of either type on - <literal>t</literal> cannot be granted immediately. Instead, - transaction <literal>T2</literal> has to wait for transaction - <literal>T1</literal> to release its lock on tuple - <literal>t</literal>. + (<replaceable>X</replaceable>) lock on row <literal>r</literal>, + a request from some distinct transaction <literal>T2</literal> + for a lock of either type on <literal>r</literal> cannot be + granted immediately. Instead, transaction <literal>T2</literal> + has to wait for transaction <literal>T1</literal> to release its + lock on row <literal>r</literal>. </para> <para> @@ -4550,7 +4555,7 @@ are two types of intention locks used in <literal>InnoDB</literal> (assume that transaction <literal>T</literal> has requested a lock of the indicated type - on table <literal>R</literal>): + on table <literal>t</literal>): </para> <itemizedlist> @@ -4560,7 +4565,7 @@ Intention shared (<replaceable>IS</replaceable>): Transaction <literal>T</literal> intends to set <replaceable>S</replaceable> locks on individual rows in - table <literal>R</literal>. + table <literal>t</literal>. </para> </listitem> @@ -4582,19 +4587,20 @@ <listitem> <para> - Before a given transaction can acquire an - <replaceable>S</replaceable> lock on a given row, it must - first acquire an <replaceable>IS</replaceable> or stronger - lock on the table containing that row. + Before a transaction can acquire an + <replaceable>S</replaceable> lock on a row in table + <literal>t</literal>, it must first acquire an + <replaceable>IS</replaceable> or stronger lock on + <literal>t</literal>. </para> </listitem> <listitem> <para> - Before a given transaction can acquire an - <replaceable>X</replaceable> lock on a given row, it must - first acquire an <replaceable>IX</replaceable> lock on the - table containing that row. + Before a transaction can acquire an + <replaceable>X</replaceable> lock on a row, it must first + acquire an <replaceable>IX</replaceable> lock on + <literal>t</literal>. </para> </listitem> @@ -4654,12 +4660,11 @@ <para> A lock is granted to a requesting transaction if it is - compatible with existing locks. A lock is not granted to a - requesting transaction if it conflicts with existing locks. A - transaction waits until the conflicting existing lock is - released. If a lock request conflicts with an existing lock and - cannot be granted because it would cause deadlock, an error - occurs. + compatible with existing locks, but not if it conflicts with + existing locks. A transaction waits until the conflicting + existing lock is released. If a lock request conflicts with an + existing lock and cannot be granted because it would cause + deadlock, an error occurs. </para> <para> @@ -4761,31 +4766,32 @@ <para> In <literal>InnoDB</literal>, all user activity occurs inside a - transaction. If the autocommit mode is enabled, each SQL - statement forms a single transaction on its own. By default, - MySQL starts new connections with autocommit enabled. + transaction. If autocommit mode is enabled, each SQL statement + forms a single transaction on its own. By default, MySQL starts + new connections with autocommit enabled. </para> <para> - If the autocommit mode is switched off with <literal>SET - AUTOCOMMIT = 0</literal>, then we can consider that a user - always has a transaction open. An SQL <literal>COMMIT</literal> - or <literal>ROLLBACK</literal> statement ends the current + If autocommit mode is disabled within a session with + <literal>SET AUTOCOMMIT = 0</literal>, the session always has a + transaction open. An SQL <literal>COMMIT</literal> or + <literal>ROLLBACK</literal> statement ends the current transaction and a new one starts. A <literal>COMMIT</literal> means that the changes made in the current transaction are made - permanent and become visible to other users. A + permanent and become visible to other sessions. A <literal>ROLLBACK</literal> statement, on the other hand, cancels all modifications made by the current transaction. Both - statements release all <literal>InnoDB</literal> locks that were - set during the current transaction. + <literal>COMMIT</literal> and <literal>ROLLBACK</literal> + release all <literal>InnoDB</literal> locks that were set during + the current transaction. </para> <para> - If the connection has autocommit enabled, the user can still - perform a multiple-statement transaction by starting it with an - explicit <literal>START TRANSACTION</literal> or - <literal>BEGIN</literal> statement and ending it with - <literal>COMMIT</literal> or <literal>ROLLBACK</literal>. + If the session has autocommit enabled, a multiple-statement + transaction can be performed by starting it with an explicit + <literal>START TRANSACTION</literal> or <literal>BEGIN</literal> + statement and ending it with <literal>COMMIT</literal> or + <literal>ROLLBACK</literal>. </para> </section> @@ -4809,39 +4815,50 @@ <programlisting> [mysqld] -transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED - | REPEATABLE-READ | SERIALIZABLE} +transaction-isolation = <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is + <literal>READ-UNCOMMITTED</literal>, + <literal>READ-COMMITTED</literal>, <literal>REPEATABLE-READ + </literal>, or <literal>SERIALIZABLE</literal>. + </para> + + <para> A user can change the isolation level for a single session or - for all new incoming connections with the - <literal role="stmt">SET TRANSACTION</literal> statement. Its - syntax is as follows: + for all subsequent connections with the <literal role="stmt">SET + TRANSACTION</literal> statement. Its syntax is as follows: </para> <programlisting> -SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL - {READ UNCOMMITTED | READ COMMITTED - | REPEATABLE READ | SERIALIZABLE} +SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL <replaceable>level</replaceable> </programlisting> <para> + The value of <replaceable>level</replaceable> is <literal>READ + UNCOMMITTED</literal>, <literal>READ COMMITTED</literal>, + <literal>REPEATABLE READ </literal>, or + <literal>SERIALIZABLE</literal>. + </para> + + <para> Note that there are hyphens in the level names for the <option>--transaction-isolation</option> option, but not for the <literal role="stmt">SET TRANSACTION</literal> statement. </para> <para> - The default behavior is to set the isolation level for the next - (not started) transaction. If you use the + The default behavior (without any <literal>SESSION</literal> or + <literal>GLOBAL</literal> keyword) is to set the isolation level + for the next (not started) transaction. If you use the <literal>GLOBAL</literal> keyword, the statement sets the - default transaction level globally for all new connections - created from that point on (but not for existing connections). - You need the <literal>SUPER</literal> privilege to do this. - Using the <literal>SESSION</literal> keyword sets the default - transaction level for all future transactions performed on the - current connection. + default transaction level globally for all connections created + from that point on (but not for existing connections). You need + the <literal>SUPER</literal> privilege to do this. Using the + <literal>SESSION</literal> keyword sets the default transaction + level for all future transactions performed within the current + session. </para> <para> @@ -4909,12 +4926,11 @@ A consistent read means that <literal>InnoDB</literal> uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by - those transactions that committed before that point of time, and - no changes made by later or uncommitted transactions. The - exception to this rule is that the query sees the changes made - by earlier statements within the same transaction. Note that the - exception to the rule causes the following anomaly: if you - update some rows in a table, a + transactions that committed before that point of time, and no + changes made by later or uncommitted transactions. The exception + to this rule is that the query sees the changes made by earlier + statements within the same transaction. This exception causes + the following anomaly: if you update some rows in a table, a <literal role="stmt">SELECT</literal> will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the @@ -4966,7 +4982,7 @@ MODE</literal> if the <literal>innodb_locks_unsafe_for_binlog</literal> option is set and the isolation level of the transaction is not set to - serializable. Thus no locks are set to rows read from selected + serializable. Thus, no locks are set on rows read from selected table. Otherwise, <literal>InnoDB</literal> uses stronger locks and the <literal role="stmt">SELECT</literal> part acts like <literal>READ COMMITTED</literal>, where each consistent read,
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r12313 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 6 Nov |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.118 sec. using MySQL 5.1.14-beta-log