Want Everyone Out of the Database? Here’s How the Pros Do It | HackerNoon

In any database environment, there are scenarios, such as performing maintenance, applying schema changes, or conducting emergency troubleshooting, where it’s necessary to restrict access to a single user. This operational state, commonly referred to as single-user mode, ensures that only one session can interact with the database at a time, preventing conflicts and ensuring data consistency during critical tasks.

However, once these administrative tasks are complete, transitioning the database back to a multi-user mode is essential to restore regular operations and allow access for applications, developers, and users. This transition process, while conceptually similar, differs significantly depending on the underlying database management system (DBMS).

This article provides a comparative guide on how to perform this transition across three widely used relational database management systems (RDBMS) platforms: SQL Server, PostgreSQL, and MySQL. SQL Server offers a built-in mechanism for toggling between single-user and multi-user modes via specific database options. In contrast, PostgreSQL and MySQL don’t provide a direct single-user mode but can simulate it by terminating existing sessions, modifying connection settings, or changing server configurations.

By understanding the unique mechanisms of each DBMS, database administrators can confidently manage user access modes, ensure system stability during maintenance, and safely bring systems back online for general use.

How to Implement in SQL Server :

For SQL Server, the article demonstrates two methods, one using SQL commands and another utilizing the graphical interface (SSMS). In the command-line example, the article shows how first to verify the current access state using:

-- Check current access mode for AdventureWorks database
SELECT DATABASEPROPERTYEX('AdventureWorks', 'UserAccess') AS AccessMode;

After confirming that the database is in single-user mode, it recommends running:

-- Enable multi-user access
ALTER DATABASE AdventureWorks SET MULTI_USER;

This command immediately restores the default state allowing multiple concurrent connections. The graphical method described involves navigating the Object Explorer by expanding the Databases folder, accessing database properties, and then selecting the appropriate option (MULTI_USER) within the state menu.

Changing in GUI via SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio
2. Navigate to Databases → AdventureWorks
3. Right-click → Properties → Options
4. Under the State section, locate “Restrict Access“
5. Select MULTI_USER from the dropdown
6. Click OK to apply changes

Limit Connections in PostgreSQL Database

PostgreSQL: PostgreSQL does not offer a direct single-user mode switch. Instead, the article outlines a two-step workaround:

Managing concurrent connections is an important part of database administration. PostgreSQL provides a built-in way to limit the number of concurrent connections to a specific database using the CONNECTION LIMIT parameter in the CREATE DATABASE or ALTER DATABASE statement.

This is especially useful in scenarios where you want to avoid excessive load on your server, control access for development or testing environments, or reserve resources for high-priority databases.

Syntax

You can specify the connection limit when creating or altering a database using the following syntax:

Create a database with a connection limit:

CREATE DATABASE db_name
WITH
   CONNECTION LIMIT = max_allowed_connection;

Alter an existing database to set a connection limit:

ALTER DATABASE db_name
WITH
   CONNECTION LIMIT = max_allowed_connection;

Parameters

• db_name: Name of the database.
• max_allowed_connection: The maximum number of concurrent connections allowed to the database. Set it to any positive integer. The default value is -1, which means unlimited connections.

Example: Creating a Database with Connection Limit

CREATE DATABASE sales_db
WITH
   OWNER = sales_admin
   CONNECTION LIMIT = 50;

In the above example, a new database named sales_db is created, and it allows up to 50 concurrent connections.

Example: Altering an Existing Database

ALTER DATABASE hr_db
WITH
   CONNECTION LIMIT = 25;

This statement modifies the hr_db database to allow no more than 25 concurrent connections.

Important Notes

• This setting applies per database, not per user or role.
• When the connection limit is reached, new connection attempts will fail with an error.
• Superusers are not exempt from the connection limit for a specific database—they also count toward the total.
• To view the current connection limits, query the pg_database catalog:

SELECT datname, datconnlimit
FROM pg_database;

By managing connection limits, DBAs can ensure resource stability and predictability, especially in multi-tenant or resource-constrained PostgreSQL environments.

1. Terminate Active Sessions: The article instructs administrators to run a query based on the pg_terminate_backend function. This command terminates all active connections except the current session, ensuring that no old sessions obstruct the change.

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = 'University' AND pid <> pg_backend_pid();
   

2. Control Connection Limit: Once sessions are terminated, the connection limit is set to one via:

   ALTER DATABASE University WITH CONNECTION LIMIT 1;
   

   After administrative tasks are complete, the limit is reverted by setting the value to -1:

   ALTER DATABASE University WITH CONNECTION LIMIT -1;
   

This maneuver effectively mimics single-user mode behavior without having a built-in single-user command.

MySQL Configuration:

Unlike PostgreSQL, MySQL does not have a dedicated single-user mode. The article details a process relying on terminating connections and adjusting global connection limits:

• Listing and Terminating Sessions: The command SHOW PROCESSLIST; is used to view active database sessions. Specific sessions can then be terminated by using:

  KILL <session_id>;
  

• Switching Connection Modes: Initially, to restrict the database for administrative tasks, the global connection parameter is reduced to one:

  SET GLOBAL max_connections = 1;
  

  Once the necessary changes or backups are made, the connections are reinstated by resetting the variable to its typical default (often 151):

  SET GLOBAL max_connections = 600;
  

Core Themes and Best Practices:

• Verification: It is crucial to check the current database mode (or current active sessions) before making changes.
• Controlled Termination: When working with PostgreSQL or MySQL, terminating active sessions in a controlled manner is essential to prevent conflicts.
• Reversion Post-Administration: After administrative tasks are conducted in the restricted mode, reset connection limits so that the database resumes normal multi-user operations.
• Documentation and Monitoring: Regular monitoring and clear documentation ensure that administrators can track changes and identify issues with concurrency.

Summary: The article provides a comparative analysis of handling multi-user mode in SQL databases. For SQL Server, the transition is straightforward using system commands or a graphical interface. In contrast, PostgreSQL and MySQL require a temporary adjustment by terminating active sessions and modifying connection limits. Adherence to best practices such as verifying the mode, managing sessions carefully, and properly reverting limits ensures that system integrity is maintained during these transitions.