MySQL Forums
Forum List  »  InnoDB

MySQL gets long semaphore locks on - but on DML operations
Posted by: Nikhil Nair
Date: August 02, 2016 09:05AM


There is a table with 2 columns integer id column - primary key auto increment and a long text column.

Rows are concurrently added, read and removed from this table by many different processes and connections. There are no DDL statements at all. Just Insert, Select and Delete - and every operation happens on at most 1 row. i.e Inserts a single row, Selects a single row by primary key and then deletes a single row also by primary key.

The table is the only table on the mysql instance (this is in a docker container).

The table has few populated rows, the size of the ibd file is 199G.


Frequently i see semaphore locks like these

--Thread 140441749952256 has waited at line 545 for 249.00 seconds the semaphore:
S-lock on RW-latch at 0x4fcb428 created in file line 2606
a writer (thread id 140441750218496) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file line 545

Sometimes the lock is held for more than 600s and innodb intentionally crashes.

Looking at the code for 5.6.26 - the function( in dict0dict is

Adds an index to the dictionary cache.

And specifically:

rw_lock_create(index_tree_rw_lock_key, &new_index->lock,


Whats causing those locks?

Tried so far

Seeing that it is contention between b-tree cursor and dictionaries and some research, I turned off adaptive hash indexing. Issue still persisted.
Then increased buffer pool from the default 128M to 1G. Issue still persists.
Since its a busy table I have not wanted to optimize the table, but in case that is the answer - I'd like to know why.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.