The transaction
A transaction is a logical set of operations in which the units that make up the set either all succeed or all fail.
A set of SQL executed using the same connection object (SQL). All will succeed or all will fail.
/* Implement data transfer Tom account -1000 Jerry account +1000 */
update account set money = money- 1000. where name = 'tom';
update account set money = money+1000 where name = 'jerry'; If the first one succeeds and the second fails, then you will find that the money is lost, and vice versa, the money becomes more. So these twosqlShould be in the same transaction, either both succeed, or both fail.Copy the codeIn fact, there are default transactions in the database, which are executed one by one by SQL statement and will be automatically submitted. However, if we want to realize our own requirements, we need to start the transaction and control the transaction.
Transaction operation in MySQL
Start Transaction or begin Starts a transaction
Commit the transaction (no more changes)
Rollback rollback rollback rollback rollback rollback
JDBC Connection transaction operation
SetAutoCommit (false) Starts the transaction
Void the commit ()
Void the rollback () rolled back
Example:
No transaction transfer operation: Tom transfers 1000 to Jerry.
public class TestTranscaction {
public static void main(String[] args) {
QueryRunner qr = new QueryRunner();
Connection con = null;
try {
con = DruidUtils.getConnection();
String sql1 = "update account set money = money - ? where name = ?";
String sql2 = "update account set money = money + ? where name = ?";
int i = qr.update(con,sql1,1000."tom");
int j = qr.update(con,sql2,1000."jerry");
System.out.println(i);
System.out.println(j);
} catch (Exception e) {
e.printStackTrace();
}finally {
DruidUtils.release(null.null,con); }}}Copy the codeAdd a transaction for the transfer case
Con.setautocommit (false);
2 transaction submission method con.com MIT ();
3 Transaction rollback method con.rollback();
public class TestTranscaction {
public static void main(String[] args) {
QueryRunner qr = new QueryRunner();
Connection con = null;
try {
con = DruidUtils.getConnection();
con.setAutoCommit(false);
String sql1 = "update account set money = money - ? where name = ?";
String sql2 = "update account set money = money + ? where name = ?";
int i = qr.update(con,sql1,1000."tom");
int j = qr.update(con,sql2,1000."jerry");
con.commit();
System.out.println(i);
System.out.println(j);
} catch (Exception e) {
e.printStackTrace();
try {
con.rollback();
} catch(SQLException throwables) { throwables.printStackTrace(); }}finally {
DruidUtils.release(null.null,con); }}}Copy the codeWe can think of it as making an exception such as counting 1/0 between two operations and finding that the database has not been changed, instead entering the catch block for the transaction to be rolled back
As long as there is no COMMIT, the transaction will not commit, so why do rollback?
When we start a transaction, we set setAutoCommit(false), which in effect places a lock on the Connection object. Only this Connection can modify data. Therefore, both COMMIT and ROLLBACK release the lock. Otherwise, connections in the connection pool will be occupied and the efficiency will be reduced.
What if the power goes out?
A transaction has a timeout rollback operation. After an incoming call, the transaction performs its own timeout operation and automatically rolls back.
DbUtils Releases resources
CommitAndCloseQuietly(Connection CON) in the DbUtils utility class can perform failed rollback and commit operations, and in addition helps us catch commit method exceptions. DbUtils.commitAndCloseQuietly(con);
/** * Add transaction to transfer case: Use QueryRunner ** How to use transaction controller: Void setAutoCommit(Boolean autoCommit) void setAutoCommit(Boolean autoCommit) void setAutoCommit(Boolean autoCommit) void setAutoCommit(Boolean autoCommit) void setAutoCommit(Boolean autoCommit) Void rollback() to rollback the data from the transaction to the data when the transaction was not enabled * * develop the table: Insert update DELETE must follow the transaction * * QueryRunner: New QueryRunner(DataSource ds) = new QueryRunner(DataSource DS) = new QueryRunner(DataSource DS) = new QueryRunner(DataSource DS) Without * transaction control in our hands, there is no * way to execute SQL statements: Update (passing the connection object) * * Exceptions in this case cannot throw exceptions * Execute an SQL statement twice, but the SQL statement is executed successfully without exceptions * Fail to execute an SQL statement, throw an exception, catch * * DButils: transaction operation */
public class TestAccount {
public static void main(String[] args) {
// Create an object QueryRunner with no constructor parameters
QueryRunner qr = new QueryRunner();
Connection con = null;
try {
// Get the connection object
con = DruidUtils.getConnection();
// Start the transaction to prevent automatic commit
con.setAutoCommit(false);
// Spell the SQL statement, Tom account -1000
String sql1 = "update account set money = money - ? where name = ?";
// Spell the SQL statement, jerry account +1000
String sql2 = "update account set money = money + ? where name = ?";
// Execute SQL statement, Tom account -1000
int i = qr.update(con, sql1, 1000."tom");
// Execute SQL statement, jerry account +1000
System.out.println(1/0);
int j = qr.update(con, sql2, 1000."jerry");
System.out.println("i = " + i);
System.out.println("j = " + j);
// The SQL statement is executed successfully, the transaction is committed, the transaction ends, and the data is persisted
//con.commit();
//Apache tool class: Dbutils
DbUtils.commitAndCloseQuietly(con);
}catch (Exception ex){
ex.printStackTrace();
// An exception occurred, SQL statement execution failed, rollback transaction
/*try { con.rollback(); }catch (SQLException e){ e.printStackTrace(); } * /
DbUtils.rollbackAndCloseQuietly(con);
}finally {
// Release resources
//DruidUtils.release(null,null,con);}}}Copy the codeClassic three-tier architecture
Framework technology in layers
Presentation layer: Struts, SpringMVC
Business layer: There is virtually no single framework because the business direction of each system is very different. In order to unify with the presentation layer and business layer, Spring was given to it. The function of this framework is to coordinate and integrate the presentation layer and business layer framework.
Persistence layer: Hibernate, MyBatis, Spring Data Jpa (new)
Stratified transfer case
Analysis of the
implementation
Pojo class:
@Data
public class Account {
private Integer id;
private String name;
private Double money;
}
Copy the codeDao layer (for database operations) :
The utility class extracts repeated code (in this case, Druid database connection pool)
/** * Implement a custom druid connection pool utility class: fetch the connection * druid connection pool configuration file, use properties, the name is arbitrary ** we need to read the configuration ourselves ** druid connection pool interface implementation class: * DataSource interface implementation class: DruidDataSource * * druid connection pool, provides the tools, the factory class object (create) * use the factory class, obtain DruidDataSource object * DruidDataSourceFactory factory class, The static createDataSource(Properties p) method returns a value that is the interface implementation class object DruidDataSource * * reads the configuration itself, key-value pairs, stored in the collection Properties, The collection object is passed to the createDataSource method */
public class DruidUtils {
private static DataSource dataSource;
static {
try {
// class loader byte input stream, read the configuration file
InputStream in =
DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
Properties prop = new Properties();
// In the set, store key-value pairs
prop.load(in);
in.close();
// The static methods of the factory class create the DataSource interface to implement the class objects
dataSource = DruidDataSourceFactory.createDataSource(prop);
}catch(Exception ex){ ex.printStackTrace(); }}/** * Defines a method that returns the DataSource interface implementation class */
public static DataSource getDataSource(a){
return dataSource;
}
/** * Defines a static method that returns a database connection object */
public static Connection getConnection(a) throws SQLException {
return dataSource.getConnection();
}
/** * Static methods: Release resources * release connection objects, result set objects, SQL statement execution objects */
public static void release(ResultSet rs, Statement stat, Connection con){
if(rs! =null)
try {
rs.close();
}catch (SQLException ex){}
if(stat! =null)
try {
stat.close();
}catch (SQLException ex){}
if(con! =null)
try {
con.close();
}catch (SQLException ex){}
}
}
Copy the codeInvoke the tool class to connect to the database and operate on the database
/** * Operate on the account data table class * business layer call, the result returned business layer */
public class AccountDao {
private QueryRunner qr = new QueryRunner();
/** * Update the data table to receive the business layer parameter: Account object */
public int updateAccount(Connection con,Account account) throws SQLException {
String sql = "update account set money = ? where name = ?";
// Execute the SQL statement
return qr.update(con,sql,account.getMoney(),account.getName());
}
/ * * * *@paramCon Database connection object *@paramName Account name *@returnQuery account data *@throws SQLException
*/
public Account queryAccountByName(Connection con,String name) throws SQLException {
// SQL to query the account
String sql = "select * from account where name = ?";
// Execute the query statement with the result set BeanHandler
return qr.query(con,sql,newBeanHandler<Account>(Account.class),name); }}Copy the codeService Layer (business) :
/** * Realize the business logic of the transfer case * receive the parameters of the presentation layer: payer, payee, amount * call the persistence layer, pass related parameters, complete the transfer logic */
public class AccountService {
private AccountDao accountDao = new AccountDao();
/ * * * *@paramToName Payer *@paramInName Recipient *@paramMoney Amount of transfer */
public void transfer(String toName,String inName,double money){
Connection con = null;
try {
// Get the connection object
con = DruidUtils.getConnection();
// Start the transaction
con.setAutoCommit(false);
// Query the account information of the payer according to the name of the payer
Account toAccount = accountDao.queryAccountByName(con, toName);
// Check the account information of the payee according to the name of the payee
Account inAccount = accountDao.queryAccountByName(con, inName);
// Transfer logic calculates the balance of the toAccount object - parameter money
toAccount.setMoney( toAccount.getMoney() - money );
// Transfer logic is calculated, inAccount object balance + parameter money
inAccount.setMoney( inAccount.getMoney() + money );
// Call the persistence layer method, update the data, passing the parameter Account object
int i = accountDao.updateAccount(con, toAccount);
int a = 1 / 0;
int j = accountDao.updateAccount(con, inAccount);
if( i > 0 && j > 0)
System.out.println("Transfer successful");
// All SQL statements have been executed without exception, commit the transaction
DbUtils.commitAndCloseQuietly(con);
}catch (Exception ex){
ex.printStackTrace();
// An exception occurred and the transaction was rolled backDbUtils.rollbackAndCloseQuietly(con); }}}Copy the code(Web)
// JavaWeb will come later
Copy the codeTransaction Characteristics (ACID)
A: Atomicity means that A transaction is an indivisible unit of work in which either all operations occur or none of them occur.
C: Consistency. Data integrity before and after transactions must be consistent.
I: Isolation. Transaction isolation means that when multiple users access the database concurrently, the transaction of one user cannot be interfered with by other things. The data of multiple concurrent transactions must be isolated from each other.
D: Persistence. Persistence means that once a transaction is committed, its changes to the database are permanent and should not have any impact on the database.
Concurrent access problem
Without regard to isolation, transactions have three kinds of concurrent access problems.
1 Dirty read: one transaction reads uncommitted data from another transaction.
2 Non-repeatable read: a transaction reads data that has been committed by another transaction. Raises another transaction in which multiple queries have inconsistent results.
3 phantom read/virtual read: one transaction has read the data that another transaction has committed (insert), resulting in inconsistent query results in the other transaction.
The isolation level of the transaction — used to address concurrent access issues
The database defines four isolation levels, one for all cases where two transactions are concurrent.
1 Read Uncommitted: read is not committed
Problems: three problems (dirty read, non-repeatable read, magic read)
Solution: None
2 Read COMMITTED: Read is committed
Existing problems: 2 problems (non-repeatable read, unreal read)
Problem solved: 1 problem (dirty read)
3 repeatable Read: Repeatable read. The data read in one transaction is consistent regardless of whether the other transaction is committed or not.
Existing problems: 1 problem (magic reading)
Problem solved: 2 problems (dirty read, unrepeatable read)
4 serializable: Only one transaction can be executed at the same time (equivalent to a single thread)
Problem: None
Problem solved: 3 problems (dirty read, non-repeatable read, magic read)
| The relationship between isolation levels and concurrent access issues | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| Read uncommitted | |||
| Read committed | Square root | ||
| Repeatable read (repeatable read) | Square root | Square root | |
| Serializable | Square root | Square root | Square root |
Security and Performance comparison:
Security: SERIalIZABLE > REPEATable READ > READ COMMITTED > READ UNcommitted
Serializable < REPEATable READ < READ COMMITTED < READ UNcommitted
Default isolation levels for common databases:
MySQL: repeatable read (repeatable read)
Oracle: Read committed
Set transaction isolation in the Connection interface
Note: The default isolation level is usually used in actual development and is the responsibility of the project manager.
/** * Java programs that control the isolation level of transactions * use the default isolation level ** method parameters of type int: 1,2,4,8 * the lower the number, the lower the isolation level */
public class A {
public static void main(String[] args) throws SQLException {
// Connection object interface Connection
// The pointer is valid for this connection
Connection con = null;
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // Set the transaction isolation level for the current connection object}}Copy the codeThis method is in the Connection interface
void setTransactionIsolation(int level)throws SQLException
Copy the codeThis parameter level should take one of the following values
Connection.transaction_READ_UNcommitted
Connection.transaction_READ_COMMITTED
Connection.TRANSACTION_REPEATABLE_READ
Connection.transaction_serialIZABLE
As you can see, all four of these are static int values specified in the Connection interface, because we refer to them as “interface name”. Member variable names “.
The Connection interface also specifies a static int variable, TRANSACTION_NONE, which represents a constant that does not support transactions. This value cannot be used to specify the transaction isolation level of the Connection.
// The value of the member variable defined in the connection interface
int TRANSACTION_NONE = 0;
int TRANSACTION_READ_UNCOMMITTED = 1;
int TRANSACTION_READ_COMMITTED = 2;
int TRANSACTION_REPEATABLE_READ = 4;
int TRANSACTION_SERIALIZABLE = 8;
Copy the code