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.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN