What is database locking in SQL?

Database locking in SQL is a mechanism used to control concurrent access to data and maintain data consistency when multiple users or transactions access the same database simultaneously.

In simple words:

Locking prevents multiple users from modifying the same data at the same time in a conflicting way.

Why Database Locking is Important

Enterprise systems often have:

Thousands of simultaneous users
Concurrent transactions
Real-time updates
Critical financial operations

Without locking:

Data corruption may occur
Transactions may overwrite each other
Inconsistent data may appear

Database Locking Solves These Problems

By:

Controlling access to shared data

Simple Real-Life Example

Think about:

A hotel room booking system

Problem Without Locking

Two users book:

Same room simultaneously

Result

Double booking issue

With Locking

When one user starts booking:

Room becomes temporarily locked

Result

Other users must wait
Data consistency maintained

Database Locking Works Similarly

Database temporarily restricts:

Access to data being modified

Database Locking Internal Architecture

Transaction Starts
       |
       v
Lock Acquired
       |
       v
Data Access/Modification
       |
       v
Transaction Complete
       |
       v
Lock Released

Main Purpose of Database Locking

Maintain data integrity
Prevent conflicting updates
Support concurrent transactions
Ensure transaction isolation

What Happens Without Locking?

Suppose account balance:

User A Withdraws

-500

User B Withdraws Simultaneously

-700

Without Proper Locking

Both may read:

Result

Incorrect balance calculation

With Locking

One transaction waits
Data remains consistent

Main Types of Database Locks

Shared Lock
Exclusive Lock
Update Lock
Intent Lock

1. Shared Lock (S Lock)

Used for:

Read operations

Behavior

Multiple transactions can read simultaneously
Modification is blocked

Example

SELECT *

FROM accounts;

Result

Rows may receive shared lock

2. Exclusive Lock (X Lock)

Used for:

INSERT
UPDATE
DELETE

Behavior

Only one transaction allowed
Others cannot read/write conflicting data

Example

UPDATE accounts

SET balance = balance - 500

WHERE account_id = 1;

Result

Exclusive lock applied

3. Update Lock

Used when:

Data may later be modified

Purpose

Reduce deadlock chances

4. Intent Lock

Indicates:

Future lower-level locks may occur

Used For

Hierarchical locking management

Granularity of Locks

Locks can apply at different levels:

Row-level lock
Page-level lock
Table-level lock
Database-level lock

1. Row-Level Lock

Locks:

Specific rows only

Advantage

Better concurrency

2. Table-Level Lock

Locks:

Entire table

Advantage

Simpler management

Disadvantage

Lower concurrency

Locking Query Flow

Transaction Begins
       |
       v
Check Lock Availability
       |
       v
Acquire Lock
       |
       v
Execute Query
       |
       v
Commit/Rollback
       |
       v
Release Lock

What is Lock Contention?

Lock contention occurs when:

Multiple transactions compete for same lock

Result

Waiting transactions
Performance slowdown

What is Deadlock?

Deadlock occurs when:

Two transactions wait for each other forever

Example

Transaction A locks Row 1
Transaction B locks Row 2

A waits for Row 2
B waits for Row 1

Result

Deadlock situation

Deadlock Resolution

Database usually:

Terminates one transaction automatically

Locking vs MVCC

Feature	Locking	MVCC
Concurrency	Controlled using locks	Uses multiple row versions
Read Blocking	Possible	Reduced
Performance	May reduce concurrency	Better read concurrency

What is MVCC?

MVCC stands for:

Multi-Version Concurrency Control

Used By

PostgreSQL
Oracle
MySQL InnoDB

Advantages of Database Locking

Maintains consistency
Prevents data corruption
Supports concurrent users
Ensures transaction isolation

Disadvantages of Database Locking

Deadlocks possible
Lock contention
Performance overhead
Reduced concurrency in heavy systems

Database Locking in Banking Systems

Banking systems heavily rely on locking for:

Account transactions
Balance updates
Fund transfers

Why Important?

Financial consistency is critical

Database Locking in E-Commerce

E-commerce systems use locking for:

Inventory management
Order processing
Payment transactions

Example

Prevent overselling products

Database Locking in Learning Platforms

Learning systems use locking for:

Exam submissions
Assessment updates
Student result processing

Database Locking in Microservices

Microservices architectures use locking for:

Distributed transactions
Inventory reservation
Payment consistency

Popular Databases Supporting Locking

MySQL
PostgreSQL
Oracle
SQL Server
MariaDB

MySQL Locking Example

SELECT *

FROM accounts

WHERE account_id = 1

FOR UPDATE;

Purpose

Locks selected rows for update

PostgreSQL Locking Example

SELECT *

FROM orders

FOR UPDATE;

Best Practices

Keep transactions short
Use proper indexes
Access tables in consistent order
Avoid unnecessary locking
Handle deadlocks gracefully

Common Interview Mistake

Many developers think:

Locking completely prevents concurrency

Reality

Locking:

Controls concurrency safely
Allows multiple safe concurrent transactions

Professional Interview Answer

Database locking in SQL is a concurrency control mechanism used to maintain data consistency and transaction isolation when multiple users or transactions access the same data simultaneously. Locks temporarily restrict access to database resources during transactional operations such as SELECT, INSERT, UPDATE, and DELETE. Common lock types include shared locks for reading, exclusive locks for writing, update locks, and intent locks. Locking helps prevent data corruption, dirty reads, lost updates, and inconsistent transactions while supporting safe concurrent access in enterprise systems. Banking systems, e-commerce platforms, ERP systems, analytics applications, and microservices architectures rely heavily on database locking to ensure reliable transactional behavior and data integrity.

Why Interviewers Like This Answer

Clearly explains concurrency control
Includes lock types understanding
Explains deadlocks and contention
Mentions transactional consistency
Provides enterprise-level examples

Frequently Asked Questions