When encountering the error 1020: Record has changed from MySQL, follow these steps immediately:
- Capture the exact query or transaction that led to the error. Use application logs or MySQL's general log if it's enabled. To enable the general log temporarily, you can use:SET global general_log = 1;
SET global log_output = 'table';
After capturing the necessary information, remember to disable it to avoid performance issues:SET global general_log = 0;
- Determine if there are concurrent transactions modifying the same record. You can check for currently running transactions using:SHOW ENGINE INNODB STATUS;
Look under the `TRANSACTIONS` section for transactions that are currently active or in a lock wait state.
- If the error is due to lock contention, review the output from `SHOW ENGINE INNODB STATUS;` for any deadlocks or lock waits and identify the conflicting transactions.
- Check if the transaction isolation level is appropriate for your use case. You can view the current level with:SELECT @@GLOBAL.txisolation, @@txisolation;
If necessary, adjust the isolation level to manage how transactions see modifications made by others.
- Ensure your application logic handles optimistic locking correctly, if applicable. This involves re-reading the record before attempting an update and ensuring the version number or timestamp matches.
- Implement logic to retry the transaction in case of such errors, especially if the error is transient and can be resolved by a subsequent attempt.
- Monitor metrics such as lock waits, row lock contention, and transaction throughput to understand if the issue is part of a larger performance problem. Use performance_schema tables or tools like MySQL Workbench for monitoring.
Executing these actions should help in diagnosing and addressing the error 1020: Record has changed in MySQL.
Let Dr. Droid create custom investigation plans for your infrastructure.
Book Demo
