PL/SQL question

From: David P. Baker <david_at_starmedia.net>
Date: 1998/02/06
Message-ID: <34DB76BA.AFB38B20@starmedia.net>

Hi.

We're trying to log data on visits to a page on our site, by country code, and we ran into a problem.

I have a table:

create table stats_cookie (
  country char(5) not null,
  lang char(3) not null,

  logtime date not null,
  host    varchar2(100),
  page    varchar2(100),
  num     number,

  unique (country, lang, logtime, host, page) );

and a procedure:

create or replace procedure log_cookie
(incountry stats_cookie.country%type,

 inlang    stats_cookie.lang%type,
 inhost    stats_cookie.host%type,
 inpage    stats_cookie.page%type,
 intime    stats_cookie.logtime%type := null

)
is
  thetime stats_cookie.logtime%type;
  thepage stats_cookie.page%type;
  cursor stat (curcountry stats_cookie.country%type,

	       curlang    stats_cookie.lang%type,
	       curtime    stats_cookie.logtime%type,
	       curhost    stats_cookie.host%type,
	       curpage    stats_cookie.page%type) is
    select num from stats_cookie
	where country = curcountry
	  and    lang = curlang
	  and logtime = curtime
	  and    host = curhost
	  and    page = curpage
	for update of num;

  thestat stat%rowtype;
begin
  if intime is null then
    select SYSDATE into thetime from DUAL; -- (A)   else
    thetime := intime;
  end if;

  thetime := TRUNC(thetime, 'HH'); -- (B)

  thepage := LTRIM(RTRIM(inpage, '/'), '/');

  open stat (incountry, inlang, thetime, inhost, thepage); -- (C)   fetch stat into thestat;

  if stat%found then                      -- (D)
    update stats_cookie set num = thestat.num + 1       -- (E)
	where current of stat;
  else                                    -- (F)

    thestat.num := 1;
    insert into stats_cookie (country, lang, logtime, host, page, num) -- (G)

        values (incountry, inlang, thetime, inhost, thepage, thestat.num);   end if;
  close stat;
  commit;
exception
  when dup_val_on_index then -- (H)
    log_cookie(incountry, inlang, inhost, inpage, intime); -- (I) end;

The logic is this: the procedure gets called with a particular country code, language code, host name, and page path. Time is left blank. The procedure sets thetime to the current date & time (A), truncated to the current hour
(B). It then opens the stat cursor (C) to select the row in the table
corresponding to that country code, language, host, page, and time. If there is such a row (D), it's num field is incremented by one (E).

If that row doesn't exist (F), a row is inserted, with num set to 1 (G).

But if another instance of the procedure creates the row after it was not found (D) but before this instance inserts (G), then the insert will fail with a dup_val_on_index exception (because country, lang, host, page, and logtime are a combined unique key). So I catch that exception (H) and rerun the function with the same arguments (including time) (I), because now that there is a row, the select (C) should return the row.

However, although the procedure worked during testing, when we made it live, with many concurrent hits, it apparently spawned an infinite number of recursive instances and crashed the server.

Now I have two questions. What did I do wrong? And separately, why doesn't Oracle (7.3.2 on Solaris) handle out-of-memory better, instead of running out of swap space and crashing hard?

The second question I don't know how to begin to answer.

For the first, I have some ideas, but I want confirmation, or an explanation, before I try again.

The only thing I can think of, at this point, is that I don't close the cursor until after the insert. This means that the cursor is still open if the exception is raised. And that means the cursor is still open when I call the procedure again. Then the procedure would attempt to open another cursor with the same select. At this point, though, the row must exist, since that's the only way we would have gotten to the exception block. So the select should succeed. But if the first cursor is open, might that be preventing the second select from succeeding? If so, the second select would fail, and the second insert would also fail again. But I would guess that the second select would *hang* in that case, not fail.

Anyway, I'm perplexed. If anyone can help, please do.

Thanks very much.

-- 
David P. Baker
Director of Engineering                                Voice: 212.548.9613
StarMedia Network, Inc.                                  Fax: 212.631.9100
http://www.starmedia.com/                       Email: david@starmedia.net

Received on Fri Feb 06 1998 - 00:00:00 CST