MySQL concurrency control

Source: Internet
Author: User

1, multiple threads at the same time modify the data, there is inconsistent data situation, that is, concurrency control problems.
2, MySQL provides read lock and write lock, read the lock can be read more lock, can not add write lock, and write lock cannot add any lock. In other words, the read lock is shared and the write lock is exclusive.
3, lock granularity, in order to better concurrency control, the granularity of the lock should be as small as possible, that is, locking only modified data. However, the lock itself has some overhead, including acquiring the lock, checking whether the lock is released, releasing the lock, and these operations also consume a certain amount of resources. The size of the lock is small, in the concurrency control, it means that more locks are needed, and the total cost of the lock is greater.
4, according to the granularity of the lock, divided into table lock and row lock, MySQL itself using table lock to achieve different purposes, such as ALTER TABLE, this time will ignore the storage engine lock mechanism. The Storage engine supports table and row locks, and the implementation of different storage engines is different.
5. Special NOTE: MySQL supports different levels of transaction isolation, the higher the isolation level, the greater the granularity of the lock, which is the more content of the lock. For example:
consider the following situation, the transaction isolation level for A/b client is read-uncommitted, and the granularity of the lock is the row lock.
Step One, a execute start transaction, modify a record
Step two, B execute start transaction, modify another record
The two are non-blocking, proving that read-uncommitted is a row lock. Similarly, it can be proved that read-committed is a row lock, Repeatable-read and serializable are table locks.
6, deadlock, MySQL in the transaction, INNODB will automatically lock according to the transaction isolation level, and release the actual transaction commit or rollback. This will present a deadlock situation, consider the following situation:
A a , the transaction isolation level of the client is read-uncommitted, and the granularity of the lock is the row lock.
Step One, a execute start transaction, modify the record 1
Step two, B execute start transaction, modify record 2
step Three, a modify the record 2
Step Four, b modify the record 1
deadlock, but the MySQL function is very powerful, can detect this deadlock, B change record 1 when error error 1213 (40001): Deadlock found time trying to get lock; try restarting Tra NS
Action

MySQL concurrency control

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.