The concept of a transaction a transaction is an event that may have multiple units that are required to either all succeed or none of them succeed. In development, there are transactions to ensure data integrity.
For example, transfer A to B corresponds to the following two SQL statements
Update account set money=money-100 where name= ‘a’; Update account set money=money+100 where name= ‘b’; The database default transaction is committed automatically, that is, when an SQL is issued it executes one. If you want to execute multiple SQL statements in one transaction, you need to use the following statement. Start transaction … Select * from MySQL;
Rollback Rollback Commit Commit mode 2
Show variables like ‘% % commit; You can view the current autoCOMMIT value in mysql database and its default value is “on” for automatic transaction. The meaning of automatic transactions is that any SQL statement executed automatically commits a transaction. Test: Set autoCOMMIT to off
1. Set AutoCOMMIT =off To disable automatic transactions.
2. You must commit the transaction manually.
Mysql default autocommit=on oracle default autocommit=off;
When a JDBC program obtains a Connection object from the database, by default the Connection object automatically submits the SQL statement sent on it to the database. 1. SetAutocommit (Boolean flag); 2. SetAutocommit (Boolean flag); If flag = false; It is equivalent to a start transaction; 2. RollBack () Transaction rollBack. Commit bank transfer case causes the following transfer operations to be performed in the same transaction in JDBC code. Update from account set money=money-100 where name= ‘a’; Update from account set money=money+100 where name= ‘b’; Savepoint sp = conn.setSavePoint (); Conn.rollback(sp); Conn.commit(); Create table account(id int primary key auto_increment, name vARCHar (20), money double);
insert into account values(null,’aaa’,1000); insert into account values(null,’bbb’,1000); insert into account values(null,’ccc’,1000);
Atomicity refers to the fact that a transaction is an indivisible unit of work in which all or none of the operations occur. Consistency Data integrity must be consistent before and after a transaction. Isolation The Isolation of transactions means that when multiple users concurrently access the database, one user’s transactions cannot be disturbed by other users’ transactions, and the data of multiple concurrent transactions must be isolated from each other. Durability Durability means that once a transaction is committed, its changes to the data in the database are permanent and they should not be affected if the database fails afterwards
Isolation level of transactions When multiple threads start their own transactions to manipulate data in the database, the database system is responsible for isolation operations to ensure the accuracy of each thread in obtaining data. Problems that can arise if isolation is not considered 1. Dirty read Uncommitted data read from one transaction to another.
Update account set money=money+100 while name= ‘B’; 2. Update account set money=money-100 while name= ‘a’; When SQL 1 is executed, but SQL 2 is not executed (when A has not submitted), if B queries his account, he will find that he has 100 yuan more money. If A waits for B to roll back, B loses $100. 2. It cannot be read repeatedly
A row in a table is read in one transaction, with different results. For example, if the bank wants to check the balance of account A, the first query is 200 yuan, and account A deposits 100 yuan to account A and submits it, the bank then makes another query, and account A is 300 yuan. The bank may be confused about which query is correct if two queries do not match. The difference between a dirty read and a dirty read is that a dirty read reads the dirty data that has not been committed in a previous transaction. A non-repeatable read reads the data that has been committed in a previous transaction. A lot of people think that this situation is right, without confusion, of course, is the latter shall prevail. We can consider this kind of situation, such as bank applications need to query results output to a computer screen and written to a file, the result in a transaction for the output destination, two queries, lead to inconsistent results file and screen, bank staff don’t know which one shall prevail. 3. Imaginary reading
Data inserted by another transaction is read in one transaction, resulting in inconsistent reads. (insert) If C deposits 100 yuan and does not submit it, then the bank makes a statement to count the total amount of all users in the account table as 500 yuan, and then C submits it, then the bank finds that the account is 600 yuan, causing false reading, and also makes the bank at a loss, which shall be the standard. 4. Lost updates
Two or more transactions update the same row, but none of these transactions is aware of the changes made by the other transactions, so the second change overrides the first
Transaction isolation Setting statement The database defines four isolation levels:
Serializable: Avoids dirty read, unrepeatable read, and virtual read. Repeatable read (serialization) : Can avoid dirty read and unrepeatable read. Cannot avoid virtual reads Read COMMITTED: Dirty reads can be avoided (reads are committed) Read Uncommitted: indicates the lowest level. This cannot be guaranteed. Security: SerialIZABLE > REPEATable Read > Read COMMITTED > Read uncommitted
Performance: SerialIZABLE < REPEATable read < Read committed < read uncommitted
Conclusion: In real development, serializable and Read uncommitted are usually not selected,
How do I set the isolation level for a transaction?
1. Setting in mysql
1. Check the transaction isolation level select @@tx_ISOLATION Query the current transaction isolation level Repeatable Read. The default transaction isolation level in mysql is read COMMITTED Set session Transaction Isolation Level set session transaction isolation level set session transaction isolation level
Set the transaction isolation level in JDBC using the method provided in the java.sqL. Connection interface void setTransactionIsolation(int level) throws SQLException parameter Level can take the following value: TRANSACTION_READ_UNCOMMITTED: indicates that dirty reads, unrepeatable reads, and virtual reads can occur. TRANSACTION_READ_COMMITTED: indicates that dirty reads cannot occur, but non-repeatable reads and virtual reads can occur. TRANSACTION_REPEATABLE_READ: indicates that no dirty read or unrepeatable read can occur, but virtual read can occur. TRANSACTION_SERIALIZABLE: CONSTANT indicating that dirty reads, unrepeatable reads, and virtual reads cannot occur. TRANSACTION_NONE, generally not used because it specifies unsupported transactions.
Isolation Level Solution Demonstration 1. Dirty read
Set the transaction isolation level to Read uncommitted. Set session Transaction Isolation Level to Read uncommitted. 1. Start transaction; Update account set money=money-500 where name= ‘aaa’; Update account set money=money+500 where name= ‘BBB’; 2. Start transaction; select * from account; Now, when transaction B reads, it finds that the money has already been transferred. Then there is dirty reading. When transaction A commits, perform rollback, commit, and query, you will find that the money is restored to the original. The two query results are inconsistent, causing unrepeatable read. 2. Solve the dirty read problem
Set transaction isolation level to Read COMMITTED set Session TRANSACTION Isolation level to read committed. 1. Start transaction; Update account set money=money-500 where name= ‘aaa’; Update account set money=money+500 where name= ‘BBB’; 2. Start transaction; select * from account; In this case, B transaction cannot read the data not submitted by A transaction when reading information, thus solving the problem of dirty read. Let A transaction commit data; In this case, the query result is different from the previous query result, and there are unrepeatable reads. 3. Solve the problem of unrepeatable read
Set transaction isolation level to Repeatable read to resolve non-repeatable reads. Set transaction isolation level of A and B to Repeatable read; set session transaction isolation level Repeatable read; 1. In transaction A
- start transaction;
- Update account set money=money-500 where name= ‘aaa’;
- Update account set money=money+500 where name= ‘BBB’;
2. In B transaction
- start transaction;
- select * from account;
Commit after transaction A commits; B the transaction is being queried. The result is the same as the last query result, and the unrepeatable read is resolved. 4. Set transaction isolation level Serializable, which resolves all issues
set session transaction isolation level Serializable; If this isolation level is set, the lock table appears. That is, one transaction can operate on a table while no other transaction can. Example: Transfer money — use transactions
Question: The service calls two methods in the DAO to complete a business operation. What if one of the methods fails?
Need transaction control question: How to do transaction control?
We start, roll back, and commit transactions at the Service layer. Question: How to ensure that the same Connection is used in a Service and a DAO for transactional operations.
Create a Connection object in the Service layer and pass it to the DAO layer. Note: When the Connecton object is finished, it is closed in finally of the Service layer
For each PreparedStatement they are closed when used in dao layer methods. On a point of procedure
1. For roll-in and roll-out operations, we need to judge whether they are successful or not. If they fail, we can judge them by throwing custom exceptions in the servlet to display information. Question:
When setting the DAO layer, public interface AccountDao {public void accountOut(String accountOut, Double Money) throws Exception;
public void accountIn(String accountIn, double money) throws Exception;
}
Copy the codeSo how do we handle the same Connection object problem when we implement this interface ourselves?
Use ThreadLocal to bind a value or object to a thread that can fetch the value or object anywhere in the thread. The Map<Thread,Object> set method stores data into a ThreadLocal, and the current key is the current Thread Object. The get method fetches data from ThreadLocal based on the current thread object.
If we’re on the same thread, as long as we store the data in any one location, we can get the data in any other location.
Private static final ThreadLocal tl = new ThreadLocal(); Connection con = tl.get(); Null if (con == null) {// 2. Get connected con = DriverManager. GetConnection (URL, USERNAME, PASSWORD); tl.set(con); // load con into ThreadLocal. }
Missing update Multiple transactions operate on the same record, and the later committed transaction overwrites the first committed transaction.
Missing updates can be resolved in two ways: 1. Pessimistic locking
Pessimistic locking: Using the internal locking mechanism of the database, the locking mechanism provided by the management transaction, assuming that lost updates will occur
1. Shared lock: Multiple locks can be added to this record select * from table lock in share mode 2. Select * from table for UPDATE add exclusive lock 2 by default. Optimistic Locking
Optimistic locking assumes that missing updates will not occur and solves the problem by adding a version field in the program
Fix missing updates: Add the version field (through the timestamp field) to the data table. After each record modification, the version field will be updated. If the version field is read, it is inconsistent with the version field at the time of modification. Create table product (id int, name vARCHar (20), updateTime timestamp);
Insert into product values(1,' fridge ',null); Update product set name=' washer 'where id = 1;Copy the codeConnection pooling: A container is created to hold multiple Connection objects. When a Connection object is used, a Connection is fetched from the container. When the Connection object is used, the Connection is reloaded into the container. This container is the connection pool. A DataSource is also called a DataSource. What it does: We can get connection objects from the connection pool.
Advantages:
Save the performance cost of creating and releasing connections – Connections in the connection pool are reused to improve application performance. Applications directly obtain links
Disadvantages of applications getting links directly
Every time a user requests a link to a database, creating a connection to a database usually consumes relatively large resources and takes a long time to create. Assuming that the website has 100,000 visits a day, the database server needs to create 100,000 connections, which greatly wastes the resources of the database and easily causes the memory overflow and extension of the database server.
The javax.sql.DataSource interface is required for compiling a database connection pool. The DataSource interface defines two overloaded getConnection methods:
Connection getConnection() Connection getConnection(String Username, String Password)
In the DataSource constructor, create a batch of connections to the database, and save the Connection to a collection object. When the user finishes using Connection and calls the connection.close () method, the Collection object should ensure that it returns itself to the Collection object of the Connection pool and does not return conn to the database. JdbcUtil creates the connection for the dataSource. Instead of binding the dataSource to the data, the dataSource should use the configuration file method to obtain the connection.
1. Create a MyDataSource class and create LinkedList 2 in this class. Initialize the List collection in its constructor and load five Connection objects into it. Create a public Connection getConnection(); Public void readd(Connection) This method reloads the Connection object into the List after it has been used.
Code issues:
1. There are standards for creating connection pools.
DataSource All connection pools must implement the Javax.sql.DataSource interface, 2. When we do this, we use the standard, how can con.close() not be destroyed, but reloaded into the connection pool.
To solve this problem, the essence is to change the behavior of the close() method in Connection. How to change the behavior of a method (by enhancing method functionality) 1. inheritance 2. Decorator pattern 1. The decorator class and the decorator class implement the same interface or inherit from the same superclass 2. Hold a decorator class reference in the decorator class. 3. Enhance methods. NewProxyInstance (ClassLoacer,Class[],InvocationHandler); Conclusion: If a Connection object is fetched from a Connection pool, the behavior of its close method has changed and it is no longer destroyed, but reloaded into the pool.
1. The connection pool must implement the Javax.sql.DataSource interface.
2. The DataSource interface has a getConnection method. Reload the Connection into the Connection pool using the close() method of Connection. Dynamic proxy enhanced Close example
Connection proxyConn = (Connection) proxy.newProxyInstance (this.getClass().getClassLoader(), Conn.getclass ().getinterfaces (), new InvocationHandler() {// Here is an inner class, The conn is returned to the pool when the close Method is called. Other methods directly invoke public Object (Object Proxy, Method Method, Object[] args) throws Throwable { if (method.getName().equals(“close”)) { pool.addLast(conn); return null; } return method.invoke(conn, args); }});
Public Demo2 {public static void main(String[] args) {public static void main(String[] args) {
Car car=new Bmw(); CD =new CarDerector(car); cd.run(); }Copy the code}
interface Car { void run(); }
class Bmw implements Car { public void run() { System.out.println(“bmw run….”); } }
class Benz implements Car { public void run() { System.out.println(“benz run….”); } }
// Implements Car {class CarDerector implements Car {
private Car car; public CarDerector(Car car) { this.car = car; } public void run() {system.out.println (" add navigation "); car.run(); }Copy the codeOpen source connection pooling Many WEB servers (Weblogic, WebSphere, Tomcat) now provide an implementation of DataSoruce, or connection pooling. Usually we call the DataSource implementation, according to its English meaning called the DataSource, the DataSource contains the database connection pool implementation. Some open source organizations provide independent implementation of data sources: DBCP database connection pool C3P0 Database connection pool Apache Tomcat built-in connection pool (Apache DBCP) does not need to write database connection code, directly from the data source database connection. Programmers should also use these data source implementations to improve database access performance. DBCP data source (understand) DBCP is an open source connection pool implementation under the Apache Software Foundation using DBCP data source, the application should add the following two JAR files to the system: commons-dbcp-1.4. jar: Commons-pool-1.5.6.jar: dependency library for connection pooling Tomcat’s connection pooling is implemented using this connection pool. The database connection pool can be used either integrally with the application server or independently by the application. Manual configuration (manual encoding) // 1. Manual configuration @test public void test1() throws SQLException {
BasicDataSource bds = new BasicDataSource(); Bds.setdriverclassname (" com.mysql.jdbc.driver "); bds.setUrl("jdbc:mysql:///day18"); bds.setUsername("root"); bds.setPassword("abc"); // Get a Connection Connection con = bds.getConnection(); ResultSet rs = con.createStatement().executeQuery( "select * from account"); while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } rs.close(); con.close(); // Reload the Connection object into the Connection pool.}Copy the codeAutomatic configuration (using a configuration file) // 2. Automatic configuration @test public void test2() throws Exception {
Properties props = new Properties(); // props.setProperty("driverClassName", "com.mysql.jdbc.Driver"); // props.setProperty("url", "jdbc:mysql:///day18"); // props.setProperty("username", "root"); // props.setProperty("password", "abc"); FileInputStream fis = new FileInputStream( "D:\\java1110\\workspace\\day18_2\\src\\dbcp.properties"); props.load(fis); DataSource ds = BasicDataSourceFactory.createDataSource(props); // Get a Connection Connection con = ds.getConnection(); ResultSet rs = con.createStatement().executeQuery( "select * from account"); while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } rs.close(); con.close(); // Reload the Connection object into the Connection pool.}Copy the codeC3P0 data Source (mandatory) C3P0 is an open source JDBC connection pool that implements data source and JNDI bindings and supports the JDBC3 specification and standard extensions of JDBC2. Currently, open source projects using it include Hibernate, Spring, etc.
C3p0 is different from DBCP
DBCP does not automatically reclaim idle connections c3P0 does automatically reclaim idle connections 1. Guide package
C3p0-0.9.1.2. jar 1. Run this command manually
ComboPooledDataSource cpds = new ComboPooledDataSource(); cpds.setDriverClass(“com.mysql.jdbc.Driver”); cpds.setJdbcUrl(“jdbc:mysql:///day18”); cpds.setUser(“root”); cpds.setPassword(“abc”);
2. Automatic c3P0 configuration files can be properties or XML. C3p0 configuration files named c3P0.properties or c3P0-config. XML and placed in the classpath (classes directory for Web applications) will be automatically found by C3P0. Note: we only need to place the configuration file under SRC at the moment. Use: ComboPooledDataSource CPDS = new ComboPooledDataSource(); It looks for the named configuration file in the specified directory and loads its contents.
@Test public void test2() throws PropertyVetoException, SQLException { ComboPooledDataSource cpds = new ComboPooledDataSource(); // Get a Connection Connection con = cpds.getConnection(); ResultSet rs = con.createStatement().executeQuery( "select * from account"); while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } rs.close(); con.close(); // String path = this.getClass().getResource("/").getPath(); // System.out.println(path); }Copy the codeConfigure a Tomcat data source built-in Connection pool for the Tomcat server (using Apache DBCP). Java Naming and Directory Interface (JNDI), which corresponds to the Javax.Naming package in J2SE. JavaEE is a JavaEE technology, which allows a Java object to be bound to a JNDI container (Tomcat), specifying a name for the object, searching for the object bound in the JNDI container through the Javax. naming package Context, and finding the bound Java object by specifying the name.
The main purpose of this API is to put A Java object in a container (supporting the JNDI container Tomcat) and give the Java object in the container a name, so that programs can retrieve the Java object by name.
The core API is Context, which represents the JNDI container and its lookup method retrieves the object with the corresponding name in the container. 1. Configure the context element using the Tomcat built-in connection pool. There are three common configuration locations for the context element
- Tomcat /conf/context. XML All virtual hosts and all projects can access the connection pool
- Tomcat/conf/Catalina/localhost/context. The XML all engineering under the present virtual host (localhost) can use the connection pool
- Current project/meta-INF /context. XML Only the current project can access the connection pool
When tomcat starts the server, create a connection pool object and bind it to the JDBC /EmployeeDB specified name
2. Access the Tomcat built-in connection pool through a program (Servlet/JSP) running inside the JNDI container
public class DataSourceServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { Context context = new InitialContext(); Context envCtx = (Context) context.lookup("java:comp/env"); // fixed path DataSource DataSource = (DataSource) envctx.lookup (" JDBC /EmployeeDB"); Connection con = datasource.getConnection(); ResultSet rs = con.createStatement().executeQuery( "select * from account"); while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } } catch (Exception e) { e.printStackTrace(); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); }Copy the code}