[MDEV-10719] 'create temporary table as select' generates unnecessary table locks Created: 2016-09-01  Updated: 2023-04-11

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Temporary, Locking
Affects Version/s: 10.1.17
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Matthew Dean (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File test.sql    
Issue Links:
Relates
relates to MDEV-5879 MySQL WL#5576 Prohibit CREATE TABLE .... Open
relates to MDEV-7660 MySQL WL#6671 "Improve scalability by... Closed
relates to MDEV-24813 Locking full table scan fails to use ... Confirmed
Sprint: 10.1.18

 Description   

A 'create temporary table as select' statement seems to produce an unnecessary table lock (which applies across all connections) against the table identifier. This can cause deadlocks in a simple case and in the more complex case (where the select is marked 'for update') can produce a ER_LOCK_WAIT_TIMEOUT error. Attached is a sql script to set up the database for reproduction. Call the procedure `sp_test` from two connections to see the error.

Breaking the statement into separate 'create' and 'insert' statements produces the expected locking behaviour whereby neither thread is held up.

Tested against mariadb 10.1.17 on ubuntu 16.04.1 LTS.

 Comments   
Comment by Elena Stepanova [ 2016-09-01 ]

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.

Comment by Matthew Dean (Inactive) [ 2016-09-02 ]

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.

Comment by Sergey Vojtovich [ 2016-11-01 ]

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;

Comment by Marko Mäkelä [ 2023-04-11 ]

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.

Generated at Thu Feb 08 07:44:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.