REPEATABLE READ

This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be read by the transaction.

SERIALIZABLE

This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.

(B)What is difference between optimistic and pessimistic locking?

In pessimistic locking transaction locks the record as soon as it selects the row to update.

While in optimistic locking it is only locked when the actual updating takes place. Let’s try to understand the concept by the figure below.

Figure 3.9 : - Optimistic and Pessimistic locking

To update any record minimum three steps need to be executed as shown in figure above.

Step1 select the record which needs to be updated, Step2 perform the necessary operation on the record and Step3 update the record. In pessimistic locking we lock the record right from Step1 to Step3 that means as soon as you select the record lock it and keep it lock until you complete the operation. No other transaction can access the same until you release the lock. While in optimistic locking you do not lock the record for the complete cycle but only when you are actually updating the record. You can see from the figure above pessimistic locking have longer locking life time than optimistic so it has least amount of concurrency.

So in order a row is locked as soon as it is selected you can use the select query something as shown below.

SELECT column1 FROM Table1 WHERE column1=2 FOR UPDATE

The FOR UPDATE clause puts a lock on the row thus ensuring no other transaction can update the same until the transaction is completed.

In order to implement optimistic locking we do something like this in the SQL update.

UPDATE Table1 SET column1=3 WHERE column1=4 and column4='ss'

In the above query we are trying to update column1 from 4 to 3. So when we actually want to update, we just make a check before the update are the old values same or has some thing changed. This can be done by putting a comparison on the where clause of the update query. For instance in the above SQL update column1 had value 4 and column4 had value "ss" so we make a compare before update to ensure that nothing has changed.

If anything has changed the update will not happen thus ensuring safe concurrency.

(B) What are deadlocks?

Deadlocks occur when two transactions are each blocked waiting for the other transaction to complete. In order to get a clear understanding of how dead lock occurs lets look at the sequence of events in the below table.

Figure 3.10: - Dead lock in action

In the above table both transactions “A” and “B” are trying to update the customer table with there respective values. That means Transaction “A” wants to update customer name from “cust1” to “cust2” while transaction B wants to update it to “cust3”. Later when actually transaction A goes to update the values it finds himself in a blocking situation as Transaction B is trying to do the same. And on the other end Transaction B also goes in to blocking stage as Transaction A is trying to update the customer name. This situation is termed as dead lock.

So what happens in such kind of dead locks…..hmmm a good database design will help you get you out of this situation. If you planned your database locking well this will not be situation it will roll back one of the transactions and make road for other thus let you come out from the chicken and egg situation.

(A) How can we set transaction level through JDBC API?

We can set and get transaction level using two API’s of JDBC “setTransactionIsolation()”

and “getTransactionIsolation()”. You can see the code snippet for the declaration of both the API’s.

Figure 3.11 : - Setting transaction level using JDBC

Once we have reference to the “Connection” object we can use the

“setTransactionIsolation()” method to set the transaction level. You need to pass the appropriate transaction level constant to the method. For instance we have set the above connection to transaction level to “Read uncommitted”.

(I) Can you explain transaction control in JDBC?

Transaction control can be provided by Connection class of the JDBC driver. By default every SQL is treated as a transaction and the transaction is committed when the SQL statement is complete. When JDBC driver fires a SQL a new transaction is started and it’s committed when the SQL is completed. This is the default behavior for JDBC drivers.

But definitely you want to control the commit and roll back of the transaction. For that you need make the connection object's auto commit property as false. Below is the code snippet for the

same:-Connection conn =

DriverManager.getConnection("jdbc:oracle:oci8:@ora8idev","username","password");

conn.setAutoCommit (false);

After this you can either use conn.commit(); to commit or conn.rollback(); to rollback a transaction. Below table has list of common transaction related method and properties in the connection object.

Figure 3.12 : - Transaction properties and methods related to connection object

(A) What are Savepoints in a transaction?

JDBC 3.0 introduced a new concept called as "Savepoint".Using save points you can mark one or more places in transaction and some time later you can perform rollback to one of those "Savepoint". Below code snippet will give a clear idea of how save points can be used

.

Figure 3.13 : - Snippet code for Savepoint’s

In the above code snippet we have defined two save points Step1 and Step2. After that we can rollback to a particular save point using rollback. For instance step3 and step4 show how we can rollback to a particular save point. You can also rollback every thing using our traditional commit and rollback as shown in step5 and step6.

Note :- In order to define a savepoint we need to define “Savepoint” data type.