Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.3 Transactions (Transaction isolation level)

Sample Question


Choose one incorrect statement about PostgreSQL's transaction isolation level.

  1. You can specify the transaction isolation level by using 'SET TRANSACTION ISOLATION LEVEL xxx' at the start of the transaction.
  2. You can specify one of four transaction isolation levels: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, or READ UNCOMMITTED.
  3. The default transaction isolation level is specified in the configuration parameters; if not specified, REPEATABLE READ is the default.
  4. REPEATABLE READ is an isolation level that prevents non-repeatable reads and also eliminates phantom reads.
  5. READ UNCOMMITTED is an isolation level that allows reading of uncommitted data, but does not result in dirty reads.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

The transaction isolation level is defined by the SQL standard, and there are four types: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED. PostgreSQL supports all of them.

Each level has defined anomalies that should not occur. For example, READ COMMITTED should not read data that has not been COMMITTED, and REPEATABLE READ should return the same value no matter how many times the same data is read. However, it is not required that these anomalies occur. For instance, READ UNCOMMITTED allows reading of data that has not been COMMITTED, thus permitting dirty reads. However, this is not mandatory, and in PostgreSQL's implementation, specifying READ UNCOMMITTED does not result in dirty reads and behaves exactly the same as READ COMMITTED. REPEATABLE READ allows phantom reads, which can see data added or removed by other concurrent transactions, but PostgreSQL's REPEATABLE READ also prevents phantom reads.

The default transaction level can be set with the configuration parameter 'default_transaction_isolation', and the default is READ COMMITTED. If you want to use a different isolation level from the default, you can issue a SET TRANSACTION statement at the beginning of the transaction, such as 'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ'. Alternatively, you can specify the isolation level in the BEGIN (or START TRANSACTION) statement that initiates the transaction.

This is a question where you need to select the incorrect option, so the correct answer is C.