Description:
Online DDLs support DML statements, but apparently, if there is any SELECT statement before that, MySQL threw a deadlock. I tested in many versions, 5.6,5.7, and even 8.0.37, and all behavior is the same. I couldn't find any documents to explain this behavior.
Also, the deadlock is not logging anywhere. I enabled innodb_print_all_deadlocks and also checked the SHOW ENGINE INNODB STATUS;
How to repeat:
session1:
mysql [localhost:8037] {msandbox} ((none)) > begin;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8037] {msandbox} ((none)) > select count(1) from test.t3;
+----------+
| count(1) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
session2:
mysql [localhost:8037] {msandbox} ((none)) > alter table test.t3 add column d varchar(100), ALGORITHM=INPLACE;
session1:
mysql [localhost:8037] {msandbox} ((none)) > update test.t3 set tcol01=10 where tcol2=136;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
session2:
mysql [localhost:8037] {msandbox} ((none)) > alter table test.t3 add column d varchar(100), ALGORITHM=INPLACE;
Query OK, 0 rows affected (13.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
Here is the output SHOW ENGINE INNODB STATUS;
mysql [localhost:8037] {msandbox} ((none)) > SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2024-07-04 12:47:20 139963925579520 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 136 srv_active, 0 srv_shutdown, 3599 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 135
OS WAIT ARRAY INFO: signal count 127
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1572
Purge done for trx's n:o < 1571 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421439378787544, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421439378788352, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421439378786736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421439378785928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
Description: Online DDLs support DML statements, but apparently, if there is any SELECT statement before that, MySQL threw a deadlock. I tested in many versions, 5.6,5.7, and even 8.0.37, and all behavior is the same. I couldn't find any documents to explain this behavior. Also, the deadlock is not logging anywhere. I enabled innodb_print_all_deadlocks and also checked the SHOW ENGINE INNODB STATUS; How to repeat: session1: mysql [localhost:8037] {msandbox} ((none)) > begin; Query OK, 0 rows affected (0.00 sec) mysql [localhost:8037] {msandbox} ((none)) > select count(1) from test.t3; +----------+ | count(1) | +----------+ | 100000 | +----------+ 1 row in set (0.01 sec) session2: mysql [localhost:8037] {msandbox} ((none)) > alter table test.t3 add column d varchar(100), ALGORITHM=INPLACE; session1: mysql [localhost:8037] {msandbox} ((none)) > update test.t3 set tcol01=10 where tcol2=136; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction session2: mysql [localhost:8037] {msandbox} ((none)) > alter table test.t3 add column d varchar(100), ALGORITHM=INPLACE; Query OK, 0 rows affected (13.52 sec) Records: 0 Duplicates: 0 Warnings: 0 Here is the output SHOW ENGINE INNODB STATUS; mysql [localhost:8037] {msandbox} ((none)) > SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2024-07-04 12:47:20 139963925579520 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 136 srv_active, 0 srv_shutdown, 3599 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 135 OS WAIT ARRAY INFO: signal count 127 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 1572 Purge done for trx's n:o < 1571 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421439378787544, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421439378788352, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421439378786736, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421439378785928, not started 0 lock struct(s), heap size 1128, 0 row lock(s)