This is my seventh day of the Gwen Challenge

1. MySQL lock:

1MySQL supports the following locking mechanisms:

1. Table level lock

Low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low. Table locks are available in two modes: supported in MyISAM engine

1. Table Read Lock
2. Table Write Lock Table Write Lock

The read operation on the MyISAM table does not block other users’ read requests to the same table, but blocks the write requests to the same table. Write operations to the MyISAM table block other users’ read and write requests to the same table. MyISAM table is serial between read and write operations, and between write and write operations! When a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations on other threads wait until the lock is released.

Mysql > alter table lock;

MyISAM will automatically lock all tables involved in a read operation before executing a SELECT statement, and will automatically lock all tables involved in a write operation before performing an UPDATE operation (UPDATE, DELETE, INSERT, etc.). This process does not require user intervention. Therefore, users generally do not need to directly use the LOCK TABLE command to explicitly LOCK MyISAM TABLE.

3.MyISAM lock scheduling:

As mentioned earlier, the MyISAM storage engine’s read and write locks are mutually exclusive and the read operations are serial. A process requests a read lock on a MyISAM table while another process requests a write lock on the same table. The answer is that the writer gets the lock first. In addition, even if the read process requests the lock first, the write lock will be inserted before the read request! This is because MySQL considers write requests generally more important than read requests. This is why MyISAM tables are not suitable for applications with a large number of update and query operations, because a large number of update operations make it difficult for query operations to acquire read locks and may block forever. This can sometimes go horribly wrong! Fortunately, we can adjust MyISAM’s scheduling behavior with some Settings.

By specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to read requests by default.

SET LOW_PRIORITY_UPDATES=1 to lower the priority of update requests sent by this connection.

Lower the priority of INSERT, UPDATE, and DELETE statements by specifying the LOW_PRIORITY attribute.

Although the above three methods are either update-first or query-first, they can still be used to solve the serious problem of read lock waiting in applications where the query is relatively important (such as user login system).

In addition, MySQL also provides a compromise method to adjust read/write conflicts, that is, to set the system parameter max_write_lock_count to an appropriate value. When the read lock of a table reaches this value, MySQL temporarily reduces the priority of the write request, giving the read process a certain chance to obtain the lock.

The write-first scheduling mechanism and workarounds have been discussed above. One more point: some queries that take a long time to run can starve the writing process! Therefore, the application should avoid long running query operations, do not always want to use a SELECT statement to solve the problem. Because this seemingly clever SQL statements are often complex and take a long time to execute, if possible, the SQL statements can be “decomposed” by using intermediate tables and other measures, so that each step of the query can be completed in a short time, thus reducing lock conflicts. If complex queries are unavoidable, try to schedule them during database downtime, such as some scheduled statistics that can be scheduled at night.

4. Row-level lock:

High overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. InnodB engine defaults to row-level locking, which is different from table locking in many ways.

1.InnoDB row lock mode:

1. Shared lock: An exclusive lock that allows one transaction to read a row, preventing other transactions from acquiring the same data set.
2. Exclusive lock (X) : Allows transaction updates to acquire exclusive locks, preventing other transactions from acquiring the same set of shared read locks and exclusive write locks.

In addition, InnoDB has two types of Intention Locks for internal use, both of which are table Locks, in order to allow row and table Locks to coexist and achieve multi-grained locking. 3. Intended Shared lock (IS) : a transaction intends to share a lock on a row. A transaction must acquire the IS lock on that table before assigning a shared lock to a row. 4. Intentional exclusive lock (IX) : a transaction that intends to lock rows exclusively must acquire an IX lock on the table before it can lock rows exclusively.

2.InnoDB row lock implementation:

InnoDB rows are locked by index entries. InnoDB uses row locks only when index conditions are used to retrieve data. Otherwise, InnoDB uses table locks. This is especially important in real development, as it can lead to a large number of lock conflicts, which can affect concurrency performance.

1. Clearance lock:

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entry of existing data that meets the condition. For records whose Key values are within the condition range but do not exist, called gaps, InnoDB also locks this GAP. This locking mechanism is called next-key locking.

For example, if there are only 101 records in the emp table, the empid values are 1,2… SQL > alter table SQL > alter table SQL

SELECT * FROM emp WHERE empid > 100 FOR UPDATE
Copy the code

InnoDB locks not only qualified records with an EMPID value of 101, but also “gaps” where empID is greater than 101 (these records do not exist).

InnoDB uses a gap lock to prevent phantom reads to meet the isolation level requirements. In the example above, if a gap lock is not used, if another transaction inserts any record with empID greater than 100, then the transaction will execute the above statement again, phantom reads will occur. On the other hand, it is to meet its recovery and replication needs.

Apparently, InnoDB’s locking mechanism blocks concurrent insertions of key values within the qualifying range when using range criteria to retrieve and lock records, often resulting in severe lock waits. Therefore, in practical development, especially for applications with a lot of concurrent inserts, we should optimize the business logic as much as possible and use equality conditions to access updated data as much as possible, avoiding scope conditions.

2. Key lock:

Next-key can be understood as a special kind of gap lock, or as a special algorithm. The illusion problem can be solved by temporary lock. A key lock exists on a non-unique index column of each data row. When a transaction holds a key lock on that data row, it will lock the data in a range of open and closed data. It is important to note that InnoDB row-level locking is index-based. Temporary locks are only associated with non-unique index columns. There are no temporary locks on unique index columns (including primary key columns).

3. Record lock:

A record lock locks a certain record in a table. The condition for a record lock must be an accurate hit index and the index must be a unique index, such as the primary key ID. Just like the SQL statement diagram used to describe the row lock above, it is quite applicable here. SELECT * FROM table WHERE id = 1 FOR UPDATE;

4. Page lock:

The overhead and locking time are between table and row locks. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average

5. Deadlock situations:

MyISAM table locks are deadlock free because MyISAM always acquires all the locks it needs at once, either satisfied all or waiting, so no deadlocks occur.

However, in InnoDB, except for transactions consisting of a single SQL, locks are acquired gradually, which makes it possible for InnoDB to deadlock.

When a deadlock occurs, InnoDB can generally detect it and cause one thing to release the lock and return, while the other one acquires the lock and completes the transaction. However, InnoDB does not automatically detect deadlocks when external locks are involved, or when table locks are involved, by setting the lock wait timeout parameter

Innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve deadlock problems. In high concurrent access situations, if a large number of transactions are suspended because the required locks cannot be acquired immediately, it can take up a lot of computer resources, causing serious performance problems and even bringing down the database. This can be avoided by setting an appropriate lock wait timeout threshold.

1. In the application, if different programs concurrently access multiple tables, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlock. If two sessions access two tables in different order, the chance of deadlock is very high! But deadlocks can be avoided if they are accessed in the same order.
2. When a program processes data in batches, the possibility of deadlocks can also be greatly reduced if the data is sorted beforehand to ensure that each thread processes records in the same order.
3. In a transaction, if you want to update records, you should apply for a sufficient level of lock directly, that is, an exclusive lock, rather than applying for a shared lock first and then applying for an exclusive lock during the update.
4. At the REPEATEABLE-READ isolation level, if two threads use SELECT… ROR UPDATE adds an exclusive lock, and both threads will be locked if the record does not match. The program finds that the record does not yet exist and tries to insert a new record. If both threads do this, a deadlock occurs. In this case, changing the isolation level to READ COMMITTED can avoid the problem.
5. When isolation level is READ COMMITED, if both threads execute SELECT first… FOR UPDATE, determines whether a record exists that matches the condition, and if not, inserts the record. At this point, only one thread can insert successfully, and another thread will wait for the lock. When the first thread commits, the second thread will have an error due to the primary key, but even though this thread failed, it will get an exclusive lock! A deadlock also occurs if a third thread requests an exclusive lock. In this case, you can simply insert and catch the primary key retry exception later, or always ROLLBACK to release the acquired exclusive lock when a primary key retry error is encountered.

If a deadlock occurs, you can use the SHOW INNODB STATUS command to determine the cause of the last deadlock and take corrective action.

3. Lock classification: