Mysql concurrent query caus some valid query to fail

[maxliguge]

Hi,
We recently moved our databse to google cloud SQL. But we are facing a really strange behavior some (valid) queries that would work like a charm before stopped working (sometimes) for no reason and mysql return an error but if we re-run it sometimes it works.

Here's an example :

UPDATE Experiences SET Description = 'test' WHERE ExperienceId = 157192;

If i run this query manually 10 times in a row it will work like 6 times but 4 times it would throw an error (numbers are random) both in Laravel or in phpmyadmin :

Column 'Description' cannot be empty (null) 

The problem doesn't seems to happen if there is no active active users on the server. We tried to clone the database on an empty server and the problem don't happen all the update works but, if there are multiple users (like there is in production) it would sometimes throw this error ...

Here are some specs : 

• The mysql version is 8.0.36
• The field 'Description' is a classic varchar(255) NOT NULL column
• There is no foreign key or so in the table, there is only one before insert trigger to generete a Hash, no event, lock or whatever else
• I'm running the exact same query manually 10 times in a row on phpmyadmin (on a different script each times) or via an API call (with the same data)
• The database is hosted on Google Cloud SQL there are 3 flags : log_bin_trust_function_creators (on) , sql_mode (NO_ENGINE_SUBSTITUTION, ALLOW_INVALID_DATES) , explicit_default_for_timestamp(off)
• we tried to remove the no_engine flag on the sql_mode but same problem
• Our other servers are on cloud run
• Of course I could just change the column to accept null value but that's not possible since this error occurs on multiple column/table

[ms4446]

The error message "Column 'Description' cannot be empty (null)" is misleading in this context. The issue is likely related to how concurrent transactions and transaction isolation are managed in Cloud SQL for MySQL.

• Concurrent Transactions: Multiple processes (users, API calls) are trying to modify the same record (ExperienceId = 157192) simultaneously. This concurrency can lead to race conditions where transactions affect each other’s operations.

• Transaction Isolation: MySQL's default isolation level (REPEATABLE READ) prevents a transaction from seeing changes made by other transactions that haven't yet committed. This can cause a transaction to operate on outdated data. If one transaction reads the original value of 'Description' and updates it, but another transaction commits a change to the same column before the first transaction can commit, the first transaction’s update can fail due to outdated data.

• Unexpected Result:  When the first transaction tries to write the updated value, it might fail if it’s based on outdated information, leading to violations of constraints such as 'Description' cannot be NULL.

1. Increased Concurrency: Cloud SQL may handle more simultaneous connections or a higher workload, increasing the likelihood of transaction conflicts.

2. Configuration Differences: Differences in MySQL settings or resource management between your previous environment and Cloud SQL can influence how concurrency is handled.

Debugging and Solutions

1. Enable Logging

• Action: Turn on the MySQL general log and slow query log in Cloud SQL. This provides a detailed record of queries and errors, helping to trace problematic transactions.

2. Analyze Logs

• Action: Look for patterns in the logs, especially around failing queries. Identify if certain transactions are consistently involved or if conflicts occur at specific times.

3. Review Triggers

• Action: Double-check the before insert trigger logic to ensure it doesn’t inadvertently modify the 'Description' column or interact in ways that could cause conflicts.

4. Adjust Transaction Isolation Levels

• Action: Experiment with the READ COMMITTED isolation level to reduce the impact of outdated data. This allows transactions to see changes made by other transactions before they commit, potentially avoiding conflicts. However, be cautious as this can introduce other anomalies.

5. Implement Optimistic Locking

• Action: Add a version column to the table and use it in the WHERE clause of your UPDATE statement. This ensures updates occur only if the row hasn’t been modified by another transaction, preventing conflicts.

6. Use Row-Level Locking

• Action: Use row-level locking to control access to rows being updated, which can prevent concurrent modifications. Example: 

  START TRANSACTION;
  SELECT ExperienceId FROM Experiences WHERE ExperienceId = 157192 FOR UPDATE;
  UPDATE Experiences SET Description = 'test' WHERE ExperienceId = 157192;
  COMMIT;
  

7. Review and Optimize MySQL Configuration

• Action: Check and optimize MySQL settings like innodb_lock_wait_timeout to better handle transaction conflicts. Ensure resource limits and connection handling are appropriate for your workload.

8. Monitor and Scale Resources

• Action: Monitor database performance and scale resources as needed to handle increased concurrency effectively. Ensure the instance configuration supports your workload.

 Example (Optimistic Locking)

Add a Version Column:

ALTER TABLE Experiences ADD COLUMN version INT DEFAULT 0;

Use in Update Statement:

UPDATE Experiences 
SET Description = 'test', version = version + 1
WHERE ExperienceId = 157192 AND version = <original_version>;

Again, the issue appears to be rooted in how concurrent transactions and isolation levels are managed in Cloud SQL for MySQL. By enabling logging, analyzing logs, reviewing triggers, adjusting transaction isolation levels, and implementing optimistic locking, you can address the conflicts leading to the error. Ensure to test these changes in a staging environment before applying them to production.

[maxliguge · Accepted solution]

Thanks for your answer, 
None of the above fixed our problem but, we managed to fix it by rolling back to mysql 5.7