Elena Stepanova added a comment - 2016-09-01 21:12 I don't immediately see which locks in the scenario can be superficial: MariaDB [test]> select * from information_schema.metadata_lock_info; + -----------+-------------------------+---------------+---------------------+--------------+------------+ | THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME | + -----------+-------------------------+---------------+---------------------+--------------+------------+ | 10 | MDL_INTENTION_EXCLUSIVE | NULL | Global read lock | | | | 12 | MDL_SHARED_WRITE | NULL | Table metadata lock | test | tbl | | 10 | MDL_SHARED_WRITE | NULL | Table metadata lock | test | tbl | + -----------+-------------------------+---------------+---------------------+--------------+------------+ 3 rows in set (0.00 sec) MariaDB [test]> show full processlist; + ----+------+-----------------+------+---------+------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | + ----+------+-----------------+------+---------+------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------+ | 9 | root | localhost:52926 | test | Query | 0 | init | show full processlist | 0.000 | | 10 | root | localhost:53029 | test | Query | 8 | Sending data | CREATE TEMPORARY TABLE `tmp` ( `id` INT UNSIGNED NOT NULL ) Engine=innodb AS   SELECT `tbl`.`id` FROM `tbl` FOR UPDATE | 0.000 | | 12 | root | localhost:53184 | test | Query | 4 | Waiting for table level lock | SELECT `tbl`.`id` FROM `tmp` INNER JOIN `tbl` ON `tmp`.`id` = `tbl`.`id` | 0.000 | + ----+------+-----------------+------+---------+------+------------------------------+----------------------------------------------------------------------------------------------------------------------------------+----------+ But in MySQL 5.6-5.7 the behavior is different, it doesn't allow FOR UPDATE here at all: MySQL [test]> call sp_test(); ERROR 1746 (HY000): Can 't update table ' tbl ' while ' tmp' is being created. I'll leave it to svoj , the locking expert, to decide if there is something to fix here, or maybe inherit MySQL behavior.

Matthew Dean (Inactive) added a comment - 2016-09-02 06:28 For me there are a few possible issues here. Firstly it seems odd to me that a table lock is required on the temporary table as part of the create statement at all. The temporary table is only visible to the current connection so why is the lock required here at all? There may of course be an underlying reason that I'm missing. Secondly there is the fact that it errors with ER_LOCK_WAIT_TIMEOUT when the situation is clearly behaving as a deadlock. If the engine could detect the deadlock in a timely manner then one could simply retry. The current situation means that any thread that might use that temporary table identifier is held up for the lock wait timeout. In my case, as the identifier was used across many procedures, this locked up the entire end-user application. Finally, as mentioned by elenst , there is the inconsistency with mysql which is always undesirable unless it can be clearly justified.

Sergey Vojtovich added a comment - 2016-11-01 11:04 - edited In a provided test case stronger lock is acquired for persistent table (tbl), not temporary (tmp). This is because InnoDB internally relies on command type (which is SQLCOM_CREATE in this case) and requests stronger TL_WRITE instead of expected TL_WRITE_ALLOW_WRITE in this case. E.g.: CREATE TEMPORARY TABLE tmp(a INT) SELECT a FROM t1 FOR UPDATE; # TL_WRITE for t1 SELECT a FROM t1 FOR UPDATE; # TL_WRITE_ALLOW_WRITE for t1 This will be fixed in 10.2 by MDEV-7660 . I leave it up to jplindst to decide if he wants to fix this bug in ha_innobase::store_lock() in earlier versions. Failure on timeout is because this deadlock involves 2 isolated locking subsystems: InnoDB row level locks and server table level locks. These subsystems are not aware of locks being held by each other. Besides server table level locks are not fixable by design: they don't support deadlock detection. Inconsistency with MySQL is subject of MDEV-5879 . Yet I don't understand the value of rejecting table modifications for CREATE ... SELECT. MTR test: --source include/have_innodb.inc   CREATE TABLE t1 ENGINE=InnoDB AS SELECT 1 AS a;   DELIMITER $$;   CREATE PROCEDURE p1() BEGIN START TRANSACTION; CREATE TEMPORARY TABLE tmp(a INT) SELECT a FROM t1 FOR UPDATE; SELECT SLEEP (1); SELECT * FROM t1; COMMIT; DROP TABLE tmp; END $$   DELIMITER ;$$   send CALL p1();   connect(con1,localhost,root,,); CALL p1(); disconnect con1;   connection default; reap; DROP TABLE t1; DROP PROCEDURE p1;

Marko Mäkelä added a comment - 2023-04-11 08:48 MariaDB 10.1 did not support actual temporary tables in InnoDB; all tables were persistent, with full locking. MariaDB 10.2.2 was the first release to treat temporary InnoDB tables differently. However, the problem remains that CREATE TABLE…SELECT as well as INSERT…SELECT acquire locks on individual records of the source table. In a full table scan, it would be much better to just acquire a shared lock on the entire source table. Perhaps we’d also want to have a special mode where these copying operations do not acquire any locks at all, but use repeatable read, just like a normal SELECT would.

Marko Mäkelä added a comment - 2024-03-20 09:05 This still affects current MariaDB Server versions. --source include/have_innodb.inc   CREATE TABLE t1 ENGINE=InnoDB AS SELECT 1 AS a; BEGIN ; SELECT * FROM t1 FOR UPDATE ;   connect (con1,localhost,root,,); CREATE TEMPORARY TABLE tmp(a INT ) SELECT a FROM t1; disconnect con1;   connection default ; DROP TABLE t1; 10.6 b8a671988954870b7db22e20d1a1409fd40f8e3d mysqltest: At line 7: query 'CREATE TEMPORARY TABLE tmp(a INT) SELECT a FROM t1' failed: ER_LOCK_WAIT_TIMEOUT (1205): Lock wait timeout exceeded; try restarting transaction To make it fail faster than in the default innodb_lock_wait_timeout=50 seconds: ./mtr --mysqld=--innodb-lock-wait-timeout=1