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. single-user mode single-user mode 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). multi-user mode 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. SQL Server PostgreSQL MySQL 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 : 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; -- 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; -- 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) Open SQL Server Management Studio
Navigate to Databases → AdventureWorks
Right-click → Properties → Options
Under the State section, locate "Restrict Access"
Select MULTI_USER from the dropdown
Click OK to apply changes Open SQL Server Management Studio Navigate to Databases → AdventureWorks AdventureWorks Right-click → Properties → Options Properties Options Under the State section, locate "Restrict Access" Restrict Access Select MULTI_USER from the dropdown MULTI_USER 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: PostgreSQL: 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. limit the number of concurrent connections to a specific database CONNECTION LIMIT CREATE DATABASE ALTER DATABASE 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 a database with a connection limit: CREATE DATABASE db_name
WITH
   CONNECTION LIMIT = max_allowed_connection; CREATE DATABASE db_name
WITH
   CONNECTION LIMIT = max_allowed_connection; Alter an existing database to set a connection limit: Alter an existing database to set a connection limit: ALTER DATABASE db_name
WITH
   CONNECTION LIMIT = max_allowed_connection; 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. db_name: Name of the database. db_name 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. max_allowed_connection -1 Example: Creating a Database with Connection Limit CREATE DATABASE sales_db
WITH
   OWNER = sales_admin
   CONNECTION LIMIT = 50; 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. sales_db up to 50 concurrent connections Example: Altering an Existing Database ALTER DATABASE hr_db
WITH
   CONNECTION LIMIT = 25; ALTER DATABASE hr_db
WITH
   CONNECTION LIMIT = 25; This statement modifies the hr_db database to allow no more than 25 concurrent connections. hr_db 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: 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. not exempt To view the current connection limits, query the pg_database catalog: pg_database SELECT datname, datconnlimit
FROM pg_database; 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. ensure resource stability and predictability 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();



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; 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(); 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. Terminate Active Sessions: pg_terminate_backend SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'University' AND pid <> pg_backend_pid(); SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'University' AND pid <> pg_backend_pid(); 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; Control Connection Limit: Once sessions are terminated, the connection limit is set to one via: Control Connection Limit: ALTER DATABASE University WITH CONNECTION LIMIT 1; 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; 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: 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; 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>; Listing and Terminating Sessions: The command SHOW PROCESSLIST; is used to view active database sessions. Specific sessions can then be terminated by using: Listing and Terminating Sessions: SHOW PROCESSLIST; KILL <session_id>; 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; Switching Connection Modes: Initially, to restrict the database for administrative tasks, the global connection parameter is reduced to one: Switching Connection Modes: SET GLOBAL max_connections = 1; 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; SET GLOBAL max_connections = 600; Core Themes and Best Practices: 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. Verification: It is crucial to check the current database mode (or current active sessions) before making changes. Verification: Controlled Termination: When working with PostgreSQL or MySQL, terminating active sessions in a controlled manner is essential to prevent conflicts. Controlled Termination: Reversion Post-Administration: After administrative tasks are conducted in the restricted mode, reset connection limits so that the database resumes normal multi-user operations. Reversion Post-Administration: Documentation and Monitoring: Regular monitoring and clear documentation ensure that administrators can track changes and identify issues with concurrency. Documentation and Monitoring: 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. Summary:

Memory-Optimized Tables in SQL Server: When, Why and How to Implement Them

Five Warning Signs You're Overspending on Database Resources

Read My Stories

Portfolio

Too Long; Didn't Read

Make resilience your competitive advantage

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

How to Create a Foreign Data Wrapper in PostgreSQL and Aurora PostgreSQL on AWS RDS

5 Common Identity and Access Management Security Risks

AWS IAM (Identity and Access Management) - In Plain English

Confidently Working With IAM Roles in AWS

Healthcare Tech is Out-Of-Date: Identity Access Management Can Revive The Old-School System

How to Create a Foreign Data Wrapper in PostgreSQL and Aurora PostgreSQL on AWS RDS

5 Common Identity and Access Management Security Risks

AWS IAM (Identity and Access Management) - In Plain English

Confidently Working With IAM Roles in AWS

Healthcare Tech is Out-Of-Date: Identity Access Management Can Revive The Old-School System

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps