Prevent failure in conditional insert in mysql database

Question

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.

Thanks!

Answer 1

Don't bother with SERIALIZABLE.

START TRANSACTION;
SELECT caseid, status, ... FROM ... WHERE ... FOR UPDATE;
check `status`, do some processing
optionally do
    INSERT ...
COMMIT;

With that logic, you can handle lots of concurrent actions.