Archive for the ‘Oracle’ Category

Circular dependencies on Foreign Key constraints (Oracle)

Tuesday, November 9th, 2010

Sometimes it’s valid to have tables that reference one another. For example:

  • A photo table stores photos, each of these photos belongs in a folder
  • A folder table, but each folder has mandatory (not null) “cover” photo

So both tables reference one another, but how do you create the tables, and how do you insert data?

  • If you try and create either of the tables, the other would need to exist in order to create the table with the foreign key constraint. The solution is to firstly create both tables without the foreign key constraints and secondly “alter” the tables to add the foreign key constraints.
  • If you try and insert a photo, the folder would need to exist; if you try and insert a folder then its cover photo would already need to exist. The solution here is to create the foreign keys with “initially deferred deferrable“; then the foreign key constraints are checked at commit-time rather than insert-time. You can insert the rows, in any order, and only when you do a commit will the system check that the constraints are not violated.

A perfect explanation here:

“Ask Tom” Oracle Seminar in Prague

Tuesday, March 10th, 2009

Today and tomorrow I am at an Ask Tom Live Seminar in Prague. Tom Kyte works for Oracle and has written many books on Oracle. For me he’s a bit of a “star”, so when I heard he was talking in Europe, I had to come. (But is that wrong? Normal people are in to stars like Robbie Williams surely, not Oracle experts.)

Wow there is so much I don’t know about Oracle evidently. I mean the solutions that he is presenting concerning Oracle 11g, are solutions to problems in features I’ve never used, such as partitioning, etc.

Some fun things I learnt:

  • In Oracle 6 “sql plus” used to be offered as a for-cost extra, as it was considered so good! (Apparently there was a previous tool, and it was worse.) Sql Plus still doesn’t support e.g. the “up” arrow key to get the previous command.
  • In Oracle 3 “insert into x select from x” would be an infinite statement as it would constantly re-select then insert the data from x that it had just inserted. This was fixed in Oracle 4. 
  • Oracle 11 can create “virtual columns”, i.e. “create table x(a number, b as (a+1))”. You can then use these virtual columns for indexes (like functional indexes), foreign key constraints, etc. A statement like “select .. from x where a+1=?” will be rewritten as “where b=?” which will then use the index. Audience member asked “can I write an update statement to update b?”
  • You can do “select …. as of <date>” and if the data is still available in the rollback segment, it will be returned.
  • Adding a column is now always instantaneous, i.e. “alter table x add y number default 4″ will not alter the data in the table, but when a row is read which was created before the alter table statement happened, the column will be added with its default value. (But presumably altering the column’s default value then causes the long write?)
  • Apparently Oracle was the first RDBMS. Well that’s what Tom Kyte claims anyway.

P.S. I should have brought my Oracle One on One with me for him to autograph… OK that would have been ridiculous.

Interesting Oracle/MySQL locking difference

Wednesday, September 5th, 2007

I know the rules for Oracle row locking well. A row can be locked for write if one updates it, or if one “select for update”s it.

  1. create table a (x number); (and equivalent in MySQL for InnoDB)
  2. Session A: insert into a values (9);
  3. Session A: commit;
  4. Session A: start transaction (in MySQL)
  5. Session A: select * from a where x=9 for update;
  6. Session B: start transaction (in MySQL)
  7. Session B: select * from a where x=9 for update;
  8. Session B hangs, waiting for row-level lock to be release from the row by Session A
  9. Session A: update a set x=4;
  10. Session A: commit;
  11. At this point, Session B returns no rows. Lock has been released, and row no longer confirms to where, so is not returned.
  12. Session A: update a set x=5;

This is where the difference occurs:

  • Oracle returns the row to session A. The command “for update” in session B did not return any rows, and thus no rows were locked, and thus session A has nothing to wait on.
  • MySQL (version 4.1.18) blocks session A waiting on the transaction in session B to be ended. That means A requires a lock owned by B. But what is this lock? Is it a row-level lock on the row which was not selected? Some other type of lock?

The reason I tested this was it just occurred to me that in a “select for update” the where clause is applied twice:

  1. Firstly to determine which rows to return. A lock is requested for those rows, which can involve waiting until the lock is released if it is owned by some other transaction.
  2. After the lock has been acquired, the rows is checked again to see if it still confirms to the where clause. If not, it is not returned (although the session blocked waiting for it)

I suspect that both databases simply do step 1 involving waiting and acquiring the lock on the rows which initially conform to the where clause. And both apply the where in step 2, as in both cases no rows were actually returned despite the wait. But Oracle, upon noticing the row will not be returned, actually releases the lock as the command implies only rows returned will be locked. But MySQL just leaves the lock form step 1 hanging around, which is wrong.


Wednesday, March 28th, 2007

From our Oracle test instance:

1360965 rows created.
Elapsed: 00:01:17.90

That's 1¼ minutes to insert (and index) over 1¼ million rows.

And this is a very old test instance. I think the hardware was last updated 2-3 years ago.

That's pretty quick!