MySql Performance Tuning (5) demonstration of using a proper lock mechanism for table locks, mysql Tuning

Source: Internet
Author: User

MySql Performance Tuning (5) demonstration of using a proper lock mechanism for table locks, mysql Tuning

MySql locks take the following forms:

1. Table-level locks; low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict, the lowest concurrency. The MyISAM engine belongs to this type.

2. Row-level locks: high overhead and slow locking; deadlocks may occur; minimum lock granularity, minimum lock conflict probability, and highest concurrency. The InnoDB engine belongs to this type.

3. Page lock: the overhead and lock time are between the table lock and the row lock. There will be deadlocks. The lock granularity is also between the two, and the concurrency is normal. NDB belongs to this type.

1. Table lock demonstration

The MyISAM storage engine only supports table locks. Therefore, you can perform the following operations:

1. Read operations on the MyISAM table will not block read requests from other processes to the same table, but will block write requests to the same table. Write operations of other processes are performed only after the read lock is released.

2. Write operations on the MyISAM table will block the read or write operations performed by other processes on the same table. Only when the write is released will the read and write operations of other processes be performed.

[Example ]:

Open another session:

Session 2 waits until the lock is released.

Execute Session 2 at the same time:

Ii. Demonstration of row locks

The InnoDB Storage engine is implemented by locking index items. This means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks.

1. Row lock

In the myiSAM engine, two sessions will respond when updating the same record, because myiSAM is a table lock.


But in InnoDB:

In Session 2,

Lock wait. Because the same record is updated.

2. search data without an index

The reason is that InnoDB uses row-level locks to retrieve data through index conditions. Otherwise, InnoDB uses table locks.

3. deadlock


In this case, Session 1 will always wait...


Session 2 changes will time out...


View session 1 again



After a deadlock occurs, InnoDB will automatically detect that it will release the lock for one transaction and roll back the transaction. The other transaction will get the lock and continue to complete the transaction. Deadlocks cannot be avoided. We can adjust the business logic to minimize the probability of deadlocks.

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.