Database transaction and connection pool

Database transaction

Introduction to database transactions

1. Transaction: a group of logical operation units that transform data from one state to another.

2. Transaction processing (transaction operation): ensure that all transactions are executed as a work unit. Even if there is a fault, it cannot be modified

Change this way of execution. When multiple operations are performed in a transaction, or all transactions are committed, these

The modification is permanently saved; Or the database management system will discard all modifications and roll back the whole transaction to the latest

Initial state.

3. In order to ensure the consistency of the data in the database, the manipulation of the data should be a discrete group of logical units: when it is all completed, the number

The consistency of data can be maintained, and when some operations in this unit fail, the whole transaction should be regarded as an error, all from the beginning

All operations after the point shall be returned to the start state.

JDBC transaction processing

1. Once the data is submitted, it cannot be rolled back.

2. When does data mean submission?

When a connection object is created, the transaction is automatically committed by default: each time an SQL statement is executed, if it is executed as

Work, it will be automatically submitted to the database without rollback.

If you close the database connection, the data will be submitted automatically. If there are multiple operations, and each operation uses its own separate connection, you cannot

Guarantee affairs. That is, multiple operations of the same transaction must be under the same connection.

3. In the JDBC program, in order to execute multiple SQL statements as a transaction:

Call setAutoCommit(false) of the Connection object; To cancel the auto commit transaction

After all SQL statements have been successfully executed, call commit(); Method commit transaction

When an exception occurs, call rollback(); Method rolls back the transaction

If the Connection is not closed at this time and may be reused, you need to restore its automatic submission state

setAutoCommit(true). Especially when using the database connection pool technology, it is recommended to restore the automatic connection pool before executing the close() method

Delivery status.

Database connection pool

The necessity of JDBC database connection pool

When developing web programs based on database, the traditional mode basically follows the following steps:

1. Establish database connection in the main program (such as servlet and beans)

2. Perform sql operations

3. Disconnect the database

Problems in the development of this model:

Ordinary JDBC database connections are obtained by using DriverManager. Each time a connection is established to the database, the

Load the Connection into the memory, and then verify the user name and password (it takes 0.05s ~ 1s). When a database Connection is required

Wait, ask the database for one, and disconnect after the execution is completed. This way will consume a lot of resources and time. data

The connection resources of the database have not been well reused. If hundreds or even thousands of people are online at the same time, they connect to the database frequently

The operation will occupy a lot of system resources, and even cause the server to crash.

For each database connection, it must be disconnected after use. Otherwise, if the program fails to close due to an exception, it will cause data loss

A memory leak in the library system will eventually cause the database to restart. (recall: what is Java's memory leak?)

This kind of development cannot control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also occur

Cause memory leak and server crash.

Database connection pool technology

In order to solve the problem of database connection in traditional development, database connection pool technology can be used.

Basic idea of database connection pool:

Is to establish a "buffer pool" for database connections. Put a certain amount of in the buffer pool in advance

Connection. When you need to establish a database connection, just take one from the "buffer pool" and put it back after use.

Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection

Connect instead of re establishing one.

During initialization, the database connection pool will create a certain number of database connections into the connection pool. The number of these database connections is determined by

Set the minimum number of database connections. No matter whether these database connections are used or not, the connection pool will always ensure at least this

More connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy

When the number of connections requested from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

working principle

As shown below:

 

Advantages of database connection pool technology

resource reuse

Because the database connection can be reused, it avoids frequent creation and releases a lot of performance overhead caused by the connection. On the basis of reducing system consumption

On the other hand, it also increases the stability of the system operation environment.

Faster system response

During the initialization of the database connection pool, several database connections have often been created and placed in the connection pool for standby. At the beginning of the connection

The initial work has been completed. For business request processing, the existing available connections are directly used to avoid database connection initialization and processing

Release the time overhead of the process, thus reducing the response time of the system

New means of resource allocation

For a system where multiple applications share the same database, the maximum size of an application can be achieved through the configuration of database connection pool at the application layer

Limit the number of available database connections to prevent an application from monopolizing all database resources

Unified connection management to avoid database connection leakage

In the more perfect implementation of database connection pool, the occupied connections can be forcibly recovered according to the pre occupation timeout setting, so as to avoid

The possible resource leakage in the regular database connection operation is

Multiple open source database connection pools

Druid database connection pool

Druid is a database connection pool implementation on Alibaba's open source platform, which combines the functions of C3P0, DBCP, Proxool and other DB pools

Advantages: log monitoring is added at the same time, which can well monitor the connection of DB pool and the execution of SQL. It can be said that it is for monitoring

The generated DB connection pool is one of the best connection pools at present.

Here are some inline snippets.

package com.atguigu.druid;

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
    public static void main(String[] args) throws Exception {
        Properties pro = new Properties();         pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = ds.getConnection();
        System.out.println(conn);
    }
}


The configuration file under src is: [druid.properties]
Here are some inline snippets.

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall



Detailed configuration parameters:

 

Apache dbutils implements CRUD operations

Use of major API s

As follows:

QueryRunner class

The introduction is shown in the figure below:

 

JDBC summary

Here are some inline snippets.

//summary
@Test
public void testUpdateWithTx() {
        
    Connection conn = null;
    try {
        //1. Get connection(
        //① Handwritten connection: JDBC utils. Getconnection();
        //② Use database connection pool: C3P0;DBCP;Druid
        //2. Perform a series of CRUD operations on the data table
        //① Use PreparedStatement to realize general addition, deletion, modification and query operations (version 1.0 \ version 2.0)
//Addition, deletion and modification of public void update (connection Conn, string SQL, object... Args) {}
//Query of version 2.0 public < T > t getInstance (connection Conn, class < T > clazz, string SQL, object... Args) {}
        //② Use the QueryRunner class provided in the jar package provided by dbutils
            
        //Submit data
        conn.commit();
            
    
    } catch (Exception e) {
        e.printStackTrace();
            
            
        try {
            //Undo Data 
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
            
    }finally{
        //3. Close the connection
        //① JDBCUtils.closeResource();
        //② Using the dbutils class provided in the jar package provided by dbutils provides the related operation of closing
            
    }
}

Tags: Database SQL Server SQL

Posted on Thu, 02 Dec 2021 22:08:52 -0500 by jonsimmonds