Expert Oracle Database Architecture

Thomas Kyte

Mentioned 3

Now in its second edition, this best-selling book by Tom Kyte of "Ask Tom" fame continues to bring you some of the best thinking on how to apply Oracle Database to produce scalable applications that perform well and deliver correct results. Tom has a simple philosophy: you can treat Oracle as a black box and just stick data into it or you can understand how it works and exploit it as a powerful computing environment. If you choose the latter, then you’ll find that there are few information management problems that you cannot solve quickly and elegantly. This fully revised second edition covers the developments up to Oracle Database 11g. Each feature is taught in a proof-by-example manner, not only discussing what it is, but also how it works, how to implement software using it, and the common pitfalls associated with it. Don’t treat Oracle Database as a black-box. Get this book. Get under the hood. Turbo-charge your career. Fully revised to cover Oracle Database 11g Proof-by-example approach: Let the evidence be your guide Dives deeply into Oracle Databases’s most powerful features What you’ll learn Develop an evidence-based approach to problem solving Manage transactions in highly concurrent environments Speed access to data through table and index design Manage files and memory structures for performance and reliability Scale up through partitioning and parallel processing Load and unload data to interface with external systems Think for yourself; don’t take Tom’s word for it! Who this book is for This book is aimed at Oracle Database administrators, at PL/SQL and Java developers writing code to be deployed inside the database, and at developers of external applications who use Oracle Database as a data store. It is the go-to book for those wishing to create efficient and scalable applications. Table of Contents Developing Successful Oracle Applications Architecture Overview Files Memory Structures Oracle Processes Locking and Latching Concurrency and Multi-versioning Transactions Redo and Undo Database Tables Indexes Datatypes Partitioning Parallel Execution Data Loading and Unloading Data Encryption

More on Amazon.com

Mentioned in questions and answers.

I have a method SaveApp() which will deactivate the existing records and insert a new one.

void SaveApp(int appID)
{
   begin transaction;
   update;
   insert;
   commit transaction;
}

Let's say in database table SalesApp, I have 2 records with appID equal to 123;

  1. record 1, appID 123, inactive
  2. record 2, appID 123, active

If I call this method SaveApp() in two threads at same time, the first transaction (let's call it T1) will update the existing two record while the second transaction (let's call it T2) wait.

after T1 finishes, there will be three records in this table now. however, somehow T2 is not aware of the newly inserted record, the update query in T2 only update the previous two records, and insert the forth one.

after these two method call, in database, we will now have 4 records, the 3rd and 4th one both are active which is wrong.

  1. record 1, appID 123, inactive
  2. record 2, appID 123, inactive
  3. record 3, appID 123, active
  4. record 4, appID 123, active

Do you know any solution can solve this problem? I have tried using isolation level serializable which does not work.

Thanks!

Do you have another table that holds one row per AppId, enforced via unique or primary key constraint? If so use select for update on the parent table to serialize access per AppId.

Create the tables:

session_1> create table parent (AppId number primary key);

Table created.

session_1> create table child (AppId number not null references Parent(AppId)
  2      , status varchar2(1) not null check (status in ('A', 'I'))
  3      , InsertedAt date not null)
  4  /

Table created.

Insert beginning values:

session_1> insert into Parent values (123);

1 row created.

session_1> insert into child values (123, 'I', sysdate);

1 row created.

session_1> insert into child values (123, 'A', sysdate);

1 row created.

session_1> commit;

Commit complete.

Begin the first transaction:

session_1> select AppId from Parent where AppId = 123 for update;

     APPID
----------
       123

session_1> update Child set Status = 'I' where AppId = 123 and Status = 'A';

1 row updated.

session_1> insert into child values (123, 'A', sysdate);

1 row created.

Prior to commit, in a second session, make sure we are only seeing the first rows:

session_2> select * from Child;

     APPID S INSERTEDAT
---------- - -------------------
       123 I 2010-08-16 18:07:17
       123 A 2010-08-16 18:07:23

Start the second transaction:

session_2> select AppId from Parent where AppId = 123 for update;

Session 2 is now blocked, waiting on session 1. And will not proceed. Commiting session 1 will unblock Session

session_1> commit;

Commit complete.

Session 2 we now see:

     APPID
----------
       123

Complete the second transaction:

session_2> update Child set Status = 'I' where AppId = 123 and Status = 'A';

1 row updated.

session_2> insert into child values (123, 'A', sysdate);

1 row created.

session_2> commit;

Commit complete.

session_2> select * from Child;

     APPID S INSERTEDAT
---------- - -------------------
       123 I 2010-08-16 18:07:17
       123 I 2010-08-16 18:07:23
       123 I 2010-08-16 18:08:08
       123 A 2010-08-16 18:13:51

EDIT Technique cribbed from Expert Oracle Database Architecture second edition by Thomas Kyte, pages 23-24. http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1430229462/ref=sr_1_2?ie=UTF8&s=books&qid=1282061675&sr=8-2

EDIT 2 I would also recommend implementing Patrick Merchand's answer to this question for a constraint that enforces the rule that an AppId can only have one active record. So the final solution would have two parts, this answer for how to do the updates in a way that gets what you want, and Patrick's to make sure that table conforms to the requirements to protect the integrity of the data.

Is it possible to see the DML (SQL Statement) that is being run that caused a trigger to be executed?

For example, inside an INSERT trigger I would like to get this:

"insert into myTable (name) values ('Fred')"

I read about ora_sql_txt(sql_text) in articles such as this but couldn't get it working - not sure if that is even leading me down the right path?

We are using Oracle 10.

Thank you in advance.

=========================

[EDITED] MORE DETAIL: We have the need to replicate an existing database (DB1) into a classified database (DB2) that is not accessible via the network. I need to keep these databases in sync. This is a one-way sync from (DB1) to (DB2), since (DB2) will contain additional tables and data that is not contained in the (DB1) system.

I have to determine a way to sync these databases without bringing them down (say, for a backup and restore) because it needs to stay live. So I thought that if I can store the actual DML being run (when data changes), I could "play-back" the DML on the new database to update it, just like someone was hand-entering it back in.

I can't bring over all the data because of the sheer size of it, and I can't just copy over the changed records because of FK constraints and the order in which I insert/update records. I figured that if I could "play-back" a log of what happened, using the exact SQL that changed the master, I could keep the databases in sync.

My current plan of attack was to keep a log of all records that were changed, inserted, and deleted and when I want to sync, the system generates DML to insert/update/delete those records. Then I just take the .SQL file to the classified system and run the script. The problem I'm running into are FKs. (Because when I generate the DML I only know what the current state of the data is, not it's path to get there - so ordering of statements is an issue). I guess I could disable all FK's, do the merge, then re-enable all FK's...

So - does my approach of storing the actual DML as-it-happens suck pondwater, or is there a better solution???

"does my approach of storing the actual DML as-it-happens suck pondwater?" Yes..

  1. Strict ordering of the DML on your DB1 does not really exist. Multiple processes, muiltiple cores, things essentially happening at the essentially the same time.

  2. And the DML, even when it happens sequentially doesn't act like it. Say the following two update statements run in seperate processes with seperate transactions, where the update in transaction 2 starts before transaction 1 commits:

     update table_a set col_a = 10 where col_b = 'A' -- transaction 1
     update table_a set col_c = 'Error' where col_a = 10 -- transaction 2
    

Since the changes made in the first transaction are not visibible to the second transaction, the rows changed by the second transaction will not include those of the first. But if you manage to capture the DML and replay it sequentially, transaction 1's changes will be visible, so transaction 2's changes will be different. (See pages 40 and 41 of Tom Kyte's Expert Oracle Database Architecture Second Edition.)

  1. Hopefully you are using bind variables, so the DML by itself wouldn't be meaningful: update table_a set col_a = :col_a where id = :id Now what? Ok, so you want the DML with it's variable bindings.

  2. Do you use sequences? If so, the next_val will not stay in synch between DB1 and DB2. (For example, instance failures can cause lost values, are both systems going to fail at the same time?) And if you are dealing with RAC, where the next_val varies depending on node, forget it.

I would start by investigating Oracle's replication.

Oracle is an Object-relational Database Management System (ORDBMS) created by Oracle Corporation.

Note: This tag is not for questions about other products owned by Oracle Corporation, such as Java and MySQL.

It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java.

When posting questions with the tag, please be sure to always include Oracle edition and version.

How to tag questions

  • about SQL in general should probably be tagged .
  • about PL/SQL in particular should probably be tagged as well as .
  • specific to a particular database version may be tagged ,, or , as appropriate, but they should also be tagged at least.
  • about the free version of Oracle, Oracle XE, may be tagged or , but should be tagged at least.
  • about Oracle HTMLDB or Application Express (any version) may be tagged , but should be tagged at least.
  • about Oracle Objects for OLE should be tagged to avoid confusion with standard provider methods of accessing Oracle data.
  • about other Oracle Corp. database products should not be tagged , rather use the more specific tag to the technology, e.g. .

Free version of Oracle database

Oracle provides a free version of the database called Oracle Database Express Edition.

Useful links

Book recommendations