0

I had problems using

CREATE TEMPORARY TABLE tmp AS (SELECT * FROM mytable);

because I got very much deadlocks. Now I use:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE TEMPORARY TABLE tmp AS (SELECT * FROM mytable);

which produces no deadlocks so far, but I want to know if I can use it this way or if I have to commit this:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE TEMPORARY TABLE tmp AS (SELECT * FROM mytable);
COMMIT;

I saw some code with COMMIT at the end and code without COMMIT so I'm not sure if SET SESSION TRANSACTION also starts a transaction or not.

Werner
  • 1,695
  • 3
  • 21
  • 42

1 Answers1

0

The use DDL statements implies an implicit autocomit eg: your 

CREATE TEMPORARY TABLE tmp AS (SELECT * FROM mytable);

see https://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html for more

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks for your reply. Sorry, I don't get it: I have AUTO_COMMIT=1, but as soon as I use START TRANSACTION, the autocommit is disabled till ROLLBACK or COMMIT. The Q is if SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; starts an transaction as START TRANSACTION does, because I found code ending an SET SESSION TRANSACTION with COMMIT (see https://stackoverflow.com/questions/33926532/get-uncommitted-data-in-mysql for example). – Werner Apr 18 '18 at 15:48
  • The use of `create table ..` involves commit . – ScaisEdge Apr 18 '18 at 16:19