List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:November 28 2011 2:47pm
Subject:Re: cope with deadlock
View as plain text  
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
Thread
cope with deadlock王科选17 Nov
  • Re: cope with deadlockMySQL)28 Nov