Connection and Transaction

Connection Interface

A Connection is the session between your java program and database. SQL statements are executed and results are returned within the context of a connection.A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.

Using Transactions

A transaction is a set of one or more statements that is executed as a unit, so either all of the statements are executed, or none of the statements is executed.

Disabling Auto-Commit Mode

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed.The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection:

                                      con.setAutoCommit(false);

Committing Transactions

After the auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.This is demonstrated in the following code, where con is an active connection:

                                      con.commit(); 

Whenever the commit method is called (either automatically when auto-commit mode is enabled or explicitly when it is disabled), all changes resulting from statements in the transaction are made permanent

Setting and Rolling Back to Savepoints:

SAVEPOINT is a marker within a transaction that allows for a partial rollback.

The method Connection.setSavepoint, sets a Savepoint object within the current transaction.

                                Savepoint save1 = con.setSavepoint();

TheConnection.rollback method is overloaded to take a Savepoint argument.

The method checks if the new price is greater than the maximumPrice value. If so, the method rolls back the transaction with the following statement:

                                con.rollback(save1);

Consequently, when the method commits the transaction by calling the Connection.commit method, it will not commit any rows whose associated Savepoint has been rolled back; it will commit all the other updated rows.

Releasing Savepoints:

• The method Connection.releaseSavepoint takes a Savepoint object as a parameter and removes it from the current transaction.                                                           
                           con.releaseSavepoint(save1);  
• After a savepoint has been released, attempting to reference it in a rollback operation causes a SQLException to be thrown.
•  Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back.
• Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question. 

When to Call Method rollback

Calling the method rollback terminates a transaction and returns any values that were modified to their previous values. If you are trying to execute one or more statements in a transaction and get a SQLException, call the method rollback to end the transaction and start the transaction all over again. That is the only way to know what has been committed and what has not been committed. Catching a SQLException tells you that something is wrong, but it does not tell you what was or was not committed. Because you cannot count on the fact that nothing was committed, calling the method rollback is the only way to be certain.

Using Transactions to Preserve Data Integrity:

In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) After a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this lock would be to prevent a user from getting a dirty read, that is, reading a value before it is made permanent. (Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.)

How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules.

The interface Connection includes five values that represent the transaction isolation levels you can use in JDBC: 

Isolation Level	Transactions	Dirty Reads	Non-Repeatable Reads	Phantom Reads
TRANSACTION_NONE	Not supported	Not applicable	Not applicable	Not applicable
TRANSACTION_READ_COMMITTED	Supported	Prevented	Allowed	Allowed
TRANSACTION_READ_UNCOMMITTED	Supported	Allowed	Allowed	Allowed
TRANSACTION_REPEATABLE_READ	Supported	Prevented	Prevented	Allowed
TRANSACTION_SERIALIZABLE	Supported	Prevented	Prevented	Prevented

• TRANSACTION_READ_COMMITED:If the application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the good choice.
• TRANSACTION_REPEATABLE_READ:If the application needs to read a row exclusively till you finish your work, then TRANSACTION_REPEATABLE_READ is the best choice.
• TRANSACTION_SERIALIZABLE:If the application needs to control all of the transaction problems (dirty read, phantom read and unrepeatable read), you can choose TRANSACTION_SERIALIZABLE for maximum safety.
• TRANSACTION_NONE:If the application doesn’t have to deal with concurrent transactions, then the best choice is TRANSACTION_NONE to improve performance.
• TRANSACTION_READ_UNCOMMITED:If the application is searching for records from the database then you can easily choose TRANSACTION_READ_UNCOMMITED because you need not worry about other programs that are inserting records at the same time.

A non-repeatable read occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

A phantom read occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row. This row is referred to as a phantom.