MySQL rolls back the insert statement, leading to a doubling of ibd files'

MySQL rolls back the insert statement, leading to a doubling of ibd files'

MySQL rolls back the insert statement, which causes the ibd file to increase by a factor of one. A simple test:

Start transaction;
Insert into tb1 values (3, repeat ('A', 65000), 'x', 1 );
-- Commit;
Rollback;

These are the sizes of files after tb1.ibd is inserted, before rollback, and after rollback:

Someone asked in the QQ group: Why does rollback increase the disk space of tb1.idb?

---------------------------------------

The reason is simple:

131072-98304 = 163840-131072 = 32768
So: Roll Back insert = delete + re-insert, while delete refers to setting flag, so... content to double.

To roll back an insert operation, you must first insert the value before the delete operation, and then re-insert the value. delete is a logical deletion, that is, setting a flag without actually deleting it. This is the reason.

However, because the transaction has not been committed and data has not been written to the disk, why does the disk space occupied by tb1.idb increase twice? Obviously, this is because the disk space of tb1.idb is pre-allocated. That is to say, when the statement is executed, the disk space has been allocated, but the transaction has not been committed, the checkpoint cannot fl uncommitted data into the disk, so the disk space is allocated, but the insert data is not written to the disk. At this time, the insert data still exists in the buffer. Disk Space is pre-allocated.

This article permanently updates the link address: