JDBC Learning notes-transactions, stored procedures, and batch processing

1. Business

1.1. Basic concepts and usage examples of transactions

A database transaction is a sequence of operations performed as a single logical unit of work, either fully executed or completely non-executed. Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable. To become a transaction, a logical unit of work must satisfy the so-called acid (atomicity, consistency, isolation, and persistence) attributes.

JDBC can manipulate connection's Setautocommit () method, give it a false argument, prompt the database to start a transaction, and, after releasing a series of SQL commands, invoke the connection commit () method, prompting the database to confirm ( Commit) operation. If an error occurs in the middle, call ROLLBACK (), prompting the database to revoke (ROLLBACK) all execution. Also, if you want to recall only one of the SQL execution points, you can set the storage point (savepoint). A demonstration of the transaction flow is as follows:

?

123456789101112131415161718192021222324252627282930313233343536

Connection conn = ...;Savepoint point = null;try {    conn.setAutoCommit(false);    Statement stmt = conn.createStatement();    stmt.executeUpdate("INSERT INTO ...");    ...    point = conn.setSavepoint();    stmt.executeUpdate("INSERT INTO ...");    ...    conn.commit();} catch (SQLException e) {    e.printStackTrace();    if (conn != null) {        try {            if (point == null) {                conn.rollback();            } else {                conn.rollback(point);                conn.releaseSavepoint(point);            }        } catch (SQLException ex) {            ex.printStackTrace();        }    }} finally {    ...    if (conn != null) {        try {            conn.setAutoCommit(true);            conn.close();        } catch (SQLException ex) {            ex.printStackTrace();        }    }}

It is necessary to note that the JDBC operational transaction is premised on the fact that the database supports transactions, and if the database itself does not support transactions, we cannot start the transaction even if we call Setautocommit (false). For MySQL, the MyISAM database engine does not support transactional operations, and the INNODB database engine supports transactional operations.

1.2. Isolation LEVEL

To understand the isolation level, the first thing to know about multiple transactions and rows is that there are data inconsistencies that can arise, and common transaction-parallel issues arise in the following categories:

Update lost

Update loss is the information that a transaction updates a field, and the effectiveness of the update is lost due to the intervention of another transaction, a simple example is as follows:

1. Transaction a updates the Data table field as AAA;
2. Transaction b updates the Data table field with BBB;
3. Transaction a submission;
4. Transaction B commits.

This sequence is a typical update loss, as all changes made in the third step are lost. If you want to avoid update loss problems, you can set the isolation level to "READ UNCOMMITTED", so that a transaction has been updated but unconfirmed data, b transactions can only do read operations, but not update operations. So the above four steps is illegal, must be a transaction fully committed, B transaction to do the update operation.

Dirty Read

The "READ UNCOMMITTED" isolation level guarantees that the B transaction cannot make a change operation until the a transaction commits, but does not prevent the B transaction from being read, but this is problematic: if the A Transaction update field is "AAA", the B transaction reads "AAA" and uses it, Then a transaction rolls back the transaction, then the "AAA" read by the B transaction is dirty data. If you want to avoid dirty read problems, you can set the isolation level to "read commited", that is, the data that the transaction reads must be data that other transactions have already acknowledged.

Non-REPEATABLE READ

Non-repeatability refers to the inconsistency of data that reads the same field two times, for example: Transaction a reads "AAA", transaction b updates the data to "BBB", transaction B commits, transaction a reads "BBB", transaction A is read two times, and the field value is different. To avoid this problem, you can set the database isolation level to "repeatable read", for which transaction a reads the field "AAA", and the other transaction can only read the field before transaction a commits and cannot update the field.

Phantom reading

Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, such as "All rows of data" in the table. At the same time, the second transaction modifies the data in the table by inserting a "new row of data" into the table. In the future, the user who operates the first transaction will see that there are no modified rows of data in the table, as if the illusion had occurred. To solve the Phantom read problem, you must set the isolation level to serializable,serializable is the highest level of the database isolation level, serialization reads, transactions can only be executed one at a time, avoiding dirty reads, non-repeatable reads, Phantom reads, but slow execution and need to be used sparingly.

1.3, pessimistic lock, optimistic lock

Pessimistic lock is to adopt a pessimistic attitude to deal with the problem of concurrency, that the system of concurrent updates will be very frequent, and the transaction failed after the cost is very large, so that we need to adopt a real sense of the lock to implement. The implementation of pessimistic locks often relies on the lock mechanism provided by the database. The basic idea of pessimistic locking is that every time a transaction reads a record, it locks the record so that other transactions want to be updated and must wait for the previous transaction to commit or rollback the unlock.

Optimistic lock, as the name implies is to maintain an optimistic attitude, we believe that the system of transaction concurrency update will not be very frequent, even if the conflict is OK, the big deal again. The basic idea is that each time a transactional update is committed, we first look at what has been modified since the last read, and if modified, the update will fail. Optimistic locks are implemented mostly based on the data version (versions) recording mechanism. What is a data version? is to add a version identity to the data, which is typically done by adding a "version" field to the database table in the version solution based on the database table. When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data.

In general, the mechanism of pessimistic locking relies on the lock mechanism of database, which is more secure, and the optimistic locking mechanism is controlled by application, which has better performance.

2. Stored Procedures

JDBC can call stored procedures, and to invoke stored procedures, we should first create a stored procedure:

?

123456789101112

//创建表，并插入数据create table g(num int,value  varchar(10));  insert into g values(1, ‘1‘),(10, ‘10‘),(60, ‘60‘),(100, ‘100‘);//创建存储过程DELIMITER $CREATE PROCEDURE p1(IN n int, OUT avg double, OUT min int)BEGIN    select avg(num) from g where num > n INTO avg;    select min(num) from g where num > n INTO min;END$DELIMITER ;

　　The JDBC call stored procedure should use CallableStatement, and the following is a simple example:

?

1234567891011121314151617

private static void ps() throws SQLException{    Connection conn = null;    CallableStatement cs = null;    try{        conn = JdbcUtils.getConnection();                cs = conn.prepareCall("call p1(?,?,?)");        cs.registerOutParameter(2, Types.DOUBLE);//设置out参数        cs.registerOutParameter(3, Types.INTEGER);//设置out参数        cs.setInt(1, 18);//设置in参数                cs.executeUpdate();        System.out.println(cs.getInt(2) + "   " + cs.getInt(3));    } finally{        JdbcUtils.free(null, cs, conn);    }   }

3. Batch Update

If you need to make a large amount of data updates to the database, it is wasteful to use the cyclic multiple operations update, for this scenario, we can use the Addbatch () method to collect SQL and use the ExecuteBatch () method to update the collected SQL batches, for example:

?

12345

Statement stmt = conn.createStatement();while(someCondition) {    stmt.addBatch("INSERT INTO ...");}stmt.executeBatch();

JDBC Learning notes-transactions, stored procedures, and batch processing