Deleting data and the undo log

  • Filter
  • Time
  • Show
Clear All
new posts

  • Deleting data and the undo log

    Does "drop table" use the undo log (rollback segment) to temporarily store records to be purged later, the way "delete from table" does?

  • #2
    Drop table cannot be undone, so I expect the answer to be negative.


    • #3
      I don't think it does, but I am not sure.


      • #4

        Since drop table is a DDL statement that:
        1. can't be part of a transaction and
        2. can't be undone

        I would say it's highly unlikely that it uses the undo log for anything.

        There are some interesting reading in the manual for the related statement TRUNCATE TABLE (which basically is like a DROP TABLE; CREATE TABLE combination) and hence is not related to normal DML statements that can be part of a transaction and in need of the undo log.


        • #5
          Inside InnoDB, absolutely everything is fully ACID. The non-transactinoality of DROP and other DDL is due to the MySQL layer outside of InnoDB.


          • #6
            Do you know what happens under the covers when a table is dropped?

            1) A bunch a DELETE commands, thus involving the undo log and possible purge lag?
            2) Write over the former table's extents with some default value?
            3) Just mark the extents as reusable without cleaning them up?
            4) Other?