The infrastructure of our system looks like this.
An AWS lambda function receives requests such as (accountId, .....). It creates an entry in the MySQL database using a newly generated UUID as caseId. (caseId, accountId, ....).
The insert is a conditional insert operation discussed in detail below.
I am able to avoid race condition by setting transaction isolation to SERIALIZABLE. However, the issue is that I do not have any control over how many concurrent requests will be successfully processed.
For example, consider following concurrent requests.
request | accountId | field1 | ... <condition> 1 a1 value1 .... true --- create a new entry with caseId Idxxx 2 a1 value2 .... false --- update existing entry with caseId Idxxx 3 a1 value3 .... false --- update existing entry with caseId Idxxx 4 a1 value4 .... false --- update existing entry with caseId Idxxx
With our current implementation we are getting CannotAquireLockException. What are the ways in which I can avoid retry failures (CannotAquireLockException) ?
The detailed table schema and condition are described below:
The database is a mysql database system with the following table schema.
Table1: case table |caseId(PK) | accountId | status | ..... Table2: case reopen table |caseId(FK)| casereopenId(PK)| caseReopenTime| Table3: Alert table Id (incrementing id) | alertId | accountId |
The lambda function tries to "create" a case in the database.
the create wrapper, generates a UUID for caseId.
The goal is :
- check if an accountId already exists in case table.
- if it does, then
- check if status is OPEN
- get the caseId for the accountId.
- check if the caseId is present in case reopen table.
- if above condition is false, then add an entry into the case table.