Description:
As per the MySQL Documentation "External locking affects only MyISAM table access."
Ref: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_external_...
https://dev.mysql.com/doc/refman/8.0/en/external-locking.html
But we still see external lock for innodb tables,
mysql [localhost] {msandbox} (test) > show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > flush status;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost] {msandbox} (test) > select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > show status like '%exter%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_external_lock | 2 |
+-----------------------+-------+
1 row in set (0.01 sec)
<,engine from information_schema.TABLES group by engine;
+----------+--------------------+
| count(*) | ENGINE |
+----------+--------------------+
| 42 | InnoDB |
| 191 | NULL |
| 112 | PERFORMANCE_SCHEMA |
| 2 | CSV |
+----------+--------------------+
4 rows in set (0.00 sec)
External Lock reference also can be seen in MySQL code for InnoDB tables.
Ref: https://dev.mysql.com/doc/internals/en/external-lock.html
https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/handler/ha_innodb.cc#L9941
MySQL documentation for External-Lock confusing for users as it says it only affects MyISAM tables but looks like that's not the case and external_lock can be used for other storage engines as well like Innodb
How to repeat:
above case
Suggested fix:
Update doc for external locking