How to access the database concurrently? The answer islock.
Recommended: "mysql video tutorial"
Next, let’s talk about the locking mechanism of the database. What locks are there in the database?
First of all, locks are a concurrency control technology. Locks are used to protect data when multiple users access the same data at the same time.
There are 2 basic lock types:
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 operation object (table or some records), but before adding the lock, you need to check whether there is an exclusive lock. If not, you can add a shared lock (N shared locks can be added to an object ), otherwise it won't work. The shared lock is usually released after the select statement is executed. Of course, it may also be released when the transaction ends (including normal end and abnormal end), mainly 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 before accessing the page; the X lock cannot be released until the end of the transaction. When executing insert, update, and delete statements, you need to add an exclusive lock to the object being operated. Before adding an exclusive lock, you must confirm that there are no other locks on the object. Once an exclusive lock is added, you cannot add other locks to the object. Any lock. The exclusive lock is usually released at the end of the transaction (of course there are exceptions, that is, when the database transaction isolation level is set to Read Uncommitted (read uncommitted data), in this case the exclusive lock will be released after the update operation is completed) released, not at the end of the transaction).
The mechanism of locking
Since a lock is used, there is a possibility of deadlock.
Four necessary conditions for deadlock:
Mutually exclusive condition: A resource can only be used by one process at a time.
Request and hold conditions: When a process is blocked due to requesting resources, it will keep the obtained resources.
Non-deprivation condition: The resources that have been obtained by the process cannot be forcibly deprived before they are used up.
Loop waiting condition: Several processes form a head-to-tail loop waiting for resources relationship.
As long as a deadlock occurs in the system, these conditions must be true, and as long as one of the above conditions is not met, a deadlock will not occur.
Preventing Deadlock
To prevent the occurrence of deadlock, you only need to destroy one of the four necessary conditions for the occurrence of deadlock.
1) Destroy the mutual exclusion 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 at all. Critical resources such as printers can only be used mutually exclusive. Therefore, it is not feasible to destroy mutual exclusion conditions to prevent deadlock, and in some cases this mutual exclusivity should be protected.
2) Destruction of inalienable conditions
When a process that has retained some inalienable resources requests new resources but cannot be satisfied, it must release all the resources it has retained. , and then reapply when needed in the future. This means that resources already occupied by a process will be temporarily released, or deprived, or thereby violate the inalienable condition.
This strategy is relatively complex to implement. Releasing the acquired resources may cause the failure of the previous stage of work. Repeatedly applying for and releasing resources will increase system overhead and reduce system throughput. This method is often used for resources whose status is easy to save and restore, such as CPU registers and memory resources. It generally cannot be used for resources such as printers.
3) Destroy the conditions for request and retention
Use the pre-static allocation method, that is, the process applies for all the resources it needs once before running. Before its resources are satisfied, it will not Put it into operation. Once put into operation, these resources will always be owned by it, and no other resource requests will be made, thus ensuring that the system will not deadlock.
This method is simple to implement, but its shortcomings are also obvious. System resources are seriously wasted. Some of these resources may only be used at the beginning of the run or near the end of the run, or even not used at all. It will also lead to a "starvation" phenomenon. When a certain resource is occupied by other processes for a long time, the process waiting for the resource will be delayed in starting to run.
4) Destroy the loop waiting condition
In order to destroy the loop waiting condition, the sequential resource allocation method can be used. First, number the resources in the system, and stipulate that each process must request resources in increasing order of numbers, and all similar resources can be requested at once. In other words, as long as a process applies to allocate resources Ri, the process can only apply for resources with numbers greater than Ri in future resource applications.
The problem with this method is that the numbering must be relatively stable, which limits the addition of new types of equipment; although the order in which most jobs actually use these resources is taken into account when numbering resources, it often happens If the order in which a job uses resources is different from the order specified by the system, resources will be wasted; in addition, this method of applying for resources in the specified order will inevitably cause trouble for users in programming.
Relieving deadlock
1) Deprive resources from the deadlock process;
2) Terminate some or all processes;
MySQL lock granularity (ie, lock level)
MySQL storage engines use three types (levels) of locking mechanisms: row-level locking, page-level locking and table-level locking .
1. Table-level lock: Directly lock the entire table. During your locking period, other processes cannot write to the table. If you have a write lock, other processes are not allowed to read. Features: low overhead, fast locking; no deadlocks; the largest lock granularity, the highest probability of lock conflicts, and the lowest concurrency.
The MyISAM storage engine uses table-level locks.
There are two modes: table shared read lock and table exclusive write lock. The command to add read lock: lock table table name read; The command to remove lock: unlock tables.
Support concurrent insertion: Support query and insertion operations to run concurrently (concurrent insertion at the end of the table).
Lock scheduling mechanism: write lock priority. A process requests a read lock on a MyISAM table, and at the same time another process also requests a write lock on the same table. How does MySQL handle it? The answer is that the writing process acquires the lock first.
2. Row-level locking: Only lock specified records, so that other processes can still operate on other records in the same table. Features: high overhead, slow locking; deadlocks may occur; the lock granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.
The InnoDB storage engine supports both row-level locks and table-level locks, but row-level locks are used by default.
3. Page-level lock: Lock a group of adjacent records at a time. The overhead and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average.
The most commonly used method to handle concurrent access by multiple users is locking. 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, (table lock) a lock placed on a table limits concurrent access to the entire table; (page lock) a lock placed on a data page limits access to the entire data page; (row lock) a lock placed on a row The lock limits concurrent access to the row only.
The concepts, implementation methods and usage scenarios of optimistic locking and pessimistic locking
There are two lock mechanisms: pessimistic locking and optimistic locking.
Pessimistic lock, as its name suggests, is pessimistic about the world. It believes that the probability of others accessing the changing data is very high, so it locks the data when the data starts to change until the change is completed. Only then released.
A typical database-dependent pessimistic lock call:
Select * from account where name="Erica" for update
This SQL statement locks all entries in the account table Records matching the search criteria (name="Erica"). Before this transaction is committed (the lock during the transaction will be released when the transaction is committed), the outside world cannot modify these records. This statement is used to lock specific rows (if there is a where clause, it is those rows that meet the where condition). When these rows are locked, other sessions can select these rows, but they cannot change or delete these rows until the statement's transaction is terminated by a commit statement or a rollback statement. It should be noted that select...for update must be placed in the MySQL transaction type, namely begin and commit, otherwise it will not work.
Pessimism may cause locking to take a long time and issue poorly, especially long transactions, which may affect the overall performance of the system.
Implementation method of pessimistic lock:
Pessimistic lock is also implemented based on database lock mechanism. Many such lock mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks, etc., which are all locked before operations.
Optimistic locking is optimistic about the world and believes that the probability of others accessing the changing data is very low. Therefore, the data will not be locked until the modification is completed and the modification is ready to be submitted to the database. When You do not lock the object when you read and change it, and release it after completing the changes. Optimistic locking cannot solve the problem of dirty reads.
The locking time of optimistic locking is shorter than that of pessimistic locking, which greatly improves the overall performance of the system under large concurrency.
Implementation method of optimistic locking:
1. Most of them are based on the data version (version) recording mechanism. It is necessary to add a version identifier for each row of data (that is, one more field for each row of data). version), each time the data is updated, the corresponding version number 1 must be updated.
Working principle: When reading data, read this version together, and when updating later, add one to this version number. At this time, the version information of the submitted data is compared with the current version information of the corresponding record in the database table. If the version number of the submitted data is greater than the current version number of the database table, it will be updated. Otherwise, it is considered to be expired data and has to be read again. Get the object and make changes.
2. Use timestamp to implement
Also add a field to the table that requires optimistic locking control. The name does not matter. The field type uses timestamp, which is similar to the version above. , also when the update is submitted, the timestamp of the data in the current database is checked and compared with the timestamp obtained before the update. If they are consistent, it is OK, otherwise it is a version conflict.
Applicable scenarios of pessimistic locking and optimistic locking:
If the amount of concurrency is not large, you can use pessimistic lock to solve the concurrency problem; but if the amount of concurrency in the system is very large, , pessimism will bring about very big performance problems, so we have to choose the optimistic locking method. Most applications now should be optimistically locked.
The above is the detailed content of Detailed explanation of issues involving locks in MySQL. For more information, please follow other related articles on the PHP Chinese website!