Shulou(Shulou.com)06/01 Report--

What is the purpose of locks in mysql? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently. Locks ensure the consistency and effectiveness of data concurrent access, and lock conflicts are also an important factor affecting the performance of database concurrent access. Locks are the concurrency control of Mysql at the server layer and the storage engine layer.

Locking consumes resources, and various operations of the lock include acquiring the lock, checking whether the lock has been released, releasing the lock, and so on.

The use of mysql locks:

To put it simply, it's lock table, don't let anyone else move.

1. There are two main types: lock sharing lock and exclusive lock

When sharing the lock, others can read it and cannot change the scale data.

When exclusive lock, others can neither read nor change the table data.

According to the above characteristics, you should know when to use the lock. If you don't want others to change the data and have an impact on yourself, add the lock. The lock must be released after it is not in use, otherwise, the application system will always report an error because of reading the data.

2. The advantage is to ensure the atomicity, integrity and consistency of the data. Only when the lock holder releases the lock can others change the data.

3. The disadvantage is that the system overhead is increased, and lock waiting may occur, resulting in abnormal operation of the database. These are all problems caused by abnormal use of locks.

Lock performance optimization recommendation

Try to use a lower isolation level; carefully design the index and use the index to access data to make the locking more accurate, so as to reduce the chance of lock conflicts to choose a reasonable transaction size, small transactions are also less likely to lock to the recordset display lock, it is best to request a sufficient level of lock at one time. For example, if you want to modify the data, it is best to apply for an exclusive lock directly, rather than apply for a shared lock first, and then request an exclusive lock when you modify it. When programs that easily cause deadlocks access a set of tables, they should try to agree to access each table in the same order. For a table, access the rows in the table in a fixed order as far as possible. This greatly reduces the chance of deadlocks to access data with equal conditions as much as possible, so as to avoid the impact of gap locks on concurrent insertions. Do not apply for more than the actual lock level unless necessary, do not show locking when querying. MySQL's MVCC can realize that queries in transactions do not need to be locked and optimize transaction performance; MVCC only works under two isolation levels: COMMITTED READ (read commit) and REPEATABLE READ (repeatable readable). For some specific transactions, table locks can be used to improve processing speed or reduce the possibility of deadlocks. Is it helpful for you to read the above? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.