Thanks for the question, Robert.

Asked: December 23, 2007 - 5:12 am UTC

Last updated: February 15, 2008 - 8:09 am UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I would like to ask for your advice on the most effective way, with respect to performance and management of code, to enforce the following example business constraint relating to relationships involving sub-types.

Consider the following example:

User (Email, CommonAttributesEtc, Type)

Among the types of User the system represented, were 'Account Manager' and 'Customer'. Now, an Account Manager was involved in other relationships in the system, but which only applied to the Account Manager type.

Suppose we have a rule that states an Account Manager can manage one or more geographical regions. Using the above schema, this new relationship can be created as follows:

If we were to implement the Account Manager type as its own sub-type table AccountManager, we could then create a foreign key constraint to this table from the location table such as:

create table locations (
locationName primary key,
otherColumns
AccountManagerID number,
constraint fk_AccountManager foreign key (AccountManagerID) references AccountManager(AccountManagerID).
)

If on the other hand, we were to use a single table to represent the sub-type, this simple foreign key method would not be available. I would like to know which alternative you would suggest:

1) Create a trigger to enforce integrity: I am firmly against this approach.

2) Maintain the logic in the various stored procedures that would affect the underlying tables. For example: Use select for update to check for existence of type 'Account Manager' in the User table, before inserting into Locations table.

3) Create a "Constant" type column in the locations table of type "Account Manager" using the same code convention as in the User table to identify an account manager, and enforce the contents of this column using a check constraint.

To declare the constraint using the check constraint with the column of constant value, you can do the following steps:

alter table locations
add column AccountManagerCode nvarchar(20) not null

alter table locations
add constraint AccountManagerCk (check AccountManagerCode = 'Account Manager')

The code value would obviously depend on how the code is stored in the User table.

This is my preferred approach at this moment, for I believe it is the easiest to maintain and the simplest to understand and implement. I also believe it has the following benefits over the other approaches named above:

- I only have to declare it once. If I were to use Stored Procedures, I would need to implement the same logic in all procedures.
- I don't need to address the issues that exists with triggers.
- All the benefits I get from RI are available for me automatically by the DBMS.
- We know that RI declared using Foreign Key constraints is much faster than the corresponding implementation using triggers or stored procedures.
- Finally, because only a single table is used to represent the collection of User types, we do not need to perform a join between the master and sub-type table to retrieve the Account Manager entity.

The only disadvantage that I can see to using this method, is that a constant column of values denoting the type discriminator column would need to be maintained in the table. Given that storage is cheap and I don't expect these situations to be the majority of relationships in a database schema, I feel this extra storage would be acceptable in realizing the benefits mentioned above.

I would like to hear your thoughts on this approach Tom.

Oh and just a question about using Triggers for integrity. In many of our table we use a trigger to maintain a summary value in a master table to represent the contents of the corresponding detail table. We can then use only the master record to enforce constraints using a check constraint, and also when we want to retrieve the total value for a given record, and avoid a scan of the child table. I have seen you mention an approach for updating the master table in many of your previous posts, and I would just like to clarify one point.

Does oracle provide the capability to update the master value by calculating the sum of the respective values that were inserted into the detail table, and adding this value to the current master value. This would only require one insert to the master table, rather than one for each row. I know in SQL Server you can use the inserted temp table to retrieve these values. I believe there is a sound reason not to use this approach, even in Oracle if indeed the capability to do so is available, but I just cannot think of one?

Regards,

Robert

and Tom said...

1) I'm not only against it, I can show it won't work unless you lock a table or a whole bunch of rows - and no one ever does.

Easiest way to get this point across: If you do not index a foreign key, and you delete from the parent table or modify the parent tables primary key - Oracle will take a full table lock on the child table. Now, if Oracle is compelled to do that - why wouldn't your application? You will SERIALIZE to death if you do integrity constraints 'correctly' in the application. You will not have data integrity (it is binary - you either have it or you do not, you do not have "95%" data integrity, you either have it - or not) if it does not.

2) not good enough, but closer than #1

3) bingo. But use a much much shorter code. And default it. And hide it (use a view) - the column never needs to be seen by the application

....
In many of our table we use a trigger to maintain a summary value in a master table to represent the contents of the corresponding detail table.
.......
materialized views do that without the need for code.

I did not understand your last paragraph.

Rating

(13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Serializable or For Update

Robert C, December 27, 2007 - 7:37 am UTC

Hi Tom,

Thanks for the great response. I'm a firm advocate of the third option and use it extensively in my designs, where necessary of course.

Would I be correct to say that in the case of options one and two mentioned above, the serializable isolation level would be insufficient to enforce these rules, and only the use of For Update can correctly ensure integrity.

Also, is it true that in Oracle, unlike SQL Server, if you update a subset of, or insert a new set of rows using a single column condition, and where no index exists on the column, Oracle will still only acquire a row level lock?

For example:

Session A

SQL> create table test (id number);
Table created.

SQL> insert into test values (1);
1 row created.

SQL> insert into test values (4);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test for update;
        ID
----------
         1
         4

Using a new connection, Session 2, (SQL Plus Session 1 is still connected):

SQL> select * from test;

        ID
----------
         1
         4

If I were to try to update one of these two rows, I would be blocked by Session 1 as expected. However, my initial thoughts were that I would still be blocked if I were to attempt to insert rows into this table, as there are no indexes present with which Oracle can use to acquire a key range lock. It appears though, that I can indeed continue to insert new rows from session 2:

SQL> insert into test values (2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;
        ID
----------
         1
         4
         2

We now continue in Session 1:

SQL> commit;
Commit complete.

SQL> select * from test;
        ID
----------
         1
         4
         2

Does Oracle lock at the row level in all such situations. I ask because I've come from using SQL Server for a few years, where the following situation does occur:

SQL Server

Session 1:

// New table, no rows, and no other connections at this time

Insert into test values (1);

Session 2:

Insert into test values (2);

The result would be Session 2 is blocked by Session 1.

Thanks Tom

December 27, 2007 - 9:48 am UTC

... Would I be correct to say that in the case of options one and two mentioned
above, the serializable isolation level would be insufficient to enforce these
rules, and only the use of For Update can correctly ensure integrity. ...

correct that serializable is insufficient.

more correct to say that LOCK TABLE is typically called for.

we are not sql server, we do not suffer from their locking issues.

You would only be blocked from inserting in the even of a duplicate UNIQUE value.

eg:

create table t (x int primary key, y int );

session 1: insert into t values ( 1, 1 );
session 2: insert into t values ( 2, 2 ); <<=== no problem
session 2: insert into t values ( 1, 1 ); <<=== this'll block on session 1 tho

Without an index...

Robert C, December 27, 2007 - 6:25 pm UTC

I had always understood Oracle (SQL Server also behaves in this way) to allow concurrent inserts into a table if an index is available. I'm curious to whether this is possible without an index, as in my example above.

Without an Index

create table test (x int, y int).

SQL Server

Session1: Insert test values (1,1)
Session2: Insert test values (2, 2) // That'll be blocked by

Oracle (From my example above)

Session1: Insert into test values (1, 1)
Session2: Insert into test values (2, 2) // No Issue

Witho an Index

create table test (x int primary key , y int).

SQL Server

Session1: Insert test values (1,1)
Session2: Insert test values (2, 2) // This will succeed

Oracle (From my example above)

Session1: Insert into test values (1, 1)
Session2: Insert into test values (2, 2) // This will succeed

I'd just like to ensure that I understand how Oracle will behave in this situation when there is no index available. To relate my question to a real world example, in my previous role there was the requirement to lock a range of values (from a foreign key column) while some calculations were being performed. The range (not rows) had to be locked as there was the possibility that the transaction would insert new rows into the range based on the results of the calculations.

Eg. SQL Server

create table myTable (x int primary key, y int foreign key myLookup (id))

insert into myLookUp values (1)
insert into myLookUp values (2)

insert into myTable values (1, 1)
insert into myTable values (2, 1)
insert into myTable values (3, 2)
insert into myTable values (4, 2)

Now, in Session One we wish to lock the range of rows (and prevent any inserts of new rows) for the foreign key values of 1 for column Y in myTable. In SQL Server can accomplish this using the serializable transaction level:

Session1: set transaction isolation level serializable
Session1: select * from myTable where y = 1
Session2: insert into myTable values (5, 2) // Insert succeeds
Session2: Insert into myTable values (5, 1) // Blocked by the range lock held by Session1.
Session1: Performs various calculations on myTable and then decides to insert the following row.
Session1: Insert into myTable values (5, 1) // Succeeds.

In Oracle, how can I achieve this same type of lock? Ie, only lock on the range of 1 for the y column. In SQL Server, if I were to remove the unique index from myLookUp, then in the above scenario, SQL Server would lock the entire myTable table.

Thanks,

December 28, 2007 - 3:34 pm UTC

regardless of an index - oracle has pretty much always allowed for concurrent insert

the only time this would not be true is when you have a UNIQUE constraint (primary key, unique index) in place - then two sessions might serialize upon attempting to insert the same value.

and in sqlserver, I believe back in the days of page level locking, you needed a clustered index to sometimes permit concurrent inserts. To spread the data around so the inserts did not go after the same block

Code for locks? Against an Oracle databasse?

Chuck Jolley, December 27, 2007 - 9:20 pm UTC

To quote Yoda: You must unlearn what you have learned.

I have been programming against Oracle for 9 years and in that time I have not coded one single lock, not even a select for update.
Not one single time in hundreds of thousands of lines of code.
There is about a 99.99% chance you can simply forget about locks.
Unless you have some awfully bizarre business rules this will simply not be an issue.
I'm sure Tom will give you examples and such, but really, the table will look to each session just the way you would intuitively expect it to.

December 28, 2007 - 3:35 pm UTC

you have to think about 'locks' when you have lost update possibilities.

search this site for 'lost update' for more discussion

To the above poster...

Robert C, December 27, 2007 - 10:01 pm UTC

To the above poster: Of course I'm not against using a database for locking. The essence of my message was to correctly understand the locking behaviour of the Oracle database in certain situations to determine whether or not explicit locking is necessary.

Coming from an SQL Server background and with a solid understanding of database theory, I merely wish to ensure that my understanding of Oracle's implementation of database transactions is correct. With such an understand, I will be able to ensure that my decisions are correct and are based on fact rather than the risk of misunderstanding.

As you will know, Oracle writers are not blocked by readers, as is the case with SQL Server. Hence, transactions need to be developed differently to address this difference. It is this very difference that I'm trying to clarify here with my questions.

With respect to your claim that the select for update clause is seldom used in reality, I will have to strongly disagree with you here, based on common sense and experience. In the case of the former, I find it impossible to comprehend that the select for update clause is used as the exception. If you expect two rows to modify the same subset of rows, the select for update clause should be used.

Consider the overlapping date problem rule, or any such transactions that requires an insert to be based wholly on the contents of the set to which the insert will be made. If the select for update clause were not to be implemented in this case, the following could occur:

Session1: Select data to read (no overlap exists)
Session2: Select data to read (because session2 will not be blocked by other transactions, it also does not detect an overlap)
Session1: Proceeds to insert a row
Session2: Proceeds to insert a row

After both transactions have committed, we will find that the rule has, in fact, been violated. Now in order to avoid this situation, the select for update clause must be used.

For another example, please see my above question.

Of course as with any discussion, my claimed understanding may be incorrect, and it is for the presence of this possibility that I am asking these very questions.

December 28, 2007 - 3:37 pm UTC

...
Consider the overlapping date problem rule, or any such transactions that
requires an insert to be based wholly on the contents of the set to which the
insert will be made. If the select for update clause were not to be implemented
in this case, the following could occur:
...

ah - but - you cannot lock that which you cannot see. You need to either lock the table OR lock a row in a parent table for this overlapping date issue (this is the example I used in my last book expert oracle database architecture in fact!)

To the above poster (2)

Robert C, December 27, 2007 - 10:07 pm UTC

I also forgot to ask: Did you read Tom's reply to my first follow up, in which he quite clearly points out that explicit locking is in fact required to enforce certain rules.

I'm really struggling to understand your view here.

December 28, 2007 - 3:37 pm UTC

explicit locking is needed in many real world cases - be is pessimistic (select for update) or optimistic (use a timestamp, ora_rowscn or something similar)

yes.

Taking the piss?

Robert C, December 27, 2007 - 10:11 pm UTC

To the above poster:

You say, quote:

I have been programming against Oracle for 9 years and in that time I have not coded one single
lock, not even a select for update.
Not one single time in hundreds of thousands of lines of code.
There is about a 99.99% chance you can simply forget about locks.

end quote

Ah.. It has only just occurred to me that your above quote was said in jest. For to suggest that one can simply just "forget about locks" would be grossly irresponsible.

Possible to edit your comments before a followup has been posted?

Robert C, December 28, 2007 - 1:54 am UTC

Just a very important point I need to clarify, for I didn't explain it very clearly earlier. When I mentioned that SQL Server can lock a range of values, I actually meant to say that SQL Server can prevent an insert into a range of values if the query includes a predicate that specifies a foreign key value.

Apologies for the confusion.

Tom,

What is your view on extending the functionality of AskTom to enable users to make amendments to their follow ups to a question?

December 28, 2007 - 3:38 pm UTC

since I don't have you "login", it would not really be feasible.

and I don't want to make you "login"

Not Kidding

Chuck Jolley, December 28, 2007 - 2:17 pm UTC

I wasn't kidding, I have never coded an explicit lock against an Oracle database.

I didn't mean you could generically "forget about locks"
But if you handle your transactions properly locking will not be something you spend much time worrying about in an Oracle database.

December 28, 2007 - 3:51 pm UTC

well, truth be told, I sort of disagree - especially in a stateless web environment... Lost updates abound.

Stateless programming - Yech

Chuck Jolley, December 28, 2007 - 4:07 pm UTC

There is that.
We only have a couple of small web interfaces to our database and collisions are not in the cards there simply because of the business rules, ie there is nothing to collide.
So I haven't had to deal with that problem.

January 01, 2008 - 5:24 pm UTC

and if you use something like APEX - you don't have to - it does the optimistic concurrency control for you.

But I would say "stateless - not yech - but the way it is"

asktom is totally stateless from a database connection perspective, but we use APEX to manage and maintain the state for us.

Editing the posts

Radhakrishna, February 14, 2008 - 3:58 am UTC

since I don't have you "login", it would not really be feasible. 

and I don't want to make you "login"

Tom - First line is fine, but for the second line, any reason for not wanting to have a login?

Thanks
Radhakrishna

February 14, 2008 - 9:29 am UTC

because it would be ANNOYING for you

and it would bug the #@%#@%#@ out of me as people email me with "i cannot log in, i forgot my name, i forgot my password, ....."

A reader, February 14, 2008 - 10:21 am UTC

"because it would be ANNOYING for you
and it would bug the #@%#@%#@ out of me as people email me with "i cannot log in, i forgot my name, i forgot my password, ....."
.. I know it's extra work, but these tasks can be automated without your interference

I think what I would like to see is advance notice on the home page that 'Tom may accept new question on so & so date at this time for x minutes...' because many time we have new question for you but we end-up posting in one of your follow-up because of lack of access to new question sets.

Robert C, February 15, 2008 - 4:05 am UTC

Many years ago when I was young (I'm 25 now), I almost invariably found the ask a question process unavailable. Living in Brisbane at the time however, I did have the time differences against me.

Over Christmas and the New Year of 2007 / 2008 I found that the option to ask a question was frequently available. Perhaps you just need to visit the site during a festive period?

February 15, 2008 - 8:09 am UTC

or a period of time when I'm not traveling so much :)

Internet Cafe at the Airport

Robert, February 20, 2008 - 7:53 am UTC

Tom,

Could you not log onto AskTom from one of the Internet Cafes at the Airport?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Serializable or For Update

Without an index...

Code for locks? Against an Oracle databasse?

To the above poster...

To the above poster (2)

Taking the piss?

Possible to edit your comments before a followup has been posted?

Not Kidding

Stateless programming - Yech

Editing the posts

Internet Cafe at the Airport

More to Explore

PL/SQL demos

PL/SQL docs

Business Rules - Triggers, Foreign Keys or Stored Procedures

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Serializable or For Update

Without an index...

Code for locks? Against an Oracle databasse?

To the above poster...

To the above poster (2)

Taking the piss?

Possible to edit your comments before a followup has been posted?

Not Kidding

Stateless programming - Yech

Editing the posts

Internet Cafe at the Airport

More to Explore

PL/SQL demos

PL/SQL docs