Lock database

From PostgreSQL wiki
Jump to navigationJump to search

LOCK DATABASE

LOCK DATABASE is a database-level lock mechanism. This functionality is not implemented in PostgreSQL, and is not planned to be yet.

Synopsis

LOCK DATABASE db_name [IN dblockmode MODE] [NOWAIT]; UNLOCK DATABASE db_name;

dblockmode having the following options: SHARE | EXCLUSIVE

EXCLUSIVE excludes all SQL query to be run on the database. No connection is allowed from external applications. SHARE authorizes SELECT queries and connections to the database. DDL and DML are blocked.

Characteristics

LOCK DATABASE is a database-level lock, waiting for any locks to be released before taking it. For example, if lock is taken on a table of this database, LOCK DATABASE waits until it is released. When waiting for locking, new table locks of this database cannot be taken.

It is also possible to set NOWAIT what makes LOCK DATABASE to return an error if lock cannot be taken immediately. mechanism to ensure that no transaction can be run on this database or no user can access to this database when lock is taken.

Lock can be released with command UNLOCK DATABASE or when session that used this database is disconnected. PostgreSQL always takes the least level of lock when using such mechanism. LOCK DATABASE can be used if database administrator or superuser needs more restrictive lock mechanism.

Usage

  • Server maintenance:

instead of taking multiple locks on tables of a database.

  • Cluster of nodes:

PostgreSQL does not ensure a DROP DATABASE query if done on multiple nodes at the same time as query is autocommit. DROP DATABASE can easily fail on a node if an application keeps a connection alive on it. However DROP succeeds if no session are alive on this node of the cluster. In this case LOCK DATABASE can be used as a 2PC like mechanism to ensure database drop in a cluster of nodes.