JDBC Transaction control Management

1. Business

(1) 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 account set money=money-100 where name= ' a ';

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

The database default transaction is automatically committed, that is, it executes a single SQL message. If you want multiple SQL to be executed in a single transaction, you need to use the following statement.

(2) Database open Transaction command

One way: Manage transactions with SQL statements

Start transaction;--Open the transaction, the SQL statement after this statement will be in a transaction, these SQL statements are not immediately executed

commit--commits the transaction, and all SQL statements in the transaction are executed once the transaction is committed.

Rollback--Rolls back the transaction and cancels all previous SQL.

Mode two: There is an autocommit variable in the database, via show variables like '%commit% '-----The autocommit value is on, which indicates that transaction autocommit is turned on.

You can set Autocommint = Off (set autocommint=0) to turn off autocommit, and the SQL statement entered will not be automatically submitted, requiring a manual roolback or commit

2. Use transactions

(1) 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. To turn off this default submission method and have multiple SQL execute in a single transaction, use the following statement:

(2) JDBC Control TRANSACTION statement

Connection.setautocommit (FALSE); Equivalent to start transaction

Connection.rollback (); Rollback

Connection.commit (); Commit

3. Demo Bank transfer case

(1) in the JDBC code, the following transfer operations are performed in the same transaction.

Update from account set money=money-100 where name= ' a ';

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

(2) Set up a transaction rollback point

SavePoint sp = Conn.setsavepoint ();

Conn.rollback (SP);

Conn.commit (); Must be submitted after rollback

Package com.itheima.transaction;

Import java.sql.Connection;

Import Java.sql.DriverManager;

Import java.sql.PreparedStatement;

Import java.sql.SQLException;

Import Java.sql.Savepoint;

Import Org.junit.Test;

Import Com.itheima.util.DaoUtil;

public class Demo1 {

@Test

public void Test1 () {

Connection conn = null;

PreparedStatement PS1 = null;

PreparedStatement PS2 = null;

SavePoint sp = null;

try{

Class.forName ("Com.mysql.jdbc.Driver");

conn = Drivermanager.getconnection ("Jdbc:mysql:///day11", "root", "root");

Conn.setautocommit (FALSE);

PS1 = conn.preparestatement ("Update account set money = money+100 where name=?");

Ps1.setstring (1, "B");

Ps1.executeupdate ();

int i = 1/0;

PS2 = conn.preparestatement ("Update account set money = money-100 where name=?");

Ps2.setstring (1, "a");

Ps2.executeupdate ();

SP = Conn.setsavepoint ();

//-----------------------------------

PS1 = conn.preparestatement ("Update account set money = money+100 where name=?");

Ps1.setstring (1, "B");

Ps1.executeupdate ();

int i = 1/0;

PS2 = conn.preparestatement ("Update account set money = money-100 where name=?");

Ps2.setstring (1, "a");

Ps2.executeupdate ();

Conn.commit ();

}catch (Exception e) {

E.printstacktrace ();

try {

if (sp!=null) {

Conn.rollback (SP);

Conn.commit ();

}else{

Conn.rollback ();

}

} catch (SQLException E1) {

TODO auto-generated Catch block

E1.printstacktrace ();

}

}finally{

Daoutil.close (conn, PS1, NULL);

Daoutil.close (conn, PS2, NULL);

}

}

}

The JDBC API supports transaction locking of the database and provides 5 operations support and 2 lock densities.

5 Kinds of lock support for:

static int transaction_none = 0;

static int transaction_read_uncommitted = 1;

static int transaction_read_committed = 2;

static int transaction_repeatable_read = 4;

static int transaction_serializable = 8;

The specific instructions are shown in table 4-2.

2 Kinds of lock density:

The last item is locked for the table and the remaining four lines are locked.

JDBC sets transaction support and locks on the basis of the default values provided by the database, and can, of course, be set manually:

Settransactionisolation (transaction_read_uncommitted);

You can view the current settings for the database:

Gettransactionisolation ()

It is important to note that the database and its drivers must support the appropriate transaction operation before the manual setting is performed.

The above setting increases with the value, its transaction independence increases, can effectively prevent the conflict between transaction operations, but also increases the overhead of locking, reduces the concurrency of accessing database between users, and the running efficiency of the program decreases. The conflict between the efficiency of the program and the consistency of data is therefore balanced. In general, for queries involving only the database, you can use the Transaction_read_uncommitted method, and for data queries far more than the updated operation, you can use transaction_read_committed mode , for more updates, you can use transaction_repeatable_read; in the case of higher data consistency, consider the last item, because it involves the table lock, so it will have a great impact on the efficiency of the program operation.

In addition, the default value of database driven transactions in Oracle is Transaction_none, that is, transactional operations are not supported, so you need to set them up manually in your program. In short, JDBC provides a relatively complete support for database transaction operations, and transaction operations can improve the efficiency of the program and maintain data consistency.

4, the characteristics of the transaction (ACID)

(1) atomicity (atomicity)
Atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.

(2) Consistency (consistency)
The integrity of the data before and after the transaction must be consistent.

(3) Isolation (isolation)
The isolation of transactions means that when multiple users access the database concurrently, the transaction of one user cannot be interfered by other users ' transactions, and the data between multiple concurrent transactions is isolated from each other.

(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.

5, the isolation level of the transaction

(1) When multiple threads Open data in their transactional operations database, the database system is responsible for isolating operations to ensure the accuracy of each thread in obtaining data.

(2) If isolation is not considered, the following problems may arise:

Dirty Read (dirty reads)

One transaction reads the data that is written by another uncommitted parallel transaction.

Non-repeatable read (non-repeatable reads)

A transaction re-reads the previously read data and discovers that the data has been modified by another committed transaction.

Phantom Read (Phantom Read)

A transaction re-executes a query that returns a set of rows that match the query criteria and finds that the rows have changed because of other recently committed transactions.

6, the isolation of the transaction

(1) 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 while name= ' B ';

2.update account set money=money-100 while 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.

A 1000

B 1000

A:

Start transaction;

Update account set money=money-100 where name= ' a ';

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

B:

Start transaction;

SELECT * from account where name= ' B ';

A:

Rollback

B:

Strat transaction;

SELECT * from account where name= ' B ';

(2) Non-repeatable reading:

Reading a row of data from a table within a transaction is different from reading the results multiple times.

For example, the bank wants to query a account balance, the first query a account is 200 yuan, at this time a to account deposited 100 yuan 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.

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.

A 1000

B 1000

Start transaction;

Select SUM (Money) from account; ----Total Deposit: 2000

Select COUNT (*) from account; Number of----General Ledger households: 2

-------------------------

B:

Start transaction;

Update account Set money = money-1000 where name= ' B ';

Commit

-------------------------

Select AVG (Money) from account; ----Account Average amount: 500

(3) Virtual reading (phantom reading)

Refers to the data that is inserted into another transaction within a transaction, causing inconsistencies in the 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.

A 1000

B 1000

C 1000

Start transaction;

Select SUM (Money) from account; ----Total Deposit: 2000

-------------------------

C:

Start transaction;

Insert into account values (NULL, ' C ', 1000);

Commit

-------------------------

Select COUNT (*) from account; Number of----General Ledger households: 3

7, transaction isolation of the SET statement

The database has a total of four isolation levels defined:

Serializable: Can avoid dirty reading, non-repeatable reading, the occurrence of false reading. (serialization)

REPEATABLE READ: Can avoid dirty read, non-repeatable read situation occurs. (Repeatable Read) can not avoid false reading

Read Committed: Prevents dirty reads from occurring (Read Committed)

Read UNCOMMITTED: lowest level, none of the above is guaranteed. (Read not submitted)

Set [Global/session] Transaction ISOLATION LEVEL setting transaction isolation Levels

SELECT @ @tx_isolation Query the current transaction isolation level

Security: serializable>repeatable read>read committed>read Uncommitted

Efficiency: serializable<repeatable read<read committed<read Uncommitted

Generally speaking, the general application chooses repeatable read or read committed as the database isolation level to be used.

MySQL default database Isolation level is: repeatable-read

How do I query the isolation level of the current database? SELECT @ @tx_isolation;

How do I set the isolation level of the current database? Set [Global/session] transaction isolation level ...;

~ The isolation level set in this way only works for the current connection.

Set TRANSACTION isolation level READ UNCOMMITTED;

Set session transaction ISOLATION level READ UNCOMMITTED;

~ The isolation level set in this way is to set the default isolation level for the database

Set global transaction ISOLATION level READ UNCOMMITTED;

8, transaction loss update problem (lost update)

(1) Two or more transactions update the same row, but the transactions do not know about the modifications made by other transactions, so the second change overrides the first modification

(2) Shared locks: shared and shared locks can coexist. Shared and exclusive locks cannot coexist. The next transaction at the Serializable isolation level queries the operation with a shared lock.

(3) Exclusive lock: Exclusive lock and all locks cannot coexist. An exclusive lock is added regardless of the isolation level when the Add or delete operation is performed

(4). The database is designed to be serializable isolation level, which prevents update loss issues.

Optimistic and pessimistic locks are not the real locks in the database, they are two solutions for how we use shared and exclusive locks to solve the problem of update loss, reflecting the attitude of people to the business:

Pessimistic lock: Pessimistic think that in most cases, there will be an update loss problem.

Each time a query is made, an exclusive lock is added manually.

SELECT * FROM table lock in share mode (read lock, shared lock)

SELECT * FROM table for update (write lock, exclusive lock)

Optimistic Lock: Optimistic that most of the cases will not have the problem of update loss. Through the timestamp field,

Design a version field in the table version, which is incremented each time a modification is made to the data in the database.

(5) If my program changes relatively few query more: optimistic lock

If my program query is less modified than the more: pessimistic lock

JDBC Transaction control Management