Home>Question Details


Robert -- Thanks for the question regarding "Business Rules - Triggers, Foreign Keys or Stored Procedures", version 10.2.0

Submitted on 23-Dec-2007 5:12 Central time zone
Tom's latest followup | Bookmark | Bottom
Last updated 15-Feb-2008 8:09

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 we 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.
Reviews    
5 stars Serializable or For Update   December 27, 2007 - 7am Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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


Followup   December 27, 2007 - 9am Central time zone:

... 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
2 stars Without an index...   December 27, 2007 - 6pm Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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,


Followup   December 28, 2007 - 3pm Central time zone:

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
5 stars Code for locks? Against an Oracle databasse?   December 27, 2007 - 9pm Central time zone
Bookmark | Bottom | Top
Reviewer: Chuck Jolley from OKC, OK USA
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.


Followup   December 28, 2007 - 3pm Central time zone:

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

search this site for 'lost update' for more discussion
4 stars To the above poster...   December 27, 2007 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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.


Followup   December 28, 2007 - 3pm Central time zone:

...
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!)
5 stars To the above poster (2)   December 27, 2007 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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.


Followup   December 28, 2007 - 3pm Central time zone:

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.
5 stars Taking the piss?   December 27, 2007 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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.


4 stars Possible to edit your comments before a followup has been posted?   December 28, 2007 - 1am Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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?


Followup   December 28, 2007 - 3pm Central time zone:

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

and I don't want to make you "login"
5 stars Not Kidding   December 28, 2007 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Chuck Jolley from OKC, OK USA
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.


Followup   December 28, 2007 - 3pm Central time zone:

well, truth be told, I sort of disagree - especially in a stateless web environment... Lost updates abound.
5 stars Stateless programming - Yech   December 28, 2007 - 4pm Central time zone
Bookmark | Bottom | Top
Reviewer: Chuck Jolley from OKC, OK USA
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.


Followup   January 1, 2008 - 5pm Central time zone:

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.
5 stars Editing the posts   February 14, 2008 - 3am Central time zone
Bookmark | Bottom | Top
Reviewer: Radhakrishna from Singapore
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

Followup   February 14, 2008 - 9am Central time zone:

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, ....."
5 stars   February 14, 2008 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader from UK
"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.


5 stars   February 15, 2008 - 4am Central time zone
Bookmark | Bottom | Top
Reviewer: Robert C 
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?


Followup   February 15, 2008 - 8am Central time zone:

or a period of time when I'm not traveling so much :)
5 stars Internet Cafe at the Airport   February 20, 2008 - 7am Central time zone
Bookmark | Bottom | Top
Reviewer: Robert 
Tom,

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




All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.

About Oracle | Legal Notices and Terms of Use | Privacy Statement