![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961294.webp)
The
InnoDB
Transaction Model and Locking
1274
However, this does not endanger transaction integrity, as discussed in
Section 14.2.7.8, “Deadlock
Detection and Rollback”
. See also
Section 14.2.13, “Limits on
InnoDB
Tables”
.
14.2.7.7. Implicit Transaction Commit and Rollback
By default, MySQL starts the session for each new connection with autocommit mode enabled,
so MySQL does a commit after each SQL statement if that statement did not return an error. If a
statement returns an error, the commit or rollback behavior depends on the error. See
Section 14.2.11,
“
InnoDB
Error Handling”
.
If a session that has autocommit disabled ends without explicitly committing the final transaction,
MySQL rolls back that transaction.
Some statements implicitly end a transaction, as if you had done a
COMMIT
before executing the
statement. For details, see
Section 13.3.3, “Statements That Cause an Implicit Commit”
.
14.2.7.8. Deadlock Detection and Rollback
InnoDB
automatically detects transaction deadlocks and rolls back a transaction or transactions to
break the deadlock.
InnoDB
tries to pick small transactions to roll back, where the size of a transaction
is determined by the number of rows inserted, updated, or deleted.
InnoDB
is aware of table locks if
innodb_table_locks = 1
(the default) and
autocommit =
0
[436]
, and the MySQL layer above it knows about row-level locks. Otherwise,
InnoDB
cannot detect
deadlocks where a table lock set by a MySQL
LOCK TABLES
statement or a lock set by a storage
engine other than
InnoDB
is involved. You must resolve these situations by setting the value of the
innodb_lock_wait_timeout
[1244]
system variable.
When
InnoDB
performs a complete rollback of a transaction, all locks set by the transaction are
released. However, if just a single SQL statement is rolled back as a result of an error, some of the
locks set by the statement may be preserved. This happens because
InnoDB
stores row locks in a
format such that it cannot know afterward which lock was set by which statement.
14.2.7.9. How to Cope with Deadlocks
Deadlocks are a classic problem in transactional databases, but they are not dangerous unless
they are so frequent that you cannot run certain transactions at all. Normally, you must write your
applications so that they are always prepared to re-issue a transaction if it gets rolled back because of
a deadlock.
InnoDB
uses automatic row-level locking. You can get deadlocks even in the case of transactions
that just insert or delete a single row. That is because these operations are not really “atomic”; they
automatically set locks on the (possibly several) index records of the row inserted or deleted.
You can cope with deadlocks and reduce the likelihood of their occurrence with the following
techniques:
• Use
SHOW ENGINE INNODB STATUS
to determine the cause of the latest deadlock. That can help
you to tune your application to avoid deadlocks.
• Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous.
Just try again.
• Commit your transactions often. Small transactions are less prone to collision.
• If you are using locking reads (
SELECT ... FOR UPDATE
or
SELECT ... LOCK IN SHARE
MODE
), try using a lower isolation level such as
READ COMMITTED
[1121]
.
• Access your tables and rows in a fixed order. Then transactions form well-defined queues and do not
deadlock.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...