Comparison between Oracle11.2.0.3 and MySQL5.6DDL

After MySQL5.6, The ONLINEDDL function is greatly enhanced. Typically, the above two aspects are used. First, ADDCOLUMN and DROPCOLUMN will no longer block DML operations and create indexes at the same time.

The online ddl function is greatly enhanced after MySQL 5.6, typically in the above two aspects. First, adding COLUMN and drop column will no longer block DML operations, and creating indexes at the same time

1. create index, DROP INDEX
2. add column, DROP COLUMN

The online ddl function is greatly enhanced after MySQL 5.6, typically in the above two aspects. First, adding COLUMN and drop column will no longer block DML operations, in terms of index creation, the LOCK = NONE mode is used by DEFAULT, but DML is not blocked. The LOCK has four DEFAULT modes: NONE, which can be SHARED, exclusive, or DEFAULT, in the LOCK = NONE mode, this situation is very similar to the create index online of Oracle. In the 5.6.19 test, if a transaction is not submitted or is in progress when the create index lock = NONE, the same is true for ORACLE. In other words, both ORACLE and MYSQL attempt to create an index or an exclusive X lock at the initial stage. Once obtained, the index will be downgraded immediately, however, this waiting process in MYSQL will block the SELECT statement. We know that the SELECT statement will not be blocked in ORACLE under any circumstances. The descriptions are as follows;

1. create index (online) when a transaction is not submitted

ORACLE 11.2.0.3 test CREATE INDEX ONLINE
Insert a data entry into the table first. Do not submit it.
Insert into testti select * from testti where rownum <= 1;
Then start another session
Create index test_in on testti (username) online;
The create index is blocked. View V $ LOCK.
SID TYPE LMODE REQUEST BLOCK
--------------------------------------------
48 TX 0 4 0
48 TM 2 0 0
48 TM 4 0 0
48 TX 6 0 0
53 TM 3 0 0
53 TX 6 0 1
We can see that SID 53 blocks SID 48, and SID 48 is blocked by the TX lock of MODE 6 when trying to obtain the Lock of MODE 4.
However, other SELECT statements are not blocked.

MYSQL 5.6.19 perform the same test create index lock = NONE
First, delete a piece of data in the table. Do not submit it.
Begin;
Mysql & gt; delete from testno where I = 122;
Query OK, 1 row affected (0.24 sec)
Start another session.
Mysql> create index test_ind on testno (j) lock = none;
Congestion
Start a session
Select * from testno limit 1;
SELECT is blocked.
Finally, check the innodb status to determine
TRANSACTIONS
------------
Trx id counter 462509
Purge done for trx's n: o <462509 undo n: o <0 state: running but idle
History list length 434
List of transactions for each session:
--- TRANSACTION 0, not started
MySQL thread id 4, OS thread handle 0x40b4c940, and query id 275 localhost root System lock
Show engine innodb status
--- TRANSACTION 462459, not started
MySQL thread id 3, OS thread handle 0x40b0b940, query id 274 localhost root Waiting for table metadata lock
Select * from testno limit 1
--- TRANSACTION 462471, not started
MySQL thread id 2, OS thread handle 0x40671940, query id 273 localhost root Waiting for table metadata lock
Create index test_ind on testno (j) lock = none
--- TRANSACTION 462492, ACTIVE 100 sec inserting
Mysql tables in use 2, locked 2
7016 lock struct (s), heap size 800296,836 672 row lock (s), undo log entries 322558
MySQL thread id 1, OS thread handle 0x40430940, query id 272 localhost root Sending data
Insert into testno select * from testno

You can see that locked 2

From this we can see that on create index, ORACLE and MYSQL will be blocked when there are items not submitted in this table.

Index (re) build online cleanup

2. create index (online)

ORACLE:
Session 1 create index test_in on testti (username) online;
Session 2 can be performed on any DML without any problems

However, ORACLE will be affected by the transactions during the create index online period. Although DML is not affected, the transactions during creation must be submitted before the entire creation process is complete.

MYSQL:
Session 1 create index test_ind on testno (j) lock = none;
Session 2 can be performed on any DML without any problems

3. DROP INDEX

About drop index, if something is accessing this table, ORACLE and MYSQL basically adopt the same method, that is, they won't let you delete it.
ORACLE Error
Drop index test_in
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
MYSQL is waiting for METADATA

If no thing is being accessed, the two types of databases for index deletion are basically the same, that is, they are simply deleted.
Data Dictionary information, and then mark the space as available, rather than deleting the data.
Mysql> drop index test_ind on testno;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

SQL> drop index test_in;
Index dropped
0.17 seconds

4. ADD COLUMN

In the current situation,
First
Insert into testti select * from testti where rownum <= 1;
Enable session
Alter table testti add test varchar2 (20 );