|
Table lock
The table lock in InnoDB contains five locking modes: LOCK_IS, LOCK_IX, LOCK_X, LOCK_S and LOCK_AUTO_INC. The compatibility between the locks complies with the definitions in the lock_compatibility_matrix array. InnoDB table locks aim to prevent the DDL and DML concurrency issues. Since the introduction of MDL lock in V5.5, the table locks of InnoDB are not that significant any more, as the MDL lock has covered most of their functions. Next we will introduce several table lock types in InnoDB. LOCK_IS/LOCK_IX This is also the so-called intention lock. You can understand it as an “implication” about the required type of row locks in future. The “IS” indicates that a share lock may be needed on some records in this table, and “IX” indicates that an exclusive lock may be needed on some records in this table. The intention lock is of the table level. The IS and IX locks are not in conflict with each other, but they are in conflict with table-level S/X locks. When applying an S lock or an X lock to the record, you must ensure that a corresponding intention lock, or another table lock with higher locking strength, exists in the same table. LOCK_X When the LOCK_X table lock is applied, all the requests with other table locks need to wait. There are usually several cases requiring the X lock, as follows: • In the last phase of the DDL operation (ha_innobase::commit_inlace_alter_table), apply the LOCK_X lock to the table to ensure that no other transactions hold the table lock. In general, the MDL lock at the server layer can achieve the same purpose, as exclusive MDL locks are applied in the commit phase of the DDL operation. But foreign key checks or some undergoing operations of a transaction that has just recovered from collapse, among others, are self-governed by InnoDB and they do not go through the server layer, thus not under the protection of the MDL lock. • When you set the session autocommit variable to OFF, execution of operations similar to LOCK TABLE tbname WRITE will apply the table-level LOCK_X lock. (ha_innobase::external_lock) • To execute the discard or import operations on a tablespace, the LOCK_X (ha_innobase::discard_or_import_tablespace) should be applied. LOCK_S • In the first phase of a DDL operation, if the current DDL operation cannot be executed through the ONLINE approach, you should apply the LOCK_S lock to the table (prepare_inplace_alter_table_dict). • When the session autocommit is set to OFF, execution of LOCK TABLE tbname READ will apply the LOCK_S lock (ha_innobase::external_lock). From the descriptions above we can see that LOCK_X and LOCK_S locks are seldom seen in most of the load scenarios. The LOCK_IS and LOCK_IX locks take a dominant place and they do not conflict with each other. An interesting question is: every time when we apply a table lock, we always need to scan all the table lock objects in the table and check for conflicted locks. Apparently, if we have a high update concurrency on the same table, this chain table will be very long. It is a fact that most table locks do not conflict with each other. Based on this, we count the number of various table lock objects in RDS MYSQL. During the check for conflicts, for example, an intention lock is currently applied for, if the count of LOCK_S and LOCK_X locks are both 0, it is deemed that no conflicts exist and the conflict check will be skipped. Since the check is performed while holding the global lock lock_sys->mutex, this optimization enjoys notable effects for a single table with high concurrency. It also reduces the duration for holding a global lock. LOCK_AUTO_INC The AUTO_INC lock applies to the table level and is not compatible with the AUTO_INC or table-level S and X locks. The locked range covers the SQL statement level. The lock is released upon the completion of the SQL statement. The locking logic of AUTO_INC is associated with the locking modes in InnoDB. Here is a brief introduction. In general, for auto increment columns, we can specify the value explicitly, or apply NULL directly and the system will apply auto increment for the column and fill the values in. We can also use the two methods in combination during batch inserts. The specific behavior of different assignment methods is subject to the influence of the innodb_autoinc_lock_mode parameter. But the statement-based copy may also influence the copied data consistency. The Official Documents provide detailed descriptions and I will not repeat it here. I will just explain the influence of the lock. The auto increment locking mode is controlled through the parameter innodb_autoinc_lock_mode. For the lock selection, see the ha_innobase::innobase_lock_autoinc function. Specifically, there are several values as follows: AUTOINC_OLD_STYLE_LOCKING（0） This is also the so-called old style locking mode (the policy before V5.1 introduced this parameter). In this policy, the AUTO_INC lock will be applied before the assignment and be released upon completion of the SQL statement. This mode ensures the consistency for the standby databases to execute statements similar to INSERT...SELECT in the statement-based copy mode. Because such statements cannot identify the number of records during execution, we can only ensure the consistency between the master and standby databases by disallowing other sessions to assign auto increment values during the execution. Apparently, this locking mode impairs the concurrent inserts performance a lot, but ensures the continuity of auto increment values assignment in an SQL statement. AUTOINC_NEW_STYLE_LOCKING（1） This is the default value of InnoDB. In this locking mode • A general INSERT or REPLACE operation will first add a dict_table_t::autoinc_mutex lock, and then judge whether other threads have applied any LOCK_AUTO_INC locks on the table. If yes, the autoinc_mutex lock is released, and the old style locking mode will be used. Otherwise, the autoinc_mutex lock will be quickly released after the auto increment values required by this INSERT operation are reserved. It is obvious that general concurrent INSERT operations do not require the LOCK_AUTO_INC lock. This has greatly enhanced the throughput. • But for some batch INSERT operations, such as LOAD DATA, INSERT...SELECT, the old style locking mode is used and the LOCK_AUTO_INC lock is applied during the SQL statement execution. Compared with the tradition mode, this locking mode also ensures the copy security in the statement-based mode, but it fails to ensure the continuity of auto increment values in an INSERT statement. In addition, when executing an INSERT statement with both explicitly-specified auto increment values and system-assigned values, some auto increment values may be wasted. For example, execute the SQL statement: INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,’d’） Suppose the current AUTO_INCREMENT value is 101. After the execution is completed in the old style mode, the next auto increment value is 103. But in the new style mode, the next available auto increment value is 105. Because when the SQL statement execution starts, four auto increment values of [101, 104] will be retrieved, which matches the number of inserted rows. Then the AUTO_INCREMENT value is assigned to 105, causing the waste of auto increment values of 103 and 104. AUTOINC_NO_LOCKING（2） In this mode, you only need to apply a mutex lock at value assignment and it will be quickly released. It won't regress to the old style mode as it does in the new style locking mode in some scenarios. Therefore, the copy security for batch inserts cannot be ensured if the value is set to 2. A small bug in auto increment locks This is a small bug reported in Mariadb Jira. In the row mode, the parse logic is not used, so we don't know whether the row records are generated by batch imports or general INSERT operations. As a result, the command type is SQLCOM_END. While we are judging whether to add the auto increment locking logic, we judge whether to ignore AUTO_INC locking based on the command type, namely SQLCOM_INSERT or SQLCOM_REPLACE. Because of this additional lock overhead, InnoDB will always add the AUTO_INC lock in the row mode. While the AUTO_INC locking also involves the overhead of global transaction resources, leading to lower performance. The fix is also simple. You need to take the SQLCOM_END command type into account. |
|
