CODEWITHSUNDEEP
mysqlinnodbalter-table
giogio
giogio

Reputation: 37

MySQL InnoDB row/table lock when performing ALTER

I created a sysbench table shown below with 25,000,000 records (5.7G in size):

Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25000001 DEFAULT CHARSET=latin1

Then added an index on c using the ALTER statement directly, which took about 18 minutes to update the table as shown below:

mysql> alter table sbtest1  add index c_1(c);
Query OK, 0 rows affected (18 min 47.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `c_1` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=25000002 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

During the 18 minutes of the table update process, i tried to perform some transactions on the table by inserting new records and also update existing records on column c, and which to my surprise all worked when i expected a lock to prevent this from happening. I have always understood that performing an ALTER on an InnoDB table, especially a large table, can result on a record lock for the duration of the process, so wondering why i was able t perform inserts and updates without any problems?

Here are some info about my server:

mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

mysql> select version()
    -> ;
+-----------+
| version() |
+-----------+
| 5.7.25-28 |
+-----------+

To me, it now seems like in MySQL 5.7, its okay to directly run the ALTER statement without any worries about locks? Is this an accurate conclusion?

UPDATED When i tried to delete the added index c_1, it only took less than a second, which also surprised me coz i expected this too take longer than actually adding an index. I have always believed that adding an index is simple and quick, yet deleting or updating one takes a long time as the entire table structure has to be altered. So a bit confused about this???

Upvotes: 0

Views: 302

Answers (1)

danblack
danblack

Reputation: 14779

Adding secondary index can be done inplace and permit concurrent DML.

Upvotes: 1

Related Questions