Why does MySQL rollback cause an increase in the ibd file ?, Mysqlibd

Why does MySQL rollback cause an increase in the ibd file ?, Mysqlibd

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?

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

First: 131072-98304 = 163840-131072 = 32768

Here we need to store 65000 characters 'A', so the field type should be text. While the processing of the text field is very special:

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrastto all other data types, for which storage is allocated once per column when the table is opened.

That is to say, for text/blob fields, they are allocated with a dedicated object for storage, and their processing is not cached in the memory, but directly written to the disk. Therefore, when the request is not submitted, you can see that tb1.idb has increased.

For the rollback of the insert operation, the previously inserted data must be deleted, while the delete operation is a logical operation, that is, setting a flag, therefore, a special object is assigned to store the object with the deletion flag set. As a result, the disk space has increased twice (this can only be explained for the moment...), each of which is 32768 bytes (15 to the power of 2 ). Once submitted or rolled back, the disk space can be recycled.

Why 32768?

The maximum storage space of text is 65656 bytes, and the minimum unit for each allocation is 32768 bytes.

That is, insert and rollback both lead to an increase in disk space allocation for the text field, and the minimum unit of allocation is 32768.

How do you replace repeat ('A', 65000) with: repeat ('A', 65535) during the test, you will find that tb1.idb increases by 65536 during insert, when rollback is executed, tb1.idb increases by 32768 again.

Therefore, rollback will cause the text field to be allocated another space, and the minimum allocation unit is 32768. It may be related to the storage structure of the text field. You need to check the source code.

 

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

Tests show that rollback operations for other non-text fields will not increase the ibd file size.

The test table creation statement is as follows:

mysql> show create table tb1\G*************************** 1. row ***************************       Table: tb1Create Table: CREATE TABLE `tb1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `webtext` text COLLATE utf8mb4_bin,  `tp` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,  `se` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin1 row in set (0.00 sec)