JDBC Transaction control

JDBC Transaction control
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, the transfer, one side more, the other side of the implementation of less.
MySQL database default transaction is automatically committed, that is, an SQL statement is a separate transaction, is automatically committed. The Oracle database default is that transactions are not automatically committed, and no one needs to commit the transaction manually, otherwise the default is the same transaction.
Transaction commands are required for multiple SQL to be placed in the same transaction
Start transaction Open transaction (all additions and deletions are made in the temp table)
Rollback ROLLBACK TRANSACTION (Cancel operation)
Commit COMMIT TRANSACTION (Confirm action)
Executing SQL in transaction management, using a temporary table in the database to save, the real record in the database is not changed when no commit, other users cannot be viewed. And only DML statements in the SQL language can be managed by transactions, that is, delete,insert,update statements can use transactions.

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 and have multiple SQL execute in a single transaction, use the following statement: JDBC Control TRANSACTION Statement Connection.setautocommit (FALSE); The equivalent of the start TRANSACTION,JDBC shutdown transaction autocommit is the new open transaction.
Connection.rollback (); Rollback
Connection.commit (); Commit
Transaction rollback Point
Saveponit
When transactions are particularly complex, there are situations where you do not need to roll back to the start state and you need to manually set the specified rollback point as the archive point.
SavePoint sp = Conn.setsavepoint ();
Conn.rollback (SP);
Conn.commit (); You must commit the action before the rollback point after the rollback

Transactional Properties
atomicity (atomicity) atomicity means that a transaction is an indivisible unit of work, that either the operations in the transaction occur or none of them occur.
The integrity of the data before and after consistency (consistency) transactions must be consistent. The isolation of
Isolation (isolation) transactions means that when multiple users access the database concurrently, the transaction of one user cannot be interfered by other users ' transactions, and data between multiple concurrent transactions is isolated from each other. Persistence (durability) persistence refers to the fact that once a transaction is committed, its changes to the data in the database are permanent, and the subsequent failure of the database should not have any effect on it
Enterprise development must ensure the atomicity of the transaction, But the complex problems that arise in transactions are caused by isolation

Isolation level of transactions
when multiple threads are opening data in their transactional operations database, the database system is responsible for isolating operations to ensure that each thread is accurate in obtaining data.
If the transaction does not consider isolation, it may cause the following problems
1, dirty read
refers to one transaction reading 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 finished and 2nd is not executed (A is not committed), if you query your account at this point, you will find yourself 100 dollars more. If A and B go back, B will lose 100 yuan. Dirty reads are issues that must be handled in a transaction
2, non-repeatable
reads a row of data from a table within a transaction, with different 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 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. The difference between the
and 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 committed by the previous transaction. 3. Virtual read
refers to the data that is inserted into a transaction that is read from another transaction, causing inconsistencies in the number of read and write records. The difference between non-repeatable reads is that non-repeatable reads are caused by an update to the data, which is caused by inserting the data into the insert.

Database to solve the problem of three types of isolation, provides the isolation level of the database (all databases are common)
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 virtual read MySQL default level Read Committed: Prevents dirty reads from occurring (Read committed) the default level of Oracle
Read UNCOMMITTED: lowest level, none of the above is guaranteed. (Read UNCOMMITTED) Harm of database isolation problem; Dirty read > non-repeatable read > virtual read
The higher the security level, the lower the efficiency, the less the harm.
Set TRANSACTION ISOLATION level sets transaction isolation levels
SELECT @ @tx_isolation Query the current transaction isolation level

Loss update issues for transactions
Essentially, a thread-safety issue occurs when 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 one.
Workaround
Pessimistic lock (Pessimistic Locking)
SELECT * FROM table lock in share mode (read lock, shared lock)
SELECT * FROM table for update (write lock, exclusive lock)
Optimistic lock (optimistic Locking)
Completed by the timestamp field.

Pessimistic lock principle: Use the lock mechanism inside the database to lock the table, when a changes the data, a will lock the data, B can not be modified, there will not be two transactions at the same time modification, pessimistic lock at the time of setting the assumption that the loss of data operations will certainly occur, in order to prevent the locking table to occur.
MySQL by default, when data is modified, the read lock in the pessimistic lock (in the transaction) is automatically locked for the data----readable but not modifiable in the current transaction prevents two transactions from modifying the data at the same time.
Transactions and locks are present at the same time. The lock is automatically freed when the transaction is closed.
Read locks can be nested, that is, a table can be locked multiple times, according to the lock of the order to determine the specific read and write permissions. Note that when a deadlock occurs, when all transactions are in the waiting state, the last lock that occurs in the deadlock state is automatically unlocked. Shared locks are very prone to deadlocks.
Write lock a table can only add an exclusive lock, and the exclusive lock and other locks have mutual exclusion effect. When you add a lock to another object after it is added, you can only wait for the query state and wait until the previous lock is released before continuing. There is no problem with concurrent access data, so there is no update loss. Since MySQL is read-lock by default, statement directives that add an exclusive lock will automatically unlock the default read lock at the bottom. However, manual read locks must be manually released to unlock them.
So the problem of resolving data loss updates with pessimistic locks can be achieved by using exclusive locks in pessimistic locks, using exclusive locks in multiple transactions, and other transactions to continue execution when the first transaction completes the release lock.

Optimistic locking principle: the use of the database is not a lock mechanism, the second is a special marker field, determine the control field state and content, to know whether the data occurred concurrent access
Optimistic locking is the assumption that the update drop problem does not occur and is marked by the Timestamp event stamp field. When a transaction commits, the timestamp of the read is compared with the latest updatatime in the table, and if there is an inconsistency, the transaction submission fails, and if it is consistent, the transaction commits successfully and the Updatatime field is updated.
UpdateTime the timestamp statement as a column in the table. Automatically updates to the current time.

JDBC Transaction control