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

What the editor shares today is a summary of MySQL's issues related to locking. Maybe you are no stranger to MySQL, or you have never known MySQL. But don't worry, today the editor will use the simplest description to explain the problem of locking in MySQL. Let's take a look.

How to access the database concurrently? The answer is to lock it. Next, what are the locking mechanisms of the database and what locks are there in the database?

First of all, locking is a concurrency control technology that is used to protect data when multiple users access the same data at the same time.

There are two basic types of locks:

Shared (S) lock: multiple transactions can block a shared page; no transaction can modify the page; usually when the page is read, the S lock is released immediately. When executing the select statement, you need to add a shared lock to the Operand (table or some records), but you need to check whether there is an exclusive lock before adding the lock. If not, you can add a shared lock (N shared locks can be added to an object), otherwise you can't. Shared locks are usually released after the execution of a select statement, or at the end of a transaction (both normal and abnormal), depending on the transaction isolation level set by the database.

Exclusive (X) lock: only one transaction is allowed to block this page; any other transaction must wait until the X lock is released to access the page; the X lock cannot be released until the end of the transaction. When executing insert, update, delete statements, you need to add an exclusive lock to the object of operation. Before adding an exclusive lock, you must make sure that there is no other lock on the object. Once you add an exclusive lock, you cannot add any other locks to the object. Exclusive locks are usually released at the end of the transaction (there are exceptions, of course, when the database transaction isolation level is set to Read Uncommitted (read uncommitted data), in which case the exclusive lock is released after the update operation, not at the end of the transaction).

Lock-pressing mechanism

Now that the lock is used, there is a possibility of deadlock.

Four necessary conditions for creating a deadlock:

Mutex: a resource can only be used by one process at a time.

Request and hold condition: when a process is blocked by a request for resources, it holds on to the resources it has acquired.

Inalienable condition: the resources that have been acquired by the process cannot be forcibly deprived until they are used up.

Loop waiting condition: a loop waiting resource relationship is formed between several processes.

As long as there is a deadlock in the system, these conditions must be established, and as long as one of the above conditions is not met, deadlock will not occur.

Prevent deadlock

It is only necessary to destroy one of the four necessary conditions to prevent the occurrence of deadlock.

1) break the mutually exclusive condition

If all system resources are allowed to be shared, the system will not enter a deadlock state. However, some resources cannot be accessed at the same time, and critical resources such as printers can only be mutually exclusive. Therefore, the method of breaking the mutex condition to prevent deadlock is not feasible, and in some cases this mutex should be protected.

2) destroy the inalienable condition

When a process that has maintained some inalienable resources requests new resources and is not satisfied, it must release all resources that have been maintained and reapply them later when needed. This means that the resources already occupied by a process will be temporarily released, or deprived, or thus undermined the inalienable conditions.

The implementation of this strategy is complicated, and releasing the acquired resources may cause the failure of the previous stage of work. Repeatedly applying and releasing resources will increase the system overhead and reduce the system throughput. This method is often used for resources that are easy to save and restore state, such as CPU registers and memory resources, and generally can not be used for resources such as printers.

3) destroy the conditions of the request and hold

The pre-static allocation method is adopted, that is, the process requests all the resources it needs before running, and does not put it into operation until its resources are satisfied. Once put into operation, these resources always belong to it, and no other resource requests are made, so as to ensure that the system will not have a deadlock.

This approach is simple to implement, but the disadvantages are also obvious. System resources are seriously wasted, some of which may only be used at the beginning or near the end of the run, or even not at all. It will also lead to "hunger", which will delay the process waiting for that resource to start running when individual resources are occupied by other processes for a long time.

4) destroy the loop waiting conditions

In order to destroy the loop waiting condition, the sequential resource allocation method can be used. First of all, the resources in the system are numbered, stipulating that each process must request resources in the order of increasing numbers, and similar resources are applied at once. In other words, as long as the process applies for the allocation of resource Ri, the process can only apply for resources whose number is greater than Ri in the future.

When there is a problem with this method, the number must be relatively stable, which limits the increase of new types of equipment; although the order in which most jobs actually use these resources has been taken into account when numbering resources, however, it often occurs that the order in which jobs use resources is different from the order specified by the system, resulting in a waste of resources. In addition, this method of applying for resources in the prescribed order is bound to bring trouble to the user's programming.

Release the deadlock

1) deprive resources from the deadlock process

2) terminate some or all of the processes

Granularity of MySQL locks (that is, the level of locks)

Each storage engine of MySQL uses three types of locking mechanisms: row-level locking, page-level locking, and table-level locking.

1. Table-level lock: lock the entire table directly. During your locking, other processes cannot write to the table. If you are a write lock, other processes are not allowed to read. Features: low overhead, fast locking; no deadlock; lock granularity is the largest, the probability of lock conflict is the highest, and the concurrency is the lowest.

The MyISAM storage engine uses table-level locks.

There are two modes: table shared read lock and table exclusive write lock. Read lock command: lock table table name read; unlock command: unlock tables.

Support for concurrent insertion: support query and insert operations to run concurrently (concurrent insert at the end of the table).

Lock scheduling mechanism: write lock priority. How does MySQL handle that one process requests a read lock for a MyISAM table while another process requests a write lock for the same table? The answer is that the writing process acquires the lock first.

2. Row-level locks: only the specified records are locked so that other processes can still operate on other records in the same table. Features: high cost, slow locking, deadlock will occur; lock granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

The InnoDB storage engine supports both row-level and table-level locks, but row-level locks are used by default.

3. Page-level lock: lock a set of adjacent records at a time. The cost and locking time are between table locks and row locks; deadlocks occur; the locking granularity is between table locks and row locks, and the concurrency is average.

The most common way to handle multi-user concurrent access is to lock it. When a user locks an object in the database, other users can no longer access the object. The impact of locking on concurrent access is reflected in the granularity of the lock. For example, a lock placed on a table restricts concurrent access to the entire table; a lock placed on a data page restricts access to the entire data page; and a lock placed on a row restricts concurrent access to that row only.

The concept, implementation and usage scenario of optimistic lock and pessimistic lock

There are two mechanisms for locking: pessimistic locking and optimistic locking.

Pessimistic lock, as its name suggests, is pessimistic about the world, believing that the probability of others accessing changing data is high, so the data is locked from the beginning of the change and is not released until the change is complete.

A typical pessimistic lock call that relies on the database:

Select * from account where name= "Erica" for update

This SQL statement locks all records in the account table that meet the retrieval criteria (name= "Erica"). Before this transaction commits (locks during the transaction are released when the transaction commits), these records cannot be modified by the outside world. This statement is used to lock specific rows (if the where clause, those rows that meet the where condition). When these rows are locked, other sessions can select them, but cannot change or delete them until the transaction of the statement is terminated by a commit statement or a rollback statement. It is important to note that select... for update should be placed in the transaction types of MySQL, namely begin and commit, otherwise it will not work.

Pessimism may cause locking for a long time and poor release, especially long transactions, affecting the overall performance of the system.

The way to be pessimistic:

Pessimistic lock is also implemented based on database locking mechanism. Many of these locking mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks and so on.

Optimistic lock, which is optimistic about the world, believes that the probability of others accessing the changing data is very low, so the data will not be locked until the modification is completed and ready to commit to the database. When you read and change the object, do not lock it, and release it when you finish the change. Optimistic locks cannot solve the problem of dirty reading.

The locking time of optimistic lock is shorter than that of pessimistic lock, which greatly improves the overall performance of the system under large concurrency.

The implementation of optimistic locks:

1. Mostly based on the data version (version) recording mechanism, you need to add a version ID for each row of data (that is, one more field version for each row of data), and update the corresponding version number + 1 each time the data is updated.

How it works: when reading the data, read this version together, and then add one to the version number when it is updated. At this point, the version information of the submitted data is compared with the current version information recorded in the corresponding database table, and if the submitted data version number is greater than the current version number of the database table, it will be updated, otherwise it is considered to be out-of-date data. Had to re-read the object and make changes.

2. Use timestamp to implement

Also add a field to the table that needs optimistic lock control. The name does not matter. The field type uses a timestamp, which is similar to the above version. When the update is submitted, the timestamp of the data in the current database is checked and compared with the timestamp taken before the update. If it is consistent, then OK, otherwise it is a version conflict.

The applicable scenarios of pessimistic lock and optimistic lock:

If the concurrency is small, pessimistic locks can be used to solve the concurrency problem; but if the concurrency of the system is very large, pessimism will bring great performance problems, so we have to choose the optimistic locking method. Now most applications are supposed to be optimistic locks.

The above is a brief introduction to the summary of MySQL issues related to locking, of course, the detailed use of the above differences have to be used by everyone to understand. If you want to know more, welcome to follow the industry information channel!

Cloud database MySQL supports and provides basic functions and solutions such as disaster recovery, backup and recovery, data encryption and data migration. With flexible backup strategy and multi-level backup system, it can provide users with more secure, stable and reliable online database services.

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.