dead lock on user update in high load environment

[rolfschmidt]

Infos:

• Used Zammad version: 5.0
• Installation method (source, package, ..): all
• Operating system: all
• Database + version: MariaDB 10.4.22
• Elasticsearch version: all
• Browser + version: all
• Ticket#1095730

Expected behavior:

• No dead locks

Actual behavior:

E, [2022-01-24T02:01:13.622956 #337439-237236780] ERROR -- : Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction (ActiveRecord::Deadlocked)
app/models/concerns/has_groups.rb:276:in `destroy_group_relations'
app/models/concerns/has_groups.rb:259:in `block in process_group_access_buffer'
app/models/concerns/has_groups.rb:251:in `flush_group_access_buffer'
app/models/concerns/has_groups.rb:258:in `process_group_access_buffer'
app/models/concerns/has_groups.rb:238:in `fill_group_access_buffer'
app/models/concerns/has_groups.rb:219:in `groups_access_map_store'
app/models/concerns/has_groups.rb:161:in `group_names_access_map='
app/models/application_model/can_associations.rb:31:in `block in associations_from_param'
app/models/application_model/can_associations.rb:25:in `each'
app/models/application_model/can_associations.rb:25:in `associations_from_param'
app/controllers/users_controller.rb:130:in `block in update'
app/controllers/users_controller.rb:120:in `update'
app/controllers/application_controller/has_download.rb:21:in `block (4 levels) in <module:HasDownload>'
app/controllers/application_controller/has_download.rb:20:in `block (3 levels) in <module:HasDownload>'
app/controllers/application_controller/has_download.rb:19:in `block (2 levels) in <module:HasDownload>'
app/controllers/application_controller/handles_transitions.rb:16:in `handle_transaction'

E, [2022-01-24T16:48:13.150687 #1939973-174420] ERROR -- : Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction (ActiveRecord::Deadlocked)
app/models/concerns/has_group_relation_definition.rb:33:in `touch_related'
app/models/concerns/has_groups.rb:276:in `destroy_group_relations'
app/models/concerns/has_groups.rb:259:in `block in process_group_access_buffer'
app/models/concerns/has_groups.rb:251:in `flush_group_access_buffer'
app/models/concerns/has_groups.rb:258:in `process_group_access_buffer'
app/models/concerns/has_groups.rb:238:in `fill_group_access_buffer'
app/models/concerns/has_groups.rb:219:in `groups_access_map_store'
app/models/concerns/has_groups.rb:161:in `group_names_access_map='
app/models/application_model/can_associations.rb:31:in `block in associations_from_param'
app/models/application_model/can_associations.rb:25:in `each'
app/models/application_model/can_associations.rb:25:in `associations_from_param'
app/controllers/users_controller.rb:130:in `block in update'
app/controllers/users_controller.rb:120:in `update'
app/controllers/application_controller/has_download.rb:21:in `block (4 levels) in <module:HasDownload>'
app/controllers/application_controller/has_download.rb:20:in `block (3 levels) in <module:HasDownload>'
app/controllers/application_controller/has_download.rb:19:in `block (2 levels) in <module:HasDownload>'
app/controllers/application_controller/handles_transitions.rb:16:in `handle_transaction'

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-24 16:53:39 0x7fee34717700
*** (1) TRANSACTION:
TRANSACTION 18717919, ACTIVE 6 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 18 lock struct(s), heap size 1128, 150 row lock(s), undo log entries 102
MySQL thread id 4750, OS thread handle 140661055108864, query id 3954168 localhost zammad Updating
DELETE FROM `groups_users` WHERE `groups_users`.`user_id` = 38 AND `groups_users`.`group_id` = 6 AND `groups_users`.`access` = 'full'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4341 page no 12 n bits 1120 index index_groups_users_on_group_id of table `zammad`.`groups_users` trx id 18717919 lock_mode X locks re                                                                                 c but not gap waiting
Record lock, heap no 571 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 00000002ae0f; asc       ;;

*** (2) TRANSACTION:
TRANSACTION 18718135, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
14 lock struct(s), heap size 1128, 48 row lock(s), undo log entries 45
MySQL thread id 4736, OS thread handle 140661058795264, query id 3954256 localhost zammad Updating
DELETE FROM `groups_users` WHERE `groups_users`.`user_id` = 7625 AND `groups_users`.`group_id` = 28 AND `groups_users`.`access` = 'full'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4341 page no 12 n bits 1120 index index_groups_users_on_group_id of table `zammad`.`groups_users` trx id 18718135 lock_mode X locks re                                                                                 c but not gap
Record lock, heap no 571 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 00000002ae0f; asc       ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4341 page no 12 n bits 1120 index index_groups_users_on_group_id of table `zammad`.`groups_users` trx id 18718135 lock_mode X locks re                                                                                 c but not gap waiting
Record lock, heap no 200 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000001c; asc     ;;
 1: len 6; hex 00000002bc9f; asc       ;;

*** WE ROLL BACK TRANSACTION (2)

Yes I'm sure this is a bug and no feature request or a general question.