Course: MySQL Training Course
Topic: Locks, Deadlocks, and Concurrent Database Access
Course Link: MySQL Training Course at JustAcademy
Demo Link: Register for a Course Demo
MySQL databases often serve multiple users and applications simultaneously. For example, one user may update a customer's address while another user processes an order for the same customer. At the same time, another application may generate a report that reads the same records.
When multiple transactions access or modify the same data, the database must coordinate their operations to maintain data consistency. MySQL uses locking mechanisms, transaction isolation, and concurrency control to manage these situations.
Three important concepts are locks, deadlocks, and concurrent database access. Locks help coordinate access to shared data. Deadlocks occur when transactions wait on one another in a circular dependency. Concurrent database access refers to multiple transactions executing during overlapping periods.
Concurrent database access occurs when two or more transactions access a database during overlapping periods. The transactions may read different records, read the same records, or attempt to modify the same data.
Concurrency allows databases to serve many users efficiently without requiring every transaction to wait for all other transactions to finish. However, simultaneous operations must be coordinated to prevent incorrect results and conflicting changes.
Suppose a product has 10 units in stock. Two customers attempt to purchase 7 units each at nearly the same time.
| Transaction | Operation | Potential Problem |
|---|---|---|
| Transaction A | Reads stock quantity as 10. | Assumes 10 units are available. |
| Transaction B | Reads stock quantity as 10. | Also assumes 10 units are available. |
| Transaction A | Reduces stock by 7. | Attempts to leave 3 units. |
| Transaction B | Reduces stock by 7. | Could produce an incorrect result if the application does not coordinate updates correctly. |
Appropriate locking and atomic updates help ensure that the database does not sell more units than are available. The exact result depends on the transaction logic, isolation level, constraints, and locking behavior.
A database lock is a mechanism that controls access to a database resource while a transaction performs an operation. A lock may protect a row, a range of index records, a table, or another database resource, depending on the storage engine and operation.
In MySQL, the InnoDB storage engine uses several types of locks to coordinate concurrent transactions. Locks help ensure that transactions follow the database's concurrency rules and prevent conflicting operations from proceeding in an unsafe manner.
Consider a bank account with a balance of ₹10,000. Transaction A starts a withdrawal while Transaction B attempts to withdraw money from the same account. A lock can make Transaction B wait until Transaction A finishes the conflicting operation.
START TRANSACTION;
SELECT account_id, balance
FROM accounts
WHERE account_id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
COMMIT;The FOR UPDATE locking read requests exclusive locks on the applicable records. For an indexed lookup that identifies one InnoDB row, this typically locks the matching record, although the exact lock scope depends on the query, indexes, isolation level, and execution plan.
MySQL uses different locking mechanisms depending on the storage engine and the type of operation being performed.
A shared lock permits a transaction to read a locked record while preventing conflicting modifications that require an exclusive lock. Multiple transactions can generally hold compatible shared locks on the same record.
In InnoDB, a locking read can request a shared lock using FOR SHARE.
START TRANSACTION;
SELECT *
FROM employees
WHERE employee_id = 101
FOR SHARE;
COMMIT;This requests shared locks on the records examined by the locking read. Other transactions may be able to obtain compatible shared locks, but a conflicting update may need to wait.
An exclusive lock is used when a transaction needs to modify a record. It prevents other transactions from acquiring incompatible locks on the same record until the lock is released.
START TRANSACTION;
SELECT *
FROM employees
WHERE employee_id = 101
FOR UPDATE;
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 101;
COMMIT;The locking read requests an exclusive lock on the applicable records. The subsequent UPDATE changes the salary, and COMMIT completes the transaction.
| Feature | Shared Lock | Exclusive Lock |
|---|---|---|
| Purpose | Coordinates locking reads. | Protects records that a transaction intends to modify. |
| Compatible shared locks | Generally allowed. | Not compatible with another shared lock on the same record. |
| Conflicting modifications | Can cause an update to wait. | Can block other conflicting reads or writes that request locks. |
| Common SQL example | SELECT ... FOR SHARE |
SELECT ... FOR UPDATE |
| Typical use | Locking data that must be read consistently before a decision. | Reading and then safely modifying selected records. |
Row-level locking allows InnoDB to lock individual index records or related index ranges rather than locking an entire table for every modification. This can improve concurrency because transactions working on unrelated records may proceed at the same time.
START TRANSACTION;
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;
COMMIT;If the query uses a suitable index to find employee 101, the update normally locks the relevant record rather than every row in the table. Additional locks may be taken depending on the query and isolation level.
A table-level lock applies to a table as a whole. Table locks can be used explicitly with SQL statements such as LOCK TABLES, and some storage engines or operations use table-level locking behavior.
LOCK TABLES employees READ;
SELECT * FROM employees;
UNLOCK TABLES;This example requests a read lock on the table for the current session. Explicit table locking has important interaction rules with transactions and storage engines. Use it carefully and consult the MySQL documentation for the installed version.
InnoDB uses intention locks to indicate that a transaction intends to acquire shared or exclusive locks on records within a table. These locks help coordinate row-level locking with certain table-level locking operations.
The main intention lock types are:
Intention locks are generally managed automatically by InnoDB rather than manually created by application developers.
A record lock protects an index record. InnoDB uses indexes as part of its locking implementation, so the index and execution plan can influence which records are locked.
A gap lock protects a gap between index records, or a gap before the first or after the last index record. Gap locking can prevent other transactions from inserting records into the protected gap under the applicable isolation and query conditions.
A next-key lock combines a record lock with a lock on the gap preceding that index record. InnoDB may use next-key locks to help prevent certain phantom rows under its default REPEATABLE READ isolation level.
| Lock Type | Purpose | Typical Context |
|---|---|---|
| Record lock | Protects an index record. | Updating or locking a matching row. |
| Gap lock | Protects a gap in an index. | Preventing inserts into certain index ranges. |
| Next-key lock | Protects a record and the preceding gap. | Range locking under applicable InnoDB isolation behavior. |
| Intention lock | Signals planned row-level locking within a table. | Coordination between row and table locking. |
InnoDB is MySQL's main transactional storage engine. It combines multi-version concurrency control (MVCC) with locking mechanisms to support concurrent reads and writes.
MVCC allows certain read operations to access a consistent version of data without always needing to wait for a transaction that is modifying the same records. InnoDB maintains information that allows consistent reads to reconstruct earlier versions of rows when necessary.
For example, a normal SELECT under a suitable isolation level can often read a consistent snapshot while another transaction has an uncommitted update. This is different from a locking read such as SELECT FOR UPDATE.
| Read Type | Example | General Behavior |
|---|---|---|
| Consistent read | SELECT * FROM employees; |
Typically reads a consistent snapshot without locking every row against updates. |
| Shared locking read | SELECT * FROM employees WHERE employee_id = 101 FOR SHARE; |
Requests shared locks on applicable records. |
| Exclusive locking read | SELECT * FROM employees WHERE employee_id = 101 FOR UPDATE; |
Requests exclusive locks on applicable records. |
The precise behavior depends on the isolation level, transaction state, query plan, and storage engine. A regular SELECT is not equivalent to a locking read.
For many InnoDB operations, locks acquired by a transaction are retained until the transaction commits or rolls back. Some statement-level and special locking behavior can differ, so applications should not assume that every lock has exactly the same lifetime.
Long transactions can keep locks for longer periods, increasing the likelihood of blocking and deadlocks. Keeping transactions short is an important performance and reliability practice.
A deadlock occurs when two or more transactions wait for resources held by one another, creating a circular waiting condition. None of the involved transactions can proceed without another transaction releasing a required lock.
InnoDB detects deadlocks and normally resolves them by rolling back one transaction, known as the deadlock victim. The other transaction can then continue if its remaining operations are valid.
Assume two bank accounts exist: account 1 and account 2.
This creates a circular wait. InnoDB can detect the deadlock and roll back one of the transactions so that the other can proceed.
| Step | Transaction A | Transaction B |
|---|---|---|
| 1 | Locks account 1. | Locks account 2. |
| 2 | Requests account 2 and waits. | Holds account 2. |
| 3 | Waiting for Transaction B. | Requests account 1 and waits. |
| 4 | Waiting for Transaction B. | Waiting for Transaction A. |
| 5 | InnoDB detects the deadlock and rolls back one transaction. | |
A deadlock and a lock wait are related but different problems.
| Feature | Deadlock | Lock Wait |
|---|---|---|
| Definition | Transactions form a circular dependency. | A transaction waits for a conflicting lock held by another transaction. |
| Can the transaction eventually proceed? | Not without resolving the circular wait. | Usually, if the blocking transaction releases the lock. |
| Typical resolution | InnoDB detects the deadlock and rolls back a victim transaction. | The blocking transaction finishes, or the waiting operation times out or is cancelled. |
| Common diagnostic | Deadlock details from InnoDB. | Performance Schema lock-wait information and timeout errors. |
A lock wait does not automatically mean a deadlock exists. A transaction may be waiting for another transaction that is performing a valid, long-running operation.
Two transactions may update the same records in opposite orders. One locks record A before record B, while the other locks record B before record A. If both transactions hold their first lock while requesting the second, a deadlock can occur.
Transactions that remain open for a long time can hold locks while performing additional work. This increases the time during which other transactions can become blocked.
If a query lacks a suitable index, MySQL may need to examine many records. The resulting locking footprint can be larger than expected for a locking read or modification statement.
When many sessions frequently update the same rows or overlapping ranges, lock contention and deadlock risk increase.
Range queries and modifications can involve several index records and, depending on isolation and query conditions, gaps between records. Overlapping ranges can increase the possibility of lock conflicts.
If different application functions access shared tables or rows in different sequences, they can create circular dependencies. Consistent access ordering helps reduce this risk.
The following exercise demonstrates how one transaction can block another. Open two separate database connections, called Session A and Session B. Run the statements in the specified order.
CREATE TABLE IF NOT EXISTS lock_demo (
id INT PRIMARY KEY,
item_name VARCHAR(100),
quantity INT NOT NULL
) ENGINE = InnoDB;
INSERT INTO lock_demo (id, item_name, quantity)
VALUES (1, 'Keyboard', 10)
ON DUPLICATE KEY UPDATE
item_name = VALUES(item_name);START TRANSACTION;
SELECT *
FROM lock_demo
WHERE id = 1
FOR UPDATE;Session A obtains an exclusive lock on the matching record and keeps the transaction open.
START TRANSACTION;
UPDATE lock_demo
SET quantity = quantity - 1
WHERE id = 1;Session B will normally wait because Session A holds a conflicting lock on the record.
COMMIT;Once Session A commits, Session B can usually continue if no other blocking condition exists. Session B must then finish its own transaction:
COMMIT;This exercise demonstrates the basic pattern that can create a deadlock. Use a test database only, because InnoDB may automatically roll back one of the transactions.
CREATE TABLE IF NOT EXISTS deadlock_demo (
id INT PRIMARY KEY,
amount INT NOT NULL
) ENGINE = InnoDB;
INSERT INTO deadlock_demo (id, amount)
VALUES (1, 100), (2, 200)
ON DUPLICATE KEY UPDATE
amount = VALUES(amount);START TRANSACTION;
UPDATE deadlock_demo
SET amount = amount + 10
WHERE id = 1;START TRANSACTION;
UPDATE deadlock_demo
SET amount = amount + 20
WHERE id = 2;UPDATE deadlock_demo
SET amount = amount + 1
WHERE id = 2;Session A waits for Session B because Session B holds the lock on record 2.
UPDATE deadlock_demo
SET amount = amount + 1
WHERE id = 1;Session B now waits for Session A. If the circular wait is established, InnoDB normally detects the deadlock and rolls back one of the transactions. The statement that receives the error depends on the timing and which transaction is selected as the victim.
One of the most effective techniques is to access shared records in the same order in every transaction. For example, if two account IDs are involved, lock the smaller ID first and the larger ID second in all related operations.
START TRANSACTION;
SELECT account_id, balance
FROM accounts
WHERE account_id = 1
FOR UPDATE;
SELECT account_id, balance
FROM accounts
WHERE account_id = 2
FOR UPDATE;
-- Perform validated updates here.
COMMIT;This example illustrates a consistent order for two known accounts. For a general application, sort the account identifiers and acquire locks in that order, and ensure all relevant code paths follow the same rule.
Indexes can help MySQL locate records efficiently and reduce unnecessary scanning. Review indexes on columns used in WHERE clauses, JOIN conditions, and ordering operations that influence locking queries.
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);Only create indexes that fit the workload. Indexes improve some operations but also add storage costs and overhead to INSERT, UPDATE, and DELETE statements.
Use precise WHERE conditions so that statements modify only the intended rows.
UPDATE products
SET quantity = quantity - 1
WHERE product_id = 101
AND quantity > 0;This statement combines the stock condition and decrement into one atomic database operation. The application should inspect the affected-row count to determine whether a unit was actually reserved. If a row was not changed, the application should handle the unavailable-stock case appropriately.
Use SELECT FOR UPDATE or SELECT FOR SHARE only when the application requires locking behavior. A normal consistent SELECT is often sufficient for reporting and read-only display operations.
Deadlocks can occur even in carefully designed systems. Applications should be prepared to retry a complete transaction when MySQL reports a deadlock, provided that retrying is safe for the operation.
When an application becomes slow or reports transaction errors, database administrators can inspect lock information to identify blocking sessions and understand the cause.
SHOW ENGINE INNODB STATUS;Review the output's LATEST DETECTED DEADLOCK section when available. It can show the transactions involved, the statements they executed, and the locks they held or requested.
The status output is a diagnostic snapshot. It may describe only the latest detected deadlock, so capture relevant details promptly when investigating recurring incidents.
SELECT
trx_id,
trx_state,
trx_started,
trx_wait_started,
trx_mysql_thread_id,
trx_query
FROM information_schema.innodb_trx;This query can show active InnoDB transactions and their state, start time, waiting time, connection thread ID, and current query when available. Access to some diagnostic information may require additional privileges.
On MySQL versions that expose these Performance Schema tables, the following query can help identify relationships between waiting and blocking transactions.
SELECT
REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
REQUESTING_THREAD_ID AS waiting_thread_id,
BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
BLOCKING_THREAD_ID AS blocking_thread_id
FROM performance_schema.data_lock_waits;The data_lock_waits table reports relationships between transactions waiting for locks and transactions blocking them. The exact diagnostic columns and available data depend on the MySQL version and configuration.
SELECT *
FROM performance_schema.data_locks;This query displays lock information exposed by Performance Schema. For large systems, filter the output by table, schema, or other relevant columns rather than retrieving every lock record.
A lock-wait timeout occurs when a transaction waits for a lock longer than the configured limit. This is different from a deadlock: a timeout does not necessarily indicate a circular dependency.
InnoDB uses the innodb_lock_wait_timeout setting to control how long many InnoDB lock waits can continue before timing out.
SELECT @@SESSION.innodb_lock_wait_timeout;SET SESSION innodb_lock_wait_timeout = 30;This sets the session's lock-wait timeout to 30 seconds, subject to the supported value range and server version.
Important: Increasing the timeout does not fix the underlying cause of blocking. It may simply make a waiting application request wait longer. Investigate why the blocking transaction is taking so long before changing timeout settings.
A lost update can occur when two transactions read the same initial value and then overwrite one another's changes in a way that causes one update to be lost. The exact risk depends on the SQL statements and transaction isolation behavior.
Imagine a product quantity of 10. An application reads the quantity into application memory, subtracts one, and later writes the calculated value back. Two application requests can both read 10 and both attempt to write 9, even though two units were intended to be removed.
UPDATE products
SET quantity = quantity - 1
WHERE product_id = 101
AND quantity > 0;This lets MySQL perform the conditional decrement as a single statement. The application should verify the affected-row count to confirm whether the update succeeded.
START TRANSACTION;
SELECT quantity
FROM products
WHERE product_id = 101
FOR UPDATE;
-- Application validates the quantity.
UPDATE products
SET quantity = quantity - 1
WHERE product_id = 101
AND quantity > 0;
COMMIT;Use this pattern when the business logic needs to inspect the locked value before deciding what to do. In production, check the update result and roll back if validation fails. A single conditional UPDATE may be simpler when no additional decision-making is required.
Transaction isolation levels influence which changes one transaction can observe while another transaction is running. They also affect locking behavior and the possibility of certain anomalies.
| Isolation Level | General Behavior | Concurrency Consideration |
|---|---|---|
| READ UNCOMMITTED | Can permit dirty reads. | Less restrictive read behavior, but weaker consistency. |
| READ COMMITTED | Consistent reads see data committed before each read begins. | Can reduce some gap-locking behavior compared with REPEATABLE READ, with exceptions. |
| REPEATABLE READ | Repeated consistent reads in a transaction generally use the same snapshot. | InnoDB uses next-key locking in applicable locking operations to prevent certain phantom inserts. |
| SERIALIZABLE | Uses stricter rules to make transactions behave more like a serial execution. | Can increase blocking and reduce concurrency. |
Isolation levels do not eliminate the need for correct transaction logic. Choose an isolation level based on the application's consistency requirements and performance characteristics, and test it under realistic concurrent workloads.
Index design can strongly influence concurrency. InnoDB locks index records and, under some conditions, ranges of records. An inefficient query may examine more index records than expected and increase the scope of locking.
UPDATE orders
SET status = 'Processing'
WHERE customer_id = 5001;If customer_id does not have a suitable index, MySQL may need to examine many rows to find matching orders. The locking footprint and execution cost can be larger than necessary.
CREATE INDEX idx_orders_customer
ON orders(customer_id);An index can improve lookup performance and help limit the records examined. Evaluate the table's existing indexes and query plan before creating an additional index.
EXPLAIN
UPDATE orders
SET status = 'Processing'
WHERE customer_id = 5001;EXPLAIN support and output for data-modification statements depend on the MySQL version. Where supported, use it to inspect the execution plan. Otherwise, review the equivalent SELECT plan and use suitable performance diagnostics.
Applications must assume that deadlocks can happen under concurrent workloads, even when database code follows good practices. A robust application recognizes the error and retries safely when appropriate.
for attempt in retry_limit:
begin_transaction()
try:
perform_database_updates()
validate_results()
commit_transaction()
break
catch deadlock_error:
rollback_if_needed()
if attempt_is_last:
raise_error()
wait_for_short_backoff()
catch other_error:
rollback_if_needed()
raise_error()This is illustrative pseudocode rather than executable SQL. The application's database driver should identify the relevant error codes and handle transaction cleanup correctly.
A transaction retry may execute application logic more than once. Avoid sending the same payment request, email, or external notification repeatedly just because the database transaction was retried. Use idempotency keys, an outbox pattern, or another suitable design for external side effects.
| Problem | Possible Cause | Recommended Solution |
|---|---|---|
| UPDATE waits for a long time. | Another transaction holds a conflicting lock. | Identify the blocking transaction and reduce unnecessary transaction duration. |
| Deadlock error occurs. | Transactions acquired locks in conflicting orders. | Use consistent access ordering and retry the complete transaction safely. |
| Unexpectedly many rows are locked. | A query examines a large range or lacks a suitable index. | Review the query plan, indexes, isolation level, and locking requirements. |
| Application requests time out. | Lock waits, long transactions, or other resource pressure. | Inspect lock-wait diagnostics and improve transaction design. |
| Inventory becomes incorrect. | Application-level read-modify-write logic is not safely coordinated. | Use atomic conditional updates or an appropriate locking transaction. |
| Transaction locks remain active. | The application did not commit or roll back promptly. | Ensure every transaction path handles success and failure correctly. |
| Deadlocks recur frequently. | High contention, inconsistent ordering, range locks, or inefficient queries. | Inspect deadlock reports and redesign the affected operations. |
Create a table with an InnoDB storage engine. Open two sessions, lock the same record with SELECT FOR UPDATE in the first session, and try to update it in the second session. Observe how the second operation waits.
Use SELECT FOR SHARE in two sessions and observe how compatible shared locks behave. Then attempt an update and observe the effect of the existing locks.
Use two sessions to update two records in opposite orders. Observe the deadlock error and examine the latest deadlock details using SHOW ENGINE INNODB STATUS.
Keep one transaction open while another waits for a conflicting lock. Inspect active transactions and Performance Schema lock-wait information.
Run an UPDATE that filters on a non-indexed column, inspect its execution plan, add an appropriate index if justified, and compare the results.
Create a products table and implement a conditional decrement that succeeds only when stock is available. Verify the affected-row count and test simultaneous purchase attempts.
Project Objective: Develop a small inventory workflow that safely handles multiple customers attempting to purchase the same product.
CREATE TABLE IF NOT EXISTS inventory_products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
stock_quantity INT NOT NULL
) ENGINE = InnoDB;
INSERT INTO inventory_products
VALUES (1, 'Wireless Mouse', 10)
ON DUPLICATE KEY UPDATE
product_name = VALUES(product_name);
START TRANSACTION;
UPDATE inventory_products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
AND stock_quantity > 0;
-- Application code checks the affected-row count.
-- If one row was updated, the reservation succeeded.
-- If zero rows were updated, roll back or handle
-- the unavailable-stock result as appropriate.
COMMIT;
SELECT * FROM inventory_products;This SQL illustrates the database operation. In a production application, the client must inspect the affected-row count before deciding to commit. If the update fails to reserve stock, the application should handle that result without treating it as a successful purchase.
A database lock is a mechanism that coordinates access to a resource so that concurrent transactions do not perform conflicting operations unsafely.
It occurs when multiple transactions access or modify database data during overlapping periods.
Shared locks allow compatible locking reads, while exclusive locks protect records for conflicting modifications and are incompatible with other locks that conflict with them.
A deadlock occurs when transactions wait for resources held by one another in a circular dependency.
InnoDB detects deadlocks and normally rolls back one transaction so that the remaining transaction can proceed.
It occurs when a transaction waits for a lock longer than the configured timeout. It does not necessarily indicate a deadlock.
It is a locking read that requests exclusive locks on the applicable records, allowing a transaction to read data it intends to modify safely.
It is a locking read that requests shared locks on applicable records.
Multi-Version Concurrency Control allows certain reads to access consistent versions of data without always blocking on concurrent modifications.
A record lock protects an index record from conflicting operations.
A gap lock protects a gap between index records and can prevent inserts into that gap under applicable conditions.
A next-key lock combines a record lock with a lock on the gap preceding the record.
Use consistent lock ordering, keep transactions short, create appropriate indexes, and avoid unnecessary locking.
SHOW ENGINE INNODB STATUS;Review the latest detected deadlock section when it is available.
Use Performance Schema tables such as data_locks and data_lock_waits, along with active transaction information.
Not always. Even well-designed systems can experience deadlocks under certain concurrent workloads. Applications should minimize their frequency and handle them safely.
Indexes can help MySQL locate matching records efficiently and reduce unnecessary scanning, which can improve performance and reduce some forms of lock contention.
A lost update occurs when concurrent changes overwrite one another in a way that causes an intended modification to disappear.
Short transactions reduce lock duration, lower contention, and decrease the chance of blocking other transactions.
It should treat the affected transaction as failed and, when safe, retry the complete logical transaction with bounded retries and appropriate error handling.
Locks, deadlocks, and concurrent database access are essential concepts for developing reliable MySQL applications. Locks coordinate access to shared data, while concurrency control allows multiple transactions to work efficiently without compromising the database's consistency rules. InnoDB combines locking and MVCC to support concurrent operations.
Deadlocks can occur when transactions acquire resources in conflicting orders. Developers can reduce their frequency by using consistent locking sequences, suitable indexes, precise queries, and short transactions. They should also monitor lock waits, inspect deadlock reports, and implement safe retry logic for failed transactions.
By understanding these techniques, developers can build database applications that handle concurrent users more reliably and maintain data integrity under demanding workloads.
Explore the Course: Learn SQL, database management, and practical MySQL concepts through the JustAcademy MySQL Training Course.
Register for a Demo: Visit JustAcademy Course Demo Registration to learn more about the training.
