MySQL locks and deadlocks

Copyright statement: original works can be reproduced. During reprinting, be sure to mark them as hyperlinks.ArticleOriginal publication, author information, and this statement. Otherwise, legal liability will be held. Http://blog.csdn.net/mayongzhan-ma yongzhan, myz, mayongzhan

The following is a summary. I have referenced some network experience. MyISAM and memory storage engines use the table-Level Lock table-level lockingbdb storage engine using the page lock page-level locking, however, the InnoDB Storage engine supports both row-Level Lock row-level locking and table-Level Lock. However, by default, row-Level Lock has a low table-Level Lock overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict, the lowest concurrency row-Level Lock overhead, slow locking; deadlock; minimum lock granularity, the probability of lock conflict is the lowest, the concurrency is also the highest page lock overhead, and the lock time is between the table lock and the row lock; a deadlock occurs; the lock granularity is between the table lock and the row lock. The concurrency is generally only from the perspective of the lock: The table-Level Lock is more suitable for queries, and only a few applications update data according to the index conditions, for example, web application row-level locks are more suitable for applications with a large number of concurrent updates to a small amount of different data based on index conditions and concurrent queries. For example, some online transaction processing system deadlocks are called deadlocks <deadlock>: two or more processes are waiting for resources during execution. If there is no external force, they cannot be pushed forward. it is said that the system is in a deadlock state or the system has a deadlock. These processes that are always waiting for mutual deadlock are called deadlock processes. table-level locks do not produce deadlocks. so the most common InnoDB is to solve deadlocks. execute show processlist to find the deadlock thread number. then kill processno. Of course, the main solution is to take a look at the specific operation. deadlocks may occur. Check the engine status of show InnoDB status. You can see which statements generate deadlocks and solve them. how to solve the problem depends on the specific problem.