native barcode generator for crystal reports free download READ UNCOMMITTED in Objective-C

Generator Data Matrix ECC200 in Objective-C READ UNCOMMITTED

The READ UNCOMMITTED isolation level allows dirty reads. Oracle does not make use of dirty reads, nor does it even allow for them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standardsbased definition that caters for non-blocking reads. As we have seen, Oracle provides for non-blocking reads by default. You would be hard-pressed to make a SELECT query block in the database (as noted earlier, there is the special case of a distributed transaction). Every single query, be it a SELECT, INSERT, UPDATE, MERGE, or DELETE, executes in a read-consistent fashion. It might seem funny to refer to an UPDATE statement as a query, but it is. UPDATE statements have two components: a read component as defined by the WHERE clause and a write component as defined by the SET clause. UPDATE statements read and write to the database; all DML statements have this ability. The case of a single row INSERT using the VALUES clause is the only exception, as such statements have no read component, just the write component. In 1 Developing Successful Oracle Applications , Oracle s method of obtaining read consistency was demonstrated by way of a simple single table query that retrieved rows that were deleted after the cursor was opened. We re now going to explore a real-world example to see what happens in Oracle using multi-versioning, as well as what happens in any number of other databases. Let s start with the same basic table and query: create table accounts ( account_number number primary key, account_balance number not null ); select sum(account_balance) from accounts; Before the query begins, assume we have the data shown in Table 7-2. Table 7-2. ACCOUNTS Table Before Modifications

1 2 ... 342,023

123 456 ... 987

Note I do not mean to imply that rows have any sort of physical ordering on disk in this example. There really

is not a first row, second row, or last row in a table. There is just a set of tables. We are assuming here that row 1 really means the first row we happened to read and row 2 is the second row we happened to read and so on.

At some point while we are in the middle of the query, a transaction moves $400.00 from account 123 to account 987. This transaction does the two updates but does not commit. The table now looks as shown in Table 7-3. Table 7-3. ACCOUNTS Table During Modifications

1 2 ... 342,023

123 456 ... 987

So, two of those rows are locked. If anyone tried to update them, that user would be blocked. So far, the behavior we are seeing is more or less consistent across all databases. The difference will be in what happens when the query gets to the locked data. When the query we are executing gets to the block containing the locked row (row 342,023) at the bottom of the table, it will notice that the data in it has changed since the time at which it started execution. To provide a consistent (correct) answer, Oracle will at this point create a copy of the block containing this row as it existed when the query began. That is, it will read a value of $100.00, the value that existed at the time the query began. Effectively, Oracle takes a detour around the modified data; it reads around it, reconstructing it from the undo (also known as a rollback) segment (discussed in detail in 9 Redo and Undo ). A consistent and correct answer comes back without waiting for the transaction to commit. Now, a database that allowed a dirty read would simply return the value it saw in account 987 at the time it read it, in this case $500.00. The query would count the transferred $400 twice. Therefore, not only does it return the wrong answer, but also it returns a total that never existed in the table at any committed point in time. In a multiuser database, a dirty read can be a dangerous feature and, personally, I have never seen the usefulness of it. Say that, rather than transferring, the transaction was actually just depositing $400.00 in account 987. The dirty read would count the $400.00 and get the right answer, wouldn t it Well, suppose the uncommitted transaction was rolled back. We have just counted $400.00 that was never actually in the database. The point here is that dirty read is not a feature; rather, it is a liability. In Oracle, it is just not needed. You get all of the advantages of a dirty read (no blocking) without any of the incorrect results.