Introduction

Transactions and table locks both help protect data, but they solve different concurrency problems. In modern MySQL applications, especially with InnoDB, transactions are usually the right default because they support commit, rollback, and fine-grained row locking. Explicit table locking is a heavier tool that is mostly reserved for special cases.

What a Transaction Gives You

A transaction groups several SQL statements into one logical unit of work. The database can commit them together or roll them back together.

1START TRANSACTION;
2
3UPDATE accounts
4SET balance = balance - 100
5WHERE id = 1;
6
7UPDATE accounts
8SET balance = balance + 100
9WHERE id = 2;
10
11COMMIT;

If something fails between the two updates, you can roll back and avoid leaving the balances inconsistent.

Transactions also integrate with isolation levels and row-level locks. In InnoDB, that means concurrent sessions often block only the specific rows involved, not the entire table.

Row Locks Inside Transactions

When you need to prevent concurrent updates to the same records, use locking reads such as FOR UPDATE.

1START TRANSACTION;
2
3SELECT balance
4FROM accounts
5WHERE id = 1
6FOR UPDATE;
7
8UPDATE accounts
9SET balance = balance - 100
10WHERE id = 1;
11
12COMMIT;

This is usually what developers actually need when they say they want locking. They want consistency for a specific set of rows, not a global freeze on the whole table.

What LOCK TABLES Does

LOCK TABLES explicitly locks whole tables for the current connection.

1LOCK TABLES inventory WRITE;
2
3UPDATE inventory
4SET quantity = quantity - 1
5WHERE sku = 'ABC-123';
6
7UNLOCK TABLES;

This can block other sessions from reading or writing depending on the lock type. It is much more disruptive to concurrency than a normal InnoDB transaction.

Table locks can still be useful in narrow cases such as legacy workflows, bulk maintenance operations, or engines that do not provide transactional row-level behavior the same way InnoDB does.

When to Prefer Transactions

Prefer transactions when:

• you need commit and rollback
• you are modifying a known set of rows
• concurrency matters
• you want the database engine to manage row-level locking efficiently

This is the common case for order placement, payments, inventory updates, and most application logic.

When Table Locking Might Still Make Sense

Explicit table locks may still appear in:

• old code built around non-transactional assumptions
• administrative maintenance tasks
• narrow workflows where you intentionally want exclusive table access

Even then, use them carefully. Table locks reduce concurrency quickly and are easy to overuse.

The Main Design Difference

A transaction is about correctness and atomicity over a set of statements. A table lock is about blocking other sessions from touching a table in certain ways.

They are not interchangeable. In InnoDB-backed applications, transactions usually provide the safer and more scalable solution.

Common Pitfalls

• Using LOCK TABLES when a normal transaction with row locks would be enough.
• Forgetting that table locks can block unrelated work and hurt concurrency.
• Assuming a transaction automatically locks the whole table.
• Mixing storage-engine assumptions, especially in older systems.
• Designing application logic around manual locking instead of proper transaction boundaries.

Summary

• Transactions are the standard tool for atomic multi-statement work in MySQL.
• InnoDB transactions usually rely on row-level locking, which is more concurrent than table locking.
• 'LOCK TABLES is a heavier mechanism that blocks broader access.'
• Use transactions for most application workflows.
• Reserve explicit table locks for special maintenance or legacy cases where whole-table coordination is truly required.