Różności …

DRM (Database Resource Manager) works by leveraging consumer groups you’ve identified and enabling you to place limits on the amount of computer resources that can be used by that group. (Predicting resource utilization, Switching consumer groups, Limiting number of connections)

Multiuser Concurrency

Database systems must protect data integrity.

The transaction is the bedrock of data integrity in multiuser databases and the foundation of all concurrency schemes. A transaction is defined as a single indivisible piece of work that affects some data. Once a transaction is committed, the changes made by that transaction become permanent and are made visible to other transactions and other users.

Transactions always occur over time, although most transactions occur over a very short period of time. Since the changes made by a transaction aren’t official until the transaction is committed, each individual transaction must be isolated from the effects of other transactions. The mechanism used to enforce transaction isolation is the lock. A lock prevents users from modifying data.

Aby zapobiec możliwości nadpisania zmian jednej transakcji przez drugą równoczesną ale np. dłużej trwającą transakcję Oracle używa dwóch rodzajów blokad (locks):

An exclusive lock (inaczej write lock) is applied and held while changes are made to data in the course of a transaction and released when the transaction is ended by either a COMMIT or a ROLLBACK statement. A write lock can be held by only one user at a time, so only one user at a time can change that data.

A read lock (shared lock) can be held by any number of users who are merely reading the data, since the same piece of data can be shared among many readers. However, a read lock prevents a write lock from being placed on the data, as the write lock is an exclusive lock. Oracle uses read locks only when a SQL operation specifically requests them with the FOR UPDATE clause in a SELECT statement.

The interference caused by conflicting locks is called contention.

Serialization – serializable transactions appear as though they have been executed in a series of distinct, ordered transactions. When one transaction begins, it is isolated from any changes that occur to its data from subsequent transactions. To the user, a serializable transaction looks as though it has the exclusive use of the database for the duration of the transaction. Serializable transactions are predictable and reproducible, the two cardinal virtues of data integrity.

Multiversion Read Consistency MVRC – Multiversion read consistency guarantees that a user sees a consistent view of the data she requests. If another user changes the underlying data during the query execution, Oracle maintains a version of the data as it existed at the time the query began. If there were transactions underway but uncommitted at the time the query began, Oracle will ensure that the query ignores the changes made by those transactions. The data returned to the query will reflect all committed transactions at the time the query started.

This feature has two dramatic effects on the way queries impact the database. First, Oracle doesn’t place any locks on data for read operations. This means that a read operation will never block a write operation. A user gets a complete “snapshot” view of the data, accurate at the point in time that the query began.

Isolation levels - describe how a transaction will interact with other transactions and how it will be isolated from other transactions.

An application programmer can set an isolation level at the session level (ALTER SESSION) or transaction level (SET TRANSACTION).

READ COMMITTED – Enforces serialization at the statement level. This means that every statement will get a consistent view of the data as it existed at the start of that statement. However, since a transaction can contain more than one statement, it’s possible that nonrepeatable reads and phantom reads can occur within the context of the complete transaction. The READ COMMITTED isolation level is the default isolation level for Oracle.

SERIALIZABLE – Enforces serialization at the transaction level. This means that every statement within a transaction will get the same consistent view of the data as it existed at the start of the transaction.

READ ONLY – this level explicitly prohibits any write operations and provides an accurate view of all the data at the time the transaction began.

Because of their differing spans of control, these two isolation levels also react differently when they encounter a transaction that blocks their operation with an exclusive lock on a requested row. Once the lock has been released by the blocking transaction, an operation executing with the READ COMMITTED isolation level will simply retry the operation. Since this operation is concerned only with the state of data when the statement begins, this is a perfectly logical approach.

On the other hand, if the blocking transaction commits changes to the data, an operation executing with a SERIALIZABLE isolation level will return an error indicating that it cannot serialize operations. This error makes sense, because the blocking transaction will have changed the state of the data from the beginning of the SERIALIZABLE transaction, making it impossible to perform any more write operations on the changed rows. In this situation, an application programmer will have to add logic to his program to return to the start of the SERIALIZABLE transaction and begin it again.

Lost updates
The most common type of integrity problem occurs when two writers are both
changing the same piece of data, and one writer’s changes overwrite the other
writer’s changes. This is the problem that exclusive locks are designed to prevent.
Dirty reads
Occur when a database allows a transaction to read data that has been changed
by another transaction but hasn’t been committed yet. The changes made by the
transaction may be rolled back, so the data read may turn out to be incorrect.
Many databases allow dirty reads to avoid the contention caused by read locks.
Nonrepeatable reads
Occur as a result of changes made by another transaction. One transaction
makes a query based on a particular condition. After the data has been returned
to the first transaction, but before the first transaction is complete, another
transaction changes the data so that some of the previously retrieved data no
longer satisfies the selection condition. If the query were repeated in the same
transaction, it would return a different set of results, so any changes made on the
basis of the original results may no longer be valid. Data that was read once can
return different results if the data is read again later in the same transaction.
Phantom reads
Also occur as a result of changes made by another transaction. One transaction
makes a query based on a particular condition. After the data has been returned
to the first transaction, but before the first transaction is complete, another
transaction inserts into the database new rows that meet the selection criteria for
the first transaction. If the first SQL statement in a transaction returned the
number of rows that initially satisfied the selection criteria, and then performed
an action on the rows that satisfied the selection criteria later in the transaction,
the number of rows affected would be different from the initial number of rows
indicated, based on the inclusion of new phantom rows.

Oracle Concurrency Features - three features are used by Oracle to implement multiversion read consistency.

Rollback segments – are structures in the Oracle database that store “undo” information for transactions in case of rollback. When a transaction starts changing some data in a block, it first writes the old image of the data to a rollback segment. A rollback segment is different from a redo log. The redo log is used to log all transactions to the database and recover the database in the event of a system failure, while the rollback segment provides rollback for transactions and read consistency. Blocks of rollback segments are cached in the System Global Area just like blocks of tables and indexes.

To preserve the integrity of the data in the database and enforce any type of serialization, it is critical to keep track of the order in which actions were performed. Oracle uses the System Change Number as an absolute determinant of the order of transactions. Oracle uses the SCN information in the redo log to reproduce transactions in the original and correct order when applying redo. Oracle also uses the SCN to determine when to clean up information in rollback segments that are no longer needed.

Since Oracle Database 10g, there is a pseudocolumn on each row that
contains the SCN, ORA_ROWSCN. You can use this to quickly determine
if a row has been updated since it was retrieved by comparing
the value read from this pseudocolumn at the start of a transaction
with the value read from this pseudocolumn at the end of the
transaction.

Locks in data blocks – A database must have a way of determining if a particular row is locked. Most
databases keep a list of locks in memory, which are managed by a lock manager
process. Oracle keeps locks with an area of the actual block in which the row is
stored. A data block is the smallest amount of data that can be read from disk for
an Oracle database, so whenever the row is requested, the block is read, and the
lock is available within the block.

Nonescalating row locks – other databases will sometimes escalate locks to a higher level of granularity within the database (na całą tabelę). With Oracle, the
lock indicator is stored within the data block itself, so there is no increase in overhead for a lock manager when the number of locks increases. Consequently, there is never any need for Oracle to escalate a lock.

A lock manager called the Distributed Lock Manager (DLM) has historically been used with Oracle Parallel Server to track locks across multiple instances of Oracle. This is a completely different and separate locking scheme that doesn’t affect the way Oracle handles row locks. The DLM technology used in Oracle Parallel Server was improved and integrated into a core product in Oracle9i, Real Application Clusters. (213)