Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Preload parameter_names with all parameters to avoid deadlocks in Derby #2857

Closed
punktilious opened this issue Oct 14, 2021 · 1 comment
Closed
Assignees
Labels
bug Something isn't working

Comments

@punktilious
Copy link
Collaborator

Describe the bug
System integration tests sometimes hit a deadlock in Derby which looks like this:

[14/10/2021, 13:25:18:061 UTC] 0000003e com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl   E Unexpected error while processing token value records.
com.ibm.fhir.database.utils.api.DataAccessException: java.sql.SQLTransactionRollbackException: A lock could not be obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, COMMON_TOKEN_VALUES, (181,66)
  Waiting XID : {73744, X} , APP, INSERT INTO common_token_values (token_value, code_system_id) VALUES (?, ?)
  Granted XID : {74019, X} 
Lock : ROW, PARAMETER_NAMES, (30,6)
  Waiting XID : {74019, S} , APP, INSERT INTO Task_RESOURCE_TOKEN_REFS(parameter_name_id, logical_resource_id, common_token_value_id, ref_version_id, composite_id) VALUES (?, ?, ?, ?, ?)
  Granted XID : {73744, X} 
. The selected victim is XID : 73744.
	at com.ibm.fhir.database.utils.derby.DerbyTranslator.translate(DerbyTranslator.java:100)
	at com.ibm.fhir.persistence.jdbc.derby.DerbyResourceReferenceDAO.doCommonTokenValuesUpsert(DerbyResourceReferenceDAO.java:225)
	at com.ibm.fhir.persistence.jdbc.derby.DerbyResourceReferenceDAO.upsertCommonTokenValues(DerbyResourceReferenceDAO.java:249)

This is triggered because we have to create new rows in PARAMETER_NAMES, and we can't order the inserts for this as well as the inserts for xx_RESOURCE_TOKEN_REFS which also has foreign key constraints (requiring locks) to COMMON_TOKEN_VALUES.

Environment
4.10.0

To Reproduce
Steps to reproduce the behavior:

  1. Run system integration tests using a Derby datastore

Expected behavior
Run should complete without deadlock errors.

Additional context
To eliminate the deadlock, the only solution here is to preload the PARAMETER_NAMES table with all known values so none need to be created at runtime. Once done, only S locks will be obtained on PARAMETER_NAMES rows and since these are non-blocking, the deadlock will not occur.

The PARAMETER_NAMES table is currently loaded during schema generation, but the new variant parameter names (where a suffix is added to a parameter) are not. It is these parameters we need to load.

@lmsurpre
Copy link
Member

Seems to be working (per CI results)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

3 participants