Skip to Main Content
  • Questions
  • locking with select for update vs. using single statement

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Dave.

Asked: January 08, 2014 - 5:54 pm UTC

Last updated: March 31, 2014 - 9:54 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

1)
Thanks for all the great work you do. By the way, when I interview people (I'm a software developer and interview people for such positions) I have recently added a standard interview question that I always include:
"Where do you put business logic/rules, in your application or in the database?"
I have yet to interview anyone that says "database". Then I ask, "What happens if I want to use a different application (other than yours) to access the data?" They look at me totally puzzled.
I subscribe to your approach of putting all the business logic you can in the DB, it makes life so much easier down the road and works so well. Sadly, this idea seems to be the minority view, as I constantly see answers like this (note the accepted answer and how many upvotes it got): http://stackoverflow.com/questions/1473624/business-logic-in-database-versus-code

Why do you think so many development people hate putting the logic in the DB?

2)
My main question though concerns your Expert Oracle book regarding the reservation system example from chapter one and how to use select for update to ensure proper concurrency.

You use the approach of SELECT FOR UPDATE to lock the resources record to ensure at most one check can be done against the schedules table for a given resource at a given point in time. Then you did the overlap check after that select. I just wondered if there was anything wrong with the approach of INSERT INTO...WHERE NOT EXISTS, and checking SQL%ROWCOUNT to make sure 1 record was inserted. In other words, we try to insert a record into schedules, but make sure there aren't any overlaps, and we do it using an insert statement. If SQL%ROWCOUNT is 1, we know the record was inserted successfully, otherwise, we know there was an overlap and can raise the error accordingly.

In the book, you say use a single SQL statement if at all possible, so this approach would fall into that category.

Here's a complete example (note that I used a little bit different date logic from what you used in the book, since the book approach would consider 8am-9am and 9am-10am overlaps, because you use a between operator which is inclusive; my approach would not consider those overlaps, but also note it requires a 3rd check so if somebody books the exact same time range we can catch it):

Is there anything I missed by doing it this way?

create table resources ( resource_name varchar2(25) primary key);
create table schedules( resource_name varchar2(25) references resources,
start_time date,
end_time date );

insert into resources values ('r1');

insert into schedules values ('r1',to_date('2014-01-01 12:00','YYYY-MM-DD HH24:MI'),
                              to_date('2014-01-01 13:00','YYYY-MM-DD HH24:MI')
                             );

create or replace procedure book_resource
(
   p_resource_name resources.resource_name%type
 , p_start_time    date
 , p_end_time      date
)
AS
BEGIN
   IF p_start_time >= p_end_time
   THEN
      raise_application_error(-20001,'p_end_time must be > p_start_time');
   END IF;
   
   INSERT INTO schedules
             ( resource_name
             , start_time
             , end_time )
        SELECT p_resource_name
             , p_start_time
             , p_end_time
          FROM dual
         WHERE NOT EXISTS
               (SELECT *
                  FROM schedules s
                 WHERE s.resource_name = p_resource_name
                   AND ( (p_start_time > s.start_time AND p_start_time < s.end_time)
                    OR   (p_end_time > s.start_time AND p_end_time < s.end_time)
                    OR   (p_start_time = s.start_time AND p_end_time = s.end_time) )
               );
   -- if a row wasn't inserted, we know the resource was already booked for this timeframe
   IF SQL%ROWCOUNT != 1
   THEN
      raise_application_error(-20001,'Sorry, this resource is already booked for the selected time range. Please try a different time range.');
   END IF;
END;
/

declare
begin
   /* this will fail because 12:00-12:30 overlaps with 12:00-13:00 */
   book_resource('r1',to_date('2014-01-01 12:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 12:30','YYYY-MM-DD HH24:MI'));
end;
/

declare
begin
   /* this will succeed because 15:00-16:00 doesn't overlap */
   book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
end;
/

declare
begin
   /* this will fail because 15:00-16:00 overlaps */
   book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
end;
/

commit;


Here's the output:
Table created.


Table created.


1 row created.


1 row created.


Procedure created.

declare
*
ERROR at line 1:
ORA-20001: Sorry, this resource is already booked for the selected time range.
Please try a different time range.
ORA-06512: at "SYSDC.BOOK_RESOURCE", line 33
ORA-06512: at line 4



PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-20001: Sorry, this resource is already booked for the selected time range.
Please try a different time range.
ORA-06512: at "SYSDC.BOOK_RESOURCE", line 33
ORA-06512: at line 4

and Tom said...

1) because they've been "taught" that "databases must be interchangeable", the programming language we use doesn't, the OS we use doesn't, the technology like SOAP or whatever doesn't - but that database - you had better be able to replace it at the drop of a hat.

One piece of the entire situation has to be disposable - every other bit and byte - not so.

Never mind the fact that an application dependent on the database lives and dies by the database. Never mind the fact that the database's are different (transactions run in different ways on different concurrency models). Never mind the fact that things are possible in one database that are not in another. Never mind the fact that many times we can do in a single command in the database what a developer might write 1,000's of lines of code for (and in many cases, the code is "wrong", doesn't work in a multi-user environment).

Because it is "common knowledge" among developers that databases are black boxes, they are hard, they are impediments.

Because they haven't looked at things really.


2) think about it.....

reads don't block writes
writes don't block reads
you cannot see my data (your where exists cannot see my data) until I commit.
I cannot see your data until you commit.
if we do something at the same time - we won't see each others work.


you used a single transaction, you cannot test multi-user issues with a single transaction.


here is my output using two different transactions (you can do this in two sqlplus sessions for example...)

ops$tkyte%ORA11GR2> declare
  2  begin
  3     /* this will succeed because 15:00-16:00 doesn't overlap */
  4     book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction; /* <================== a new transaction */
  3  begin
  4     /* this will fail because 15:00-16:00 overlaps */
  5     book_resource('r1',to_date('2014-01-01 15:00','YYYY-MM-DD HH24:MI'),to_date('2014-01-01 16:00','YYYY-MM-DD HH24:MI'));
  6     commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA11GR2> select * from schedules;

RESOURCE_NAME             START_TIME           END_TIME
------------------------- -------------------- --------------------
r1                        01-jan-2014 12:00:00 01-jan-2014 13:00:00
r1                        01-jan-2014 15:00:00 01-jan-2014 16:00:00
r1                        01-jan-2014 15:00:00 01-jan-2014 16:00:00




bummer....


this is why developers might actually have to understand how the database they are using works.... if they do not, they will create bogus data....



how many databases have you seen where the "application manages all of the rules" that have data integrity issues? For me, the hit ratio is about 100% - in 100% of the cases, there are integrity issues in the data. In many cases, the developers KNOW this and have actually written "data integrity checkers" that they run to find and attempt to fix the issue after the fact.


Rating

  (1 rating)

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

Comments

thanks, but another question on the book logic

Dave, March 28, 2014 - 1:29 pm UTC

Thanks for the helpful answer. Your insights are spot on. I especially liked this sentence:
"Because it is "common knowledge" among developers that databases are black boxes, they are hard, they are impediments. "

That's so true. They seem to hate the database and look at it as evil. My favorite quote of all time on this topic is this one from David Heinemeier Hansson, creator of Ruby on Rails:
"We took a pretty radical stand: Stored procedures and all things that make your database clever are evil," Hansson said. "If you tell a lot of IT shops that, they'll be majorly offended, because that's just the way they do things."  
source reference: http://news.cnet.com/Ruby-on-Rails-chases-simplicity-in-programming---page-2/2100-1007_3-5920169-2.html

All I know is that when I've been on projects where we use stored procedures, everybody wins. We have reusable code, we aren't limited by some tool or framework that writes a query in a stupid way, and we have the data logic in one place. I remember several years ago when we were porting an app from VB6/ASP to ASP.Net; luckily, a lot of the data logic was in stored procedures, so we were able to reuse all that existing code.

In short, the stored procedure approach just "works".

Along this same topic, one other question about the reservation query logic you use in the book, which is this:

  AND (start_time between p_start_time and p_end_time
   OR  end_time between p_start_time and p_end_time)

I know this will treat 12:00 to 13:00 and 13:00 to 14:00 as overlaps, but wouldn't it also have problems with these two date ranges:
12:00 - 13:00
12:15 - 12:45

Here's an example:

create table resources ( resource_name varchar2(25) primary key);
create table schedules( resource_name varchar2(25) references resources,
start_time date,
end_time date );

insert into resources values ('r1');

insert into schedules values ('r1',to_date('2014-01-01 12:00','YYYY-MM-DD 
HH24:MI'),
                              to_date('2014-01-01 13:00','YYYY-MM-DD HH24:MI')
                             );

create or replace procedure book_resource
(
   p_resource_name resources.resource_name%type
 , p_start_time    date
 , p_end_time      date
)
AS
BEGIN
   IF p_start_time >= p_end_time
   THEN
      raise_application_error(-20001,'p_end_time must be > p_start_time');
   END IF;
   
   INSERT INTO schedules
             ( resource_name
             , start_time
             , end_time )
        SELECT p_resource_name
             , p_start_time
             , p_end_time
          FROM dual
         WHERE NOT EXISTS
               (SELECT *
                  FROM schedules s
                 WHERE s.resource_name = p_resource_name
                   AND (start_time between p_start_time and p_end_time
                    OR  end_time between p_start_time and p_end_time)
               );
   -- if a row wasn't inserted, we know the resource was already booked for 
this timeframe
   IF SQL%ROWCOUNT != 1
   THEN
      raise_application_error(-20001,'Sorry, this resource is already booked 
for the selected time range. Please try a different time range.');
   END IF;
END;
/

declare
begin
   /* shouldn't this fail? */
   book_resource('r1',to_date('2014-01-01 12:15','YYYY-MM-DD 
HH24:MI'),to_date('2014-01-01 12:45','YYYY-MM-DD HH24:MI'));
end;
/


When I run this, the procedure is successful:

SQL> @temp

Table created.


Table created.


1 row created.


1 row created.


Procedure created.


PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select * from schedules;

RESOURCE_NAME             START_TIME           END_TIME
------------------------- -------------------- --------------------
r1                        01-jan-2014 12:00:00 01-jan-2014 13:00:00
r1                        01-jan-2014 12:15:00 01-jan-2014 12:45:00

Tom Kyte
March 31, 2014 - 9:54 am UTC

that was a bug in the logic from an early edition ;)

the corrected code in current editions is:

 14     INSERT INTO schedules
 15               ( resource_name
 16               , start_time
 17               , end_time )
 18          SELECT p_resource_name
 19               , p_start_time
 20               , p_end_time
 21            FROM dual
 22           WHERE NOT EXISTS
 23                 (SELECT *
 24                     from schedules
 25                    where resource_name = p_resource_name
 26                      and (start_time < p_end_time)
 27                          AND (end_time > p_start_time)
 28                 );

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library