Update tabeli
1. The client modifies the employee name on the screen. The client process sends a
SQL UPDATE statement over the network to the server process.
2. The server process obtains a System Change Number and reads the data block
containing the target row.
3. The server records row lock information in the data block.
4. The server writes the old image of the data to the redo buffers in memory, and
then writes the changes to a rollback segment and modifies the employee data,
which includes writing the SCN to the ORA_ROWSCN pseudocolumn in Oracle
Database 10g or newer database releases.
5. The server process writes the redo buffers to disk, and then writes the rollback
segments and the changed data to disk. The rollback segment changes are part of
the redo, since the redo log stores all changes coming from the transaction.
6. The HR clerk commits the transaction.
7. Log Writer (LGWR) writes the redo information for the entire transaction,
including the SCN that marks the time the transaction was committed, from the
redo log buffer to the current redo log file on disk. When the operating system
confirms that the write to the redo log file has successfully completed, the transaction
is considered committed.
8. The server process sends a message to the client confirming the commit.
A confilicting write operation
1. Client A modifies the employee name on the screen. Client A sends a SQL
UPDATE statement over the network to the server process.
2. The server process obtains an SCN for the statement and reads the data block
containing the target row.
3. The server records row lock information in the data block.
4. The server process writes the changes to the redo log buffer.
5. The server process copies the old image of the employee data about to be
changed to a rollback segment. Once the server process has completed this
work, the process modifies the employee data, which includes writing the SCN
to the ORA_ROWSCN pseudocolumn in Oracle Database 10g or newer database
releases.
6. Client B modifies the employee name on the screen and sends a SQL UPDATE
statement to the server.
7. The server process obtains an SCN and reads the data block containing the target
row.
8. The server process sees that there is a lock on the target row from the information
in the header of the data block, so it takes one of two actions. If the
isolation level on Client B’s transaction is READ COMMITTED, the server
process waits for the blocking transaction to complete. If the isolation level for
Client B’s transaction is SERIALIZABLE, an error is returned to the client.
9. Client A commits the transaction, the server process takes the appropriate
action, and the server sends a message to Client A confirming the commit.
10. If Client B executed the SQL statement with the READ COMMITTED isolation
level, the SQL statement then proceeds through its normal operation.
Read and write operation.
1. Client A sends a SQL SELECT statement over the network to the server process.
2. The server process obtains an SCN for the statement and begins to read the
requested data for the query. For each data block that it reads, it compares the
SCN of the SELECT statement with the SCNs for any transactions for the relevant
rows of the data block. If the server finds a transaction with a later SCN
than the current SELECT statement, the server process uses data in the rollback
segments to create a “consistent read” version of the data block, current as of the
time the SELECT was issued. This is what provides the multiversion read consistency
(MVRC) and avoids the need for Oracle to use read locks on data. If a row
has been updated since the transaction started, Oracle simply gets the earlier version
of the data for a consistent view.
3. Client B sends a SQL UPDATE statement for a row in the EMP table that has
not yet been read by Client A’s SELECT statement. The server process gets an
SCN for the statement and begins the operation.
4. Client B commits his changes. The server process completes the operation,
which includes recording information in the data block that contained the modified
row that allows Oracle to determine the SCN for the update transaction.
5. The server process for Client A’s read operation comes to the newly modified
block. It sees that the data block contains changes made by a transaction that
has an SCN that is later than the SCN of the SELECT statement. The server process
looks in the data block header, which has a pointer to the rollback segment
that contains the data as it existed when Client A’s transaction started. The rollback
segment uses the old version of the data to create a version of the block as
it existed when the SELECT statement started. Client A’s SELECT statement
reads the desired rows from this consistent version of the data block.
Oracle is one of the only databases on the market that doesn’t use read locks, Oracle will essentially always deliver the lowest amount of database contention.
A workspace is a way to isolate data from changes in the general database environment. Workspace Manager accomplishes this by creating workspace-specific versions of data. When youcreate a workspace, you essentially create a snapshot of the data in the workspace at a specific point in time. Further changes to that data from outside the workspace do not affect the view of the data in the workspace, and changes made to data within the workspace are not seen by users outside the workspace. Changes to data within a workspace are visible only to other workspace. Workspaces allow youto essentially create separate data environments for specialized usage.
The key to workspaces is the support of multiple versions of the same data. To use workspaces to version data in a table, youmu st first enable the table for versioning. Workspace Manager can version-enable one or more user tables in the database. users. The unit of versioning is a row. Versioned rows are stored in the same table as the original rows. Oracle implements workspaces by adding metadata to the rows of a table. The timestamp allows users in a workspace to go back to any point in time and view the database from the perspective of changes made in that workspace up to another point in time.
Workspace operations:
Rollback
Youcan roll back changes to a workspace to return the workspace to the point
in time when the workspace was created. Youcan also designate savepoints,
which allow you to roll back the changes in a workspace to a subsequent point
in time.
Refresh
Refreshing a workspace means bringing the data in a workspace into agreement
with the same data in the overall database.
Merge
A merge operation rolls changes made in a workspace into its parent workspace.
Transaction Processing
ACID properties of a transaction:
Atomic
The entire transaction succeeds or fails as a complete unit.
Consistent
A completed transaction leaves the affected data in a consistent or correct state.
Isolated
Each transaction executes in isolation and doesn’t affect the states of others.
Durable
The changes resulting from committed transactions are persistent.
OLTP
High transaction volumes and large user populations. Online transaction processing implies direct and conversational interaction between the transaction processing system and its users. (223)