My Oracle Support Banner

During "Online" DDL, Some DML Waits On Semaphore Last Locked In "btr0bulk.cc line 53" (Doc ID 2480928.1)

Last updated on DECEMBER 05, 2018

Applies to:

MySQL Server - Version 5.7 and later
Information in this document applies to any platform.

Goal

Problem:
-----------
An otherwise non-locking ALTER TABLE .. ADD KEY leads to an expected long semaphore wait near the end.
InnoDB status show it like this: (note: btr0bulk.cc line 53 )
  


--Thread 3036 has waited at row0ins.cc line 2936 for 172.00 seconds the semaphore:
S-lock on RW-latch at 0000019A27BB06C8 created in file dict0dict.cc line 2737
a writer (thread id 2244) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: dffffffe
Last time read locked in file row0ins.cc line 2936
Last time write locked in file G:\\ade\\build\\sb_0-30854123-1538632997.08\\mysqlcom-5.7.24\\storage\\innobase\\btr\\btr0bulk.cc line 53

 

...


---TRANSACTION 109708897, ACTIVE 172 sec inserting mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 37, OS thread handle 3036, query id 768978048 localhost ::1 root update
insert into t(c,d,e,f,m,b,l) values(uuid(),uuid(),uuid(),uuid(),floor(rand()*100000),floor(rand()*100000),repeat(uuid(),10))
---TRANSACTION 108850281, ACTIVE 2452 sec
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 16, OS thread handle 2244, query id 762945328 localhost ::1 root altering table
alter table t add index idx(q), algorithm=inplace, lock=none
Trx read view will not see trx with id >= 108850283, sees < 108850282



Processlist
----------------

Id User Host db Command Time State Info
16 root localhost test Query 2451 altering table alter table t add index idx(q), algorithm=inplace, lock=none
37 root localhost test Query 171 update insert into t(c,d,e,f,m,b,l) values(uuid(),uuid(),uuid(),uuid(),floor(rand()*100000),floor(rand()*100000),repeat(uuid(),10))

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.