Thing:
Things are a logical set of operations in which the units that make up the set of operations either succeed or fail entirely In short, a set of operations either succeeds or fails; one thing has n components or n components succeed at the same time or n components fail at the same time.
mysql transaction
1) Default transaction:
A sql statement is a transaction, which opens and commits by default
MySql commits automatically by default, that is, it commits a transaction by executing an sql statement;
2) Manual transactions:
1) Open a transaction as shown: start transaction;
2) Transaction commit: commit represents all SQLs from open transaction to transaction commit that consider valid and genuine updates to the database
3) Rollback of transactions: rollback represents the rollback of transactions All sql operations between the opening of a transaction and the rollback of a transaction are considered invalid databases have not been updated 
There are two ways to manage transactions in MySql:
Mode 1: Close automatic transaction for mysql
set autocommit = 0;Note: This setting is only valid for current connections, new windows are still automatic transactions
Mode 2: Manually open a transaction:
start transaction; --Manually open a transaction --Pretend to do a series of sql operations Commit; --commit a transaction: equivalent to persisting data in a data database Rollback; --rollback transaction: equivalent to undoing all previous operations
Be careful:
1) After a transaction is opened, it must eventually be committed or rolled back
2) Manual transaction is the default in oracle and automatic transaction is the default in mysql
3) Connnections controlling transactions must be the same
The connection to execute sql and the connnection to open a transaction must be the same to control the transaction
Transaction actions in jdbc:
API for Connection Interface (search connection in jdk api)
- setAutoCommit(false): Set the default not to automatically commit transactions
- commit(); commit the transaction.Persist data in database
- rollback(); rollback of the transaction.Undo an action in something
- rollback(savepoint sp); rollback of transaction to savepoint
Transfer cases:
Transaction control needs to be in the same connection, so in the following case, an object connection is passed uniformly, guaranteeing that it is in the same connection.
package com.itheima.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import com.itheima.domain.Transfer;
public class Test01 {
@Test
public void test01() throws Exception{
//Register Driver
Class.forName("com.mysql.jdbc.Driver");
//Connect to database
String url = "jdbc:mysql://localhost:3306/day21";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
//Close automatic transaction
connection.setAutoCommit(false);
//Encapsulate a javaBean
Transfer transfer = new Transfer();
transfer.setFromUser("jack");
transfer.setToUser("rose");
transfer.setMoney(500);
try {
//Transfer money
transferOut(connection, transfer);
//If an accident occurs
//int i = 5/0;
//Transfer into money
transferIn(connection, transfer);
//Submit Transaction
connection.commit();
} catch (Exception e) {
e.printStackTrace();
//rollback
connection.rollback();
System.out.println("Transfer failure");
}finally{
//close resource
connection.close();
}
}
/**
* @Title: Test01.java
* @Description: TODO(Transfer of funds from the transferor's account)
* @param connection
* @author jjizh
* @date 2017 30 June 2003 8:33:00 p.m.
* @version V1.0
* @throws SQLException
*/
public void transferOut(Connection connection,Transfer transfer) throws SQLException{
//Get transfer amount and from whom
double money = transfer.getMoney();
String name = transfer.getFromUser();
//Create an object to execute sql statements
String sql = "update user set money = money - ? where name = ? ";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
prepareStatement.setDouble(1, money);
prepareStatement.setString(2, name);
//Execute sql statement
prepareStatement.executeUpdate();
//close resource
prepareStatement.close();
}
/**
* @Title: Test01.java
* @Description: TODO(Transfer of funds to payee)
* @param connection
* @param transfer
* @throws SQLException
* @author jjizh
* @date 2017 30 June 2003 8:48:22 p.m.
* @version V1.0
*/
public void transferIn(Connection connection,Transfer transfer) throws SQLException{
//Obtain transfer amount and to whom
double money = transfer.getMoney();
String name = transfer.getToUser();
//Create an object to execute sql statements
String sql = "update user set money = money + ? where name = ? ";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
prepareStatement.setDouble(1, money);
prepareStatement.setString(2, name);
//Execute sql statement
prepareStatement.executeUpdate();
//close resource
prepareStatement.close();
}
}DBUtils Transaction Operation
api in DBUtils
* commitAndClose(Connection conn); * commitAndCloseQuietly(Connection conn); do not throw an exception * rollbackAndClose(Connction conn); * rollbackAndCloseQuietly(Connection conn); no exceptions thrown
queryRunner operation sql:
1) new QueryRunner(DataSource ds): The default is automatic transactions (this is the method we used before)
query(String sql,ResultSetHandler rsh,Object...params);
update(String sql,Object...params);
Using the above method, we did not call setAutoCommit(false), did not commit a transaction, and did not close resources
2) new QueryRunner(): Manual transaction
query(Connection conn,String sql,ResultSetHandler rsh,Object...params);
update(Connection conn,String sql,Object...params);
When using the query and update methods, we need to pass in a connection object, and finally we need to commit or roll back the transaction manually, as well as release the conn manually.
Case Supplementary Points:
1) Actively throw an exception
throw new Exception("")
if(!flag2){
throw new RuntimeException("Sorry, transition failed");
}2) Get thrown information from exceptions
Exception.getMessage();
catch (Exception e) {
e.printStackTrace();
request.setAttribute("msg", e.getMessage());
}Code:
Transferservlet:
public class TransferServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
try {
//Create transfer object
Transfer t = new Transfer();
//Receive parameters
Map<String, String[]> map = request.getParameterMap();
//Pass parameter into object
BeanUtils.populate(t, map);
//Call service to complete transfer
TransferService ts = new TransferService();
ts.transfer(t);
request.setAttribute("msg", "Transfer succeeded");
request.setAttribute("transfer", t);
request.getRequestDispatcher("/index.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
request.setAttribute("msg", e.getMessage());
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}TransferService:
public class TransferService {
public void transfer(Transfer t) throws Exception {
Connection connection = null;
try {
//Get Connected
connection = JDBCUtils.getConnection();
//Open Manual Transaction
connection.setAutoCommit(false);
TransferDao td = new TransferDao();
//Outgoing
boolean flag1 = td.moneyOut(connection,t);
if(!flag1){
throw new RuntimeException("Sorry, the transfer failed");
}
//To change into
boolean flag2 = td.moneyIn(connection,t);
if(!flag2){
throw new RuntimeException("Sorry, transition failed");
}
//Submit commit
connection.commit();
} catch (Exception e) {
JDBCUtils.rollbackQietly(connection);
throw e;
} finally{
//Release Resources
JDBCUtils.closeQuietly(connection);
}
}
}Final Transfer Case:
ThreadLocal
This class provides thread-local variables.These variables differ from their common counterparts because each thread accessing a variable through its get or set method has its own local variable, which is independent of the initial copy of the variable.ThreadLocal instances are typically private static fields in classes that want to associate a state with a thread, such as a user ID or transaction ID.
For example:
private static ThreadLocal tl = new ThreadLocal<T>();
private static ThreadLocal tl = new ThreadLocal<Connection>();threadLocal is equivalent to a map, with the id of the current thread as a key, and a value stored as the corresponding
In api:
set (T value) passes in a value directly, setting a value
get (): No arguments need to be passed in, key is the current thread
remove (): No arguments need to be passed in, key is the current thread 
So the optimization process for transfers can be optimized to look like this: 
So:
The service layer code is optimized to:
TransferService_thrl:
public class TransferService_thrl {
public void transfer(Transfer t) throws Exception {
try {
//Open Manual
JDBCUtils.startTransaction();
TransferDao_thrl td = new TransferDao_thrl();
//Outgoing
boolean flag1 = td.moneyOut(t);
if(!flag1){
throw new RuntimeException("Turn out failed!!!!");
}
//To change into
boolean flag2 = td.moneyIn(t);
if(!flag2){
throw new RuntimeException("Turn into failure!!!!");
}
//Submit Transaction
JDBCUtils.commitAndClose();
} catch (Exception e) {
JDBCUtils.rollbackAndClose();
e.printStackTrace();
}
}
}The functions in JDBCUtils are optimized to:
public class JDBCUtils {
private static DataSource ds = new ComboPooledDataSource();
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
public static DataSource getDataSource(){
return ds;
}
public static Connection getConnection() throws SQLException{
//Get the connection from the current thread;
Connection connection = tl.get();
if(connection == null){
connection = ds.getConnection();
tl.set(connection);
}
return connection;
}
//Open a transaction
public static void startTransaction() throws SQLException {
Connection connection = getConnection();
connection.setAutoCommit(false);
}
//Submit Transaction
public static void commitAndClose() {
try {
Connection connection = getConnection();
connection.commit();
connection.close();
} catch (SQLException e) {
// quiet
// e.printStackTrace();
}
}
public static void rollbackAndClose() {
try {
Connection connection = getConnection();
connection.rollback();
connection.close();
} catch (SQLException e) {
// quiet
// e.printStackTrace();
}
}
}Note: The service in the servlet is executed each time in a new thread
Transaction Attribute ACID
1) Atomicity: Atomicity
- Transactions are inseparable and can contain multiple operations that either succeed or fail
2) Consistency: Consistency
- Business status before and after transaction execution should be consistent with other business status
3) Isolation: isolation
-Execution of one transaction, unaffected by other transactions
4) Persistence: Durability
- Once a transaction is committed, the data is persisted in the database
Concurrent access issues:
If isolation is not considered, there are three concurrent access issues with transactions:
-Dirty Read: One transaction read data that another transaction did not commit
-Non-repeatable read: In a transaction, the results of two queries are inconsistent (for update)
-Virtual Read: In a transaction, the results of two queries are inconsistent (for insert)
Set isolation level to avoid problems
- read uncommitted read uncommitted
-All of the above problems are unavoidable
- read committed read submitted
- to avoid dirty reading, non-repeatable reading and hallucinations
- repeatable read
-It avoids dirty and unrepeatable reading and hallucinations
- serializable serialization
- All problems can be avoided, one transaction is executing and another transaction is waiting
- Security: serializable > repeatable read > read committed > read uncommitted
Efficiency is just the opposite of above
Default isolation level for mysql: repeatable read
oracle's default isolation level: read commttied-read only-serializable
-
java code modifies the isolation level of the database (understand):
- conn.setTransactionIsolation(int level)
In MySql:
1) Set the isolation level of the database to read uncommitted
set session transaction isolation level read uncommitted;
2) View isolation levels
select @@tx_isolation;
3) Set the isolation level of the database to read committed
set session transaction isolation level read committed;
Unavoidable unrepeatable reading and hallucinations
4) Set the isolation level of the database to repeatable read
set session transaction isolation level repeatable read;
5) Set the isolation level of the database to serializable
set session transaction isolation level serializable;
Output diamond in jsp
Output in console:
@Test
public void test01(){
int size = 10;
for(int x=-size;x<=size;x++){
for(int y=-size;y<=size;y++){
if(y+x<=size && y+x>=-size && y-x<=size && y-x>=-size ){
System.out.print("*");
}else{
System.out.print(" ");
}
}
System.out.println();
}
}
}Output on jsp page
Because jstl is used in jsp pages, loops can only start from 0, not negative numbers, so you need to manually adjust the coordinate system position
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Output Diamond</title>
</head>
<body>
<c:forEach var="x" begin="0" end="5" step="1" varStatus="xStatus">
<c:forEach var="y" begin="0" end="5" step="1" varStatus="yStatus">
<c:choose>
<c:when test="${x+y<=6 && x+y>=2 && x-y<=2 && x-y>=-2}">
*
</c:when>
<c:otherwise>
</c:otherwise>
</c:choose>
</c:forEach>
<br/>
</c:forEach>
</body>
</html>JSP Three-tier Architecture
Package name: 