Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23223

If you cancel CTAS Query on MariaDB Server, it will not be canceled and Rollback will be executed

    XMLWordPrintable

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              jmlee 이종명
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: