Chapter 6 database transactions

Posted by erisande on Mon, 03 Jan 2022 08:29:17 +0100

6.1 introduction to database transactions

Transaction: a set of logical operation units that transform data from one state to another.
Transaction processing (transaction operation): ensure that all transactions are executed as a unit of work. Even in case of failure, this execution mode cannot be changed. When multiple operations are performed in a transaction, or all transactions are committed, these modifications will be permanently saved; Or the database management system will discard all the modifications made, and the whole transaction * * rollback * * will return to the original state.
In order to ensure the consistency of data in the database, the manipulation of data should be a discrete group of logical units: when it is completed, the consistency of data can be maintained, and when some operations in this unit fail, the whole transaction should be regarded as an error, and all operations after the starting point should be returned to the starting state.

6.2 JDBC transaction processing

Once the data is submitted, it cannot be rolled back.
When does data mean submission?
- When a connection object is created, the transaction is automatically committed by default: each time an SQL statement is executed, if the execution is successful, it will be automatically committed to the database instead of rolling back.
- **Close the database connection and the data will be submitted automatically** If there are multiple operations, and each operation uses its own separate connection, the transaction cannot be guaranteed. That is, multiple operations of the same transaction must be under the same connection.
In order to execute multiple SQL statements as one transaction in JDBC program:
- Call setAutoCommit(false) of the Connection object; To cancel the auto commit transaction
- After all SQL statements have been successfully executed, call commit(); Method commit transaction
- When an exception occurs, call rollback(); Method rolls back the transaction
If the Connection is not closed at this time and may be reused, you need to restore its automatic submission state setAutoCommit(true). Especially when using database Connection pool technology, it is recommended to restore the auto commit state before executing the close() method.

[case: user AA transfers 100 to user BB]

public void testJDBCTransaction() {
	Connection conn = null;
	try {
		// 1. Get database connection
		conn = JDBCUtils.getConnection();
		// 2. Start transaction
		conn.setAutoCommit(false);
		// 3. Database operation
		String sql1 = "update user_table set balance = balance - 100 where user = ?";
		update(conn, sql1, "AA");

		// Analog network exception
		//System.out.println(10 / 0);

		String sql2 = "update user_table set balance = balance + 100 where user = ?";
		update(conn, sql2, "BB");
		// 4. If there is no exception, commit the transaction
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
		// 5. If there are exceptions, roll back the transaction
		try {
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
    } finally {
        try {
			//6. Restore the automatic submission function of each DML operation
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
        //7. Close the connection
		JDBCUtils.closeResource(conn, null, null); 
    }  
}

Among them, the methods of database operation are:

//General addition, deletion and modification operations after using transactions (version 2.0)
public void update(Connection conn ,String sql, Object... args) {
	PreparedStatement ps = null;
	try {
		// 1. Get the instance of PreparedStatement (or: precompiled sql statement)
		ps = conn.prepareStatement(sql);
		// 2. Fill placeholder
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		// 3. Execute sql statement
		ps.execute();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// 4. Close resources
		JDBCUtils.closeResource(null, ps);

	}
}

6.3 ACID attribute of transaction

Atomicity
Atomicity means that a transaction is an indivisible unit of work, and operations in a transaction either occur or do not occur.
Consistency
Transactions must transition the database from one consistency state to another.
Isolation
Transaction isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.
Durability
Persistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it.

6.3. 1. Database concurrency

For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, various concurrency problems will be caused:
- Dirty read: for two transactions T1 and T2, T1 reads the fields that have been updated by T2 but have not been committed. After that, if T2 rolls back, the content read by T1 is temporary and invalid.
- Non repeatable reading: for two transactions T1 and T2, T1 reads a field, and then T2 updates the field. After that, T1 reads the same field again, and the value is different.
- Phantom reading: for two transactions T1 and T2, T1 reads a field from a table, and T2 inserts some new rows into the table. After that, if T1 reads the same table again, several more rows will appear.
Isolation of database transactions: the database system must have the ability to isolate and run various transactions concurrently, so that they will not affect each other and avoid various concurrency problems.
The degree to which a transaction is isolated from other transactions is called the isolation level. The database specifies multiple transaction isolation levels. Different isolation levels correspond to different interference levels. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

6.3. 2 four isolation levels

The database provides four transaction isolation levels:

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-adjtoghf-1640420190922) (Shang Silicon Valley song Hongkang _JDBC.assets/1555586275271.png)]
Oracle supports two transaction isolation levels: read committed and serial. The default transaction isolation level of Oracle is read committed.
Mysql supports four transaction isolation levels. The default transaction isolation level of Mysql is REPEATABLE READ.

6.3. 3 set isolation level in MySql

Every time you start a mysql program, you get a separate database connection Each database connection has a global variable @@tx_isolation, indicating the current transaction isolation level.
To view the current isolation level:
```
SELECT @@tx_isolation;
```
Set the isolation level of the current mySQL connection:
```
set  transaction isolation level read committed;
```

Set the global isolation level of the database system:

set global transaction isolation level read committed;

Supplementary operation:

Create mysql database user:

create user tom identified by 'abc123';

Grant permissions

#Grant the tom user who logs in through the network with full permissions on all libraries and tables, and set the password to abc123
grant all privileges on *.* to tom@'%'  identified by 'abc123'; 

 #Use the local command line method to grant the tom user the authority to insert, delete, modify and query all tables under the atguigudb library.
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';

Source: Shang Silicon Valley

Topics: Database SQL

Programmer Think