[MDEV-24494] Lock on simple UPDATE query with Semaphore wait > 600 seconds Created: 2020-12-27  Updated: 2021-01-12

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Update, Storage Engine - InnoDB
Affects Version/s: 10.4.17
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: lock
Environment:

Centos 7


 Description   

MariaDB 10.4.17 server randomly hangs on simple UPDATE statements.

UPDATE loginlogs SET rxbytes = rxbytes + ?, txbytes = txbytes + ? WHERE logid = ?

When I query 

SELECT * FROM sys.innodb_lock_waits

I get output such as

wait_started    wait_age        wait_age_secs   locked_table    locked_index    locked_type     waiting_trx_id  waiting_trx_started     waiting_trx_age waiting_trx_rows_locked waiting_trx_rows_modified       waiting_pid     waiting_query   waiting_lock_id waiting_lock_mode       blocking_trx_id blocking_pid    blocking_query  blocking_lock_id        blocking_lock_mode      blocking_trx_started    blocking_trx_age        blocking_trx_rows_locked        blocking_trx_rows_modified      sql_kill_blocking_query sql_kill_blocking_connection
 
2020-12-27 07:43:32     00:00:04        4       `db`.`loginlogs`   PRIMARY RECORD  37075679818     2020-12-27 07:43:32     00:00:04        1       0       19194139        UPDATE loginlogs SET  ... HERE logid = 64634225227638257       37075679818:921:15944673:61     X       37075617021     19191704        UPDATE loginlogs SET ... HERE logid = 64634225227638257       37075617021:921:15944673:61     X       2020-12-27 07:43:07     00:00:29        1       0       KILL QUERY 19191704     KILL 19191704

So one UPDATE is blocking another update due to X lock. The blocking UPDATE does not show up in innodb lock waits, so no idea why it keeps on waiting with the X lock on loginlogs table. logid is PRIMARY KEY.

The UPDATE is not part of any transaction, its a stand-alone statement.

The lock then ends up in this:

2020-12-27  7:59:00 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung

And then MariaDB restarts.
Generated at Thu Feb 08 09:30:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.