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

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

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.

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

ALTER TABLE your_table ENGINE = InnoDB;

Verify the change:

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.

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

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:

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.

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

-- 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:

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

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

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.

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

// 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