List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:May 9 2012 1:01pm
Subject:Re: One table gets locked by itself
View as plain text  
On 5/9/2012 6:17 AM, Johan De Meersman wrote:
> ----- Original Message -----
>
>> From: "Claudio Nanni"<claudio.nanni@stripped>
>
>> Yes indeed,
>> but I think we are talking about MySQL level deadlocks,
>> that can happen only with row level locking and transactions.
>> If the deadlock is generated at application level then you can have
>> it on anything, also blackhole :-)
> Yup, but you don't know where the lock comes from, so that's possible.
>
> However, I just checked the documentation, and he *is* right after all: MySQL claims
> to be deadlock-free for table-locking engines, they simply lock all tables at once (I
> suppose that means any lock statement will invalidate the previous one) and always lock
> tables in the same order.
>

Actually, we do not lock all tables at the same time. Each table is 
locked as it is needed by the connection that needs it.

As long as this is an implicit lock (created by the SQL command to be 
executed) and not a lock held by an explicit LOCK command then there is 
no way for any two connections to deadlock on a MyISAM (or another 
non-transactional table). It is only when a lock extends beyond the 
single-object scenario that makes a deadlock is possible. As each 
statement against a MyISAM table generally only needs to lock one object 
(the table) no two MySQL-based statements can deadlock. Even in those 
cases where one statement uses two objects (such as a INSERT...SELECT... 
or multi-table UPDATE command) the lock prioritization process for 
MyISAM prevents any two threads from cross-locking at the same time.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
One table gets locked by itselfabhishek jain8 May
  • Re: One table gets locked by itselfDarryle8 May
    • RE: One table gets locked by itselfabhishek jain8 May
      • Re: One table gets locked by itselfDarryle Steplight8 May
        • RE: One table gets locked by itselfRick James8 May
          • RE: One table gets locked by itselfClaudio Nanni8 May
  • Re: One table gets locked by itselfnixofortune8 May
    • Re: One table gets locked by itselfDarryle Steplight8 May
    • Re: One table gets locked by itselfnixofortune8 May
      • Re: One table gets locked by itselfClaudio Nanni8 May
      • Re: One table gets locked by itselfJohan De Meersman9 May
        • Re: One table gets locked by itselfClaudio Nanni9 May
          • Re: One table gets locked by itselfJohan De Meersman9 May
            • Re: One table gets locked by itselfShawn Green9 May