A workload where many workers concurrently pull a number from a InnoDB sequence and insert the number into another InnoDB table using transactions, occasionally causes all worker threads to hang in SEQUENCE::next_value. Everything is fine when no transactions are used (autocommit) or when the sequence object is using the MyISAM engine.

How to repeat:

You need Lua-enabled sysbench, i.e. from https://github.com/hgxl64/sysbench-mariadb.git. Then use the attached test script sequence_native.lua:

mysql -S ... -u root -e "drop database if exists sbtest"

mysql -S ... -u root -e "create database sbtest"

sysbench --test=sequence_native.lua --oltp-tables-count=32 \

 --mysql-table-engine=InnoDB --sequence-cache=10 \

 --mysql-user=root --mysql-socket=... prepare

sysbench --test=sequence_native.lua --oltp-tables-count=32 \

 --trx-size=10 --num-threads=2 --report-interval=1 --max-time=0 \

 --max-requests=0 --mysql-user=root --mysql-socket=... run

You might need to alter the sequence cache size or number of threads. The Lua script understands a few options:
--sequence-cache is used for the CACHE option when creating the sequence
--sequence-engine is used for the ENGINE option of the sequence
--trx-size gives the number of inserts in each transaction

My guess is, that something goes wrong if the sequence cache runs empty for multiple worker threads and they have to dive for the base table at the same time. The bigger the sequence cache is, the more worker threads are needed to trigger the hang. I.e. with Cache=1000 I need 16 threads.

Attached is also my.cnf and a stack trace from a hanging mysqld.