What is Record Lock in MySQL: Explanation and Examples

A record lock in MySQL is a mechanism that prevents multiple transactions from modifying the same row of data at the same time. It ensures data consistency by locking individual rows during updates or deletes until the transaction completes.

How It Works

Imagine a library where only one person can read a book at a time to avoid confusion. Similarly, a record lock in MySQL locks a specific row in a table so that only one transaction can change it at once. This prevents conflicts when multiple users try to update the same data simultaneously.

When a transaction locks a record, other transactions that want to modify that same record must wait until the lock is released. This lock is held until the transaction finishes by either committing (saving changes) or rolling back (discarding changes). This way, MySQL keeps data accurate and avoids errors caused by overlapping changes.

Example

This example shows how a record lock works using transactions and the SELECT ... FOR UPDATE statement to lock a row before updating it.

sql

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Output

Query OK, 1 row affected (for UPDATE lock acquired)
Query OK, 1 row affected (update applied)
Query OK, 0 rows affected (commit successful)

When to Use

Use record locks when you need to safely update or delete specific rows in a table without interference from other transactions. This is common in banking systems, inventory management, or any application where multiple users might change the same data at the same time.

For example, when transferring money between accounts, locking the involved account rows prevents incorrect balances caused by simultaneous updates. Record locks help maintain data integrity and prevent race conditions in concurrent environments.

Key Points

• Record locks prevent multiple transactions from changing the same row simultaneously.
• They are held until the transaction commits or rolls back.
• SELECT ... FOR UPDATE is commonly used to acquire record locks.
• Record locking helps maintain data consistency in multi-user environments.

Key Takeaways

Record locks in MySQL protect individual rows during updates or deletes to avoid conflicts.

Locks are held until the transaction finishes, ensuring data consistency.

Use SELECT ... FOR UPDATE to explicitly lock rows before modifying them.

Record locking is essential in applications with concurrent data access to prevent errors.