MySQL Tutorial: A summary of the lock mechanism in MySQL

Source: Internet
Author: User
Tags lock queue mysql tutorial

Original link:

Today to briefly summarize the locking mechanism of MySQL, improper welcome to PAT Bricks!

1, for MySQL, there are three kinds of lock level: page level, table level, row level.

The typical page-level representative engine is BDB.

The typical representative engine for the table level is myisam,memory and ISAM, a long time ago.

The typical line-level representative engine is InnoDB.

2, we use the most of the actual application is the row lock.

The advantages of row-level locks are as follows:

1. Reduce lock state when many connections have different queries.

2), if there is an exception, you can reduce the loss of data. Because you can roll back only one row or a few lines of data at a time.

The disadvantages of row-level locks are as follows:

1), more memory than page-level locks and table-level locks.

2), the query is more than page-level locks and table-level locks need more I/O, so we often use row-level locks in the write operation rather than read operations.

3), easy to appear deadlock.

3, the MySQL uses writes the queue and reads the queue to realize to the database writes and reads the operation.

For write locks as follows:

1), if the table is not locked, then write lock on it.

2), otherwise, put the request into the write lock queue.

For read locks as follows:

1), if the table does not write lock, then add a read lock.

2), otherwise, put the request into the read lock queue.

Of course, we can use low_priority and high_priority to change these behaviors in both write and read operations.

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: 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.