Mar 242014
 

The following update forms the basis of a DIY connection pool. It is intended to allocate an unused session to whoever requests it:

declare

  sess connection_pool.session_name%type;
  
begin
  
  update connection_pool
  set    in_use = 'Y',
         last_used = systimestamp
  where  rowid in (
      select rowid 
      from   (
          select rowid
          from   connection_pool
          where  in_use = 'N'
          order  by last_used
      )
      where  rownum = 1
  )
  returning session_name 
  into      sess;
  
  dbms_output.put_line ( sess );
  
end;
/

There’s a serious flaw with this approach however – can you spot it?

Answers in the comments!

  8 Responses to “Quiz: What’s wrong with this update?”

  1. restarts or minirollbacks by Tom Kyte

  2. Btw also you need aliases for rowids and tables

  3. Hi Chris,

    Is it the usage of rownum = 1? which could, violate the intention to use the first last_used (Order by Last_used).

    Regards,
    Suthan,

  4. There’s a serious flaw with this approach however – can you spot it?

    It doesn’t work as intended ?
    See:


    Session A > create table connection_pool(session_name varchar2(30) primary key, in_use char(1) not null check(in_use in ('Y', 'N')), last_used timestamp);

    Table created.

    Session A > insert into connection_pool select 'C'||level, 'N', null from dual connect by level commit;

    Commit complete.

    Session A > declare
    2
    3 sess connection_pool.session_name%type;
    4
    5 begin
    6
    7 update connection_pool
    8 set in_use = 'Y',
    9 last_used = systimestamp
    10 where rowid in (
    11 select rowid
    12 from (
    13 select rowid
    14 from connection_pool
    15 where in_use = 'N'
    16 order by last_used
    17 )
    18 where rownum = 1
    19 )
    20 returning session_name
    21 into sess;
    22
    23 dbms_output.put_line ( sess );
    24
    25 end;
    26 /
    C1

    PL/SQL procedure successfully completed.

    Then, in Session B

    Session B > declare
    2
    3 sess connection_pool.session_name%type;
    4
    5 begin
    6
    7 update connection_pool
    8 set in_use = 'Y',
    9 last_used = systimestamp
    10 where rowid in (
    11 select rowid
    12 from (
    13 select rowid
    14 from connection_pool
    15 where in_use = 'N'
    16 order by last_used
    17 )
    18 where rownum = 1
    19 )
    20 returning session_name
    21 into sess;
    22
    23 dbms_output.put_line ( sess );
    24
    25 end;
    26 /

    Note that Session B is blocked.

    Then, in Session A


    Session A > commit;

    Commit complete.

    and just an instant later, Session B gets

    C1

    PL/SQL procedure successfully completed.

    That was not intended, I assume ?

    I think it is called “lost update” or something like that.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>