[MDEV-29700] Insert statements creating lock, semaphore timeout and database crash Created: 2022-10-04  Updated: 2022-11-01  Resolved: 2022-11-01

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: JC Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: crash, semaphore, timoue, wait
Environment:

production

Attachments: Text File innodb_status_node0.log    

 Description   

Hello,

We have the following error on mariadb logs

{{---TRANSACTION 36378384337, ACTIVE 14 sec inserting

mysql tables in use 1, locked 1

1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1

MySQL thread id 75420, OS thread handle 139610418177792, query id 1658346 172.16.0.215 mastermaestro Update

INSERT INTO `actions` SET `actor` ='6399000', `target` ='6399000', `descr` ='test@icloud.com set bos_mobile_search_filter_selection to {\"start_format\":\"all\",\"category\":\"freeroll\",\"status\":\"all\",\"str_search\":\"\"}', `tag` ='bos_mobile_search_filter_selection', `actor_username` ='test@icloud.com'

}}

and error logs are filled with below errors before crashing:

2022-10-04 13:48:08 0 [Note] InnoDB: A semaphore wait:
--Thread 139665300420352 has waited at row0ins.cc line 2628 for 255.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f0648066f78 created in file buf0buf.cc line 1221
a writer (thread id 139610400573184) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time write locked in file mtr0mtr.ic line 112
2022-10-04 13:48:08 0 [Note] InnoDB: A semaphore wait:
--Thread 139665296123648 has waited at row0ins.cc line 2628 for 253.00 seconds the semaphore:
SX-lock on RW-latch at 0x7f0648066f78 created in file buf0buf.cc line 1221
a writer (thread id 139610400573184) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time write locked in file mtr0mtr.ic line 112

Anyone can guide me where to troubleshoot? I've seen some forums re: indexing but we did some changes and still having the same issue.

 Comments   
Comment by JC [ 2022-10-04 ]

CREATE TABLE `actions` (
`id` bigint(21) NOT NULL AUTO_INCREMENT,
`actor` bigint(21) NOT NULL,
`target` bigint(21) NOT NULL,
`descr` varchar(255) NOT NULL,
`tag` varchar(50) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`actor_username` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
KEY `created_at` (`created_at`),
KEY `descr` (`descr`),
KEY `actor_username` (`actor_username`),
KEY `actions_actor_tag_target_created_at_index` (`actor`,`tag`,`target`,`created_at`),
KEY `actions_tag_target_index` (`tag`,`target`),
KEY `actions_target_tag_created_at_index` (`target`,`tag`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2286921060 DEFAULT CHARSET=utf8

Comment by JC [ 2022-10-04 ]

mariadb v- 10.5.9

Comment by Alice Sherepa [ 2022-10-04 ]

please upgrade to the latest version of MariaDB server (10.5.17 currently) and check if the problem goes away. This might be smth like MDEV-26445 (fixed in 10.5.13)

Generated at Thu Feb 08 10:10:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.