Defect #1904
closed
Deadlock while change system identifier
100%
Description
We founded use case that creates entity events that is in running state forever.
How to simulate this issue:- we have clean CzechIdM environment without some projspec implementation in version 9.7.10,
- we have one mapped system (table, in virtual this works) with one role,
- as identifier is mapped username,
- assign this role to some user (role will be assigned correctly),
- change username and check entity events.
The query:
select * from pg_stat_activity;
show us two query that has lock (in column query is query with two updates sys_system_entity and sys_provisioning_operation):
datid datname pid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change wait_event_type wait_event state backend_xid backend_xmin query backend_type 16384 bcv_idm_storage 1265 16385 idmadmin PostgreSQL JDBC Driver 172.17.0.1 56452 2019-10-15 18:43:35 2019-10-15 18:47:03 2019-10-15 18:47:03 2019-10-15 18:47:03 Client ClientRead idle in transaction 467702 update sys_system_entity set created=$1, creator=$2, creator_id=$3, modified=$4, modifier=$5, modifier_id=$6, original_creator=$7, original_creator_id=$8, original_modifier=$9, original_modifier_id=$10, realm_id=$11, transaction_id=$12, entity_type=$13, system_id=$14, uid=$15, wish=$16 where id=$17 client backend 16384 bcv_idm_storage 1292 16385 idmadmin PostgreSQL JDBC Driver 172.17.0.1 56524 2019-10-15 18:46:47 2019-10-15 18:47:03 2019-10-15 18:47:03 2019-10-15 18:47:03 Lock transactionid active 467703 467702 update sys_provisioning_operation set created=$1, creator=$2, creator_id=$3, modified=$4, modifier=$5, modifier_id=$6, original_creator=$7, original_creator_id=$8, original_modifier=$9, original_modifier_id=$10, realm_id=$11, transaction_id=$12, provisioning_batch_id=$13, current_attempt=$14, entity_identifier=$15, entity_type=$16, max_attempts=$17, operation_type=$18, provisioning_context=$19, result_cause=$20, result_code=$21, result_model=$22, result_state=$23, role_request_id=$24, system_id=$25, system_entity_id=$26 where id=$27 client backend
Event detail:
Provisoning operation detail:
There is gif with simulation: https://up.xyxy.cz/f.php?h=0kfNr5UO&p=1 (sorry for link, the gif has more than 5mb)
Affected version: 9.7.9
Files
Related issues
Updated by Radek Tomiška over 4 years ago
- Status changed from New to In Progress
- Assignee set to Radek Tomiška
Updated by Radek Tomiška over 4 years ago
- Related to Defect #1884: Provisioning update in created state was executed after delete provisioning operation added
Updated by Radek Tomiška over 4 years ago
Quick workaround:
ALTER TABLE sys_provisioning_operation DROP constraint fk_k1ad29ndiwuldqem93a01c6qx; ALTER TABLE sys_provisioning_operation DROP constraint fk_7vyyfd4iyidh411rqdoa3086b; ALTER TABLE sys_provisioning_operation DROP constraint fk_reb3mqn6a4f5mxdeipcrhthw9;
We need to test it more deeply, but this change script (with if-exists clausule) will be propably in next hotfix version.
Updated by Radek Tomiška over 4 years ago
- Status changed from In Progress to Needs feedback
- Assignee changed from Radek Tomiška to Vít Švanda
- % Done changed from 0 to 90
The issue is in saving provisioning operation in new transaction combined with saving system entity in normal transaction (without new transaction and referenced from provisioning operation). If system entity is saved in new transaction too (just when provisioning operation is processed), then issue is fixed and FK can remain.
We don't want to change the behavior with adding the new way how to save system entity in LTS => change script is created for LTS with FK removal (change script is compatible with workaround above).
FK will be added again and this related issue will be correctly implemented in version 10: #1896.
Commit:
https://github.com/bcvsolutions/CzechIdMng/commit/944e64d0297423d64aa039f252e022575769edd8
Could you do a feedback, please?
Updated by Radek Tomiška over 4 years ago
- Related to Task #1896: Add foreign keys to all entities added
Updated by Vít Švanda over 4 years ago
- Assignee changed from Vít Švanda to Radek Tomiška
- % Done changed from 90 to 100
I did a review. My FKs on ProvisioningOperation were deleted. I tried to change UID on table system and on my virtual system too. All successfully, without lock on DB. Thanks for fix.
Updated by Vít Švanda over 4 years ago
- Status changed from Needs feedback to Resolved
Updated by Radek Tomiška over 4 years ago
- Status changed from Resolved to Closed