Javaweb Learning Summary (38)--Business

First, the concept of business

　　a transaction is a logical set of actions that make up the units of this set of operations, either all succeed or all are unsuccessful.
For example: a--b transfer, corresponding to the following two SQL statements
Update from account set money=money+100 where name= ' B ';
Update from account set money=money-100 where name= ' A ';

Second, the MySQL database operation transaction command

1. Write the test SQL script as follows:

1/* Create Account Table */2 CREATE TABLE accounts (3     ID int primary KEY auto_increment, 4     name varchar (), 5 money     float 6) ; 7  8/* INSERT TEST Data */9 INSERT INTO account (Name,money) VALUES (' A ', +); INSERT into account (Name,money) VALUES (' B ', 1000 Insert into account (Name,money) VALUES (' C ', 1000);

Below we simulate a--b transfer in MySQL database this business scenario

2.1. Open transaction (START transaction)

Use "Start transaction" to open the transaction for the MySQL database as follows:

　　

We first simulate a failed transfer scenario in the database, and first execute the UPDATE statement to reduce the money for a user by 100 dollars, as shown in:

　　

Then we close the current operation of the DOS command-line window, so that the database has just executed the UPDATE statement of the transaction is not committed, then we have a user's modification is not really a modification, the next time in query a user's money, still the previous 1000, as shown:

　　

2.2. Commit a transaction (commit)

Below we are in the database simulation A--b Transfer Success Scenario

　　

After we manually commit (commit) The database transaction, the a--b transfer of 100 dollars of this business operation is really successful, a account of less 100,b account of 100 more.

2.3. ROLLBACK TRANSACTION (rollback)

　　

By manually rolling back the transaction, all operations are invalidated so that the data goes back to its original initial state!

Iii. using transactions in JDBC

When the JDBC program obtains a connection object to the database, by default the Connection object submits the SQL statement sent on it to the database automatically. If you want to turn off this default submission method and have multiple SQL execute in a single transaction, use the following JDBC control transaction statements

• Connection.setautocommit (false);//Open transaction (START transaction)
• Connection.rollback ();//ROLLBACK TRANSACTION (rollback)
• Connection.commit ();//COMMIT TRANSACTION (COMMIT)

3.1. JDBC Usage Transaction Example

Demonstrate the bank transfer case in the JDBC code so that the following transfer operations are performed in the same transaction

"Update account set money=money-100 where name= ' A '"

Update account set money=money+100 where name= ' B '

The code is as follows:

  1 package Me.gacl.demo;  2 3 Import java.sql.Connection;  4 Import java.sql.PreparedStatement;  5 Import Java.sql.ResultSet;  6 Import java.sql.SQLException;  7 Import Me.gacl.utils.JdbcUtils;  8 Import Org.junit.Test;         9/** * @ClassName: TransactionDemo1 * @Description: Use transactions in JDBC to model the transfer of a. CREATE TABLE account (15 ID int PRIMARY KEY auto_increment, + name varchar (+), + 18); Insert into account (Name,money) VALUES (' A ', 1000); Insert into account (Name,money) VALUES (' B ', 1000); Insert into account (Name,money) VALUES (' C ', 1000);  * @author: Aloof and Pale wolf * @date: 2014-9-22 11:16:17 * * */TransactionDemo1 {27 28/** 29 *     @Method: TestTransaction1 * @Description: The business scenario when the demo transfer was successful * @Anthor: Aloof and pale wolf * * */@Test 35 public void TestTransaction1 () {Connection conn = null; PNS PreparedStatement st = null; Esultset rs = nulL try{conn = Jdbcutils.getconnection (); Conn.setautocommit (false);//Notification Database open transaction (start transaction), String SQL1 = "Update account set money=money-100 where name= ' A '"; st = Conn.preparestatement (SQL1); St.executeupdate (); String sql2 = "Update account set money=money+100 where name= ' B '"; st = Conn.preparestatement (SQL2); St.executeupdate (); Conn.commit ();//The above two SQL execution UPDATE statement after the success of the notification database commit TRANSACTION (COMMIT) SYSTEM.OUT.PRINTLN ("Success!!!  "); log4j Wuyi}catch (Exception e) {e.printstacktrace ();}finally{ S.release (Conn, St, RS); /** * @Method: TestTransaction1 * @Description: An exception occurred during the analog transfer process resulting in a partial SQL execution loss Let the database automatically roll back the transaction after the defeat * @Anthor: Aloof Wolf * * * * * * * * * * * * * * * * * * * Conn public void TestTransaction2 () Ection COnn = null; PreparedStatement st = null; ResultSet rs = null; try{conn = Jdbcutils.getconnection (); Conn.setautocommit (false);//Notification Database open transaction (start transaction) SQL1 String = "Update account set money=money-100 where name= ' A '"; The "St = Conn.preparestatement" (SQL1); St.executeupdate (); 76//Use this code to simulate the execution of the SQL1 after the program has an exception that causes the subsequent SQL to fail to execute properly, the transaction will not be committed, the database automatically perform a rollback operation (int x = 1/0); SQL2 String = "Update account set money=money+100 where name= ' B '"; The St = Conn.preparestatement (SQL2); St.executeupdate (); Bayi Conn.commit ();//The above two SQL execution UPDATE statement after the success of the notification database commit TRANSACTION (COMMIT) SYSTEM.OUT.PRINTLN ("Success!!!") "); }catch (Exception e) {e.printstacktrace ();}finally{Jdbcutils.releas E (Conn, St, RS); /** * @Method: TesttransactIon1 * @Description: An exception occurred during the analog transfer process that caused a portion of SQL execution to fail when manually notifying the database to roll back the transaction. * @Anthor: Aloof Wolf 94 * * * * * * * @Test 97         public void TestTransaction3 () {98 Connection conn = null; PreparedStatement st = null;100 ResultSet rs = null;101 102 try{103 conn = jdbcutils.getconnection (); 104 Conn.set Autocommit (false);//Notify Database open transaction (start transaction) SQL1 String = "Update account set money=money-100 where NA Me= ' A ' "; 106th = Conn.preparestatement (SQL1); 107th.executeupdate (); 108//Use this code to simulate the execution of sq After L1, the program has an exception and the subsequent SQL fails to execute properly, and the transaction fails to commit 109 int x = 1/0;110 String sql2 = "Update account set Money=mo             ney+100 where name= ' B ' "; 111st = Conn.preparestatement (SQL2); St.executeupdate (); 113 Conn.commit ();//The above two SQL execution UPDATE statement after the success of the notification database commit TRANSACTION (COMMIT) SYSTEM.OUT.PRINTLN ("Success!!!       ");}catch (Exception e) {116      try {117//Catch an exception after you manually notify the database to perform a ROLLBACK transaction operation 118 Conn.rollback (); 119} catch (Sqlex Ception E1) {e1.printstacktrace (); 121}122 e.printstacktrace (); 123}fi nally{124 Jdbcutils.release (Conn, St, RS); 125}126}127}

3.2. Set transaction rollback point

In development, it may be necessary to manually set a rollback point for a transaction and set the transaction rollback point in JDBC using the following statement

SavePoint sp = Conn.setsavepoint ();
Conn.rollback (SP);
Conn.commit ();//rollback must notify the database to commit the transaction
　To set the transaction rollback point example :

 1 package Me.gacl.demo; 2 3 Import java.sql.Connection; 4 Import java.sql.PreparedStatement; 5 Import Java.sql.ResultSet; 6 Import java.sql.SQLException; 7 Import Java.sql.Savepoint; 8 9 Import me.gacl.utils.jdbcutils;10 import org.junit.test;11/**13 * @ClassName: TransactionDemo114 * @Description: The transaction is used to model the transfer of the CREATE TABLE account (the ID int primary key auto_increment,18 name varchar (40), 1 9 Money float20): INSERT into account (Name,money) VALUES (' A ', +); INSERT into account (Name,mone Y) VALUES (' B ', +); Name,money values (' C ', +); 11:1 * @author: Aloof and Pale wolf * @date: 2014-9-22 PM 6:1726 *27 * * * public class TransactionDemo2 {/**31 * @Method: testTransaction132 * @Description: Analog transfer Business scenarios When successful * @Anthor: Aloof Wolf *35 * * * @Test37 public void TestTransaction1 () {Connection con  n = null;39 PreparedStatement st = null;40 ResultSet rs = null;41       SavePoint sp = null;42 try{44 conn = Jdbcutils.getconnection (); conn.s Etautocommit (false);//Notify Database open transaction (start transaction). String SQL1 = "Update account set Money=mo ney-100 where name= ' A ' ";"--St.executeupdate st = Conn.preparestatement (SQL1); 50 5  1//SET transaction rollback point Conn.setsavepoint SP = n/A (); String sql2 = "Update account             Set money=money+100 where name= ' B ' ";" st = Conn.preparestatement (SQL2); St.executeupdate (); 57 58//Program execution Here An exception occurs, the following SQL3 statement execution will break the 1/0;60 int x = the L3 = "Update account set money=money+100 where name= ' C '"; Conn.preparestatement (SQL3); St.executeupdate (); Conn.commi              T ();}catch (Exception e) {69   /**70 * We sent 3 UPDATE statements to the database above, the SQL3 statement cannot be executed due to the exception of the program, the database transaction cannot be submitted properly, 72                  * Since the SET transaction rollback point is after the normal execution of the SQL1 statement, 73 * Before the SQL2 statement is executed normally, the update operation performed by the SQL1 is not rolled back when the database is notified that the transaction is rolled back 74 * Only the update operation is rolled back to SQL2, that is, in the three UPDATE statement above, SQL1 the modification operation of this statement is working, the modification operation of the SQL2 is not working because the transaction rollback does not work, SQL3 because the program exception does not have the opportunity to execute 7                 6 */77 Conn.rollback (SP);//rollback to set transaction rollback POINT 78//rollback to remember to notify the database to commit TRANSACTION 79             Conn.commit (); SQLException catch (E1) {Bayi e1.printstacktrace (); 82}83 E.printstacktrace ();}finally{85 Jdbcutils.release (Conn, St, RS); 86}87}88}

Iv. four characteristics of the transaction (ACID) 4.1, atomicity (atomicity)

　　atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either succeed or all fail . For example, in the same transaction, the SQL statement, or all of the execution succeeded, or all failed to execute

4.2. Consistency (consistency)

The concept of consistency in official web transactions is that transactions must transform a database from one consistent state to another . In the case of transfer, A to B transfer, assuming that before the transfer of the two users of the total amount of money is 2000, then A to B after the transfer, regardless of the two account how to turn, a user's money and b users of the total sum of 2000, this is the consistency of the transaction.

4.3. Isolation (Isolation)

The isolation of transactions is when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

4.4. Persistence (Durability)

Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then should not have any effect on the database even if it has failed.

The most troublesome of the four major features of a transaction is isolation, which focuses on the isolation level of a transaction

V. Isolation level of the transaction

When multiple threads Open data in their transactional operations database, the database system is responsible for isolating operations to ensure that each thread is accurate in obtaining data.

5.1. Issues that may arise from transactions that do not consider isolation

If the transaction does not consider isolation, the following issues may be raised:

1. Dirty Reading

　　　　　Dirty read refers to a transaction that reads uncommitted data from another transaction .

This is very dangerous, assuming a to B transfer 100 yuan, the corresponding SQL statement is as follows
1.update account set money=money+100 where name= ' B ';
2.update account set money=money-100 where name= ' A ';
When the 1th SQL is executed, and the 2nd is not executed (A is not committed), if at this point B queries its own account, you will find that they have more than 100 dollars. If A and B go back, B will lose 100 yuan.

2. Non-repeatable reading

　　Non-repeatable reading refers to reading a row of data from a table within a transaction, with multiple read results.
For example, the bank wants to query a account balance, the first query a account is 200 yuan, at this time a to the account memory of 100 and submitted, the bank then made a query, at this time a account is 300 yuan. Bank two queries inconsistent, may be very confused, do not know which query is accurate.
non-repeatable read the difference between dirty reads is that dirty reads are dirty data that was not committed by the previous transaction, and non-repeatable reads are re-reading the data that was submitted by the previous transaction .
Many people think this situation is right, need not be confused, of course, the latter will prevail. We can consider such a situation, such as banking procedures need to output the results of the query to the computer screen and write to the file, the result in a transaction for the destination output, the two queries are inconsistent, resulting in inconsistent results in the file and screen, the bank staff do not know which to prevail.

3. Virtual reading (phantom reading)

　　Virtual Read (phantom reading) refers to the data that is inserted into another transaction within a transaction, resulting in inconsistent read and backward reads .
If the deposit of 100 yuan is not submitted, then the bank to do the report statistics account of all users of the total amount of 500 yuan, and then C submitted, then the bank again to find accounts of 600 yuan, resulting in a virtual reading will also make the bank at a loss, in the end to whichever.

5.2, transaction isolation of the SET statement

The MySQL database has a total of four isolation levels defined:

1. Serializable (serialization): Can avoid dirty reading, non-repeatable reading, the occurrence of false reading.
2. REPEATABLE READ (Repeatable Read): Can avoid dirty read, non-repeatable read situation occurs.
3. Read Committed: Prevents dirty reads from occurring.
4. READ UNCOMMITTED (unread): lowest level, none of the above is guaranteed.

MySQL database queries the current transaction ISOLATION level:SELECT @ @tx_isolation

For example:

　　

　　MySQL database default transaction ISOLATION Level is:REPEATABLE READ (repeatable Read)

MySQL database set TRANSACTION ISOLATION LEVEL:SET TRANSACTION Isolation Levels Isolation level name

For example:

　　

5.3. Use MySQL database to demonstrate concurrency problems at different isolation levels

Open two windows simultaneously to simulate 2 concurrent users accessing the database

1. Dirty reads, non-repeatable reads, and virtual reads are thrown when the isolation level of the transaction is set to READ uncommitted

A window
Set TRANSACTION ISOLATION level read uncommitted;--setting a user's database isolation levels to READ UNCOMMITTED (READ UNCOMMITTED)
Start transaction;--Open Transaction
SELECT * from account;--query a account for existing money, go to the b window to operate
SELECT * from account--found a more than 100 yuan, at this time a read the B uncommitted data (dirty Read)

b window
Start transaction;--Open Transaction
Update account set money=money+100 where name= ' a ';--do not commit, go to A window query

2. When the isolation level of a transaction is set to read committed, non-repeatable reads and reads are thrown, but dirty reads are avoided

A window
Set TRANSACTION ISOLATION level Read Committed;
Start transaction;
SELECT * FROM account;--discovery a account is 1000 yuan, go to window B
SELECT * from account;--found an account of 100 more, at this time, a read the data submitted by other transactions, two read a account read a different result (non-repeatable read)
b window
Start transaction;
Update account set money=money+100 where name= ' AAA ';
commit;--go to Window a

3. When the isolation level of a transaction is set to repeatable read (MySQL default level), a virtual read is thrown, but the dirty read is avoided, the read is not repeatable

A window
Set TRANSACTION ISOLATION level repeatable read;
Start transaction;
SELECT * FROM account;--Discovery table has 4 records, go to Window B
SELECT * from account;--may find that the table has 5 records, when a read to another transaction inserted data (virtual Read)
b window
Start transaction;
Insert into account (Name,money) VALUES (' GGG ', 1000);
commit;--go to Window a

4, when the isolation level of the transaction is set to serializable, all problems will be avoided

A window
Set TRANSACTION isolation level Serializable;
Start transaction;
SELECT * from account;--go to Window B

b window
Start transaction;
Insert into account (Name,money) VALUES (' GGG ', 1000);--found unable to insert, can only wait for a end transaction to insert

Javaweb Learning Summary (38)--Business