Briefly describe how Oracle Database implements serializable transaction isolation. Include an example

Question

What will be an ideal response?

Answer 1

Whenever a transaction begins, Oracle Database records a System Change Number (SCN), which is a database-wide value that is incremented every time a change is made to the database. As a transaction proceeds, for each record the transaction reads, Oracle retrieves the committed values that the record contained when its SCN was less than or equal to the SCN of the current transaction. This prevents any dirty reads, nonrepeatable reads, or phantom reads. As long as the transaction does not attempt to update or delete a record that has a committed change with an SCN greater than the SCN of the current transaction, the transaction will proceed normally. If the transaction does attempt to change a record with a greater SCN, then an error is produced that must be handled by the application program. For example, suppose a transaction begins when the SCN is 100. The transaction first reads a record with an SCN of 97. The transaction is allowed to proceed since the record's changes were committed prior to the start of the current transaction. The transaction next attempts to read a record with SCN 102. At this point the transaction will be cancelled since allowing it to proceed could violate serializability.