Mysql optimization locks

(1) to obtain the lock wait condition, you can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables :; + -------------------------- + ---------- + | Variable_name | Value | + ---------------------------- + ---------- + | Table_locks_imme

(1) to obtain the lock wait condition, you can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables :; + -------------------------- + ---------- + | Variable_name | Value | + ---------------------------- + ---------- + | Table_locks_imme

(1) to obtain the lock wait condition, you can analyze the table lock contention on the system by checking the table_locks_waited and table_locks_immediate status variables :; + rows + ---------- + | Variable_name | Value | + ---------------------------- + ---------- + | Table_locks_immediate | 105 | Table_locks_waited | 3 | + rows + ---------- + 2 rows in set (0.00 sec)
You can check the Innodb_row_lock status variable to analyze the contention for row locks on the system :; + bytes + ---------- + | Variable_name | Value | + bytes + ---------- + | bytes | 0 | Innodb_row_lock_time | 2001 | bytes | 667 | Innodb_row_lock_time_max | 845 | Innodb_row_lock_waits | 3 + -------------------------------------- + ---------- + 5 rows in set (0.00 sec)
In addition, for Innodb tables, if you need to view the current lock Wait status, you can set InnoDB Monitors and then view it through Show innodb status. The setting method is as follows: create table innodb_monitor (a INT) ENGINE = INNODB; the monitor can be stopped by issuing the following statement: drop table innodb_monitor; after setting the monitor, in the display content of show innodb status, there will be detailed information about the current lock wait, including the table name, lock type, lock record, and so on, so as to facilitate further analysis and problem determination. After the monitor is enabled, the monitored content is recorded in the log every 15 seconds by default. the err file becomes very large, so after you confirm the cause of the problem, remember to delete the monitoring table to turn off the monitor. You can also use the -- console option to start the server to close the log file writing.

(2) When table locks are used
Table-level locks are superior to row-level locks in the following situations:
1. Many operations are read tables.
2. Read and update indexes with strict conditions. When updating or deleting indexes, you can use a separate index to read them:
3. UPDATE tbl_name SET column = value WHERE unique_key_col = key_value;
4. delete from tbl_name WHERE unique_key_col = key_value;
5. SELECT and INSERT statements are executed concurrently, but there are only a few UPDATE and DELETE statements.
6. Many scan tables and group by operations on the entire table, but there is no write table.

(3) When to use the row lock
Advantages of Row-level locking:
1. There are only a few lock conflicts when different rows are accessed in many threads.
2. Only a few changes are allowed during rollback.
3. You can lock a single row for a long time.
Disadvantages of Row-level locking:
1. More memory is occupied than page-level or table-level locking.
2. When used in most tables, it is slower than page-level or table-level locking because you must obtain more locks.
3. If you often perform group by operations on most data or scan the entire table frequently, it is much slower than other locks.
4. Use high-level locking. By supporting different types of locking, you can easily adjust the application because the lock cost is lower than the row-level locking.

(4) insert... select...
When insert... select... during record insertion, if the select Table is of the innodb type, the records of the select table will be locked no matter what type of table is inserted. For those applications migrated from oracle, pay special attention to the fact that oracle does not have similar problems, so insert... select... operations are very common. For example, you can perform statistical analysis on a large number of records and insert the intermediate results of the statistics to another table, therefore, the corresponding index may not be set. If no adjustment is made after the database is migrated to mysql, during this operation, all records are locked due to the fact that the table to be selected is a full table scan, it will have a very serious impact on other operations of the application. The main reason is that mysql has a different replication mechanism than oracle. If the select table is not locked, the insert result set may be different from the database during restoration, this causes inconsistency between the master and slave data. If you do not use master-slave replication, disabling binlog does not prevent lock on the select record. In some documents, innodb_locks_unsafe_for_binlog can be set to avoid this phenomenon. When this parameter is set to true, the select result set will not be locked, but such settings may bring very serious risks. If you use this binlog to recover data from the database or perform disaster recovery for the primary database, the performance may be different from that of the primary database. Therefore, we do not recommend setting this parameter to avoid insert... select... the resulting lock. If necessary, the insert statements that may scan a large amount of data... select operation. We recommend that you use select... the combination of into outfile and load data infile will not lock the record.

(5) Impact of next-key lock on concurrent Inserts
In row-level locking, InnoDB uses an algorithm named next-key locking. InnoDB performs row-level locking in this way: When it searches for or scans the index of a table, it sets a shared or exclusive lock for the index records it encounters. Therefore, row-level locking is actually an index record locking. InnoDB's locking of index records also reflects the "gap" before index records ". If a user shares or exclusively locks the record R on an index, another user cannot insert a new index record in the index order immediately before the R. The gap lock is executed to prevent the so-called "ghost problem ". You can use the next-key lock to implement a unique check on your application: If you read data in Shared Mode and do not see the duplicate rows you want to insert, then you can insert your row safely and know that the next-key lock set for your row's successor during the reading process prevents anyone from inserting a duplicate row to your row. Therefore, the next-key lock allows you to lock something that does not exist in your table.

(6) Impact of isolation level on concurrent Inserts
Repeatable read is the default isolation level of InnoDB. SELECT... for update, SELECT... lock in share mode, the UPDATE and DELETE statements only LOCK the index records found, but do not LOCK the gap before the record. Use other search conditions. These operations use the next-key lock, the next-key lock, or the gap lock to lock the index range of the search, and prevent other users from inserting new data. In continuous READ, there is an important difference from the read committed isolation level: at this level, all the continuous READ reads within the same transaction are determined by the same snapshot for the first READ. This Convention means that if you issue several non-formatted SELECT statements in the same transaction, these SELECT statements are also consistent with each other. Read committed isolation level is an Oracle isolation level. All SELECT... for update and SELECT... the lock in share mod statement only locks the index record, but does not LOCK the gap before the record. Therefore, you can insert a new record next to the locked record at will. The UPDATE and DELETE statements use a unique index with a unique search condition to lock only the found index records, excluding the gap before the record. In the range UPDATE and DELETE statements, InnoDB must set the next-key lock or gap lock for the gap covered by the range and block insertion by other users. This is necessary because to make MySQL replication and recovery take effect, the "ghost line" must be blocked. If an application is migrated from an ORACLE-based application to a MYSQL database, we recommend that you use this isolation level to provide database services, because this isolation level is closest to the default isolation level of ORACLE, the locks that may be encountered during migration are minimized.

(7) How to Reduce lock conflicts
1. For tables of the Myisam type:
1) Myisam tables can be changed to Innodb tables to reduce lock conflicts.
2) try to split the table horizontally or change it to the Myisam partition based on the application situation, which will also help to reduce lock conflicts.
2. Innodb tables:
1) first, make sure that you try to use the index to retrieve records when getting row locks from a table. If index access is not used, even if you only want to update a row of records, it is also locked for the entire table. Make sure that the SQL statement uses indexes to access records. When necessary, use the explain statement to check the SQL Execution Plan and determine whether the index is used as expected.
2) because mysql's row lock is a lock applied to indexes rather than a record, although it is used to access records of different rows, if it is the same index key, it will be locked. When designing applications, you should also note that this is a big difference from Oracle.
3) when the table has multiple indexes, different transactions can use different indexes to lock different rows. When the table has a primary key or a unique index, it is not necessary to use a primary key or a unique index to lock a record. Other common indexes can also be used to retrieve records and only lock rows that meet the conditions.
4) use show innodb status to determine the cause of the last deadlock. The query results contain detailed information about deadlocked transactions, including the content of the executed SQL statement, what locks have been obtained by each thread, and what locks are waiting, and which thread is finally rolled back. Detailed analysis of the cause of the deadlock, you can improve the program to effectively avoid the occurrence of the deadlock.
5) If the application does not mind the occurrence of deadlocks, You can process the deadlocks found in the application.
6) determine a more reasonable Transaction size, and smaller transactions tend to be less conflicted.
7) if you are using locked READ (SELECT... for update or... lock in share mode), try to use a lower isolation level, such as read committed.
8) access your tables and rows in a fixed order. Transactions form well-defined queries without deadlocks.