MySQL lock mechanism

Source: Internet
Author: User
Tags aliases

MySQL lock mechanism is divided into table-level lock and row-level lock table-level lock to the entire table back lock, write lock or read lock (Unlock tables)

LOCK TABLE table_name [as Alias_name] READ

Release the lock using unlock tables. Aliases can be used for tables, and aliases must be used if aliases are used. A successful request for a read lock assumes that no thread is currently using a write lock on the table, otherwise the statement will be blocked. After the request for a read lock succeeds, other threads can also read the table, but the thread is not allowed to write to it, even if it is not allowed by the current threads. When a table is locked, it is only possible to read a table, and an error occurs when reading other tables (TableName is not locked with lock TABLES)

LOCK TABLE table_name [as Alias_name] [low_priority] WRITE

Aliases can also be used, unlike read locks, where the priority of a lock can be specified in a write lock. Low_priority is a lock with lower priority than read lock, and low_priority has the lowest priority when multiple threads request multiple locks at the same time (Low_priority,read,write). The prerequisite for a read lock application is that there are no threads reading and other write locks on the table, otherwise it will be blocked.

Table-level locks are useful in both MyISAM and InnoDB, where the cost of creating a lock is small and there is no deadlock, because the entire table is locked, so the concurrency is low. Table-level locks are recommended when you need to do GROUP by operations on most data frequently or when you need to scan the entire table frequently.

Row-level locks have shared locks (read locks), exclusive locks (write locks)

Row-level locks are added only when retrieved through an index, which is indexed, and table-level locks are used if an SQL statement does not use a row-level lock for the index. The disadvantage of row-level locks is that they are slow and memory intensive because of the need to request a large number of lock resources

Shared lock multiple transactions can share a lock on the same data, can access the data, but read-only non-change, MySQL will be in the query results of each row in the shared lock, when no other thread on the query result set in the use of exclusive locks, you can successfully request a shared lock, otherwise it will be blocked. Other threads can also read tables that use shared locks, and those threads are reading the same version of the data

SELECT inch SHARE MODE;

Exclusive lock, gets the lock transaction readable, modified, MySQL will be in the query results each row is exclusive lock, when no other thread on any row in the query result set using an exclusive lock, you can successfully request an exclusive lock, otherwise it will be blocked

SELECT  for UPDATE;
Dead lock

The root cause of deadlocks is that more than two processes require the other to release resources so that the process waits. The code is because two or more transactions require another to release resources.

MySQL lock mechanism

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.