How to fix ERROR 1020: Record has changed since last read in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 181 views

ERROR 1020 (ER_CHECKREAD) occurs when another transaction modifies a record between reads in a multi-user environment. This commonly affects MyISAM tables and can be resolved through retry logic, isolation level adjustments, or row-level locking.

Quick fix

Fixes 1020

const MAX_RETRIES = 3;
const RETRY_DELAY = 100; // ms

async function executeWithRetry(query) {
  for (let attempt = 1; attempt <= MAX_RETRIES; attempt++) {
    try {
      return await db.execute(query);
    } catch (err) {
      if (err.errno === 1020 && attempt < MAX_RETRIES) {
        await new Promise(r => setTimeout(r, RETRY_DELAY * attempt));
        continue;
      }
      throw err;
    }
  }
}
  1. Implement transaction retry logic
  2. Upgrade MyISAM tables to InnoDB
  3. Adjust transaction isolation level
  4. Add indexes to optimize queries
mysql — error
const MAX_RETRIES = 3;
const RETRY_DELAY = 100; // ms
async function executeWithRetry(query) {
  for (let attempt = 1; attempt <= MAX_RETRIES; attempt++) {
    try {
      return await db.execute(query);
    } catch (err) {
      if (err.errno === 1020 && attempt < MAX_RETRIES) {
        await new Promise(r => setTimeout(r, RETRY_DELAY * attempt));
        continue;
      }
      throw err;
    }
  }
}
ERROR 1020: Record has changed since last read

What this error means

MySQL Error 1020 – SQLSTATE: HY000 (ER_CHECKREAD) signals a data consistency conflict in multi-user environments. When a transaction reads a record, MySQL stores its version. If another concurrent transaction modifies that same record before the first transaction completes, MySQL detects this inconsistency and raises ERROR 1020. This error is primarily associated with MyISAM tables, which lack MVCC (Multi-Version Concurrency Control) and full ACID guarantees. InnoDB tables use consistent snapshots and don't exhibit this race condition. The error serves as a safeguard against reading stale or inconsistent data during concurrent operations.

Typical symptoms

  • Transactions fail intermittently with "Record has changed since last read" message
  • Errors occur more frequently under high concurrent load
  • MyISAM tables are affected more than InnoDB tables
  • DELETE operations fail when using WHERE ... IN subqueries
  • Multiple HANDLER READ operations in sequence encounter conflicts

Common causes

  1. Another transaction modifies or deletes a record after your transaction reads it
  2. Using MyISAM storage engine, which lacks MVCC support
  3. High concurrency with multiple simultaneous readers and writers on the same table
  4. Table changes occur between sequential reads in a scan operation
  5. Inadequate transaction isolation level for your concurrency requirements
  6. Missing indexes causing full table scans that take longer to complete

How to fix "ERROR 1020: Record has changed since last read"

1Implement transaction retry logic

The simplest solution is to catch ERROR 1020 and retry the transaction. Since the conflict is transient, a retry often succeeds:

javascript
const MAX_RETRIES = 3;
const RETRY_DELAY = 100; // ms

async function executeWithRetry(query) {
  for (let attempt = 1; attempt <= MAX_RETRIES; attempt++) {
    try {
      return await db.execute(query);
    } catch (err) {
      if (err.errno === 1020 && attempt < MAX_RETRIES) {
        await new Promise(r => setTimeout(r, RETRY_DELAY * attempt));
        continue;
      }
      throw err;
    }
  }
}

This approach works best when conflicts are occasional and retries resolve quickly.

2Upgrade MyISAM tables to InnoDB

InnoDB's MVCC eliminates ERROR 1020 entirely. Convert MyISAM tables to InnoDB:

sql
ALTER TABLE your_table ENGINE = InnoDB;

Verify the change:

sql
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your_table';

InnoDB provides:
- MVCC for consistent reads without blocking writers
- Row-level locking instead of table-level
- ACID transaction guarantees
- Better performance for concurrent workloads

This is the recommended long-term solution.

3Adjust transaction isolation level

MySQL supports four isolation levels. The default REPEATABLE READ can cause conflicts; try READ COMMITTED:

sql
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM your_table WHERE id = 123;
UPDATE your_table SET column = value WHERE id = 123;
COMMIT;

Or set it globally:

sql
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Isolation levels (from strictest to loosest):
- SERIALIZABLE: Highest consistency, lowest concurrency
- REPEATABLE READ: Default, can cause ERROR 1020
- READ COMMITTED: Lower isolation, fewer conflicts
- READ UNCOMMITTED: Dirty reads possible, rare to use

Warning: Lower isolation levels may allow dirty or phantom reads, so understand your consistency requirements first.

4Add indexes to optimize queries

Unindexed scans require reading every table row, increasing conflict likelihood:

sql
-- Before: Without index
SELECT * FROM orders WHERE customer_id = 5;

-- After: With index
CREATE INDEX idx_customer_id ON orders(customer_id);

Verify the index is being used:

sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

Look for "Using index" in the output. Indexed queries:
- Reduce scan time
- Minimize the window for concurrent modifications
- Decrease lock contention
- Improve overall performance

5Use explicit locking with SELECT FOR UPDATE

For critical operations, lock the row before reading to prevent concurrent modifications:

sql
BEGIN;
SELECT * FROM your_table WHERE id = 123 FOR UPDATE;
-- Row is now locked, no other transaction can modify it
UPDATE your_table SET column = value WHERE id = 123;
COMMIT;

This ensures:
- No other transaction can modify the locked row
- Consistent read and write operations
- No race conditions during the transaction

Trade-off: This reduces concurrency, so use only when necessary.

6Avoid long-running transactions

Long transactions increase the window for conflicts. Keep transactions short:

javascript
// Bad: Long transaction
db.beginTransaction();
const user = await db.users.findById(id);
// ... expensive operation (API call, file processing, etc.)
await db.users.update(id, userData);
db.commit();

// Good: Transaction only for database operations
const user = await db.users.findById(id);
// ... expensive operation (outside transaction)
db.beginTransaction();
await db.users.update(id, userData);
db.commit();

Principles:
- Move non-database operations outside transactions
- Commit/rollback as soon as possible
- Minimize the time locks are held
- Reduce overlap with other concurrent transactions

Advanced notes

ERROR 1020 is fundamentally a MyISAM issue. InnoDB's MVCC architecture prevents it entirely by maintaining multiple versions of each row. If you must use MyISAM (rare in modern applications), understand that concurrent writes risk ERROR 1020 even with single-connection scenarios (MySQL bug #26055 documents this edge case).

For distributed systems, application-level retry logic is often more practical than database-level changes. Implement exponential backoff to avoid retry storms during high contention.

Some frameworks (Sequelize, TypeORM) have built-in retry mechanisms for transient errors—check your ORM's documentation. MariaDB, MySQL's open-source fork, similarly exhibits this behavior on MyISAM tables.

The HANDLER statement (used for sequential table scans) is particularly prone to ERROR 1020 if the table changes between HANDLER READ commands. This is documented in MySQL source code and MariaDB documentation.

Frequently asked questions

What does "1020" mean?
MySQL Error 1020 – SQLSTATE: HY000 (ER_CHECKREAD) signals a data consistency conflict in multi-user environments. When a transaction reads a record, MySQL stores its version. If another concurrent transaction modifies that same record before the first transaction completes, MySQL detects this inconsistency and raises ERROR 1020.
How do I fix "1020"?
ERROR 1020 (ER_CHECKREAD) occurs when another transaction modifies a record between reads in a multi-user environment. This commonly affects MyISAM tables and can be resolved through retry logic, isolation level adjustments, or row-level locking.
What causes "1020"?
Another transaction modifies or deletes a record after your transaction reads it Using MyISAM storage engine, which lacks MVCC support High concurrency with multiple simultaneous readers and writers on the same table
Is the "1020" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading