The properties of transactions are ACID

Transactions are the basic unit of concurrency control. A transaction is a sequence of SQL statement operations that are either executed or not executed. It is an indivisible unit of work.

For example, in a bank transfer, a debit is deducted from one account and a debit is added to another account. Both operations are performed or neither is performed.

Transactions for a database must be ACID, which stands for Atomic, Consistensy, Isolation, and persistence.

1. Atomicity

All operations in a transaction either complete or do not complete and do not end up somewhere in the middle. If an error occurs during the execution of a transaction, it is rolled back to the state before the transaction began, as if the transaction had never been executed.

2. Consistency

The database must be in a consistent state before and after a transaction.

If the transaction completes successfully, all changes to the database take effect.

If a transaction execution error occurs, all changes to the database are rolled back (undone) and returned to the original state.

3. Persistence:

When a transaction is committed, its changes to the database are permanent, and subsequent database failures have no impact on the data.

4. Isolation

When multiple users concurrently access the database, the transactions of one user cannot be disturbed by the transactions of other users, and multiple concurrent transactions must be isolated from each other.

Multiple transaction events are independent of each other and cannot interfere with each other.

Isolate the problem

Dirty read: a transaction reads another transactionUncommitted data

Suppose that A transfers 100 yuan to B, the corresponding SQL statement is as follows:

Update account set money=money-100 where name= ‘a’; Update account set money=money+100 where nam= ‘b’;

After the two SQL statements are executed, B queries his own account and has 100 yuan more.

After B leaves, A rolls back the transaction, so B loses $100.

A transaction reads data that was not committed by another transaction. Very serious. Dirty reading should be avoided whenever possible.

Non-repeatable reads: data from a table is read multiple times within a transaction, with different results.

Phantom reading (virtual reading)

The insertion or deletion of data records by another transaction in one transaction, resulting in inconsistent data reads by the current transaction.

Third deposit 100 yuan but did not submit, at this moment the bank does statistic of report form total amount is 500 yuan, third will business is submitted, the bank became 600 yuan again statistic, two statistic result is inconsistent, the bank can be at a loss.

One transaction reads data that another transaction has committed, emphasizing changes in the number of records, often with SQL types of INSERT and DELETE.

The difference between virtual and non-repeatable reads:

Virtual reads emphasize changes in the number of data table records, primarily in INSERT and DELETE statements.

Non-repeatable reads emphasize changes to the contents of the data table, mainly update statements.

Isolation level of the database

Isolation level performance issues

Performance comparison:

Serializable has the worst performance, with multiple transactions queued.

serializable < repeatable read < read committed < read uncommitted

Safety comparison:

Serializable has the best security performance and all problems can be avoided

serializable > repeatable read > read committed > read uncommitted

Analysis:

Serializable performance is poor

Read uncommitted Dirty reads cannot be avoided, which is a serious problem

Summary: The database vendor has chosen this scenario: read committed and repeatable reads

Repeatable read – mysql select repeatable read

Oracle selects read Committed

Transaction management in Java

Add the transaction

Demand: A transfers 100 yuan to B, simulation is abnormal. (Emphasize the importance of transactions: all or nothing.)Copy the code

	@Test
	public void demo2(a) {
		// Demand: A transfers 100 yuan to B, simulation is abnormal. (Emphasize the importance of transactions: all or nothing.)
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			// int j = 1 / 0;
			// Get the connection
			conn = JDBCUtils.getConnection();
			// Start the transaction
			conn.setAutoCommit(false);

			// a-100
			// Get the object to send the SQL
			String sql = "update account set money=money-100 where name='a'";
			stmt = conn.prepareStatement(sql);
			// Send SQL to get results
			stmt.executeUpdate();

			// b+100
			// Get the object to send the SQL
			String sql2 = "update account set money=money+100 where name='b'";
			stmt = conn.prepareStatement(sql2);
			// Send SQL to get results
			stmt.executeUpdate();

			int j = 1 / 0;
			System.out.println("All ok, commit transaction");
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Exception occurred, rollback transaction");
			try {
				if(conn ! =null) { conn.rollback(); }}catch(Exception e1) { e1.printStackTrace(); }}finally{ JDBCUtils.release(conn, stmt); }}Copy the code

DBUtils transaction operation

/* DBUtils transaction */
public class TransactionDemo2 {

    /* 1. Get connection 2. Start transaction 3. Commit transaction to release resource 5. If an exception occurs, roll back transaction to release resource */
    @Test
    public void test1(a) throws SQLException {
        Connection conn = null;
        try {
            //1. Get the connection
            conn =  DruidUtils.getConnetion();
            // 2. Start transaction
            conn.setAutoCommit(false);
            // 3. SQL = add money, subtract money
            QueryRunner qr = new QueryRunner();
            / / lose money
            String sql = "update account set money=money-? where name=?";
            qr.update(conn, sql, 1000."jack");
            // Simulation error
            int n =1/0;
            / / add money
            sql = "update account set money=money+? where name=?";
            qr.update(conn, sql, 1000."rose");
            //4. Commit transaction to release resources
            DbUtils.commitAndCloseQuietly(conn);
        } catch (Exception e) {
            e.printStackTrace();
            //5. If an exception occurs, roll back the transaction to release resourcesDbUtils.rollbackAndCloseQuietly(conn); }}}Copy the code