One thing has n components, which either succeed at the same time or fail at the same time. It's about putting n constituent units into one transaction.
2.mysql transactions
Default transaction: An sql statement is a transaction, which opens and commits the transaction by default
Manual transactions:
1) Open a transaction displayed: start transaction
2) Transaction submission: commit represents all sql in the middle from open transaction to transaction submission, which considers valid and genuine updates to the database
3) transaction rollback: rollback represents transaction rollback, from opening transaction to transaction rollback, all sql operations in the middle think that the invalid database has not been updated.
3.JDBC Transaction Operation
The default is automatic transactions:
Execute the SQL statement: executeUpdate() executeUpdate () method is executed once, automatically committing on behalf of the transaction
Manual transactions through jdbc's API:
Open transaction: conn.setAutoCommit(false);
Commit transaction: conn.commit();
Rollback transaction: conn.rollback();
Note: The connection that controls the transaction must be the same
The connection that executes sql and the connection that opens a transaction must be the same in order to control the transaction
4.DBUtils transaction operation
1)QueryRunner
Parametric construction: Query Runner runner = new Query Runner (Data Source Data Source);
When the data source (Connection pool) is passed into Query Runner as a parameter, it will get a database link pool cluster from the link pool to operate the database, so it can operate the database directly by using the method without the Connection parameter.
Parametric-free structure: Query Runner runner = new Query Runner ();
The parameterless structure does not pass the data source as a parameter to QueryRunner, so we use the method with connection parameter when using QueryRunner object to operate database.
5. Transaction characteristics and isolation level (interview)
1) Transaction characteristics ACID
1. Atomicity refers to the fact that a transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur.
2. Data integrity must be consistent before and after a consistent transaction
3. Isolation of multiple transactions. Transaction isolation means that when multiple users access the database concurrently, one user's transaction can not be interfered by other users'transactions, and data between multiple concurrent transactions should be isolated from each other.
4. Persistence means that once a transaction is submitted, its changes to the data in the database are persistent. Next, even if the database fails to send, it should not have any impact on it.
2) Concurrent problems - caused by isolation
1. Dirty Reading: Transaction B reads data that transaction A has not yet submitted - --- Requires transaction B to read data submitted by transaction A.
2. Non-repeatable reading: The content of the data read twice in a transaction is inconsistent - - the requirement is that the data read multiple times in a transaction is consistent.
3. Fantasy/Virtual Reading: In a transaction, the number of data read twice is inconsistent - the number of data read multiple times in a transaction is consistent
3) Transaction isolation level
1.read uncommitted: read uncommitted data that has not yet been submitted - no problem can solve dirty reading
2.read commited: Read submitted data -- solve dirty reading
3.repeatable read: re-read: can solve dirty reading and non-repeatable reading
4.serializable: Serialization - Can solve dirty reading, non-repeatable reading, virtual reading, equivalent to the lock table
Note: mysql database default isolation level: select @@tx_isolation;
set session transaction isolate level read uncommitted;
Conclusion:
mysql transaction control:
Open transaction: start transaction;
Submit: commit;
Roll back transactions: rollback;
jdbc transaction control:
Open transaction: conn.getAutoCommit(false);
Commit transaction: conn.commit();
Rollback method: conn.rollback();
Transaction control for DBUtils: also through jdbc
ThreadLocal: It's possible to pass parameters through thread binding
Concept:
Transaction characteristics: atomicity, consistency, isolation, persistence
Concurrent problems: dirty reading, no re-reading, virtual reading and illusory reading
Solving concurrency problems: setting isolation levels
read uncommitted
read committed;
repeatable read(mysql default)
serialazable
Isolation level performance: read uncommitted > read committed > repeatable read > serialazable
Security: Conversely
index.jsp
<body>
<h1>Transfer page</h1>
<form action="${pageContext.request.contextPath }/accountServlet" method="post">
<table border="1" width="400">
<tr>
<td>Drawee:</td>
<td><input type="text" name="from"/></td>
</tr>
<tr>
<td>Payee:</td>
<td><input type="text" name="to"/></td>
</tr>
<tr>
<td>Transfer amount:</td>
<td><input type="text" name="money"/></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Transfer accounts"/></td>
</tr>
</table>
</form>
</body>public class AccountServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AccountServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
/**
* receive data
* Encapsulated data
* Call the business layer
* Page jump responds
*/
response.getWriter().write("hello");
request.setCharacterEncoding("utf-8");
//receive data
String from=request.getParameter("from");
String to=request.getParameter("to");
double money=Double.parseDouble(request.getParameter("money"));
//Call the business layer
AccountService accountService=new AccountService();
accountService.transfer(from,to,money);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
public class AccountService {
/**
* Method of Business Level Transfer
* Drawee
* Payee
* Transfer amount
*/
public void transfer(String from,String to,double money){
Connection conn=null;
try{
//Get links
String url="jdbc:mysql://localhost:3306/web";
String userName="root";
String pwd="";
try{
conn=DriverManager.getConnection(url,userName,pwd);
if(conn!=null){
}
}catch(Exception e){
e.printStackTrace();
}
//conn.JdbcUtils.getConnection();
//Open transaction
conn.setAutoCommit(false);
//Call DAO
AccountDao accountDao=new AccountDao();
accountDao.outMoney(conn,from,money);
accountDao.inMoney(conn,to,money);
conn.commit();
}catch(SQLException e){
try{
if(conn!=null){
conn.rollback();
}
}catch(Exception e2){}
throw new RuntimeException(e);
//e.printStackTrace();
}finally{
}
}
}
public class AccountDao {
public void outMoney(Connection conn,String outUser,int money) throws SQLException{
PreparedStatement psmt=null;
ResultSet rs=null;
try{
String sql="update account set money=money-? where username=?";
psmt=conn.prepareStatement(sql);
psmt.setInt(1, money);
psmt.setString(2, outUser);
int r=psmt.executeUpdate();
System.out.println(r);
}catch(Exception e){
throw new RuntimeException(e);
}finally{
psmt.close();
rs.close();
}
}
public void inMoney(Connection conn,String inUser,int money) throws SQLException{
PreparedStatement psmt=null;
ResultSet rs=null;
try{
String sql="update account set money=money+? where username=?";
psmt=conn.prepareStatement(sql);
psmt.setInt(1, money);
psmt.setString(2, inUser);
int r=psmt.executeUpdate();
System.out.println(r);
}catch(Exception e){
throw new RuntimeException(e);
}finally{
psmt.close();
rs.close();
}
}
}