On 11/17/2011 01:41, 王科选 wrote:
hi,
From this url:
http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html , mysql
says "If you are using locking reads (|SELECT ... FOR UPDATE|
<http://dev.mysql.com/doc/refman/5.5/en/select.html>or|SELECT ... LOCK
IN SHARE MODE|), try using a lower isolation level such as|READ
COMMITTED|
<http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed>."
What's the reason? I have read some online material, but still don't get
the point, can anyone explain why?
The reason behind using less locking has to do with the reason for
deadlocks in the first place. A deadlock happens when two consumers of
a resource need access to the parts of that resource that the other
consumer controls. An absurdly simple example of a deadlock:
* There is a table of 1000 records
* User A starts updating the table in incrementing order (1, 2, 3, ...)
* User B starts updating the table in descending order (1000, 999, 998, ...)
* The two transactions meet somewhere in the middle of the table.
Because neither A nor B could complete its sequence of changes without
access to the rows controlled by the other transaction, we have achieved
a deadlock. One of the transactions will be rolled back to allow the
other to continue.
Deadlocking cannot be eliminated from any system that shares resources
in a random-access method among multiple users. There are, however,
many ways to reduce deadlocking:
* Always access your resources in the same sequence. This means both
table sequence and row sequence per table.
* Only lock those resources that you absolutely need for your
transaction. The fewer things you need to lock, the less likely it will
be that another session will need to use them too.
* Keep your locks for the least time possible. By reducing the duration
of your locks, you are also reducing the chances that another session
will need to use those resources at the same time you are using them.
* Use shared locks instead of exclusive locks whenever possible. When it
comes to transaction isolation in InnoDB, the less isolation you
require, the more likely you are to generate a shared lock vs an
exclusive lock.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
