Database Management SystemsLock Granularity

Lock Granularity

LevelIntermediate

Duration75 mins

TopicLock Granularity

4 / 5

Database-Level Locks

Locking the Entire Database

At the extreme end of the lock granularity spectrum lies the database-level lock—a lock that encompasses an entire database instance, affecting every table, every row, and every operation within that database.

Database-level locks are rare in normal application workloads. You won't encounter them in typical transaction processing. But they are critical for system-level operations: backup and restore, database migrations, major version upgrades, and catastrophic recovery scenarios.

Consider these scenarios where database-level coordination is essential:

A backup tool needs a consistent snapshot of the entire database while applications continue running
A migration script must ensure no writes occur while relocating the database to new storage
A failover system needs to guarantee that the old primary has completely stopped accepting writes
A crash recovery process must prevent access while rebuilding transaction logs

Understanding database-level locks helps you plan for maintenance windows, design backup strategies, and troubleshoot scenarios where the entire database becomes unresponsive.

What You Will Learn

By the end of this page, you will understand: (1) What database-level locks are and when they're used, (2) How different databases implement database-wide coordination, (3) Backup and restore locking implications, (4) Single-user mode and exclusive database access, and (5) Best practices for operations requiring database-level coordination.

Understanding Database-Level Locks

A database-level lock is a lock that covers an entire database instance. When active, it controls access to all objects within that database—every table, view, stored procedure, and piece of data.

Formal Definition:

Let D be a database containing tables T₁, T₂, ..., Tₙ. A database-level lock on D grants or restricts access to all tables (and their rows) simultaneously. No finer-grained operation within D can proceed without compatibility with the database-level lock.

Key Characteristics:

Database-Level Lock Characteristics

•Maximum Scope: A single lock covers the entire database—all schemas, tables, rows, and metadata.
•System-Level Granularity: Beyond application transactions; used for administrative operations.
•Rare in Normal Operations: Most applications never explicitly acquire database-level locks.
•Implicit in Many Admin Tasks: Backups, restores, and maintenance operations implicitly acquire database-wide coordination.
•Connection-Level Impact: Often implemented by controlling which connections can access the database.

Implementation Approaches:

Different databases implement database-level coordination in various ways:

Explicit Database Locks: Some databases (e.g., SQL Server) support explicit database lock modes.
Single-User Mode: A database mode where only one connection is allowed.
Offline Status: Setting a database offline completely prevents all access.
Global Read Locks: System-wide read locks that prevent all writes (used for consistent backups).
Connection Denial: Preventing new connections while existing ones complete.

Example: SQL Server Database Lock Modes:

ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Now only one connection can access ProductionDB
-- All other connections are terminated

-- Perform exclusive operation
RESTORE DATABASE ProductionDB FROM DISK = 'backup.bak' WITH REPLACE;

-- Return to multi-user mode
ALTER DATABASE ProductionDB SET MULTI_USER;

Extreme Serialization

Database-level locks represent the ultimate serialization—access to the entire database is controlled by a single lock. This is appropriate only for administrative operations that genuinely require exclusive access. Using database-level locks for application logic indicates a serious design flaw.

SQL Server Database Access Modes

Microsoft SQL Server provides explicit database access modes that control how many users can connect to a database simultaneously.

Database User Access Modes:

SQL Server Database Access Modes
Mode	Description	Use Case
MULTI_USER	Default mode. Any number of users can connect.	Normal operation
SINGLE_USER	Only one connection at a time. Others are rejected.	Restore, major maintenance
RESTRICTED_USER	Only db_owner, dbcreator, and sysadmin can connect.	Migration, schema changes

SQL Server Database Access Control
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Check current database access mode
SELECT name, user_access_desc 
FROM sys.databases 
WHERE name = 'ProductionDB';
 
-- Switch to single-user mode
-- ROLLBACK IMMEDIATE: Kill all existing connections immediately
-- ROLLBACK AFTER n SECONDS: Wait up to n seconds
-- NO_WAIT: Fail if connections exist
ALTER DATABASE ProductionDB 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
-- Perform exclusive operation
DBCC CHECKDB('ProductionDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
 
-- Return to multi-user
ALTER DATABASE ProductionDB SET MULTI_USER;
 
-- Restricted user mode: Only admins can connect
ALTER DATABASE ProductionDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
 
-- View active connections before switching mode
SELECT 
    session_id,
    login_name,
    program_name,
    client_interface_name
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('ProductionDB');
 
-- Kill specific session if needed
KILL 52;  -- Kill session ID 52

Database States:

Beyond access modes, SQL Server has database states that affect accessibility:

SQL Server Database States
State	Description	Cause
ONLINE	Database is available for access	Normal operation
OFFLINE	Database is not accessible; must be explicitly brought online	Administrative action
RESTORING	Database is being restored from backup	RESTORE operation in progress
RECOVERING	Database is running crash recovery	Server startup, restore completion
SUSPECT	Database may be corrupted	Corruption or hardware failure
EMERGENCY	Database in emergency mode for repairs	DBA intervention for corruption

SQL Server Database State Control
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Take database offline (no access allowed)
ALTER DATABASE ProductionDB SET OFFLINE;
 
-- Bring database online
ALTER DATABASE ProductionDB SET ONLINE;
 
-- Check database state
SELECT name, state_desc, user_access_desc
FROM sys.databases
WHERE name = 'ProductionDB';
 
-- Emergency mode: Allows reading corrupt database for data recovery
ALTER DATABASE CorruptDB SET EMERGENCY;
SELECT * FROM CorruptDB.dbo.critical_data;  -- Attempt to read data
-- Then run DBCC CHECKDB with REPAIR options

Single-User Mode Gotcha

When you switch to SINGLE_USER mode, the first connection to attempt access claims the single slot. If SSMS or another tool connects before your maintenance script, you'll be locked out of your own database. Use RESTRICTED_USER for more controlled access, or ensure scripts connect immediately after mode change.

PostgreSQL Database-Level Control

PostgreSQL approaches database-level coordination differently from SQL Server, using connection limits and explicit connection prevention rather than explicit database locks.

Connection Limiting:

PostgreSQL Connection Control
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Limit connections to a database (0 = disallow all new connections)
ALTER DATABASE productiondb CONNECTION LIMIT 0;
 
-- Allow only 5 connections
ALTER DATABASE productiondb CONNECTION LIMIT 5;
 
-- Allow unlimited connections (default)
ALTER DATABASE productiondb CONNECTION LIMIT -1;
 
-- Check current settings
SELECT datname, datconnlimit 
FROM pg_database 
WHERE datname = 'productiondb';
 
-- Check active connections to the database
SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE datname = 'productiondb';
 
-- Terminate all connections to a specific database  
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'productiondb' 
  AND pid <> pg_backend_pid();  -- Don't kill yourself

Preventing New Connections:

PostgreSQL uses the datallowconn catalog field to control whether new connections are allowed:

PostgreSQL Connection Prevention
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Prevent new connections (existing connections remain)
UPDATE pg_database 
SET datallowconn = false 
WHERE datname = 'productiondb';
 
-- Allow connections again
UPDATE pg_database 
SET datallowconn = true 
WHERE datname = 'productiondb';
 
-- Comprehensive approach: Block new connections + terminate existing
-- Step 1: Block new connections
UPDATE pg_database SET datallowconn = false WHERE datname = 'productiondb';
 
-- Step 2: Terminate existing connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'productiondb';
 
-- Step 3: Perform maintenance (from template1 or postgres database)
-- e.g., DROP DATABASE productiondb;
 
-- Step 4: Restore access
UPDATE pg_database SET datallowconn = true WHERE datname = 'productiondb';

Global Read Lock for Backups:

While PostgreSQL doesn't have explicit database-level locks, it provides mechanisms for consistent backups without blocking normal operations:

PostgreSQL Backup Considerations
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- pg_dump uses transaction snapshot for consistency
-- No explicit locking required; uses MVCC
 
-- For full cluster backup, pg_basebackup uses checkpoint and WAL
pg_basebackup -D /backup/data -Fp -Xs -P
 
-- Logical replication slot holds WAL for consistent reads
SELECT pg_create_logical_replication_slot('backup_slot', 'pgoutput');
 
-- Advisory locks for application-level coordination
-- Not database-level, but useful for coordinating maintenance
SELECT pg_advisory_lock(12345);  -- Application agrees this means "maintenance mode"
-- Perform maintenance
SELECT pg_advisory_unlock(12345);
 
-- Check if advisory lock is held
SELECT pg_try_advisory_lock(12345);  -- Returns true if acquired, false if held

PostgreSQL Philosophy

PostgreSQL's MVCC architecture reduces the need for database-level locks. Backups use transaction snapshots and don't block writers. DDL uses per-object locks. Full database exclusivity is achieved through connection control rather than explicit locks. This design enables near-zero-downtime operations but requires more manual coordination for exclusive access.

MySQL Global Locks

MySQL provides explicit global lock mechanisms that affect the entire server instance, not just a single database. These are primarily used for consistent backups.

FLUSH TABLES WITH READ LOCK (FTWRL):

The classic MySQL approach to consistent backups:

MySQL Global Read Lock
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Acquire global read lock
-- This blocks ALL writes across ALL databases on the server
FLUSH TABLES WITH READ LOCK;
 
-- At this point:
-- - All open tables are flushed to disk
-- - A global read lock is held
-- - No INSERT, UPDATE, DELETE can proceed anywhere
-- - All writes queue up, waiting
 
-- Perform backup (e.g., copy data files)
-- Typically run: mysqldump --all-databases > backup.sql
-- Or: xtrabackup / physical file copy
 
-- Release the lock
UNLOCK TABLES;
 
-- Alternative: Lock specific tables for backup
LOCK TABLES orders READ, customers READ, products READ;
-- Backup these tables
UNLOCK TABLES;

LOCK INSTANCE FOR BACKUP (MySQL 8.0+):

MySQL 8.0 introduced a less disruptive backup lock:

MySQL 8.0 Backup Lock
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Lock instance for backup (MySQL 8.0+)
-- Blocks DDL and PURGE BINARY LOGS, but allows DML
LOCK INSTANCE FOR BACKUP;
 
-- At this point:
-- - SELECT, INSERT, UPDATE, DELETE continue normally
-- - DDL (CREATE, ALTER, DROP) is blocked
-- - Undo log purging is paused
-- - Redo log archiving continues
 
-- This is sufficient for consistent XtraBackup / Enterprise Backup
 
-- When backup is complete
UNLOCK INSTANCE;
 
-- Check if backup lock is held
SELECT * FROM performance_schema.metadata_locks 
WHERE LOCK_TYPE = 'BACKUP_LOCK';

MySQL Lock Comparison
Lock Type	Blocks Writes	Blocks DDL	Blocks Reads	Use Case
FLUSH TABLES WITH READ LOCK	Yes	Yes	No	Traditional full backup
LOCK INSTANCE FOR BACKUP	No	Yes	No	Hot backup (MySQL 8.0+)
LOCK TABLES ... READ	Selected tables only	No	No	Selective table backup
READ_ONLY mode	All non-SUPER users	Yes (implicit)	No	Maintenance mode

MySQL Read-Only Mode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Set server to read-only mode
-- Only users with SUPER privilege can write
SET GLOBAL read_only = ON;
 
-- For true read-only (blocks even SUPER users)
SET GLOBAL super_read_only = ON;
 
-- Check status
SHOW VARIABLES LIKE '%read_only%';
 
-- Return to normal
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;
 
-- Use in replication: Ensure replica doesn't accept writes
-- Typically done via my.cnf:
-- read_only = 1
-- super_read_only = 1

FTWRL Production Impact

FLUSH TABLES WITH READ LOCK blocks ALL writes across the entire MySQL server—not just one database. On a busy server, this can cause massive queue buildup within seconds. For production systems, use LOCK INSTANCE FOR BACKUP (MySQL 8.0+), MySQL Enterprise Backup, or Percona XtraBackup, which support true hot backups.

Backup and Restore Implications

Database backup and restore operations are the most common scenarios requiring database-level coordination. Understanding the locking implications helps plan for minimal downtime.

Backup Locking Spectrum:

Backup Approaches by Lock Impact

•Cold Backup (Full Lock): Database offline during backup. 100% consistent, 100% downtime. Example: File copy of offline database.
•Warm Backup (Read Lock): Database readable but not writable. Consistent snapshot without downtime for readers. Example: FLUSH TABLES WITH READ LOCK + mysqldump.
•Hot Backup (MVCC/Snapshot): Database fully operational. Backup reads consistent snapshot via MVCC. Example: pg_dump, SQL Server backup with transaction log.
•Continuous Replication: No locking; backup from replica or continuous log shipping. Example: Read from replica, point-in-time recovery from logs.

Database-Specific Backup Lock Behavior:

Backup Lock Behavior by Database
Database	Backup Method	Lock Type	Blocks Writes
PostgreSQL	pg_dump	None (MVCC snapshot)	No
PostgreSQL	pg_basebackup	None (checkpoint + WAL)	No
SQL Server	BACKUP DATABASE	None (log-based)	No
SQL Server	Copy-only backup	None	No
MySQL	mysqldump (default)	Per-table locks	Briefly per table
MySQL	mysqldump --single-transaction	None (InnoDB snapshot)	No
MySQL	XtraBackup	Backup lock or brief FTWRL	Briefly at end
Oracle	RMAN	None (read consistency)	No

Restore Locking:

Restore operations typically require exclusive database access:

Restore Lock Requirements
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- SQL Server: Restore requires single-user or restricted
ALTER DATABASE TargetDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE TargetDB FROM DISK = 'C:\Backup\TargetDB.bak' WITH REPLACE;
ALTER DATABASE TargetDB SET MULTI_USER;
 
-- PostgreSQL: Can restore to new database (no lock on existing)
createdb new_target_db
pg_restore -d new_target_db backup.dump
 
-- PostgreSQL: Restore over existing requires no connections
-- Terminate connections first
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'target_db';
dropdb target_db
createdb target_db
pg_restore -d target_db backup.dump
 
-- MySQL: Restore typically creates new tables or requires explicit DROP
mysql target_db < backup.sql  -- Creates tables (may fail if exist)
-- Or with DROP statements in backup:
mysqldump --add-drop-table ... > backup.sql
mysql target_db < backup.sql

Blue-Green for Zero-Downtime Restore

For zero-downtime restores, use a blue-green approach: restore to a new database while the old one serves traffic, then switch application connections when the restore completes. This avoids any production locking but requires additional storage and connection management.

Replication and Failover Coordination

Database-level coordination is critical in replication and failover scenarios. Ensuring exactly one primary accepts writes at any time is fundamental to data consistency.

The Split-Brain Problem:

In distributed databases, split-brain occurs when two nodes both believe they are the primary and accept writes independently. This causes data divergence that can be impossible to reconcile.

Split-Brain Scenario:

Time 0: Node A is primary, Node B is replica
Time 1: Network partition—A and B cannot communicate
Time 2: Monitoring system thinks A is dead, promotes B to primary
Time 3: Reality—A is still running and accepting writes
Time 4: Network heals—A and B both have different writes
→ Data is now inconsistent, manual intervention required

Prevention via Database-Level Locks:

Failover Coordination
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- SQL Server: Before demoting old primary
-- Ensure no writes can proceed
ALTER DATABASE ProductionDB SET READ_ONLY;
-- Or more aggressively:
ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
-- Then allow new primary to take over
 
-- PostgreSQL: Standby promotion
-- On old primary, ensure it's fenced (cannot accept writes)
-- Option 1: Stop the service
pg_ctl stop -D /var/lib/postgresql/data -m fast
 
-- Option 2: Make read-only via config
-- Set default_transaction_read_only = on
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();
 
-- On standby, promote to primary
pg_ctl promote -D /var/lib/postgresql/data
-- Or use pg_promote() function (PG 12+)
SELECT pg_promote();
 
-- MySQL: Read-only for failover coordination
-- On old primary:
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;
 
-- On new primary (after replication catches up):
SET GLOBAL read_only = OFF;

Fencing Mechanisms:

Beyond database settings, robust failover systems use additional fencing mechanisms:

Fencing Approaches

•STONITH (Shoot The Other Node In The Head): Physically power off the old primary via IPMI/iLO/management interface to guarantee it cannot accept writes.
•Network Isolation: Configure network switches to block all traffic to the old primary.
•Disk Reservation: Shared storage allows only one server to write (SCSI-3 Persistent Reservations).
•VIP Takeover: Move the Virtual IP address to the new primary; clients cannot reach old primary.
•Write Blocking at Database Level: Set database to read-only before promoting new primary.

Read-Only Is Not Enough

Setting a database to read-only provides write blocking, but doesn't address split-brain if the network partition prevents the setting from being applied. Production failover systems must combine database-level coordination with external fencing (STONITH, network isolation) for true safety.

Best Practices and Patterns

Database-level locks should be used sparingly and with careful planning. The following best practices help minimize impact while achieving necessary coordination.

Planning for Exclusive Access:

Exclusive Access Best Practices

•Schedule Maintenance Windows: Plan exclusive operations for periods of low activity.
•Communicate Downtime: Notify stakeholders before operations requiring database-level locks.
•Prepare Scripts in Advance: Test all commands on non-production systems first.
•Set Timeouts: Configure statement and lock timeouts to fail fast if something goes wrong.
•Monitor Connection Queues: Watch for connection buildup during maintenance operations.
•Have Rollback Plans: Know how to quickly restore normal access if problems occur.

Minimizing Lock Duration:

Minimizing Exclusive Lock Duration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Pattern: Minimize time in single-user mode
-- Step 1: Prepare everything before acquiring exclusive access
-- - Test scripts
-- - Verify backup
-- - Pre-stage any files
 
-- Step 2: Quickly transition, perform operation, restore
BEGIN TRANSACTION;
    ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    
    -- Perform the minimal necessary operation
    RESTORE DATABASE ProductionDB 
    FROM DISK = 'C:\Backup\ProductionDB.bak' 
    WITH REPLACE;
    
    ALTER DATABASE ProductionDB SET MULTI_USER;
COMMIT;
-- Total exclusive time: Only as long as the restore itself
 
-- PostgreSQL: Minimize connection blocking
-- Before blocking, terminate long-running queries
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'active' 
  AND query_start < now() - interval '5 minutes'
  AND datname = 'productiondb';
 
-- Then block new connections
UPDATE pg_database SET datallowconn = false WHERE datname = 'productiondb';
 
-- Perform operation immediately
DROP DATABASE productiondb;
CREATE DATABASE productiondb;
 
-- Restore access
UPDATE pg_database SET datallowconn = true WHERE datname = 'productiondb';

Using Alternatives When Possible:

Alternatives to Full Database Locks
Goal	Instead Of	Consider
Consistent backup	FLUSH TABLES WITH READ LOCK	XtraBackup, pg_dump with MVCC
Schema migration	Single-user mode	Online DDL, pt-online-schema-change
Full table reload	LOCK TABLE EXCLUSIVE	TRUNCATE + parallel load
Major version upgrade	Extended downtime	Logical replication with minimal switchover
Data center migration	Cold migration	Replication to new site with cutover

The Modern Approach

Modern database operations aim for near-zero downtime. Before implementing database-level locks, investigate whether your DBMS provides non-blocking alternatives. The investment in learning these alternatives pays off in reduced operational risk and improved availability.

Summary

Database-level locks represent the coarsest lock granularity, providing complete control over an entire database instance. They are essential for administrative operations but should be avoided in application workloads. Let's consolidate the key concepts:

Key Takeaways

•Definition: Database-level locks control access to an entire database—all tables, rows, and metadata—with a single coordination mechanism.
•Implementation Varies: SQL Server uses explicit modes (SINGLE_USER, RESTRICTED_USER); PostgreSQL uses connection control; MySQL has global locks.
•Primary Use Cases: Backup/restore, major migrations, failover coordination, and emergency maintenance.
•Backup Considerations: Modern databases support hot backups with minimal locking; understand your DBMS's options.
•Failover Coordination: Database-level read-only settings help prevent split-brain, but external fencing is also needed.
•Minimize Duration: Plan carefully to minimize the time spent with database-level locks active.
•Modern Alternatives: Online DDL, MVCC-based backups, and replication reduce the need for full database locks.

Looking Ahead:

We've now covered the full spectrum from row to page to table to database-level locks. In the final page of this module, we'll explore intention locks—the mechanism that enables hierarchical locking to work efficiently. Intention locks allow fine-grained and coarse-grained locks to coexist without expensive compatibility checking.

Page Complete

You now understand database-level locking—its mechanics across major database systems, use cases in backup/restore and failover, and best practices for minimizing impact. Database-level locks are powerful administrative tools that require careful planning. Next, we'll explore intention locks, which enable the lock granularity hierarchy to function efficiently.

4 / 5

Loading learning content...

Database Management SystemsLock Granularity

Lock Granularity

LevelIntermediate

Duration75 mins

TopicLock Granularity

4 / 5

Database-Level Locks

Locking the Entire Database

Consider these scenarios where database-level coordination is essential:

A backup tool needs a consistent snapshot of the entire database while applications continue running
A migration script must ensure no writes occur while relocating the database to new storage
A failover system needs to guarantee that the old primary has completely stopped accepting writes
A crash recovery process must prevent access while rebuilding transaction logs

Understanding database-level locks helps you plan for maintenance windows, design backup strategies, and troubleshoot scenarios where the entire database becomes unresponsive.

What You Will Learn

Understanding Database-Level Locks

Formal Definition:

Key Characteristics:

Database-Level Lock Characteristics

•Maximum Scope: A single lock covers the entire database—all schemas, tables, rows, and metadata.
•System-Level Granularity: Beyond application transactions; used for administrative operations.
•Rare in Normal Operations: Most applications never explicitly acquire database-level locks.
•Implicit in Many Admin Tasks: Backups, restores, and maintenance operations implicitly acquire database-wide coordination.
•Connection-Level Impact: Often implemented by controlling which connections can access the database.

Implementation Approaches:

Different databases implement database-level coordination in various ways:

Explicit Database Locks: Some databases (e.g., SQL Server) support explicit database lock modes.
Single-User Mode: A database mode where only one connection is allowed.
Offline Status: Setting a database offline completely prevents all access.
Global Read Locks: System-wide read locks that prevent all writes (used for consistent backups).
Connection Denial: Preventing new connections while existing ones complete.

Example: SQL Server Database Lock Modes:

ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Now only one connection can access ProductionDB
-- All other connections are terminated

-- Perform exclusive operation
RESTORE DATABASE ProductionDB FROM DISK = 'backup.bak' WITH REPLACE;

-- Return to multi-user mode
ALTER DATABASE ProductionDB SET MULTI_USER;

Extreme Serialization

SQL Server Database Access Modes

Microsoft SQL Server provides explicit database access modes that control how many users can connect to a database simultaneously.

Database User Access Modes:

SQL Server Database Access Modes
Mode	Description	Use Case
MULTI_USER	Default mode. Any number of users can connect.	Normal operation
SINGLE_USER	Only one connection at a time. Others are rejected.	Restore, major maintenance
RESTRICTED_USER	Only db_owner, dbcreator, and sysadmin can connect.	Migration, schema changes

SQL Server Database Access Control
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Check current database access mode
SELECT name, user_access_desc 
FROM sys.databases 
WHERE name = 'ProductionDB';
 
-- Switch to single-user mode
-- ROLLBACK IMMEDIATE: Kill all existing connections immediately
-- ROLLBACK AFTER n SECONDS: Wait up to n seconds
-- NO_WAIT: Fail if connections exist
ALTER DATABASE ProductionDB 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
-- Perform exclusive operation
DBCC CHECKDB('ProductionDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
 
-- Return to multi-user
ALTER DATABASE ProductionDB SET MULTI_USER;
 
-- Restricted user mode: Only admins can connect
ALTER DATABASE ProductionDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
 
-- View active connections before switching mode
SELECT 
    session_id,
    login_name,
    program_name,
    client_interface_name
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('ProductionDB');
 
-- Kill specific session if needed
KILL 52;  -- Kill session ID 52

Database States:

Beyond access modes, SQL Server has database states that affect accessibility:

SQL Server Database States
State	Description	Cause
ONLINE	Database is available for access	Normal operation
OFFLINE	Database is not accessible; must be explicitly brought online	Administrative action
RESTORING	Database is being restored from backup	RESTORE operation in progress
RECOVERING	Database is running crash recovery	Server startup, restore completion
SUSPECT	Database may be corrupted	Corruption or hardware failure
EMERGENCY	Database in emergency mode for repairs	DBA intervention for corruption

SQL Server Database State Control
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Take database offline (no access allowed)
ALTER DATABASE ProductionDB SET OFFLINE;
 
-- Bring database online
ALTER DATABASE ProductionDB SET ONLINE;
 
-- Check database state
SELECT name, state_desc, user_access_desc
FROM sys.databases
WHERE name = 'ProductionDB';
 
-- Emergency mode: Allows reading corrupt database for data recovery
ALTER DATABASE CorruptDB SET EMERGENCY;
SELECT * FROM CorruptDB.dbo.critical_data;  -- Attempt to read data
-- Then run DBCC CHECKDB with REPAIR options

Single-User Mode Gotcha

PostgreSQL Database-Level Control

PostgreSQL approaches database-level coordination differently from SQL Server, using connection limits and explicit connection prevention rather than explicit database locks.

Connection Limiting:

PostgreSQL Connection Control
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Limit connections to a database (0 = disallow all new connections)
ALTER DATABASE productiondb CONNECTION LIMIT 0;
 
-- Allow only 5 connections
ALTER DATABASE productiondb CONNECTION LIMIT 5;
 
-- Allow unlimited connections (default)
ALTER DATABASE productiondb CONNECTION LIMIT -1;
 
-- Check current settings
SELECT datname, datconnlimit 
FROM pg_database 
WHERE datname = 'productiondb';
 
-- Check active connections to the database
SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE datname = 'productiondb';
 
-- Terminate all connections to a specific database  
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'productiondb' 
  AND pid <> pg_backend_pid();  -- Don't kill yourself

Preventing New Connections:

PostgreSQL uses the datallowconn catalog field to control whether new connections are allowed:

PostgreSQL Connection Prevention
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Prevent new connections (existing connections remain)
UPDATE pg_database 
SET datallowconn = false 
WHERE datname = 'productiondb';
 
-- Allow connections again
UPDATE pg_database 
SET datallowconn = true 
WHERE datname = 'productiondb';
 
-- Comprehensive approach: Block new connections + terminate existing
-- Step 1: Block new connections
UPDATE pg_database SET datallowconn = false WHERE datname = 'productiondb';
 
-- Step 2: Terminate existing connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'productiondb';
 
-- Step 3: Perform maintenance (from template1 or postgres database)
-- e.g., DROP DATABASE productiondb;
 
-- Step 4: Restore access
UPDATE pg_database SET datallowconn = true WHERE datname = 'productiondb';

Global Read Lock for Backups:

While PostgreSQL doesn't have explicit database-level locks, it provides mechanisms for consistent backups without blocking normal operations:

PostgreSQL Backup Considerations
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- pg_dump uses transaction snapshot for consistency
-- No explicit locking required; uses MVCC
 
-- For full cluster backup, pg_basebackup uses checkpoint and WAL
pg_basebackup -D /backup/data -Fp -Xs -P
 
-- Logical replication slot holds WAL for consistent reads
SELECT pg_create_logical_replication_slot('backup_slot', 'pgoutput');
 
-- Advisory locks for application-level coordination
-- Not database-level, but useful for coordinating maintenance
SELECT pg_advisory_lock(12345);  -- Application agrees this means "maintenance mode"
-- Perform maintenance
SELECT pg_advisory_unlock(12345);
 
-- Check if advisory lock is held
SELECT pg_try_advisory_lock(12345);  -- Returns true if acquired, false if held

PostgreSQL Philosophy

MySQL Global Locks

MySQL provides explicit global lock mechanisms that affect the entire server instance, not just a single database. These are primarily used for consistent backups.

FLUSH TABLES WITH READ LOCK (FTWRL):

The classic MySQL approach to consistent backups:

MySQL Global Read Lock
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Acquire global read lock
-- This blocks ALL writes across ALL databases on the server
FLUSH TABLES WITH READ LOCK;
 
-- At this point:
-- - All open tables are flushed to disk
-- - A global read lock is held
-- - No INSERT, UPDATE, DELETE can proceed anywhere
-- - All writes queue up, waiting
 
-- Perform backup (e.g., copy data files)
-- Typically run: mysqldump --all-databases > backup.sql
-- Or: xtrabackup / physical file copy
 
-- Release the lock
UNLOCK TABLES;
 
-- Alternative: Lock specific tables for backup
LOCK TABLES orders READ, customers READ, products READ;
-- Backup these tables
UNLOCK TABLES;

LOCK INSTANCE FOR BACKUP (MySQL 8.0+):

MySQL 8.0 introduced a less disruptive backup lock:

MySQL 8.0 Backup Lock
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Lock instance for backup (MySQL 8.0+)
-- Blocks DDL and PURGE BINARY LOGS, but allows DML
LOCK INSTANCE FOR BACKUP;
 
-- At this point:
-- - SELECT, INSERT, UPDATE, DELETE continue normally
-- - DDL (CREATE, ALTER, DROP) is blocked
-- - Undo log purging is paused
-- - Redo log archiving continues
 
-- This is sufficient for consistent XtraBackup / Enterprise Backup
 
-- When backup is complete
UNLOCK INSTANCE;
 
-- Check if backup lock is held
SELECT * FROM performance_schema.metadata_locks 
WHERE LOCK_TYPE = 'BACKUP_LOCK';

MySQL Lock Comparison
Lock Type	Blocks Writes	Blocks DDL	Blocks Reads	Use Case
FLUSH TABLES WITH READ LOCK	Yes	Yes	No	Traditional full backup
LOCK INSTANCE FOR BACKUP	No	Yes	No	Hot backup (MySQL 8.0+)
LOCK TABLES ... READ	Selected tables only	No	No	Selective table backup
READ_ONLY mode	All non-SUPER users	Yes (implicit)	No	Maintenance mode

MySQL Read-Only Mode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Set server to read-only mode
-- Only users with SUPER privilege can write
SET GLOBAL read_only = ON;
 
-- For true read-only (blocks even SUPER users)
SET GLOBAL super_read_only = ON;
 
-- Check status
SHOW VARIABLES LIKE '%read_only%';
 
-- Return to normal
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;
 
-- Use in replication: Ensure replica doesn't accept writes
-- Typically done via my.cnf:
-- read_only = 1
-- super_read_only = 1

FTWRL Production Impact

Backup and Restore Implications

Database backup and restore operations are the most common scenarios requiring database-level coordination. Understanding the locking implications helps plan for minimal downtime.

Backup Locking Spectrum:

Backup Approaches by Lock Impact

•Cold Backup (Full Lock): Database offline during backup. 100% consistent, 100% downtime. Example: File copy of offline database.
•Warm Backup (Read Lock): Database readable but not writable. Consistent snapshot without downtime for readers. Example: FLUSH TABLES WITH READ LOCK + mysqldump.
•Hot Backup (MVCC/Snapshot): Database fully operational. Backup reads consistent snapshot via MVCC. Example: pg_dump, SQL Server backup with transaction log.
•Continuous Replication: No locking; backup from replica or continuous log shipping. Example: Read from replica, point-in-time recovery from logs.

Database-Specific Backup Lock Behavior:

Backup Lock Behavior by Database
Database	Backup Method	Lock Type	Blocks Writes
PostgreSQL	pg_dump	None (MVCC snapshot)	No
PostgreSQL	pg_basebackup	None (checkpoint + WAL)	No
SQL Server	BACKUP DATABASE	None (log-based)	No
SQL Server	Copy-only backup	None	No
MySQL	mysqldump (default)	Per-table locks	Briefly per table
MySQL	mysqldump --single-transaction	None (InnoDB snapshot)	No
MySQL	XtraBackup	Backup lock or brief FTWRL	Briefly at end
Oracle	RMAN	None (read consistency)	No

Restore Locking:

Restore operations typically require exclusive database access:

Restore Lock Requirements
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- SQL Server: Restore requires single-user or restricted
ALTER DATABASE TargetDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE TargetDB FROM DISK = 'C:\Backup\TargetDB.bak' WITH REPLACE;
ALTER DATABASE TargetDB SET MULTI_USER;
 
-- PostgreSQL: Can restore to new database (no lock on existing)
createdb new_target_db
pg_restore -d new_target_db backup.dump
 
-- PostgreSQL: Restore over existing requires no connections
-- Terminate connections first
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'target_db';
dropdb target_db
createdb target_db
pg_restore -d target_db backup.dump
 
-- MySQL: Restore typically creates new tables or requires explicit DROP
mysql target_db < backup.sql  -- Creates tables (may fail if exist)
-- Or with DROP statements in backup:
mysqldump --add-drop-table ... > backup.sql
mysql target_db < backup.sql

Blue-Green for Zero-Downtime Restore

Replication and Failover Coordination

Database-level coordination is critical in replication and failover scenarios. Ensuring exactly one primary accepts writes at any time is fundamental to data consistency.

The Split-Brain Problem:

In distributed databases, split-brain occurs when two nodes both believe they are the primary and accept writes independently. This causes data divergence that can be impossible to reconcile.

Split-Brain Scenario:

Time 0: Node A is primary, Node B is replica
Time 1: Network partition—A and B cannot communicate
Time 2: Monitoring system thinks A is dead, promotes B to primary
Time 3: Reality—A is still running and accepting writes
Time 4: Network heals—A and B both have different writes
→ Data is now inconsistent, manual intervention required

Prevention via Database-Level Locks:

Failover Coordination
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- SQL Server: Before demoting old primary
-- Ensure no writes can proceed
ALTER DATABASE ProductionDB SET READ_ONLY;
-- Or more aggressively:
ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
-- Then allow new primary to take over
 
-- PostgreSQL: Standby promotion
-- On old primary, ensure it's fenced (cannot accept writes)
-- Option 1: Stop the service
pg_ctl stop -D /var/lib/postgresql/data -m fast
 
-- Option 2: Make read-only via config
-- Set default_transaction_read_only = on
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();
 
-- On standby, promote to primary
pg_ctl promote -D /var/lib/postgresql/data
-- Or use pg_promote() function (PG 12+)
SELECT pg_promote();
 
-- MySQL: Read-only for failover coordination
-- On old primary:
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;
 
-- On new primary (after replication catches up):
SET GLOBAL read_only = OFF;

Fencing Mechanisms:

Beyond database settings, robust failover systems use additional fencing mechanisms:

Fencing Approaches

•STONITH (Shoot The Other Node In The Head): Physically power off the old primary via IPMI/iLO/management interface to guarantee it cannot accept writes.
•Network Isolation: Configure network switches to block all traffic to the old primary.
•Disk Reservation: Shared storage allows only one server to write (SCSI-3 Persistent Reservations).
•VIP Takeover: Move the Virtual IP address to the new primary; clients cannot reach old primary.
•Write Blocking at Database Level: Set database to read-only before promoting new primary.

Read-Only Is Not Enough

Best Practices and Patterns

Database-level locks should be used sparingly and with careful planning. The following best practices help minimize impact while achieving necessary coordination.

Planning for Exclusive Access:

Exclusive Access Best Practices

•Schedule Maintenance Windows: Plan exclusive operations for periods of low activity.
•Communicate Downtime: Notify stakeholders before operations requiring database-level locks.
•Prepare Scripts in Advance: Test all commands on non-production systems first.
•Set Timeouts: Configure statement and lock timeouts to fail fast if something goes wrong.
•Monitor Connection Queues: Watch for connection buildup during maintenance operations.
•Have Rollback Plans: Know how to quickly restore normal access if problems occur.

Minimizing Lock Duration:

Minimizing Exclusive Lock Duration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Pattern: Minimize time in single-user mode
-- Step 1: Prepare everything before acquiring exclusive access
-- - Test scripts
-- - Verify backup
-- - Pre-stage any files
 
-- Step 2: Quickly transition, perform operation, restore
BEGIN TRANSACTION;
    ALTER DATABASE ProductionDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    
    -- Perform the minimal necessary operation
    RESTORE DATABASE ProductionDB 
    FROM DISK = 'C:\Backup\ProductionDB.bak' 
    WITH REPLACE;
    
    ALTER DATABASE ProductionDB SET MULTI_USER;
COMMIT;
-- Total exclusive time: Only as long as the restore itself
 
-- PostgreSQL: Minimize connection blocking
-- Before blocking, terminate long-running queries
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'active' 
  AND query_start < now() - interval '5 minutes'
  AND datname = 'productiondb';
 
-- Then block new connections
UPDATE pg_database SET datallowconn = false WHERE datname = 'productiondb';
 
-- Perform operation immediately
DROP DATABASE productiondb;
CREATE DATABASE productiondb;
 
-- Restore access
UPDATE pg_database SET datallowconn = true WHERE datname = 'productiondb';

Using Alternatives When Possible:

Alternatives to Full Database Locks
Goal	Instead Of	Consider
Consistent backup	FLUSH TABLES WITH READ LOCK	XtraBackup, pg_dump with MVCC
Schema migration	Single-user mode	Online DDL, pt-online-schema-change
Full table reload	LOCK TABLE EXCLUSIVE	TRUNCATE + parallel load
Major version upgrade	Extended downtime	Logical replication with minimal switchover
Data center migration	Cold migration	Replication to new site with cutover

The Modern Approach

Summary

Key Takeaways

•Definition: Database-level locks control access to an entire database—all tables, rows, and metadata—with a single coordination mechanism.
•Implementation Varies: SQL Server uses explicit modes (SINGLE_USER, RESTRICTED_USER); PostgreSQL uses connection control; MySQL has global locks.
•Primary Use Cases: Backup/restore, major migrations, failover coordination, and emergency maintenance.
•Backup Considerations: Modern databases support hot backups with minimal locking; understand your DBMS's options.
•Failover Coordination: Database-level read-only settings help prevent split-brain, but external fencing is also needed.
•Minimize Duration: Plan carefully to minimize the time spent with database-level locks active.
•Modern Alternatives: Online DDL, MVCC-based backups, and replication reduce the need for full database locks.

Looking Ahead:

Page Complete

4 / 5