Hi, Any idea with respect to the answer for this question Two programs, ProgramA and ProgramB, access the same database table concurrently. Both programs connect to the database with a transaction isolation level of TRANSACTION_READ_COMMITTED. ProgramA begins a multiple row update but has not yet committed its changes. At the same time ProgramB selects a row that has been modified by ProgramA. What happens?
a) ProgramA receives an SQLException
b) ProgramB receives an SQLException
c) ProgramB reads the row and continues
d) ProgramB blocks until ProgramA commits or rolls back
Looks like the answers are either of c or d. If it is d however it is however very restrictive is not very scalable. lets say that in the scenario we introduce ProgramC ProgramD, ProgramE.... what happens. Everone is blocked waiting for ProgramA to complete. I think this is not proper and hence maybe only c is the answer
Answer A and B are not correct. No program receives an SQLException. That is the reason for feature of Isolation Levels where one transaction is isolated (unaware) of any updations by another transaction. That leaves out C and D. D would have been true if the isolation level was 'Serializable'. Hence c is correct. That is what I think. Please correct me if I am wrong.
The difference between program A and program B is that program A is modifying a record, and program B is merely reading a record. UNCOMMITTED READ is a dirty read - program B would be allowed to read data that was not yet committed by program A. If the question used UNCOMMITTED READ, the correct answer would have been C. COMMITTED READ ensures that program B will never read data that program A has changed but not committed (as in the IBM example above, correct answer D). However, COMMITTED READ does not prevent another program from changing data that program B has read. So if the two program were run in reverse order, program A would not have blocked, since program B was only reading. In this case, if program B read the same records a second time it could have gotten different data. REPEATABLE READ prevents other programs from reading records that you have accessed as part of your transaction. If the two programs were run in reverse order, program A would have blocked when it tried to access a record that was read in program B's transaction. However, program A could possibly insert additional records that might might be selected by program B's query. In this case, a second query by program B might provide additional records, or phantoms. SERIALIZABLE prevents phantoms, generally by locking the entire table (very expensive). Why don't they ask questions like this on Who wants to be a Millionaire or Weakest Link? danl