About Question enthuware.oce-jpad.v6.2.544 :

[alex]

Hi,

Please verify a last sentence about READ_COMMITTED.
From the question we don't understand what DB we use abd what kind of isolation level is used by DB. Default level for Oracle is READ_COMMITED. So I think there should be an explanation of case with Pessimistic lock. In this case we indeed block database row.

Thanks,
Alex

[admin]

In such situation where DB isolation level is not specified in the question, you need to assume all possibilities and select the answer.

[disznoperzselo]

READ_COMMITTED isolation level allows Non-Repeatable Reads phenomena. In the last paragraph we should use REPEATABLE_READ isolation level instead to achive the required consistent state. Can you confirm this or there is something else going on?

[himaiMinh]

Hi, in the explanation,

when Client 1 updates the Account with new balance, its old value of status is also written back to the database.

I assume the "old value of status" is referring to "outstanding" in step 1.

[__JJ__]

Also note that the outcome would be different if the Database isolation level is READ_COMMITTED. In this case, as soon as client 1 reads an Account entity within a transaction, the database would lock the row.

I'm not sure how that can be true. Oracle uses READ COMMITTED and famously with Oracle, readers don't block writers and writers don't block readers.

[admin]

Locking the row does not imply locking the table and locking the row does not mean blocking readers. Take a look at this article from Oracle:
https://www.oracle.com/technetwork/test ... 82389.html
See the example that shows how READ COMMITTED works. It saysm "The transaction has not yet committed, so rows containing the information for accounts 123 and 987 are locked."

[__JJ__]

Locking the row does not imply locking the table and locking the row does not mean blocking readers. Take a look at this article from Oracle:
https://www.oracle.com/technetwork/test ... 82389.html
See the example that shows how READ COMMITTED works. It saysm "The transaction has not yet committed, so rows containing the information for accounts 123 and 987 are locked."

Yes but what they're saying there is that Oracle doesn't work in the same way that other databases do; other databases use read locks whereas Oracle doesn't; hence in Oracle readers don't block readers (whereas in other dbs they do), readers don't block writers, writers don't block readers; only writers block writers.

http://www.datadisk.co.uk/html_docs/oracle/locking.htm

Oracle uses a different locking method then most other databases, Oracle locking policy consists of the following:

Oracle locks data at the row level on modification only. There is no lock escalation to a block or table level, ever.
Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.
A writer of data does not block a reader of data.
A writer of data is blocked only when another writer of data has already locked the row it was going after.

[admin]

OK, I see your point. It should be REPEATABLE_READ.
thank you for your feedback!

[johnlong]

I think it is not possible to provide definitive answer, as transactional context of operations is not specificed.

What if Client 2 commits at step 2, and Client 1 starts new transaction at step 3.
In this case Answer 2 would be correct : After 3, the status of the account instance will be "normal".