If you cancel CTAS Query on MariaDB Server, Rollback is executed without cancellation.

For version 10.2.29 the rollback runs very slowly when CTAS Query is cancelled.

For version 10.2.7, the CTAS Query cancellation does not execute Rollback and cancels.

• Test with version 10.2.7 and version 10.2.29 regarding transaction rollback

  /* View the size of the Transaciton Rollback. */

  SELECT plist.id

  , plist.USER

  , plist.host

  , plist.db

  , plist.command

  , plist.time

  , trx.trx_state

  , trx.trx_started

  , trx.trx_requested_lock_id

  , trx.trx_wait_started

  , trx.trx_weight

  , plist.info

  , trx.trx_query

  FROM information_schema.processlist AS plist

  LEFT JOIN information_schema.innodb_trx AS trx ON plist.id = trx.trx_mysql_thread_id

  WHERE plist.USER = 'jmlee'

  AND trx.trx_weight IS NOT NULL;

  /* Test for INSERT Query */

  START TRANSACTION;

  INSERT INTO temp_jmlee_order

  SELECT *

  FROM table_order

  WHERE order_dt < '2017-01-01';

  ROLLBACK;

  Results)

  Version 10.2.29 / ROWS : 407,082 / INSERT : 17.52s / ROLLBACK : 21.58s

  Version 10.2.07 / ROWS : 407,082 / INSERT : 20.87s / ROLLBACK : 22.12s

  /* Test for UPDATE Query */

  START TRANSACTION;

  UPDATE temp_jmlee_order AS too

  JOIN table_order AS o ON too.order_seq = o.order_seq

  JOIN table_order_item AS oi ON o.order_seq = oi.order_seq

  JOIN table_order_item_box AS oid ON oi.order_item_seq = oid.order_item_seq

  AND too.order_item_seq = oid.order_item_seq

  AND too.box_seq = oid.box_seq

  SET too.device_type_cd = o.device_type_cd

  , too.inbd_date = concat(LEFT(oid.inbd_date, 7), '-01')

  WHERE 1=1;

  ROLLBACK;

  Results)

  Version 10.2.29 / ROWS : 350,723 / UPDATE : 12.36s / ROLLBACK : 08.39s

  Version 10.2.07 / ROWS : 350,723 / UPDATE : 24.99s / ROLLBACK : 08.18s

  /* Test for DELETE Query */

  START TRANSACTION;

  DELETE

  FROM temp_jmlee_order

  WHERE 1=1;

  ROLLBACK;

  Results)

  Version 10.2.29 / ROWS : 407,082 / UPDATE : 10.76s / ROLLBACK : 06.85s

  Version 10.2.07 / ROWS : 407,082 / UPDATE : 20.09s / ROLLBACK : 06.56s

  /* Test for CTAS Query */

  SET @END_DT = CAST('2017-01-01' AS DATE);

  START TRANSACTION;

  INSERT INTO temp_jmlee_order

  SELECT o.order_seq

  , oi.order_item_seq

  , oid.box_seq

  , ifnull(sum(floor(CAST(( IF(p.proc_yn = 'Y', 1, ifnull(CAST(oid.fix_kg AS DECIMAL(10,2)), 0))

  * IF(p.proc_yn = 'N' AND p.large_purchase_gbn = 'Y', 1, ifnull(oid.box_cnt, 0))

  * ( ifnull(IF(oi.group_purchase_gbn = 'N', oi.price_per_kg, ifnull(oi.purchase_price_per_kg, oi.price_per_kg)), 0)

  + ifnull(oi.opt1_price, 0) + ifnull(oi.opt2_price, 0) + ifnull(oi.opt3_price, 0) + ifnull(oi.opt4_price, 0))

  )

  + (ifnull(oid.dist_price + oid.dist_over_region_price, 0) * IF(p.large_purchase_gbn = 'Y', 1, ifnull(oid.box_cnt, 0)))

  + 0.5 AS DECIMAL(15,2)))), 0) AS charge_price

  FROM table_order

  JOIN table_order_item AS oi ON o.order_seq = oi.order_seq

  JOIN table_order_item_box AS oid ON oi.order_item_seq = oid.order_item_seq

  JOIN table_item AS i ON oi.item_seq = i.item_seq

  JOIN table_product AS p ON i.product_seq = p.product_seq

  WHERE o.order_dt < @END_DT

  GROUP BY o.order_seq

  , oi.order_item_seq

  , oid.box_seq;

  ROLLBACK;

  Results)

  Version 10.2.29 / ROWS : 359,271 / INSERT : 47.81s / ROLLBACK : 02.44s

  Version 10.2.07 / ROWS : 359,271 / INSERT : 46.33s / ROLLBACK : 02.43s

  SET @END_DT = CAST('2017-01-01' AS DATE);

  CREATE OR REPLACE TABLE temp_jmlee_order

  AS

  SELECT o.order_seq

  , oi.order_item_seq

  , oid.box_seq

  , ifnull(sum(floor(CAST(( IF(p.proc_yn = 'Y', 1, ifnull(CAST(oid.fix_kg AS DECIMAL(10,2)), 0))

  * IF(p.proc_yn = 'N' AND p.large_purchase_gbn = 'Y', 1, ifnull(oid.box_cnt, 0))

  * ( ifnull(IF(oi.group_purchase_gbn = 'N', oi.price_per_kg, ifnull(oi.purchase_price_per_kg, oi.price_per_kg)), 0)

  + ifnull(oi.opt1_price, 0) + ifnull(oi.opt2_price, 0) + ifnull(oi.opt3_price, 0) + ifnull(oi.opt4_price, 0))

  )

  + (ifnull(oid.dist_price + oid.dist_over_region_price, 0) * IF(p.large_purchase_gbn = 'Y', 1, ifnull(oid.box_cnt, 0)))

  + 0.5 AS DECIMAL(15,2)))), 0) AS charge_price

  FROM table_order

  JOIN table_order_item AS oi ON o.order_seq = oi.order_seq

  JOIN table_order_item_box AS oid ON oi.order_item_seq = oid.order_item_seq

  JOIN table_item AS i ON oi.item_seq = i.item_seq

  JOIN table_product AS p ON i.product_seq = p.product_seq

  WHERE o.order_dt < @END_DT

  GROUP BY o.order_seq

  , oi.order_item_seq

  , oid.box_seq;

  Results)

  Version 10.2.29 / ROWS : 359,271 / Run Rollback on Query Cancellation

  Version 10.2.07 / ROWS : 359,271 / Rollback not executed on query cancellation

• Test result : There is a problem with rollback when canceling CTAS query at 10.2.29.