Mysql lock mechanism

• Table Read Lock: does not block other users’ Read requests to the same Table, but blocks all write requests to the same Table.
• Table Write Lock: blocks other users’ read and Write operations on the same Table.

The MyISAM table is serial between read and write operations and between write operations. When a thread acquires 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.

By default, write locks have a higher priority than read locks: when a lock is released, the lock is given priority to the lock acquisition requests waiting in the write lock queue, and then to the lock acquisition requests waiting in the read lock queue.

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. At the same time, some needs a long-running query operation, also can make writing “starve” thread, applications should try to avoid long-running query operations (in the middle of the possible by using tables and other measures of SQL statements to do certain “decomposition”, make every step of the query can be in a relatively short time to complete, 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).

The read lock and write lock priority can be changed:

• 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.
• Set max_write_lock_count to an appropriate value. When a read lock reaches this value, MySQL temporarily lowers the priority of write requests to give the read process a chance to acquire the lock.

MyISAM will automatically lock the tables involved before the SELECT statement is executed, and will automatically lock the tables involved before the UPDATE operation (UPDATE, DELETE, INSERT, etc.). This process does not require user intervention. You do not need to use the LOCK TABLE command to explicitly LOCK the MyISAM TABLE.

In the case of automatic locking, MyISAM always obtains all the locks required by the SQL statement at once, which is why no Deadlock Free occurs in MyISAM tables.

MyISAM storage engine supports concurrent inserts to reduce contention between read and write operations on a given table:

If the MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix and concurrent use of INSERT and SELECT statements from MyISAM tables without locking — you can INSERT rows into MyISAM tables while other threads are reading. Free blocks in the middle of a file may result from deleted or updated rows in the middle of a table. Concurrent inserts are disabled if there is idle speed in the middle of the file, but it is automatically re-enabled when all free blocks are filled with new data. To control this behavior, you can use MySQL’s concurrent_INSERT system variable.

If you explicitly acquire a table LOCK using LOCK TABLES, you can request a READ LOCAL LOCK instead of a READ LOCK so that concurrent inserts can be used by other sessions while the table is locked.

• When conCURRENT_INSERT is set to 0, concurrent inserts are not allowed.
• When conCURRENT_INSERT is set to 1, MyISAM allows the table to be read in one thread if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table)

At the same time, another thread inserts records from the tail of the table. This is also the default setting for MySQL.

• When conCURRENT_INSERT is set to 2, concurrent insertion of records at the end of the MyISAM table is allowed regardless of whether there is a void in the table.

The contention of table lock in the system can be analyzed by checking the state variables of TABLE_LOCKS_waited and TABLE_locks_IMMEDIATE. If the value of Table_LOCKs_WAITED is high, it indicates that there is serious table-level lock contention:

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
Copy the code

InnoDB implements two types of row locking:

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

InnoDB also has two types of Intention Locks for internal use, both of which are table Locks:

• Intended shared lock (IS) : a transaction that intends to assign a shared lock to a row must acquire an IS lock on that table before assigning a shared lock to a row.
• Intentional exclusive lock (IX) : a transaction that intends to lock a row exclusively must acquire an IX lock on the table before it can lock a row exclusively.

Compatibility of lock mode:

InnoDB grants the requested lock to a transaction if its lock mode is compatible with the current one; Otherwise, if the two are incompatible, the transaction waits for the lock to be released.

• Intent locks are automatically added by InnoDB without user intervention.
• For UPDATE, DELETE, and INSERT statements, InnoDB automatically assigns an exclusive lock (X) to the data set involved;
• InnoDB does not place any locks on normal SELECT statements;

A transaction can explicitly assign a shared or exclusive lock to a recordset by:

• SELECT * FROM table_name WHERE… LOCK IN SHARE MODE. Other sessions can still query records, and can also add a share mode lock to the record. However, if the current transaction needs to update the record, a deadlock is likely.
• SELECT * FROM table_name WHERE table_name = “SELECT * FROM table_name WHERE table_name =” FOR UPDATE. Other sessions can query the record, but cannot apply a shared or exclusive lock to the record. Instead, they wait to acquire the lock

InnoDB uses a two-phase lock protocol during transaction execution:

Locks can be performed at any time and InnoDB automatically locks when needed depending on the isolation level;

Locks are released only when commit or ROLLBACK is performed, and all locks are released at the same time.

select ... Lock in share mode select... For update // Exclusive lockCopy the code

**select for update: **

When the SELECT query is executed, the corresponding index access item is locked (X lock), which means that the lock corresponding to this statement is equivalent to the effect of the update.

The select *** for update clause is used to ensure that the data queried is the latest and that only you can modify the data.

** Select lock in share mode: **

The in Share mode clause adds a share lock to the found data, indicating that other transactions can only perform simple select operations on the data, but not DML operations.

Select *** lock in share mode to ensure that the data is not being modified by other transactions, that is, ensure that the data is the latest, and do not allow others to modify the data. However, you may not be able to modify the data, because other transactions may also lock the data in share mode.

Select for UPDATE statement, equivalent to an UPDATE statement. In busy situations, if transactions are not committed or rolled back in time, other transactions may wait for a long time, affecting the concurrency efficiency of the database.

The Select Lock in Share mode statement is a feature that places a shared lock (S lock) on the searched data. It allows other transactions to also lock S on the data, but does not allow modification of the data. If you do not commit or rollback in time, a large number of transactions may be waiting.

** For update and lock in share mode

Once a transaction acquires this lock, no other transaction can perform a for update on the data. The latter is a shared lock. Multiple transactions can execute a lock in share mode on the same data at the same time.

InnoDB row lock implementation: **

• InnoDB locks rows by locking index entries, unlike Oracle, which locks rows in data blocks. InnoDB’s row-locking implementation means that InnoDB uses row-locking only when data is retrieved by index criteria. Otherwise, InnoDB uses table locking!

• InnoDB uses row locks to lock data whether using a primary key index, a unique index, or a normal index.
• Row locks can only be used if the execution plan actually uses an index: Even if index fields are used in conditions, it is up to MySQL to decide whether to use indexes to retrieve data by determining the cost of different execution plans. If MySQL decides that a full table scan is more efficient, such as for some small tables, it will not use indexes. In this case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the execution plan of the SQL (you can check the execution plan of the SQL by Explain) to see if the index is actually being used.

• MySQL locks rows on indexes, not records, so if multiple sessions use the same index key, lock conflicts may occur. To get the lock). Keep this in mind when designing applications.

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet 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.

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 application development, especially for applications with a large number of concurrent inserts, we should try to optimize business logic, try to use equality conditions to access updated data, and avoid using scope conditions.

• Prevent phantoms to meet the requirements of the relevant isolation level;
• Meet the needs of recovery and replication:

MySQL uses BINLOG to input SQL statements that UPDATE data successfully, such as INSERT, UPDATE, and DELETE, and thus restore MySQL databases and replicate primary and secondary data. The MySQL recovery mechanism (replication is a continuous binlog-based recovery performed by Slave MySQL) has the following features:

1. MySQL restore is SQL statement level, that is, re-execute the SQL statement in BINLOG.
2. MySQL’s Binlog is recorded in the order in which transactions were committed, and recovery is performed in that order.

Thus, MySQL’s recovery mechanism requires that, before a transaction is committed, other concurrent transactions cannot insert any records that meet their locking conditions, that is, phantom reads are not allowed.

Get InnoDB row lock contention:

You can analyze row lock contention on your system by examining the InnoDB_row_lock state variable:

mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | InnoDB_row_lock_current_waits | | 0 / / the number of current are waiting for the lock; | | InnoDB_row_lock_time | 0 / / since system startup locking the total time length; | | InnoDB_row_lock_time_avg | 0 / / every time waiting for the average time spent; | | InnoDB_row_lock_time_max | 0 / / from the system start to now wait for the time it takes the most often time; | | InnoDB_row_lock_waits | 0 / / after system start to now waiting for the number of times a total; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 5 rows in the set (0.01 SEC)Copy the code

**LOCK TABLES and UNLOCK TABLES: **

Mysql also supports Lock tables and unlock tables, which are implemented at the Server layer (Mysql Server layer) and are independent of the storage engine. They serve their own purpose and do not replace transactions. (Not recommended except when Autocommint is disabled) :

• LOCK TABLES locks the TABLES used by the current thread. If the table is locked by another thread, the current thread waits until it can obtain all the locks.
• UNLOCK TABLES releases any locks acquired by the current thread. When the current thread executes another LOCK TABLES, or when the connection to the server is closed, all TABLES locked by the current thread are implicitly unlocked

**LOCK TABLES syntax: **

• MySQL will not LOCK InnoDB TABLES if AUTOCOMMIT is set to 0.
• Do not release the table lock with UNLOCK TABLES before the transaction ends, because UNLOCK TABLES implicitly commits the transaction;
• COMMIT or ROLLBACK cannot release table locks that have been added using LOCK TABLES. UNLOCK TABLES must be used to release table locks.

The correct way to do this is as follows:

For example, if you wanted to write table T1 and read from table T, you could do as follows:

SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;Copy the code

** Scenarios using LOCK TABLES: **

Table locking (both InnoDB tables and MyISAM) is generally used to simulate transaction operations to a certain extent, so that multiple tables can be read consistently at one point in time. (Similar to MyISAM’s default table lock behavior)

When you explicitly LOCK a table with LOCK TABLES, you must acquire all the locks involved in the table at the same time, and MySQL does not support LOCK escalation. That is, after executing LOCK TABLES, only explicitly locked TABLES can be accessed, not unlocked TABLES. Also, if a read lock is added, only queries can be performed, not updates.

In fact, in the case of MyISAM automatic locks (table locks), MyISAM always obtains all the locks needed for SQL statements at once, which is why no Deadlock Free will occur in MyISAM tables.

For example, there is an order table, Orders, which records the total amount of each order, and an order detail table, order_detail, which records the subtotal amount of each product of each order. Suppose we need to check whether the total amount of the two tables is consistent. You may need to execute the following two SQL statements:

Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Copy the code

At this point, if you do not lock both tables first, you may get wrong results because the ORDER_detail table may have changed during the execution of the first statement.

Therefore, the correct approach should be:

Lock tables orders read local, order_detail read local; 
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables;
Copy the code

LOCK TABLES — added the “local” option to LOCK TABLES. This option allows you to hold a read LOCK on a table and allow other users to insert TABLES concurrently from the end of the table, as long as the MyISAM storage engine supports concurrent inserts.

• Deadlock generation:

1. A deadlock is a vicious cycle in which two or more transactions occupy the same resource and request to lock the resource occupied by the other.
2. Deadlocks can occur when a transaction tries to lock resources in a different order. Deadlocks can also occur when multiple transactions simultaneously lock the same resource.
3. The behavior and order of locks is dependent on the storage engine. By executing statements in the same order, some storage engines generate deadlocks and some do not — deadlocks have two causes: true data conflicts; Storage engine implementation.

• Deadlock detection: various deadlock detection and deadlock timeout mechanisms are implemented in database systems. InnoDB storage engine can detect deadlocked loop dependencies and immediately return an error.
• Deadlock recovery: After a deadlock occurs, a deadlock can only be broken by partially or completely rolling back one of the transactions. InnoDB currently handles deadlocks by rolling back the transactions that hold the least row-level exclusive locks. So transactional applications must be designed with deadlocks in mind, and in most cases simply need to re-execute transactions rolled back by deadlocks.
• Deadlock detection of external locks: InnoDB can automatically detect the occurrence of a deadlock, and cause one transaction to release the lock and fall back, and another transaction to acquire the lock and continue to complete the transaction. InnoDB does not automatically detect deadlocks when an external lock is involved, or when a table lock is involved. Innodb_lock_wait_timeout is used to solve this problem
• Deadlocks affect performance: Deadlocks affect performance rather than causing serious errors, because InnoDB automatically detects deadlock conditions and rolls back one of the affected transactions. On high-concurrency systems, deadlock detection can cause slow down when many threads are waiting for the same lock. Sometimes when a deadlock occurs, it may be more effective to disable deadlock detection (using the Innodb_deadlock_detect configuration option), in which case you can rely on the Innodb_LOCK_WAIT_TIMEOUT setting for transaction rollback.

**MyISAM avoids deadlocks: **

• In the case of automatic locking, MyISAM always obtains all the locks required by the SQL statement at once, so MyISAM tables do not deadlock.

InnoDB avoids deadlocks: **

• To avoid deadlocks when performing multiple concurrent writes on a single InnoDB table, you can start a transaction by using SELECT… The FOR UPDATE statement to obtain the necessary locks, even if the row change statement is executed later.
• In a transaction, if you want to update records, you should directly apply for a lock of sufficient level, that is, exclusive lock, rather than apply for a shared lock first and then apply for an exclusive lock during the update. At this time, when users apply for an exclusive lock again, other transactions may have already obtained the shared lock of the same record, resulting in lock conflict or even deadlock
• If a transaction needs to modify or lock more than one table, lock statements should be used in the same order in each transaction. In an 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
• Through the SELECT… LOCK IN SHARE MODE A read LOCK for a row may cause a deadlock if the current transaction needs to update the record.
• Change transaction isolation level: If a deadlock occurs, use the SHOW INNODB STATUS command to determine the cause of the last deadlock. The return results include details about deadlock-related transactions, such as the SQL statement that caused the deadlock, the locks that the transaction has acquired, what locks it is waiting for, and the transactions that were rolled back. Based on this, we can analyze the cause of deadlock and improve measures.

• Use low isolation levels whenever possible;
• Carefully design indexes and use indexes to access data as much as possible to make locking more precise and thus reduce the chance of lock conflicts
• By choosing a reasonable transaction size, small transactions are less likely to have lock conflicts
• When locking a recordset display, it is best to request a sufficient level of locking at once. For example, if you want to modify data, it is better to apply for the exclusive lock directly rather than apply for the shared lock first, and then apply for the exclusive lock during the modification, which is prone to deadlock

• When different programs access a set of tables, they should try to agree to access the tables in the same order. For a table, they should try to access the rows in the table in a fixed order. This greatly reduces the chance of deadlocks

• Try to access data with equality conditions to avoid the impact of gap locks on concurrent inserts
• Do not apply for more locks than you actually need
• Do not show locks when querying unless necessary. MySQL MVCC can realize transaction query without locking, optimize transaction performance; MVCC only works at COMMITTED READ and REPEATABLE READ isolation levels

• For certain transactions, table locks can be used to speed up processing or reduce the likelihood of deadlocks