Database connection
Encyclopedia
A database connection is a facility in computer science
that allows client
software to communicate with database server
software, whether on the same machine or not. A connection is required to send commands
and receive answers.
Connections are a key concept in data-centric programming. Since some DBMS engines require considerable time to connect, connection pooling was invented to improve performance. No command can be performed against a database without an "open and available" connection to it.
Connections are built by supplying an underlying driver or provider
with a connection string
, which is a way of addressing a specific database
or server
and instance as well as user authentication credentials (for example,
, if one exists) can be set. The Connection String is composed of a set of key/value pairs as dictated by the data access interface and data provider being used.
Databases, such as PostgreSQL
, only allow one operation to be performed at a time on each connection. If a request for data (a SQL
Select
statement) is sent to the database and a result set is returned, the connection is open but not available for other operations until the client finishes consuming the result set. Other databases, like SQL Server 2005
(and later), do not impose this limitation. However, databases that provide multiple operations per connection usually incur far more overhead than those that permit only a single operation task at a time.
Connection pool
ing is a technique designed to alleviate this problem. A pool of database connections can be created and then shared among the applications that need to access the database. When an application needs database access, it requests a connection from the pool. When it is finished, it returns the connection to the pool, where it becomes available for use by other applications.
The connection object obtained from the connection pool is often a wrapper around the actual database connection. The wrapper understands its relationship with the pool, and hides the details of the pool from the application. For example, the wrapper object can implement a "close" method that can be called just like the "close" method on the database connection. Unlike the method on the database connection, the method on the wrapper may not actually close the database connection, but instead return it to the pool. The application need not be aware of the connection pooling when it calls the methods on the wrapper object.
This approach encourages the practice of opening a connection in an application only when needed, and closing it as soon as the work is done, rather than holding a connection open for the entire life of the application. In this manner, a relatively small number of connections can service a large number of requests. This is also called multiplexing
.
In a client/server architecture, on the other hand, a persistent connection is typically used so that server state can be managed. This "state" includes server-side cursors, temporary products, connection-specific functional settings, and so on.
It is desirable to set some limit on the number of connections in the pool. Using too many connections may just cause thrashing
rather than get more useful work done. In case an operation is attempted and all connections are in use, the operation can block until a connection is returned to the pool, or an error may be returned.
Computer science
Computer science or computing science is the study of the theoretical foundations of information and computation and of practical techniques for their implementation and application in computer systems...
that allows client
Client (computing)
A client is an application or system that accesses a service made available by a server. The server is often on another computer system, in which case the client accesses the service by way of a network....
software to communicate with database server
Database server
A database server is a computer program that provides database services to other computer programs or computers, as defined by the client–server model. The term may also refer to a computer dedicated to running such a program...
software, whether on the same machine or not. A connection is required to send commands
Command (computing)
In computing, a command is a directive to a computer program acting as an interpreter of some kind, in order to perform a specific task. Most commonly a command is a directive to some kind of command line interface, such as a shell....
and receive answers.
Connections are a key concept in data-centric programming. Since some DBMS engines require considerable time to connect, connection pooling was invented to improve performance. No command can be performed against a database without an "open and available" connection to it.
Connections are built by supplying an underlying driver or provider
Provider
The provider model is a design pattern formulated by Microsoft for use in the ASP.NET Starter Kits and formalized in .NET version 2.0. It is used to allow an application to choose from one of multiple implementations or "condiments" in the application configuration, for example, to provide access...
with a connection string
Connection string
In computing, a connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection...
, which is a way of addressing a specific database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
or server
Server (computing)
In the context of client-server architecture, a server is a computer program running to serve the requests of other programs, the "clients". Thus, the "server" performs some computational task on behalf of "clients"...
and instance as well as user authentication credentials (for example,
Server=sql_box;Database=Common;User ID=uid;Pwd=password;). Once a connection has been built it can be opened and closed at will, and properties (such as the command time-out length, or transactionDatabase transaction
A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
, if one exists) can be set. The Connection String is composed of a set of key/value pairs as dictated by the data access interface and data provider being used.
Databases, such as PostgreSQL
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
, only allow one operation to be performed at a time on each connection. If a request for data (a SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
Select
Select (SQL)
The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
statement) is sent to the database and a result set is returned, the connection is open but not available for other operations until the client finishes consuming the result set. Other databases, like SQL Server 2005
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
(and later), do not impose this limitation. However, databases that provide multiple operations per connection usually incur far more overhead than those that permit only a single operation task at a time.
Pooling
Database connections are finite and expensive and can take a disproportionately long time to create relative to the operations performed on them. It is very inefficient for an application to create and close a database connection whenever it needs to update a database.Connection pool
Connection Pool
In software engineering,a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required....
ing is a technique designed to alleviate this problem. A pool of database connections can be created and then shared among the applications that need to access the database. When an application needs database access, it requests a connection from the pool. When it is finished, it returns the connection to the pool, where it becomes available for use by other applications.
The connection object obtained from the connection pool is often a wrapper around the actual database connection. The wrapper understands its relationship with the pool, and hides the details of the pool from the application. For example, the wrapper object can implement a "close" method that can be called just like the "close" method on the database connection. Unlike the method on the database connection, the method on the wrapper may not actually close the database connection, but instead return it to the pool. The application need not be aware of the connection pooling when it calls the methods on the wrapper object.
This approach encourages the practice of opening a connection in an application only when needed, and closing it as soon as the work is done, rather than holding a connection open for the entire life of the application. In this manner, a relatively small number of connections can service a large number of requests. This is also called multiplexing
Multiplexing
The multiplexed signal is transmitted over a communication channel, which may be a physical transmission medium. The multiplexing divides the capacity of the low-level communication channel into several higher-level logical channels, one for each message signal or data stream to be transferred...
.
In a client/server architecture, on the other hand, a persistent connection is typically used so that server state can be managed. This "state" includes server-side cursors, temporary products, connection-specific functional settings, and so on.
It is desirable to set some limit on the number of connections in the pool. Using too many connections may just cause thrashing
Thrashing
The term thrashing may refer to:* Thrashing , an effect of resource contention* A severe corporal punishment* A clear victory* The dance style moshing* Threshing, a process in agriculture* Skateboard Thrashin'...
rather than get more useful work done. In case an operation is attempted and all connections are in use, the operation can block until a connection is returned to the pool, or an error may be returned.